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).

Requirements

• 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.
• Create an ER diagram using Visio include the associative tables based on your analysis.
• Identify all foreign keys needed in the database design.

Step	Task	Points
1a	Primary key identified for each entity (four tables): patients, doctors, procedures and appointments	5
1b	Attributes identified for each entity (four tables): patients, doctors, procedures and appointments	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
Total		50