Computer Science homework help. Scenario
A small surgery center needs your help to create a database. The office manager has identified the following types of data (entities): patients, doctors, procedures and appointments using the following business rules:
Patients can have many doctors and many doctors can see many patients.
Patients can have many procedures and many procedures are done on patients.
Doctors can have multiple appointments. (hint: these are many to many relationships, so associative tables need to be added)
- Please identify the attributes and primary keys needed for each of the entities.
- Identify the data types you would use for each of the attributes.
- Determine the relationships between the entities and find the one-to-many and many-to-many relationships.
- Use the ER matrix to determine the relationships.
- Create an ER diagram using Visio including the associative tables based on your analysis.
- Identify all foreign keys needed in the database design.
- Create the database and relationships in Access and populate the tables using the spreadsheets.
- To use the database as the main office application, what other entities and attributes might be needed?
Second, download a copy of the spreadsheet I’m attaching here. If you “mouse over” the cells with a “red comment flag”, you can read my comments about the various fields. You’ll see I added some additional tables, as well as some data. You can either manually type this in to your database, or give it a try to copy & paste some of the tables, and then “tweak them” to add captions, adjust field sizes, formats, etc.
Activities
Due Week 8
- Upload completed Visio Conceptual Design.
- Upload completed Access database file
- A 3-5 page APA paper discussing the following topics:
- Discuss Database impact on the workplace,
- Discuss database benefits when businesses use querie,
- Forms, and reports.
- Discuss at least two security concerns should be discussed with a proposed solution to mitigate the security issues.
The report portion of this includes
A 3-5 page APA paper discussing the following topics:
- Discuss Database impact on the workplace,
- Discuss database benefits when businesses use queries, Forms, and reports.
- Discuss at least two security concerns should be discussed with a proposed solution to mitigate the security issues.
Grading Rubric
| Step | Task | Points |
| 1 | Attributes | |
| 1a | Primary key identified for each entity (four tables) | 5 |
| 1b | Attributes identified for each entity (four tables) | 5 |
| 2 | Data Types | |
| 2a | Identified data types | 5 |
| 3 | Relationships | |
| 3a | Identify one-to-many relationships. | 5 |
| 3b | Identify many-to-many relationships. | 5 |
| 4 | Foreign Keys | |
| 4a | Add foreign keys to tables as needed. | 10 |
| 5 | ER Diagrams | |
| 5a | Create an ER diagram using Visio. | 15 |
| 6 | Create and Populate the Database | |
| 6a | Create the tables and relationships in Access. | 15 |
| 6b | Integrate the data from the spreadsheets | 20 |
| 6c | Create query 1 | 10 |
| 6d | Create query 2. | 10 |
| 6e | Create query 3. | 10 |
| 7 | Written report | |
| 7a | Discuss database benefits when businesses use queries, forms, and reports | 10 |
| 7b | Discuss at least two security concerns should be discussed with a proposed solution to mitigate the security issues. | 10 |
| 7c | Edit, spellcheck, and list references. | 5 |
| Total Points | 140 |
