Normalization
Normalization decomposes tables to reduce redundancy and remove insertion, update and deletion anomalies.
Functional Dependency
X → Y means: if two tuples agree on X, they must agree on Y. Armstrong's axioms: reflexivity (if Y ⊆ X then X → Y), augmentation (X → Y ⇒ XZ → YZ) and transitivity (X → Y, Y → Z ⇒ X → Z).
Normal Forms
| Normal form | Condition |
|---|---|
| 1NF | All attribute values are atomic (no repeating groups or multivalued cells) |
| 2NF | 1NF + no partial dependency (no non-prime attribute depends on part of a candidate key) |
| 3NF | 2NF + no transitive dependency: for every X → A, X is a super key or A is prime |
| BCNF | For every non-trivial X → A, X is a super key |
| 4NF | BCNF + no non-trivial multivalued dependencies |
| 5NF | No join dependencies (lossless decomposition is not possible further) |
Worked Example
R(roll, course, name, fee) with FDs: roll → name, course → fee. Key = {roll, course}.
roll → name : name depends on PART of the key → partial dependency → not 2NF
course → fee : partial dependency again
Decompose into 2NF:
Student(roll, name)
Course(course, fee)
Enroll(roll, course)
All three are also in 3NF and BCNF.Properties of a Good Decomposition
- Lossless join: R1 ∩ R2 → R1 or R1 ∩ R2 → R2.
- Dependency preservation: all original FDs can be checked without joining.
Trade-offA 3NF decomposition can always be lossless and dependency-preserving. BCNF is always lossless but may lose dependency preservation.
Track your progress through the DBMS course