Normalization
Decomposing schemas to remove redundancy. Normal forms are tests, not decorations.
Level: intermediate
What is it?
Normalization organizes relations to reduce undesirable redundancy using functional dependencies and normal-form rules. It decomposes a wide relation into related tables while aiming to preserve information and the dependencies that matter.
Why does it matter?
Redundant facts create update anomalies: changing an address in one row but not another leaves conflicting truths. A normalized design stores each fact in a place that reflects its dependencies, while joins reconstruct combined views when needed.
Mental model
Ask what determines what. If a fact is repeated in several rows because it belongs to a different entity, model that dependency explicitly instead of copying the fact everywhere.
How it works
- First normal form requires attributes to hold values from their intended domains rather than nested repeating groups in the classic model.
- Second normal form rules out non-prime attributes depending on only part of a candidate key, relative to the functional dependencies.
- Third normal form constrains nontrivial dependencies on non-superkeys when the right-hand side is non-prime.
- BCNF requires every nontrivial functional dependency's determinant to be a superkey.
- Decomposition should be assessed for losslessness and dependency preservation; normalization is not merely splitting tables until they look small.
Steps
- A table stores student_id, course_id, student_name, course_name, grade with key (student_id, course_id).
- student_name depends only on student_id, while course_name depends only on course_id.
- Move student and course facts to their own relations and keep the enrollment fact in Enrollment.
- Use keys and foreign keys to preserve identity and relationships across the decomposition.
Real-world usage
- Designing schemas that avoid insert, update, and delete anomalies.
- Explaining 1NF, 2NF, 3NF, and BCNF exam problems.
- Deciding when a reporting model may intentionally denormalize for performance.
Common mistakes
- Treating every repeated value as automatically wrong; repetition can be legitimate depending on dependencies and requirements.
- Using the number of columns or tables as the definition of normality.
- Ignoring lossless joins and dependency preservation when decomposing relations.
Interview questions
What anomalies does normalization try to prevent?
Insertion, update, and deletion anomalies caused by storing multiple independent facts redundantly in one relation.
Is every normalized schema faster?
No. Normalization improves integrity and reduces redundancy, but some read workloads may need more joins; performance should be measured and denormalization justified by evidence.