NORMALIZING DATA MODEL
I. OBJECTIVES
At the end of this exercise, students must be able to:
a) Analyze data and identify the functional dependencies
b) Normalize data to third normal form
II. BACKGROUND INFORMATION
What is Normalization?
Normalization is a formal process for deciding which attributes should be grouped together in a relation.
Steps:
1. Remove multivalued attributes (1NF)
2. Remove partial dependencies (2NF)
3. Remove transitive dependencies (3NF)
Figure 4.1 INVOICE data of Pine Valley Furniture Company
[pic]
Functional dependency
The value of one attribute (the determinant) determines the value of another attribute.
Figure 4.2 Functional Dependency Diagram for INVOICE
[pic]
Order_ID → Order_Date, Customer_ID, Customer_Name, Customer_Address
Customer_ID → Customer_Name, Customer_Address
Product_ID →Product_Description, Product_Finish, Unit_Price
Order_ID, Product_ID → Order_Quantity
First Normal Form (1NF)
All attributes must be single-valued.
Figure 4.3 INVOICE relation of Pine Valley Furniture (1NF)
[pic]
Product_ID → Product_Description, Product_Finish, Unit_Price
Order_ID, Product_ID → Ordered_Quantity
Second Normal Form (2NF)
A relation is in 2NF, if it is in 1NF and every non-key attribute is fully functionally dependent on the entire primary key (no partial dependencies).
Figure 4.4 Removing partial dependencies
[pic]
Partial dependencies are removed, but there are still transitive dependencies.
Third Normal Form (3NF)
For a relation to be in 3NF, it is must be in 2NF and no transitive dependencies (functional dependencies on non-primary key attributes).
Figure 4.5 Removing transitive dependencies
[pic]
III. EXPERIMENTAL PROCEDURE
Overview
Patients visit the hospital and their visit history is maintained by the hospital staff. Different physicians may be available on different dates. They diagnose and treat the patients of all categories. Some of treatments are free while others are to be paid by the patients. Sample data of the case is shown in the following chart.
Table 4.1 Patient History Report
PatientID |Name |Address |Visit Date |Physician |Diagnosis |Treatment | |P-100809 |A |City: X |12-02-2007
20-02-2007
29-02-2007
15-03-2007 |Dr. Z
Dr. F
Dr. R
Dr. L |Chest Infection
Cold
Hepatitis-A
Eyes Infection |Free
Free
Paid
Paid | |P-200145 |N |City: Y |10-01-2007
15-02-2007
25-03-2007 |Dr. L
Dr. K
Dr. A |Bone Fracture
Cough
Flu |Paid
Free
Free | |
Task 1
Draw a dependency diagram and transform the above data to first normal form by eliminating repeating groups such that each row in the relation is atomic. Be sure to create an appropriate name for the relation and identify primary key/s.
Functional dependency:
Relation with sample data:
Table 4.2 First Normal Form
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
Task 2
Remove partial dependencies to convert the relation in Task 1 to second normal form. Be sure to create an appropriate name for the relation and identify primary key/s.
Functional dependency:
Relation with sample data:
Task 3
Convert the relations in Task 2 to third normal form by removing transitive dependencies. Be sure to create an appropriate name for the relation and identify primary key/s.
Functional dependency:
Relation with sample data:
IV. QUESTION AND ANSWER
What is a well-structured relation? Why are well-structured relations important in logical database design?
_________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
IV. REFERENCES
Hoffer, J.A., Prescott, M.B., McFadden, F.R. (2007). Modern Database Management 8th Edition. New Jersey: Pearson Prentice Hall.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.