Chapter 3.3 - 3.
5:
Normalization
DATABASE MANAGEMENT AND DESIGN
Normalization
The process of transforming a relational schema to an
equivalent one with anomalies eliminated
Translation – organize data to eliminate the errors that
occur when we:
◦ Add data (insertion anomaly)
◦ Delete data (deletion anomaly)
◦ Update data (update anomaly)
The Normalization Process
Sale (SaleDate, FirstName, LastName, ProductName, Category, Color, ItemSize,
ManufacturerName, ManufacturerState, Price)
◦ What are the potential problems with this relation?
◦ Combine data from Sale, Customer, Product, and Manufacturer
◦ It doesn't even have a key
◦ Its design requires the tedious, error-prone entry of redundant data.
• Example: ManufacturerState
The Normalization Process
Sale (SaleDate, FirstName, LastName, ProductName, Category, Color, ItemSize, ManufacturerName,
ManufacturerState, Price)
Sale (SaleID, SaleDate, CustomerID, ProductID, Quantity, Tax, Shipping)
◦ Foreign Key: CustomerID references Customer
◦ Foreign Key: ProductID references Product
Customer (CustomerID, FirstName, LastName, StreetAddress, City, State, PostalCode, Country)
Product (ProductID, ProductName, Color, ItemSize, ManufacturerID, ListPrice, Gender,
Category)
◦ Foreign Key: ManufacturerID references Manufacturer
Manufacturer (ManufacturerID, ManufacturerName, Address1, Address2, City, State,
PostalCode)
Functional Dependencies
A relationship between columns where the values in one or more columns determines
the values for another column
Example: FD: ManufacturerID à ManufacturerState*
Why do we say that ManufacturerID determines Manufacturer State?
* Notes:
◦ This is read, “ManufacturerID functionally determines ManufacturerState.”
◦ Item on the left side is called the “determinant”
Normal Forms
First Normal Form à Atomic values
Second Normal Form à No non-key attributes are
functionally dependent on only part of the key
Third Normal Form à for every FD: X à Y, X is a candidate
key for the relation
First Normal Form
All attribute values must be atomic
It means that every attribute for one tuple can only have one value. In other words, for any
single row, no column has more than one value.
Employee table
EmployeeName Skills
John Programming, Query Optimization
Mary Systems Analysis, Programming
LuAnn Data Analysis, Query Optimization, Systems Analysis
How do I fix Employee table that is not in
First Normal Form?
1. Create a new table. The key to the new table is the primary key from the
original table plus the multi-valued attribute. It is a composite key.
2. 2. Remove the multi-valued attribute from the original table.
3. 3. Repeat steps 1 and 2 for every multi-valued attribute in the relation.
Employee table
EmployeeName Skills
John Programming, Query Optimization
Mary Systems Analysis, Programming
LuAnn Data Analysis, Query Optimization, Systems Analysis
First Normal Form
EmployeeName Skills
John Programming, Query Optimization
Mary Systems Analysis, Programming
LuAnn Data Analysis, Query Optimization, Systems Analysis
Solution: The simplest solution is to split the Employee relation into two relations.
EmployeeName Skills
John Programming
John Query Optimization
Mary Systems Analysis
Mary Programming
Problems with Violating 1st Normal Form
1. Selecting tuples based on values of the multi-value
attribute
2. Sorting tuples
3. Requires understanding of issues related to fourth normal
form
Second Normal Form
No non-key attribute can be functionally dependent on only part of a key. That means:
• Second Normal Form is only an issue when the Primary Key is also a Composite Key with multiple
attributes.
• Second Normal Form means that all the other (non-key) attributes must be dependent on the
entire composite key and not just part of the key.
StudentID CourseID StudentName CreditHours Grade
1 7 John Doe 3.0 B+
2 7 Mary Smith 3.0 A-
2 5 Mary Smith 3.0 B
3 6 Joe Jones 3.0 B-
Functional Dependencies
◦ FD: StudentID à StudentName
◦ FD: CourseID à CreditHours
◦ FD: StudentID & CourseID à Grade
Problems with Violating 2nd Normal Form
Data redundancy: might lead to data inconsistencies
Insertion Anomaly: if this is the only place StudentName stored and
a student has not completed a course there may be no place to store
that student’s information
Update Anomaly: If number of credit hours for a course changes, all
instances of that course must be changed.
StudentID CourseID StudentName CreditHours Grade
1 7 John Doe 3.0 B+
2 7 Mary Smith 3.0 A-
2 5 Mary Smith 3.0 B
3 6 Joe Jones 3.0 B-
how do I fix a table that is not in 2nd
Normal Form?
1. Make a new relation using that partial key attribute as the key of
the new table.
2. Include all those attributes that are dependent on that attribute
that was the partial key and remove them from the original table.
3. Keep doing steps 1 and 2 for any other partial dependencies.
4. If you missed any attributes in step 2, add them to the applicable
new relation.
Second Normal Form Solution
Problem:
StudentID CourseID StudentName CreditHours Grade
1 7 John Doe 3.0 B+
2 7 Mary Smith 3.0 A-
2 5 Mary Smith 3.0 B
3 6 Joe Jones 3.0 B-
Solution:
StudentID StudentName CourseID StudentName StudentID CourseID Grade
1 John Doe 7 3.0 1 7 B+
2 Mary Smith 7 3.0 2 7 A-
2 Mary Smith 5 3.0 2 5 B
3 Joe Jones 6 3.0 3 6 B-
Third Normal Form
For every functional dependency – FD: X à Y, X must be a candidate
key for the relation
Consider this relation: Employee(EmpID, SkillType, Bonus)
Functional dependencies:
◦ FD: EmpID à SkillType, Bonus
◦ FD: SkillType à Bonus
SkillType is not a candidate key for the employee relation, therefore
the relation is not in third normal form
Problems with Violating 3rd Normal Form
Redundancy: bonus rate repeats for all employees with the same
skill
Update/Deletion Anomalies:
◦ If bonus rate changes, it must be changed multiple places
◦ If the only instance of a specific bonus rate is deleted, that information is lost
Insertion Anomalies: if no employee has a specific skill, there may be
no place to store the accompanying bonus rate
What does Third Normal Form really
mean?
Third Normal Form looks for dependencies between
non-key attributes.
This means that one non-key attribute cannot be
dependent on another non-key attribute.
How do I fix a table that is not in Third
Normal Form?
1. Make a new relation using the determinant attribute as the key of
the new table.
2. Include all those attributes that are dependent on that attribute
and remove them from the original table.
3. Keep doing steps 1 and 2 for any other non-key attribute
dependencies.
4. If you missed any dependent attributes in step 2, add them to the
applicable new relation.
Third Normal Form Solution
Problem: Employee(EmpID, SkillType, Bonus)
Functional dependencies:
◦ FD: EmpID à SkillType, Bonus
◦ FD: SkillType à Bonus
Solution:
Employee (EmpID, SkillType)
◦ Foreign Key: SkillType references Skill
Skill (SkillType, BonusRate)
Fourth Normal Form
Faculty, committee assignments, and course assignments
FName Committee Course FName Committee Course FName Committee Course
Jones Admissions IM101 Jones Admissions IM101 Jones Admissions IM101
Jones Scholarship IM102 Jones Scholarship IM102 Jones Scholarship IM101
Jones Scholarship IM103 Jones - IM103 Jones Admissions IM102
Jones Scholarship IM102
FName Committee Course FName Committee Course
Jones Admissions IM103
Jones Admissions Jones Admissions IM101
Jones Scholarship IM103
Jones Scholarship Jones
Jones IM101 Jones IM102
This is a multi-valued dependency
Jones IM102 Jones Scholarship IM103
Jones IM103
Fourth Normal Form Solution
Place all multi-valued attributes in relations with their appropriate key
FName Committee FName Course
Jones Admissions Jones IM101
Jones Scholarship Jones IM102
Jones IM103
Boyce-Codd Normal Form
Every relation in BCNF is also in 3NF
◦ Relation in 3NF is not necessarily in BCNF
Boyce-Codd Normal Form Example
Below we have a college enrolment table with columns student_id, subject and professor.
student_id subject professor
101 Java [Link]
101 C++ [Link]
102 Java P.Java2
103 C# [Link]
104 Java [Link]
Why this table is not in BCNF?
In the table above, student_id, subject form primary key, which means subject column is
a prime attribute.
But, there is one more dependency, professor → subject.
And while subject is a prime attribute, professor is a non-prime attribute, which is not
allowed by BCNF.
How to satisfy BCNF?
To make this relation(table) satisfy BCNF, we will decompose this table into two
tables, student table and professor table.
Below we have the structure for both the tables.
Student Table Professor Table
student_id p_id p_id professor subject
101 1 1 [Link] Java
101 2 2 [Link] C++