In the name of Allah, the most beneficent, the most merciful
Advanced Database Systems
Lecture # 7
Normalization (revision)
Instructor: Furqan Shahid
Normalization definition
• A process of decomposing poorly structured tables into smaller well-
structured tables.
• The poorly structured tables contain anomalies, therefore, we decompose
them into smaller anomaly-free tables
Instructor: Furqan Shahid
Normal forms
• During normalization, we ensure presence of certain properties in the
table. A normal form refers to a state of the table when one or more
specific properties are present into it.
• Common normal forms include:
• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)
• Boyce Codd normal form (BCNF)
• Fourth normal form (4NF)
• Domain key normal form (DKNF)
Instructor: Furqan Shahid
First normal form (1NF) - definition
• A table (or relation) is in 1NF when no repeating group present in it
Repeating group
EMPLOYEE:
EmpID FirstName LastName Salary DateOfBirth ContactNo Designation
1001 Ahmad Tahir 50000 12-Jun-1992 0333-9999999 Manager
0300-9999999
1002 Zubair Saeed 70000 02-Feb-1990 0333-8888888 Manager
0321-8888888
0349-8888888
Instructor: Furqan Shahid
First normal form (1NF) –
Removing the repeating group
• Make attribute containing repeating group, a part of the primary key
EMPLOYEE:
EmpID FirstName LastName Salary DateOfBirth ContactNo Designation
1001 Ahmad Tahir 50000 12-Jun-1992 0333-9999999 Manager
1001 Ahmad Tahir 50000 12-Jun-1992 0300-9999999 Manager
1002 Zubair Saeed 70000 02-Feb-1990 0333-8888888 Manager
1002 Zubair Saeed 70000 02-Feb-1990 0321-8888888 Manager
1002 Zubair Saeed 70000 02-Feb-1990 0333-8888888 Manager
Instructor: Furqan Shahid
Second normal form (2NF) - definition
• A table (or relation) is in 2NF when already in 1NF and no partial
dependency present in it
• Partial dependency: Functional dependency of a non-primary-key
attribute on a part of primary key
• Functional dependency: The concept of determining value of one
attribute by another attribute, written as:
Attribute-1 → Attribute-2
Determinant
Instructor: Furqan Shahid
Functional dependency examples
EmpNO → DateOfBirth
(Empno is determinant of DateOfBirth)
EmpNO → Salary
(Empno is determinant of Salary)
However, FirstName is not determinant of DateOfBirth or Salary. Why?
Instructor: Furqan Shahid
Second normal form (2NF) cont.
• Primary key is determinant of all non-primary-key attributes
• Means, every non-primary-key attribute functionally depends on the
primary key
• For a table to be in 2NF, each non-primary-attribute should be
functionally dependent on full primary key (not on a part of primary
key)
Instructor: Furqan Shahid
Second normal form (2NF) example
Causes partial dependency
EMPLOYEE:
EmpID FirstName LastName Salary DateOfBirth ContactNo Designation
1001 Ahmad Tahir 50000 12-Jun-1992 0333-9999999 Manager
1001 Ahmad Tahir 50000 12-Jun-1992 0300-9999999 Manager
1002 Zubair Saeed 70000 02-Feb-1990 0333-8888888 Manager
1002 Zubair Saeed 70000 02-Feb-1990 0321-8888888 Manager
1002 Zubair Saeed 70000 02-Feb-1990 0333-8888888 Manager
EMP_CONTACT:
2NF
EMPLOYEE:
EmpID FirstName LastName Salary DateOfBirth Designation EmpID ContactNo
1001 Ahmad Tahir 50000 12-Jun-1992 Manager 1001 0333-9999999
1002 Zubair Saeed 70000 02-Feb-1990 Manager 1001 0300-9999999
1002 0333-8888888
1002 0321-8888888
1002 0333-8888888
Instructor: Furqan Shahid
Repeating group (not in 1NF)
Normalization example-2
STUDENT
RegNo Name Address DateOfBirth PassedCourse CourseTitle CourseCredits ObtainedGPA
1001 Usman H#1,St#1, City1 17-May-1990 CS-001 PF 4 3.0
CS-002 OOP 4 3.5
1002 Uzair H#2,St#2,City1 09-Aug-1991 CS-001 PF 4 3.5
CS-002 OOP 4 2.5
CS-003 DBMS 3 4.0
1NF
STUDENT
RegNo Name Address DateOfBirth PassedCourse CourseTitle CourseCredits ObtainedGPA
1001 Usman H#1,St#1, City1 17-May-1990 CS-001 PF 4 3.0
1001 Usman H#1,St#1, City1 17-May-1990 CS-002 OOP 4 3.5
1002 Uzair H#2,St#2,City1 09-Aug-1991 CS-001 PF 4 3.5
1002 Uzair H#2,St#2,City1 09-Aug-1991 CS-002 OOP 4 2.5
1002 Uzair H#2,St#2,City1 09-Aug-1991 CS-003 DBMS 3 4.0
Instructor: Furqan Shahid
Example-2 (Functional dependencies)
Currently:
(RegNo, PassedCourse) → Name, Address, DateOfBirth, CourseTitle,
CourseCredits, ObtainedGPA
Should be transformed as:
RegNo → Name, Address, DateOfBirth
CourseCode → CourseTitle, CourseCredits
(RegNo, PassedCourse) → ObtainedGPA
Instructor: Furqan Shahid
STUDENT:
Example-2 (second normal form)
RegNo Name Address DateOfBirth PassedCourse CourseTitle CourseCredits ObtainedGPA
1001 Usman H#1,St#1, City1 17-May-1990 CS-001 PF 4 3.0
1001 Usman H#1,St#1, City1 17-May-1990 CS-002 OOP 4 3.5
1002 Uzair H#2,St#2,City1 09-Aug-1991 CS-001 PF 4 3.5
1002 Uzair H#2,St#2,City1 09-Aug-1991 CS-002 OOP 4 2.5
1002 Uzair H#2,St#2,City1 09-Aug-1991 CS-003 DBMS 3 4.0
STUDENT:
2NF
RegNo Name Address DateOfBirth STD_RESULT:
1001 Usman H#1,St#1, City1 17-May-1990 RegNo PassedCourse ObtainedGPA
1002 Uzair H#2,St#2,City1 09-Aug-1991 1001 CS-001 3.0
COURSE: 1001 CS-002 3.5
CourseCode CourseTitle CourseCredits 1002 CS-001 3.5
CS-001 PF 4 1002 CS-002 2.5
CS-002 OOP 4 1002 CS-003 4.0
CS-003 DBMS 3
Instructor: Furqan Shahid
Thank you
Instructor: Furqan Shahid
