NORMALIZATION
Normalization is the process of organizing data in a relational database to reduce redundancy
and improve data integrity by dividing large tables into smaller related tables based on
functional dependencies.
Why Normalization?
Minimize data redundancy
Avoid update anomalies
Ensure logical data storage
Maintain consistency
Hierarchy of Normal Forms
1. 1st Normal Form
2. 2nd Normal Form
3. 3rd Normal Form
4. Boyce codd Normal Form
5. 4th Normal Form
6. 5th Normal Form
1. First Normal Form (1NF)
Definition
A relation is in First Normal Form if:
All columns contain atomic values (i.e., indivisible values).
Each row is unique (i.e., no duplicate rows).
Each column has a unique name.
The order in which data is stored does not matter.
All the Column should be in same domain.
student course registration table:
StudentID StudentName Age Subjects
101 Ram 17 DBMS, OS
102 Ravi 18 CN, TOC
103 Meena seventeen DBMS
104 Priya 19 OS, CN, AI
105 Karthik eighteen AI, ML
Table AFTER converting to 1NF:
StudentID StudentName Age Subject
101 Ram 17 DBMS
101 Ram 17 OS
102 Ravi 18 CN
102 Ravi 18 TOC
103 Meena 17 DBMS
104 Priya 19 OS
104 Priya 19 CN
104 Priya 18 AI
105 Karthik 18 AI
105 Karthik 18 ML
2. Second Normal Form (2NF)
A relation is in Second Normal Form (2NF) if:
1. It is already in First Normal Form (1NF)
2. Every non-key attribute must depend on the FULL primary key (no
partial dependency).
Primary Key = (StudentID + CourseID)
StudentI CourseI StudentNam CourseNam Credit
D D e e s
101 C1 Ram DBMS 4
101 C2 Ram OS 3
102 C1 Ravi DBMS 4
103 C3 Meena CN 3
104 C2 Priya OS 3
Here,
StudentName depends only on StudentID.
CourseName depends only on CourseID.
Credits depends only on CourseID.
Convert into 2NF
Student Course
StudentID StudentName CourseID CourseName Credits
101 Ram C1 DBMS 4
102 Ravi C2 OS 3
103 Meena C3 CN 3
104 Priya
Enrollment
Studen Cours
tID eID
101 C1
101 C2
102 C1
103 C3
104 C2
3. Third Normal Form (3NF)
A relation is in Third Normal Form (3NF) if:
It is already in Second Normal Form (2NF)
There is no transitive dependency.
Transitive dependency
When a non-key attribute depends on another non-key attribute instead of primary key.
Primary Key → Non-key → Another Non-key (Indirect dependency)
Example
Primary Key = StudentID
Student Department table:
StudentID StudentName DeptID DeptName
101 Ram D1 CSE
102 Ravi D2 ECE
103 Meena D1 CSE
104 Priya D3 IT
105 Karthik D2 ECE
Here
StudentID → DeptID
DeptID → DeptName
So: StudentID indirectly determines DeptName.
Convert into 3NF
Student Table
StudentID StudentName DeptID
101 Ram D1
102 Ravi D2
103 Meena D1
104 Priya D3
105 Karthik D2
Department Table
DeptID DeptName
D1 CSE
D2 ECE
D3 IT
3.5 Boyce-Codd Normal Form (BCNF) (3.5NF)
A relation is in BCNF if:
It is in 3NF
Every determinant must be a candidate key.
Determinant:
A→B Here A is a Determinant then A must be a candidate key.
Primary key = (StudentID, Course)
StudentID Course Instructor
101 DBMS Kumar
102 OS Raja
103 DBMS Kumar
104 CN Meena
105 OS Raja
Assume: Each course has only ONE instructor. So dependency: Course → Instructor
Here Course is not a candiatekey.
Convert into BCNF
Course Table Enrollment Table
StudentID Course
Course Instructor 101 DBMS
DBMS Kumar 102 OS
103 DBMS
OS Raja
104 CN
CN Meena 105 OS
4. Fourth Normal Form (4NF)
A relation is in 4NF if:
• It is in BCNF
• It has no non-trivial Multivalued Dependency.
Multivalued Dependency (MVD)
If:
A →→ B
It means:
For one value of A, there are multiple independent values of B.
STUDENT_HOBBY_LANGUAGE
StudentID Hobby Language
101 Dance English
101 Dance Tamil
101 Music English
101 Music Tamil
102 Cricket Hindi
102 Cricket English
StudentID →→ Hobby
StudentID →→ Language
Here:
StudentID is candidate key
But Hobby and Language are independent multivalued attributes.
So:
The table is in BCNF
But NOT in 4NF
Because of multivalued dependency.
Convert into 4NF
Student_Hobby Student_Language
StudentID Hobby StudentID Language
101 Dance 101 English
101 Music 101 Tamil
102 Hindi
102 English
StudentID Hobby
102 Cricket
Now
No multivalued dependency
• No redundancy
• Both tables are in 4NF
5. Fifth Normal Form (5NF) (Project-Join Normal Form)
A relation is in 5NF if:
• It is in 4NF
• Every non-trivial Join Dependency is implied by the candidate key
Join Dependency (JD)
If a relation R can be decomposed into:
R1, R2, R3
And
R = R1 ⨝ R2 ⨝ R3 (Lossless Join)
Then a Join Dependency exists.
Example for 5th Normal Form
Subjec
Class Teacher
t
class
math kartik
10
class
math yash
9
class
math yash
10
class
science yash
10
We can split into Class Teacher
class
kartik
10
class
yash
9
class
yash
10
Subject Class
math class 9
class
math
10
class
science
10
Subject Class Teacher
Math class 9 yash
Math class 10 kartik
Math class 10 yash
Science class 10 kartik
Science class 10 yash
5NF Decomposition (Three Tables)
Further split eliminates join dependencies; natural join reconstructs original without extras.
R1 (Subject, Class)
Subject Class
math class 9
math class 10
science class 10
R2 (Class, Teacher)
Class Teacher
class 10 Kartik
class 9 Yash
class 10 Yash
R3 (Subject, Teacher)
Subject Teacher
math Yash
math Kartik
science yash
Now
• No further Join Dependency exists
• No redundancy
• All tables are in 5NF
Non-Loss (Lossless) Decomposition
A decomposition of a relation R into R1 and R2 is said to be Non-Loss (Lossless)
Decomposition if:
After joining R1 and R2,
we get exactly the original relation,
with no extra tuples and no missing tuples.
STUDENT_COURSE
StudentID Course Instructor
101 DBMS Kumar
102 OS Raja
103 DBMS Kumar
We decompose into:
R1 (Course, Instructor) R2 (StudentID, Course)
Course Instructor StudentID Course
DBMS Kumar 101 DBMS
OS Raja 102 OS
103 DBMS
Perform Natural Join on Course
R2 ⨝ R1
StudentID Course Instructor
101 DBMS Kumar
102 OS Raja
103 DBMS Kumar
Lossy Decomposition
StudentID Course Instructor
101 DBMS Kumar
101 OS Raja
102 OS Raja
R1 (StudentID, Course) R2 (StudentID, Instructor)
StudentID Course
StudentID Instructor
101 DBMS
101 Kumar
101 OS
101 Raja
102 OS
N 102 Raja
Join R2 ⨝ R1
StudentID Course Instructor
101 DBMS Kumar
101 DBMS Raja ❌
101 OS Kumar ❌
101 OS Raja
102 OS Raja
So this is Lossy Decomposition.
Dependency Preservation
A decomposition of a relation R into R1 and R2 is said to be Dependency Preserving if:
After decomposition,
All the functional dependencies can be enforced on the individual decomposed tables
without joining them.
If we need to join the tables to check a dependency → Then that is Not Dependency
Preserving.
STUDENT_COURSE
StudentID Course Instructor
101 DBMS Kumar
102 OS Raja
103 DBMS Kumar
1. StudentID → Course
2. Course → Instructor
We decompose into:
R1 (StudentID, Course) R2 (Course, Instructor)
StudentID Course
Course Instructor
101 DBMS
DBMS Kumar
102 OS
OS Raja
103 DBMS
FD1: StudentID → Course
FD2: Course → Instructor
Both dependencies are preserved in individual tables. So No need to join tables.
Therefore, this is Dependency Preserving Decomposition
Same Example for NOT Dependency Preserving
R1 (StudentID, Instructor) R2 (StudentID, Course)
StudentID Instructor StudentID Course
101 Kumar 101 DBMS
102 Raja 102 OS
103 Kumar 103 DBMS
FD1: StudentID → Course
FD2: Course → Instructor (Cannot be checked in R1 or R2 individually)
To verify Course → Instructor, we must join R1 and R2.
So dependency is NOT preserved.
Therefore, this is NOT Dependency Preserving Decomposition.