Module 10: Normalization

Spot redundancy and anomalies, find functional dependencies and keys, and normalise to 3NF and BCNF.

1. Redundancy and the three anomalies: Redundancy stores one fact many times, which causes update, insert and delete anomalies; normalization stores each fact once.
2. Functional dependencies, closure and keys: X → Y: same X, same Y. Closure X⁺ finds what X decides; a minimal X with X⁺ = R is a candidate key. Check an FD in data with GROUP BY X HAVING COUNT(DISTINCT Y) 1.
3. 1NF, 2NF and 3NF: 1NF: atomic cells. 2NF: no non-prime attribute depends on part of a key. 3NF: for every X → A, X is a superkey or A is prime.
4. BCNF, lossless joins and when to denormalise: BCNF: every determinant is a superkey. A split is lossless when the common columns are a key of one side; 3NF always keeps dependencies, BCNF may not. Denormalise only on purpose.

Ends with a 14-question checkpoint (80% passes) and a project: Normalise the hostel canteen sheet.