Question

1. Functional Dependency and normalization.

The following database table describes a hospital containing physicians and the patients they had appointments with.

Physician ID

Physician Name

Patient Name

Appointment ID

Appointment Date

Patient Phone Number

Patient Symptoms

Patient Email

Physician Phone Number

1. Define the functional dependencies in the database table. (10 pts)

2. Normalize the table to follow third normal form (10 pts)

Answer 1

a) Functional Dependencies in the given database table explained below

• Physician ID is the key for this relation
• Physician name is fully functionally dependent on Physician ID
• Patient Name is functionally dependent on Physician ID
• Patient Phone Number, Patient Email , Patient Symptoms , Appointment _ID are fully functionally dependent on Patient Name .
• Appointment Date is fully functionally dependant on Appointment ID
• Appointment Date is partially dependent on Patient Name
• Appointment _ID is functionally dependent on Patient Name
• Appointment _ID partially dependent on Physician ID
•   Since Patient Name is dependent on Physician ID ,  Patient Phone Number, Patient Email ,Patient Symptoms Appointment ID and Appointment Date are partially functionally dependent on Physician ID

b) The given table above has been normalized in 3NF below

Physician [ Physician_ID, Physician Name ]

Patient [ Patient_ID, Patient_Name, Patient_Phone_Number, Patient_Email, Patient_Symptoms ]

Appointment [ Appointment_ID, Appointment_Date, Patient_ID, Physician_ID ]

Note : Primary key attributes are underlined . Foreign keys are in italics