5.11NF, 2NF, 3NF and BCNF
The idea
Normal forms grade how much redundancy FDs allow. 2NF: no partial dependency. 3NF: left side is a super key or right side is prime. BCNF: left side is always a super key.
Think of it like this: Tidying a cupboard in stages: first no duplicate items, then every item stored under the right label, then no exceptions at all.
Why it exists
Everything in normalisation is stated through candidate keys and prime attributes.
How it works
- Find the candidate keys and prime attributes (parts of any key).
- BCNF: every non-trivial X → A has X a super key.
- 3NF: same, but allowed if A is prime.
- 2NF: no non-prime attribute depends on a proper part of a key.
- BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF.
Worked example: R(A, B, C, D), F = {AB → C, C → D, D → A}.
Keys AB, BC, BD; every attribute is prime, so the relation is in 3NF. C → D has a non-key left side, so it is not in BCNF.
Now play with it
- →Load each preset in the checker and look at the verdict column.
- →Change an FD to make the relation BCNF.
| FD | Left side a super key? | Right side prime? | Partial dependency? | Verdict |
|---|---|---|---|---|
| AB → C | yes | yes | no | fine for BCNF |
| C → D | no | yes | no | breaks BCNF, ok for 3NF |
| D → A | no | yes | no | breaks BCNF, ok for 3NF |
BCNF: every non-trivial FD has a super key on the left. 3NF: or the right side is prime. 2NF: no non-prime attribute depends on part of a key.
| Normal form | Rule | What it removes |
|---|---|---|
| 1NF | atomic attribute values | repeating groups, sets in a cell |
| 2NF | 1NF and no partial dependency on a candidate key | redundancy from part of a key |
| 3NF | X is a super key or the right side is prime | transitive dependencies through non-key attributes |
| BCNF | every non-trivial X → A has X a super key | all FD-based redundancy |
Check yourself
A relation where every attribute is prime is always in at least
Exam notes (the dense version)
1NF: attributes are atomic. 2NF: 1NF + no non-prime attribute depends on a proper part of a candidate key (partial dependency). 3NF: for every non-trivial X → A, X is a super key or A is prime. BCNF: for every non-trivial X → A, X is a super key. BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF. A relation whose keys are all single attributes is automatically in 2NF; a relation with only two attributes is always in BCNF.
Previous year questions: Normal Forms & Decomposition (11)
PYQs are grouped by topic, so this is the full set for Normal Forms & Decomposition; try the ones that match this subtopic. Open all 11 on the topic page.