1. What is Normalization?

Normalization is the process of organizing data in a database to reduce data redundancy and prevent data anomalies.

In simple words:

Normalization means breaking a large table into smaller related tables so that data is stored efficiently and consistently.

Why do we need Normalization?

Imagine we create a college database.

Without Normalization

Student Table

Problems:

1. Data Redundancy

Instructor Amit's phone number is stored multiple times.

Amit -> 999999
Amit -> 999999

If 10,000 students take DBMS, the same information repeats thousands of times.

2. Update Anomaly

Suppose Amit changes his phone number. You need to update every row.

If one row is missed, inconsistent data exists.

3. Insert Anomaly

Suppose a new instructor joins but doesn't have any students yet.

Where do you store:

Instructor = John
Phone = 555555

You cannot insert it cleanly because Student_ID is required.

4. Delete Anomaly

Suppose the last student drops the DBMS course.

Deleting that student also removes:

DBMS course information
Instructor Amit information

because everything was stored together.

Normalization solves these problems.

Normal Forms

Normalization happens in stages called Normal Forms.

For interviews: 1NF, 2NF, 3NF, and BCNF are must know.

1NF

A table is in 1NF if:

1. Every column contains atomic values.
2. There are no repeating groups.
3. Each row is unique.

Not 1NF

Problem: Phone Numbers contains multiple values.

Convert to 1NF

Now every cell has one value.

2NF

Before understanding 2NF, we need Functional Dependency.

Functional Dependency

It means:

One attribute determines another attribute.

Represented as:

A -> B

Meaning: if we know A, we can find B.

Example:

Student_ID -> Student_Name

because Student_ID uniquely identifies Student_Name.

2NF Rules

A table is in 2NF if:

1. It is already in 1NF.
2. No partial dependency exists.

What is Partial Dependency?

It happens when a non-key attribute depends on only part of a composite key.

Example:

Primary Key:

(Student_ID, Course_ID)

Dependencies:

Student_ID -> Student_Name
Course_ID -> Course_Name

Only part of the key determines those columns. This is a partial dependency.

Solution: split the table.

Student

Course

Enrollment

Now there is no duplicate data and no partial dependency.

3NF

A table is in 3NF if:

1. It is already in 2NF.
2. No transitive dependency exists.

What is Transitive Dependency?

When a non-key attribute depends on another non-key attribute.

Example:

Primary Key:

Emp_ID

Dependencies:

Emp_ID -> Dept_ID
Dept_ID -> Dept_Name

Therefore:

Emp_ID -> Dept_Name

indirectly. This is transitive dependency.

Solution: split into Employee and Department tables.

Employee

Department

Now the relationship is handled using a foreign key.

BCNF

BCNF is a stronger version of 3NF.

Rule:

Every determinant must be a candidate key.

Meaning:

If A -> B, then A should be a candidate key.

If a determinant is not a candidate key, a BCNF violation exists.

Normalization Summary

Interview One-Liner

Q: Why do we normalize databases?

Normalization is used to reduce redundancy, maintain data consistency, and avoid insertion, update, and deletion anomalies by organizing data into multiple related tables.

Key Takeaway

Normalization is good because it keeps data consistent and avoids repeated information. But in real systems, excessive normalization can make reads slower because data must be joined from many tables. That is why denormalization is sometimes used intentionally for performance.