Module 16 · Normalization · Lesson 2 of 4
1NF, 2NF and 3NF
The three normal forms every engineer should be able to apply: the update, insert and delete anomalies that redundancy causes, atomic values for 1NF, removing partial dependencies for 2NF and transitive ones for 3NF.
Why it matters
A table that stores the same fact in many rows will eventually disagree with itself. Normal forms are the checklist for removing that redundancy, and 1NF to 3NF is where nearly all of the practical value lies. It is one of the most frequently asked DBMS topics, usually as "normalize this table", and doing it fluently also explains schema review comments like "this column belongs in another table".
The idea
Take a table that tracks enrolments with everything in one place:
| roll_no | name | dept_id | dept_name | subject_id | subject_title | grade |
|---|---|---|---|---|---|---|
| 101 | Asha | D1 | Computer Science | C10 | Databases | A |
| 101 | Asha | D1 | Computer Science | C20 | Networks | B |
| 102 | Ravi | D2 | Electrical | C10 | Databases | B |
Redundancy causes three anomalies. Update: rename the Databases subject and you must change every enrolment row; miss one and the data disagrees. Insertion: you cannot record a new subject until someone enrols in it, because the key needs a roll number. Deletion: if the last student in a subject drops it, the subject's title disappears too. Normalization removes each source of redundancy by splitting the table so every fact is stored once.
First normal form
A relation is in 1NF when every attribute holds a single atomic value: no lists, no repeating groups such as phone1, phone2, phone3, and no nested tables. A row like (101, "Asha", "C10, C20") breaks 1NF; the fix is one row per subject, or a separate table for the repeated item. 1NF is the foundation, because the higher forms reason about individual values.
Second normal form
A relation is in 2NF when it is in 1NF and no non-prime attribute depends on only part of a candidate key (no partial dependencies). With key (roll_no, subject_id), name and dept_id depend on roll_no alone, and subject_title depends on subject_id alone. Split those out:
- 1
Student(roll_no, name, dept_id, dept_name)
Everything that depends on roll_no alone. - 2
Subject(subject_id, subject_title)
Everything that depends on subject_id alone. - 3
Enrolment(roll_no, subject_id, grade)
What depends on the whole key.
A relation whose candidate keys are all single attributes is automatically in 2NF, since there is no "part" of the key to depend on.
Third normal form
A relation is in 3NF when it is in 2NF and no non-prime attribute depends transitively on a key through another non-prime attribute. Formally, for every non-trivial dependency X → A, either X is a super key or A is prime. In Student, roll_no → dept_id → dept_name: dept_name depends on the key only through dept_id. Split again: Student(roll_no, name, dept_id) and Department(dept_id, dept_name).
Checking a table quickly
| Ask | If yes, violates | Fix |
|---|---|---|
| Any cell holds a list or repeating columns? | 1NF | One row per value, or a child table |
| Composite key, and some non-key column depends on part of it? | 2NF | Move those columns to a table keyed by that part |
| A non-key column depends on another non-key column? | 3NF | Move it to a table keyed by its determinant |
A handy summary: in 3NF, every non-key attribute depends on "the key, the whole key, and nothing but the key".
Trade-offs
Normalizing removes anomalies and makes each fact live in one place, at the cost of more tables and joins at read time. Read-heavy systems sometimes denormalize deliberately (store dept_name on the student row, keep a counter column), as the System Design track's denormalization lesson discusses; that is a conscious trade with a plan to keep copies in sync, not a substitute for knowing the normalized design.
In an interview
Work it aloud: "The key is (roll_no, subject_id). Student details depend only on roll_no and subject title only on subject_id: partial dependencies, so split for 2NF. dept_name depends on dept_id, a non-key: transitive, so split again for 3NF. Four tables, and joins recover the original." Naming the anomaly each step removes is what makes the answer complete.
Check yourself
Orders(order_id, customer_id, customer_city, order_date) with key order_id and the dependency customer_id → customer_city. Which normal form does it violate, and why?