Database Normalization — Why?

Chapter 7 · normalization is a systematic process of organizing data in a relational database to reduce redundancy and improve data integrity.

Definition

In simple words, normalization means dividing a large table into smaller related tables so that data is stored only where it logically belongs.

Formal definition. Database normalization is the process of structuring relational database tables based on functional dependencies and keys, to minimize redundancy and avoid data anomalies such as insertion, update and deletion anomalies.

The problem — one big table

Suppose a university stores student and course information in one table:

Student_ID	Student_Name	Program	Course_ID	Course_Title	Instructor
101	Rahim	CSE	CSE101	Database	Dr. Hasan
101	Rahim	CSE	CSE102	Programming	Dr. Karim
102	Karim	EEE	CSE101	Database	Dr. Hasan

This table has repeated data. For example:

Rahim and CSE are repeated for Student_ID 101.
Database and Dr. Hasan are repeated for Course_ID CSE101.

This repetition of data leads to several problems!

The three anomalies

Anomaly	Problem in this table
Update anomaly	If Dr. Hasan’s name changes, it must be updated in many rows. If one row is missed, the database becomes inconsistent.
Insertion anomaly	A new course cannot be inserted unless at least one student is enrolled in that course.
Deletion anomaly	If Student_ID 102 drops CSE101, information about CSE101 may be lost if that was the only row storing it.

Normalization solves these problems.

Types of normal form

Form	Name	Rule in one line
1NF	First Normal Form	Atomic values · no repeating groups · unique rows
2NF	Second Normal Form	1NF + no partial dependency
3NF	Third Normal Form	2NF + no transitive dependency
BCNF	Boyce-Codd Normal Form	Every determinant is a super key
4NF	Fourth Normal Form	No multi-valued dependencies
5NF	Fifth Normal Form	No join dependencies

In most academic and practical database design, 1NF, 2NF, 3NF and BCNF are the most important — and they are the four this course examines.

Unnormalized table (UNF)

repeating groups, non-atomic cells

make atomic

1NF — atomic values, no repeating groups, unique rows

split by FD

2NF — remove partial dependencies on part of a composite key

split by FD

3NF — remove transitive dependencies (non-key → non-key)

split by FD

BCNF — every determinant must be a super key

Each step is a decomposition guided by the functional dependencies.

Next: First Normal Form →