# Normalization — 1NF, 2NF, 3NF and BCNF with One Worked Example

Source: https://skills101.in/learn/dbms-sql/normalization — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

**Normalization** is the process of splitting tables so that **each fact is stored once**. It removes the problems (anomalies) that repetition causes.

![Steps from an unnormalised table to 1NF (one value per cell), 2NF (no partial dependency), 3NF (no transitive dependency) and BCNF (every determinant is a candidate key)](https://skills101.in/learn-img/dbms-sql/normalization.svg)

## 1. The problem: anomalies

Look at this table of course registrations:

| roll_no | name | course | course_fee | teacher | teacher_phone |
|---|---|---|---|---|---|
| 101 | Asha | DSA | 4999 | Gupta | 98290… |
| 101 | Asha | SQL | 2999 | Rao | 98111… |
| 102 | Ravi | DSA | 4999 | Gupta | 98290… |

- **Update anomaly:** Gupta changes his phone — you must update many rows; miss one and data is inconsistent.
- **Insert anomaly:** you can't add a new course until some student registers for it.
- **Delete anomaly:** if Ravi was the only DSA student and leaves, you lose the DSA fee and teacher too.

## 2. Functional dependency (FD)

**A → B** means: if you know A, you know B. Examples above:
- `roll_no → name`
- `course → course_fee, teacher`
- `teacher → teacher_phone`
- `(roll_no, course)` is the key of the table.

Normal forms are rules about which FDs are allowed.

## 3. First normal form (1NF)

> Every cell holds **one** value; no repeating groups.

✗ `courses = "DSA, SQL"` in one cell → ✓ one row per (student, course), as in the table above. The table above **is** in 1NF.

## 4. Second normal form (2NF)

> 1NF + **no partial dependency**: no non-key column depends on only **part** of a composite key.

The key is `(roll_no, course)`, but `name` depends only on `roll_no`, and `course_fee`, `teacher` depend only on `course`. Split:

- `students(roll_no, name)`
- `courses(course, course_fee, teacher, teacher_phone)`
- `registrations(roll_no, course)`

(A table with a **single-column** key is automatically in 2NF.)

## 5. Third normal form (3NF)

> 2NF + **no transitive dependency**: a non-key column must not depend on another non-key column.

In `courses`, `course → teacher` and `teacher → teacher_phone`, so `teacher_phone` depends on the key only **through** `teacher`. Split:

- `courses(course, course_fee, teacher)`
- `teachers(teacher, teacher_phone)`

Now every fact lives in exactly one place. Gupta's phone is stored **once**.

## 6. BCNF (Boyce–Codd)

> For **every** FD **A → B**, A must be a **candidate key**.

3NF allows one rare exception (when B is part of a key); BCNF removes it. Example: `(student, subject) → teacher` and `teacher → subject` (each teacher teaches one subject). `teacher` is a determinant but not a candidate key → split into `(student, teacher)` and `(teacher, subject)`.

## 7. Summary table

| Form | Rule | Removes |
|---|---|---|
| 1NF | atomic values | multi-valued cells |
| 2NF | no partial dependency | repetition due to part of a composite key |
| 3NF | no transitive dependency | non-key → non-key repetition |
| BCNF | every determinant is a candidate key | remaining dependency anomalies |

## 8. Denormalization

Sometimes, for **read speed** (reports, dashboards), teams deliberately keep some repeated data. This is **denormalization** — a conscious trade-off, not a mistake.

## Common mistakes

- Thinking normalization means "always more tables is better". Stop at 3NF/BCNF for most apps.
- Confusing partial dependency (part of a key) with transitive dependency (through a non-key column).
- Forgetting that 2NF only matters for **composite** keys.

## Quick recap

- Anomalies: update, insert, delete — caused by repetition.
- FD A → B: knowing A tells you B.
- 1NF atomic · 2NF no partial · 3NF no transitive · BCNF every determinant is a key.
- Denormalize only on purpose, for performance.


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/normalization