Database Normalization Concepts

Problem Explain database normalization and the normal forms.

Be ready to discuss

• The purpose: organise a relational schema so each fact is stored exactly once, eliminating redundancy and the anomalies it causes.
• The three anomalies, with an example of each: update (change a fact in one place, miss it in another), insert (can't record a fact without unrelated data), delete (removing a row destroys an unrelated fact).
• 1NF: atomic column values, no repeating groups or arrays crammed into a column.
• 2NF: 1NF plus every non-key column depends on the whole primary key - removes partial dependencies, only relevant for composite keys.
• 3NF: 2NF plus no transitive dependencies - non-key columns depend on the key alone, not on other non-key columns.
• BCNF and beyond: every determinant must be a candidate key; 4NF and 5NF handle multi-valued and join dependencies.
• Functional dependencies and candidate keys as the formal tools for deciding which form a schema is in.
• The read cost: more normalization means more joins, and hot paths can suffer.
• Deliberate denormalization in practice: duplicated columns, summary tables, materialized views, and star schemas in analytics - trading redundancy and write complexity for read speed.