A small surgery center needs your help to create 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).

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 many-to-many relationships.
4. Create n ER diagram using Visio include the associative tables based on your analysis.
5. Identify all foreign keys needed in the database design.
6. Create the database and relationships in Access and populate the tables using the spreadsheets (unless your Professor wants you to type the data into the tables).

| DoctorID I DoctorLastName | DoctorFirstName 1 Doctor Specialty | | DOC001 | Johnson | Virginia | Family Practice | DOC002 | Miller | Alexis | Pediatrics | DOC003 | Stewart | Dave | Cardiovascular 1 | DOC004 | Young | Phillip | Rehabilitation 1 | DOC005 | Wentz | Carson Neurology AppointmentID ADAM8910002 JENN9101003 JOHN7890001 NELS1011005 SMIT6789001 AppointmentDate AppointmentTime 04-Oct-19 12:00 P.M. 03-Oct-19 7:00 A.M. 02-Oct-19 10:00 A.M. 09-Oct-19 9:30 A.M. 01-Oct-19 9:00 A.M. Appointment Location Hershey Hospital Palmyra Family Practice Hershey Rehab Clinic Hershey General Clinic Hershey General Clinic PatientST PA PA PatientID 123-45-6789 234-56-7890 345-67-8910 456-78-9101 567-89-1011 PatientLastNamePatientFirstName Patient City Smith John Hershey Johnson Alex Palmyra Adams Jake Anville Jennings Mary Middletown Nelson Sue Cleona PatientStreet Main First Water Mill Second Patient PostalCode 17234 17078 17345 '17057 '17987 PA PA PA ProdcedurelD 001 002 003 004 ProcedureName Yearly Physical Immunization Shots Stress Test Physical Therapy Flu Vaccination Procedure Prep None None Fast 12 Hours clear liquids no solid food None None ProcedureLocation Hershey General Clinic Palmyra Family Practice Hershey Hospital Hershey Rehab Clinic Hershey General Clinic ProcedureDuration 30 minutes 30 minutes 1 hour 30 minutes 2 hours 30 minutes 005