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

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

1. The problem: anomalies

Look at this table of course registrations:

• 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

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.