Normalization
What is Normalization
Normalization is the process of organizing data in a database to:
Reduce data redundancy (duplicate data)
Improve data integrity
Eliminate insertion, update, and deletion anomalies
Make the database efficient and consistent
Normalization divides large tables into smaller, well-structured tables and
establishes relationships between them.
Why Normalization is Required?
Without normalization:
Same data is stored multiple times
Updating data becomes difficult
Inconsistencies appear in the database
Problems without Normalization (Anomalies)
Insertion Anomaly – Cannot insert data without other data
Update Anomaly – Same data must be updated in many rows
Deletion Anomaly – Deleting one record deletes useful information
Advantages of Normalization
Normalization helps to minimize data redundancy.
Greater overall database organization.
Data consistency within the database.
Much more flexible database design.
Enforces the concept of relational integrity.
Disadvantages of Normalization
You cannot start building the database before knowing what the user needs.
The performance degrades when normalizing the relations to higher normal
forms, i.e., 4NF, 5NF.
It is very time-consuming and difficult to normalize relations of a higher
degree.
Careless decomposition may lead to a bad database design, leading to serious
problems.
Types of Normal Forms:
Example (Un-Normalized Table)
STUDENT_COURSE Table
StudentID StudentName Course1 Course2 Course3 Instructor
101 Ayesha Java SQL Python Bob
102 Rahul Java SQL NULL Mary
Problems:
Multiple courses in one row
Repeating instructor data
Difficult to add new courses
First Normal Form (1NF)
Rule 1: Single Valued Attributes
Rule 2: Attribute Domain should not change
Rule 3: Unique name for Attributes/Columns
Rule 4: Order doesn't matters
Convert to 1NF
StudentID StudentName Course Instructor
101 Ayesha Java Nafisa
101 Ayesha SQL Nafisa
101 Ayesha Python Nafisa
102 Rahul Java Nafisa
102 Rahul SQL Nafisa
• Atomic values achieved
• Still redundancy exists
Second Normal Form (2NF)
Rule
Table must be in 1NF
No partial dependency
All non-key attributes must depend on the entire primary key
Primary Key
(StudentID, Course)
Partial Dependency Problem
StudentName depends only on StudentID
Instructor depends only on Course
What is Partial Dependency?
Partial Dependency exists, when for a composite primary key, any attribute in
the table depends only on a part of the primary key and not on the complete
primary key.
Candidate key : student_id + subject_id
Split Tables
Student Table
StudentID StudentName
101 Ayesha
102 Rahul
Course Table
Course Instructor
Java Bob
SQL Bob
Python Bob
ENROLLMENT Table
StudentID Course
101 Java
101 SQL
101 Python
102 Java
102 SQL
✔ Partial dependency removed
✔ 2NF achieved
Third Normal Form (3NF)
Rule
Table must be in 2NF
No transitive dependency
Non-key attributes should not depend on other non-key attributes
What is Transitive Dependency?
A transitive dependency in a database is an indirect relationship between
values in the same table that causes a functional dependency.
To achieve the normalization standard of Third Normal Form (3NF), you must
eliminate any transitive dependency.
By its nature, a transitive dependency requires three or more attributes (or
database columns) that have a functional dependency between them,
meaning that Column A in a table relies on Column B through an intermediate
Column C
If A->B and B->C are two FDs then A->C is called transitive dependency
Transitive Dependency Example
Book → Author: Here, the Book attribute determines the Author attribute. If
you know the book name, you can learn the author's name.
However, Author doesn't determine Book, because an author can write
multiple books.
Author → Author_Nationality: Likewise, the Author attribute determines
the Author_Nationality, but not the other way around—just because we know
the author's nationality doesn't mean we can determine the author.
But this table introduces a transitive dependency:
Book →Author_Nationality: If we know the book name, we can determine
the author's nationality via the Author column.
Example of Transitive Dependency
EmpID EmpName DeptID DeptName
1 Ali D10 HR
2 Sara D20 IT
DeptName depends on DeptID
DeptID depends on EmpID
Transitive dependency exists
Convert to 3NF
EMPLOYEE Table
EmpID EmpName DeptID
1 Ali D10
2 Sara D20
Department Table
DeptID DeptName
D10 HR
D20 IT
Transitive dependency removed
3NF achieved
Disadvantages
❌ More tables
❌ More joins may reduce performance