Unformatted Attachment Preview
Scenario (please see case study files that will be used to create database; these
are in additional attachments). Those attachments are appointments.xlsx,
Doctors.text, and Patients.xlsx
A small surgery center needs your help to create a database. The office manager has
identified the following types of data (entities): patients, doctors and appointments,
using the following business rules:
Patients can have many doctors and doctors can see many patients.
Doctors can have multiple appointments with patients (but only one patient at a time).
Remember that many-to-many relationships need to have associative tables between
them. That is, since the relationship represents a specific doctor and a specific patient
at a specific time, you need an entity that represents that instance (the appointment).
Otherwise, you won't be able to track individual appointments.
1. Please identify the attributes and primary keys needed for each of the entities.
2. Identify the data types you would use for each of the attributes.
3. Determine the relationships between the entities and find the one-to-many and manyto-many relationships.
4. Create an ER diagram using Visio include the associative tables based on your
5. Identify all foreign keys needed in the database design.
Please make sure the below deliverables are met in accordance with the above
1a. Primary key identified for each entity (three tables): patients, doctors, appointments
1b. Attributes identified for each entity (three tables): patients, doctors, appointments
2a. Identified data types
3a. Identify one-to-many relationships
3b. Identify many-to-many relationships
4a. Add foreign keys to tables as needed
5a. Create an ER diagram using Visio