DATABASE DESIGN: NORMALIZATION Q & A
Examine the Patient Medication Form for the Wellmeadows Hospital case study shown in Figure

(a)

Identify the functional dependencies represented by the data shown in the form in Figure Patient No → Full Name Ward No → Ward Name Drug No → Name, Description, Dosage, Method of Admin Patient No, Drug No, Start Date → Units per Day, Finish date

The functional dependencies for Bed No are unclear. If Bed No was a unique number for the entire hospital, then could say that Bed No→Ward No. However, from further examination of the requirements specification, we can observe that Bed No is to do with the allocation of patients on the waiting list to beds. (b) Describe and illustrate the process of normalizing the data shown in Figure to first (1NF), second (2NF), third (3NF), and BCNF. First Normal Form Patient No, Drug No, Start Date, Full Name, Ward No, Ward Name, Bed No, Name, Description, Dosage, Method of Admin, Units per Day, Finish Date Second Normal Form Patient No, Drug No, Start Date, Ward No, Ward Name, Bed No, Units per Day, Finish Date Drug No, Name, Description, Dosage, Method of Admin

Patient No, Full Name Third Normal Form/BCNF Patient No, Drug No, Start Date, Ward No, Bed No, Units per Day, Finish Date Drug No, Name, Description, Dosage, Method of Admin Patient No, Full Name Ward No, Ward Name (c) Identify the primary, alternate, and foreign keys in your BCNF relations. Patient No(FK), Drug No(FK), Start Date, Ward No(FK), Bed No, Units per Day, Finish Date Drug No, Name, Description, Dosage, Method of Admin Patient No, Full Name Ward No, Ward Name Primary keys underlined.

The table shown in Figure lists dentist/patient appointment data. A patient is given an appointment at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a