Normalisation

מילון מונחים

English	משמעות
insert anomaly	A design problem in which adding one fact requires adding unrelated or unavailable facts.
update anomaly	A design problem in which one fact is repeated and must be changed consistently in several rows.
delete anomaly	A design problem in which removing one fact unintentionally removes another needed fact.
functional dependency	A constraint under which each value of one attribute set determines one value of another attribute set.
second normal form	First normal form with no non-prime attribute depending on only part of any candidate key.
third normal form	A relation where every nontrivial functional dependency has a superkey determinant or a prime right-hand attribute.
Boyce-Codd	A normal form requiring the determinant of every nontrivial functional dependency to be a superkey.

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 מטבעות + בונוס קטן בהשלמה הראשונה היום