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?
NOTE: The Case Study is an individual project.
A
ppointments
AppointmentI
D
ADAM
8
9
1
000
2
0
4
-Oct-19
J
E
NN910100
3
7
:00 A.M.
Hershey Rehab
C
linic
NELS101100
5
SMIT
6
789001
Hershey General Clinic
1
2
3
4
5
6
7
8
9
A
B
C
D
E
————————————————————————————————————-
| DoctorID | DoctorLastName | DoctorFirstName | DoctorSpecialty |
————————————————————————————————————-
| DOC001 | Johnson | Virginia | Family Practice |
————————————————————————————————————-
| DOC002 | Miller | Alexis | Pediatrics |
————————————————————————————————————-
| DOC003 | Stewart | Dave | Cardiovascular |
————————————————————————————————————-
| DOC004 | Young | Phillip | Rehabilitation |
————————————————————————————————————-
| DOC005 | Wentz | Carson | Neurology |
————————————————————————————————————-
Patients
PatientI
D
Name
Patient
C
ity
1
2
3
–
4
5
–
6
7
8
9
P
A
First
PA
PA
PA
1
2
3
4
5
6
7
8
9
A
B
C
D
E
Procedure
ProdcedureI
D
00
1
Hershey General
C
linic
3
0 minutes
00
2
None
00
4
None
00
5
None
30 minutes
1
2
3
4
5
6
7
8
9
A
B
C
D
E