Database Normalization — Normal Forms Explained

What is Normalization?

Normalization is the process of organizing data in a database to reduce redundancy and improve integrity. It involves decomposing tables into smaller, well-structured relations that satisfy progressive normal forms.

Eliminate redundancy

Avoid anomalies

Enforce data integrity

Simplify queries

1NF First Normal Form

Rule: Each column must contain atomic (indivisible) values, and each row must be uniquely identifiable. No repeating groups or arrays.

❌ Before 1NF

Problem: The Courses column stores multiple values (non‑atomic).

StudentID	Name	Courses
101	Alice	Math, Physics
102	Bob	Chemistry
103	Carol	Math, Biology, CS

Repeating / multi‑value column violates 1NF.

✅ After 1NF

Solution: Each course in its own row — atomic values.

StudentID	Name	Course
101	Alice	Math
101	Alice	Physics
102	Bob	Chemistry
103	Carol	Math
103	Carol	Biology
103	Carol	CS

All values are atomic; each row is unique.

2NF Second Normal Form

Rule: Must be in 1NF, and all non‑key attributes must be fully functionally dependent on the entire primary key (no partial dependency).

❌ Before 2NF

Problem: OrderID + ProductID is the PK, but ProductName depends only on ProductID (partial dependency).

OrderID	ProductID	ProductName	Qty
1001	P01	Laptop	2
1001	P02	Mouse	5
1002	P01	Laptop	1

ProductName repeats for same ProductID.

✅ After 2NF

Solution: Split into two tables — OrderDetails and Products.

📦 OrderDetails

OrderID	ProductID	Qty
1001	P01	2
1001	P02	5
1002	P01	1

🏷️ Products

ProductID	ProductName
P01	Laptop
P02	Mouse

No partial dependencies; each fact stored once.

3NF Third Normal Form

Rule: Must be in 2NF, and no transitive dependency — non‑key attributes must not depend on other non‑key attributes.

❌ Before 3NF

Problem: DeptName depends on DeptID, which is not the PK (EmpID). Transitive dependency: EmpID → DeptID → DeptName.

EmpID	EmpName	DeptID	DeptName
E01	John	D10	Sales
E02	Sarah	D20	Engineering
E03	Mike	D10	Sales

DeptName repeats for same DeptID.

✅ After 3NF

Solution: Separate Employees and Departments.

👤 Employees

EmpID	EmpName	DeptID
E01	John	D10
E02	Sarah	D20
E03	Mike	D10

🏢 Departments

DeptID	DeptName
D10	Sales
D20	Engineering

Transitive dependency removed.

BCNF Boyce‑Codd Normal Form

Rule: A stricter version of 3NF. For every functional dependency X → Y, X must be a superkey. Eliminates anomalies caused by overlapping candidate keys.

❌ Before BCNF

Problem: Professor determines Course, but Professor is not a superkey. Also, Student + Course is a candidate key, but Professor depends on Course.

Student	Course	Professor
S001	Math 101	Dr. Smith
S002	Math 101	Dr. Smith
S001	CS 200	Dr. Jones

Professor depends on Course, but Course is not a superkey.

✅ After BCNF

Solution: Split into Enrollments and CourseProfessors.

📚 Enrollments

Student	Course
S001	Math 101
S002	Math 101
S001	CS 200

👨‍🏫 CourseProfessors

Course	Professor
Math 101	Dr. Smith
CS 200	Dr. Jones

Every determinant is now a superkey.

BCNF vs 3NF: Every BCNF relation is in 3NF, but not every 3NF relation is in BCNF. BCNF handles overlapping candidate keys more strictly.

4NF & 5NF Higher Normal Forms

These forms address multi‑valued and join dependencies. They are less common in practice but important for complex, highly normalized designs.

4NF — Fourth Normal Form

Rule: Must be in BCNF and have no multi‑valued dependencies. A multi‑valued dependency exists when two independent attributes depend on a third.

Before: Student → {Hobbies}, {Languages} (independent)
After: Split into StudentHobbies and StudentLanguages.

5NF — Fifth Normal Form

Rule: Must be in 4NF and have no join dependencies. Also known as Project‑Join Normal Form (PJNF).

Idea: A table is in 5NF if it cannot be decomposed into smaller tables without losing information — all join dependencies are implied by candidate keys.

When to use 4NF / 5NF? Usually for data warehousing or when dealing with complex many‑to‑many relationships. Most applications stop at 3NF or BCNF for practical performance reasons.

Summary

A quick recap of the normal forms and their key requirements.

1NFAtomic values, no repeating groups

2NFNo partial dependencies

3NFNo transitive dependencies

BCNFEvery determinant is a superkey

4NFNo multi‑valued dependencies

5NFNo join dependencies

Key Takeaway

Normalization reduces redundancy and prevents update, insert, and delete anomalies. Start with 1NF, then progress to 3NF / BCNF for most real‑world applications. Higher forms (4NF, 5NF) are used when dealing with complex multi‑valued or join dependencies.