Normalisation

Словарь

English

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}

Практика

Changing a course title in 14 enrollment rows is —

Сложность 1/5 · 100 монет + небольшой бонус за первое завершение сегодня

English

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 → title is part-key, title → dept → building transitive. Split into Course, Dept, Enroll — each fact once, each anomaly gone.

{exercise:uni-cs0803-forms}

Практика

A part-key dependency breaks —

Сложность 1/5 · 100 монет + небольшой бонус за первое завершение сегодня

English

Room(building, room, capacity, projector_type) with all rooms in a building sharing one projector type: building → projector_type is a non-key determinant — split Building(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}

Практика

A non-key column determining another breaks —

Сложность 1/5 · 100 монет + небольшой бонус за первое завершение сегодня