Normalisation
Introduced
Vocabulary
| English | Meaning |
|---|---|
| insert anomaly | insert anomaly |
| update anomaly | update anomaly |
| delete anomaly | delete anomaly |
| functional dependency | functional dependency |
| second normal form | second normal form |
| third normal form | third normal form |
| Boyce-Codd | Boyce-Codd |
The anomalies
- A denormalised table invites three: an insert anomaly (cannot add a course with no student), an update anomaly (change one fact in many rows), a delete anomaly (lose the fact with the last row carrying it). The disease is the repeated fact.
{exercise:uni-cs0803-anomalies}
Practice
Changing a course title in 14 enrollment rows is —
Difficulty 1/5 · 100 coins + a small bonus on first completion today
One fact stored many times: the update anomaly.
Dependencies drive the split
- A functional dependency $A \to B$: each A-value determines one B-value. Second normal form removes part-key dependencies; third normal form removes transitive ones; Boyce-Codd makes every determinant a key.
Enroll(student, course, title, dept, building):course → titleis part-key,title → dept → buildingtransitive. Split into Course, Dept, Enroll — each fact once, each anomaly gone.
{exercise:uni-cs0803-forms}
Practice
A part-key dependency breaks —
Difficulty 1/5 · 100 coins + a small bonus on first completion today
2NF removes dependencies on part of the key; 3NF the transitive ones.
When to stop
Room(building, room, capacity, projector_type)with all rooms in a building sharing one projector type:building → projector_typeis a non-key determinant — splitBuilding(building, projector_type).- A stored running total is derivable, but recomputing per view can cost more than the anomaly risk: controlled denormalisation with a refresh trigger is legitimate once the normal form is understood. Normalise to reason; denormalise on evidence.
{exercise:uni-cs0803-stop}
Practice
A non-key column determining another breaks —
Difficulty 1/5 · 100 coins + a small bonus on first completion today
A transitive chain through a non-key determinant is the 3NF violation.