👉What is Database Normalization?
DBMS Normalization: Normalization is a database design process that organizes data efficiently by eliminating redundancy and minimizing insertion, update, and deletion anomalies. It involves breaking larger tables into smaller related tables and linking them using relationships.
Purpose of Normalization:
✅ Eliminates redundant data
✅ Ensures data integrity
✅ Optimizes storage and retrieval
✅ Reduces anomalies in database transactions
The relational model, introduced by Edgar Codd, first introduced First Normal Form (1NF) and later expanded into Second (2NF) and Third (3NF) Normal Forms. Further refinement led to Boyce-Codd Normal Form (BCNF), 4NF, and 5NF.
👉Types of Normal Forms in DBMS
1NF (First Normal Form)
🔹 Ensures that all columns contain atomic (indivisible) values.
🔹 Each record is unique and identified by a primary key.
🔹 Eliminates repeating groups by structuring data into tables and columns.
Example (Before 1NF – Unnormalized Table):
| Customer ID | Name | Movies Rented |
| 101 | John | Avengers, Batman |
| 102 | Alice | Superman, Spiderman |
Issues: “Movies Rented” contains multiple values, violating atomicity.
✅ After 1NF (Normalized Table):
| Customer ID | Name | Movie Rented |
| 101 | John | Avengers |
| 101 | John | Batman |
| 102 | Alice | Superman |
| 102 | Alice | Spiderman |
2NF (Second Normal Form)
🔹DBMS Normalization: Follows 1NF and removes partial dependencies.
🔹 Each non-key attribute must be fully dependent on the entire primary key.
🔹 Breaks the table into separate tables to remove redundant data.
Example: Consider a table where each movie has a rental price.
| Customer ID | Name | Movie Rented | Rental Price |
| 101 | John | Avengers | $5 |
| 102 | Alice | Superman | $4 |
Issue: Rental price depends on Movie Rented, not on Customer ID.
✅ After 2NF (Splitting into Two Tables):
Customers Table:
| Customer ID | Name |
| 101 | John |
| 102 | Alice |
Movies Table:
| Movie Rented | Rental Price |
| Avengers | $5 |
| Superman | $4 |
3NF (Third Normal Form)
🔹 DBMS Normalization: Follows 2NF and removes transitive dependencies.
🔹 A non-key column should not depend on another non-key column.
Example (Before 3NF – Transitive Dependency Present):
| Customer ID | Name | City | Zip Code |
| 101 | John | New York | 10001 |
| 102 | Alice | Chicago | 60601 |
Issue: Zip Code depends on City, not directly on Customer ID.
✅ After 3NF (Splitting into Two Tables):
Customers Table:
| Customer ID | Name | City |
| 101 | John | New York |
| 102 | Alice | Chicago |
City-Zip Table:
| City | Zip Code |
| New York | 10001 |
| Chicago | 60601 |
👉BCNF (Boyce-Codd Normal Form)
🔹 DBMS Normalization: A stricter version of 3NF that removes anomalies that 3NF does not handle.
🔹 Every determinant in a table must be a candidate key.
Example (Before BCNF – Violation Exists):
| Professor ID | Subject | Department |
| 1 | Math | Science |
| 2 | Physics | Science |
Issue: Subject → Department dependency violates BCNF rules.
✅ After BCNF (Splitting into Two Tables):
Professors Table:
| Professor ID | Subject |
| 1 | Math |
| 2 | Physics |
Subjects Table:
| Subject | Department |
| Math | Science |
| Physics | Science |
👉Advanced Normal Forms
- DBMS Normalization: 4NF (Fourth Normal Form): Removes multi-valued dependencies to ensure that an entity only contains independent multi-valued facts.
- 5NF (Fifth Normal Form / PJNF): Deals with complex join dependencies and ensures data reconstruction without loss.
- 6NF (Sixth Normal Form): Theoretical; used for temporal databases to handle time-dependent data changes.
👉Summary
✅ DBMS Normalization: 1NF – Eliminates repeating groups (atomicity).
✅ 2NF – Eliminates partial dependency (full dependency on the primary key).
✅ 3NF – Eliminates transitive dependency.
✅ BCNF – Ensures every determinant is a candidate key.
✅ 4NF & 5NF – Handle advanced anomalies and join dependencies.
Normalization optimizes database performance, ensures consistency, and minimizes redundancy, making it an essential part of database design!
