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-1a (Contract#, Client#, Estimated_cost, Completion_date, (Staff#, Staff_name, Staff_location)) CONTRACT-1b (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, Secretary)) 1NF:
PATIENT-1 (Patient#, Name, DOB, Address) PRESCRIPTION-1 (Prescription#, Drug, Date, Dosage, Doctor, Secretary, Patient#) 2NF: PATIENT-2 (Patient#, Name, DOB, Address) or OK PRESCRIPTION-2 (Prescription#, Drug, Date, Dosage, 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, Secretary) 4. DOCTOR (Doctor#, DoctorName, Secretary, (Patient#, PatientName, PatientDOB, PatientAddress, (Prescription#, Drug, Date, Dosage))) 1NF: DOCTOR-1 (Doctor#, DoctorName, Secretary) (intermediate step) 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#) 2NF: 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 3NF: 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
Normalization Exercise 5 Produce the Third Normal Form of this document by normalization.
A Sample Answer •
0NF o
ORDER(order#, customer#, name, address, orderdate(product#, description, quantity, unitprice))
•
1NF o
ORDER(order#, customer#, name, address, orderdate)
o
ORDER_LINE(order#, product#, description, quantity, unitprice)
•
•
2NF o
ORDER(order#, customer#, name, address, orderdate)
o
ORDER_LINE(order#, product#, quantity)
o
PRODUCT(product#, description, unitprice)
3NF o
ORDER(order#, customer#, orderdate)
o
CUSTOMER(customer#, name, address)
o
ORDER_LINE(order#, product#, quantity)
o
PRODUCT(product#, description, unitprice)
http://support.microsoft.com/kb/100139 --di ko macopy ng maaus
Normalization
Database design theory includes standards and guidelines to help us efficiently design our tables and create a normalized database. These are referred to as
normal forms.
Let's begin with a summary of the normal forms.
First Normal Form (1NF): • Break each field down to the smallest meaningful value • Remove repeating groups of data • Create a separate table for each set of related data Second Normal Form(2NF) • Create new tables for data that applies to more than one record in a table • Add a related field (foreign key) to the table Third Normal Form (3NF) • Remove fields that do not relate to, or provide a fact about, the primary key The normal forms are cumulative and there are actually a total of five normal forms starting with the first normal form (1NF) through the fifth normal form (5NF). However, the fourth and fifth normal forms are rarely applied. Most databases achieve normalization by the third normal form (3NF) so the first three normal forms will be the focal point of this article. First Normal Form
A table is in its 1NF when each field contains the smallest meaningful value and does not contain repeating groups of data. What does this mean exactly? Let's break it down step-by-step. Take the following table for example:
If we apply the first portion of 1NF, t he Name, and Spouse\Children fields do not contain the smallest meaningful value. Instead, the first name, last name, and the children's names should be placed in separate fields:
Of course there are still flaws in the table, but keep in mind there is more than one step in the normalization process. Once the first step is accomplished then we proceed to the second portion of 1NF: the table should not contain repeating groups. Spouse, Child 1, Child 2, and Child 3 would be considered repeating groups. They are dependants of the employee. The dependants need to be moved to another table and each dependant should be added as records in the table:
Also consider that Lenny and Carl do not have dependants so there are no related records for them in the Dependants table. However should either add a dependant in the future we simply add a related record. There is no need to modify the database design. Second Normal Form
Create new tables for data that applies to more than one record in a table and add a related field (foreign key) to the table:
The Manager fields applies to multiple records in the table. Therefore, it should be moved to another table, duplicate values removed and a field to establish a relationship between the tables should be added.
Third Normal Form
Remove fields that do not relate to, or provide a fact about, the primary key.
Take the Manager, Dept, and Sector fields for example. The Manager and Sector are facts about the Dept—not the primary key. These fields should be moved to another table and a field to establish a relationship between the tables should be added.
As an additional note, if your table contains a multiple-field primary key then all non-key fields should be a fact about the entire key. If a field describes only a one field in the key then it should be moved to another table. The following image shows the completed database design.
