Normalization Exercises

Convert each of the following schemas to 3NF, showing all intermediate stages, that is, 1NF and 2NF.

1. BRANCH (Branch#, Branch_Addr, (ISBN, Title, Author, Publisher, Num_copies))

1NF:

BRANCH-1 (Branch#, Branch_Addr)

STOCKS-1 (Branch#, ISBN, Title, Author, Publisher, Num_copies)

 

2NF:

BRANCH-2(Branch#, Branch_Addr) or OK

STOCKS-2 (Branch#, ISBN, Num_copies)

BOOK-2 (ISBN, Title, Author, Publisher)

 

3NF:

BRANCH-3(Branch#, Branch_Addr) or OK

STOCK-3 (Branch#, ISBN, Num_copies) or OK

BOOK-3 (ISBN, Title, Author, Publisher) or OK


2. CLIENT (Client#, Name, Location, Manager#, Manager_name, Manager_location, (Contract#, Estimated_cost, Completion_date, (Staff#, Staff_name, Staff_location)))

1NF:

CLIENT-1 (Client#, Name, Location, Manager#, Manager_name, Manager_location)

(intermediate step)

CONTRACT (Contract#, Client#, Estimated_cost, Completion_date, (Staff#, Staff_name, Staff_location))

CONTRACT-1 (Contract#, Client#, Estimated_cost, Completion_date)

STAFF-1 (Staff#, Contract#, Staff_name, Staff_location)

 

2NF:

CLIENT-2 (Client#, Name, Location, Manager#, Manager_name, Manager_location) or OK

CONTRACT-2 (Contract#, Client#, Estimated_cost, Completion_date) or OK

STAFF-2 (Staff#, Staff_name)

ASSIGNMENT-2 (Staff#, Contract#, Staff_location)

 

3NF:

CLIENT-3 (Client#, Name, Location, Manager#)

MANAGER-3(Manager#, Manager_name, Manager_location)

CONTRACT-3 (Contract#, Client#, Estimated_cost, Completion_date) or OK

STAFF-3 (Staff#, Staff_name) or OK

ASSIGNMENT-3 (Staff#, Contract#, Staff_location) or OK


3. PATIENT (Patient#, Name, DOB, Address, (Prescription#, Drug, Date, Dosage, Doctor#, Doctor, Secretary))

1NF:

PATIENT-1 (Patient#, Name, DOB, Address)

PRESCRIPTION-1 (Prescription#, Drug, Date, Dosage, Doctor#, Doctor, Secretary, Patient#)

 

2NF:

PATIENT-2 (Patient#, Name, DOB, Address) or OK

PRESCRIPTION-2 (Prescription#, Drug, Date, Dosage, Doctor#, Doctor, Secretary, Patient#) or OK

 

3NF:

PATIENT-3 (Patient#, Name, DOB, Address) or OK

PRESCRIPTION-3 (Prescription#, Drug, Date, Dosage, Doctor#, Patient#)

DOCTOR-3 (Doctor#, Doctor, Secretary)


4. DOCTOR (Doctor#, DoctorName, Secretary, (Patient#, PatientName, PatientDOB, PatientAddress, (Prescription#, Drug, Date, Dosage)))

DOCTOR-1 (Doctor#, DoctorName, Secretary)

PATIENT-1a(Patient#, PatientName, PatientDOB, PatientAddress, (Prescription#, Drug, Date, Dosage), Doctor#)

PATIENT-1b(Patient#, PatientName, PatientDOB, PatientAddress, Doctor#)

PRESCRIPTION-1 (Prescription#, Drug, Date, Dosage, Patient#)

 

DOCTOR-2 (Doctor#, DoctorName, Secretary) or OK

PATIENT-2(Patient#, PatientName, PatientDOB, PatientAddress, Doctor#) or OK

PRESCRIPTION-2 (Prescription#, Drug, Date, Dosage, Patient#) or OK

 

DOCTOR-3 (Doctor#, DoctorName, Secretary) or OK

PATIENT-3(Patient#, PatientName, PatientDOB, PatientAddress, Doctor#) or OK

PRESCRIPTION-3 (Prescription#, Drug, Date, Dosage, Patient#) or OK