Case Study – Database Design

1
CASE STUDY – DATABASE DESIGN
Case Study


Database Design


DeVry University

, 2
CASE STUDY – DATABASE DESIGN




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 Data Type Required
Key
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
City Short text (20) Y
State Short text (2) Y
Postal code Short text (10) Y
Phone number Short text (10) N