Case Study Database Design (ASSURED A)

Document Content and Description Below

Case Study Database Design (ASSURED A) A small surgery center requires a new database. The office manager has identified the following types of data: patients, doctors, procedures and appointments.... From this data requirement the following main entities have been created: Patient, Doctor, Appointment and Procedure. The relationships between the main entities can be explained as follows: • Doctors can see many patients (many to many relationship) • Patients can have many procedures (many to many relationship) • Doctors can service many appointments (many to many relationship) Due to the multiple many to many relationships between the main entities, then it is necessary to create associative tables to join the main tables together. The associative tables are: Join Patient Procedure, Join Doctor Patient and Join Doctor Appointment. The following tables detail the entities, their attributes, the data types of each attribute, if the attribute is a primary or foreign key and whether or not the attribute is required. PATIENT ENTITY Attribute (P)rimary/(F)oreign Key Data Type Required Patient ID P Short text (10) Y Last name Short text (25) Y First name Short text (25) Y Date of birth Date/time Y Social security number Short text (10) Y Sex Short text (1) Y Street address Short text (25) Y 3 CASE STUDY – DATABASE DESIGN PATIENT ENTITY Attribute (P)rimary/(F)oreign Key Data Type Required City Short text (20) Y State Short text (2) Y Postal code Short text (10) Y Phone number Short text (10) N [Show More]

Last updated: 1 year ago

Preview 1 out of 7 pages