Roadmap › SQL Server & T-SQL › Module: Database Design
Lessons in this course (19)
2. Normalisation (1NF → BCNF) and denormalisation
Basic★ Must knowLesson 2 of 19
| Form | Rule | Fix example |
|---|---|---|
| 1NF | Atomic values, no repeating groups | Phones = '98..,99..' → separate CustomerPhones rows |
| 2NF | 1NF + no partial dependency on part of a composite key | OrderItems(OrderId, ProductId, ProductName) → move ProductName to Products |
| 3NF | 2NF + no transitive dependency (non-key → non-key) | Employees(DeptId, DeptName) → move DeptName to Departments |
| BCNF | Every determinant is a candidate key | Stricter 3NF for overlapping candidate keys |
| 4NF / 5NF | No multi-valued / join dependencies | Rare in interviews |
Denormalisation — deliberately adding redundancy (e.g. storing OrderTotal, reporting tables, star schemas) to speed reads at the cost of update complexity. OLTP systems: normalise to 3NF. Data warehouses: denormalised star schemas (facts + dimensions).