Lecture 7 — Functional Dependencies & Normalization

Functional dependencies, the three anomalies, 1NF → 2NF → 3NF (informal and formal), BCNF including the classic 3NF-but-not-BCNF table, and validation with merits & demerits.

Sections

➡️

Functional Dependencies

X → Y, determinant vs dependent, why Y → X does not follow, and the vocabulary used by every normal form.

⚠️

Why Normalize? Anomalies

The definition, the one-big-table example, the update/insertion/deletion anomalies, and the list of normal forms.

1️⃣

1NF — First Normal Form

Atomic values, no repeating groups, unique rows — each rule with the bad table and the fixed table.

2️⃣

2NF — Second Normal Form

Partial dependency defined on (X, Y, Z, W), the enrolment example, and the three-table decomposition.

3️⃣

3NF — Third Normal Form

Transitive dependency, the Employee/Project example with its anomalies, plus the formal super-key/prime-attribute definition applied twice.

🅱️

BCNF — Boyce-Codd Normal Form

Every determinant must be a super key, the Teacher/Course/Room example, and the classic table that is in 3NF but not in BCNF.

✅

Validation, Merits & Demerits

Lossless join, dependency preservation, normal-form checking, and the benefits vs shortcomings of normalizing.

Quick summary

FD  X → Y : if two rows agree on X they must agree on Y.
   X = determinant, Y = dependent.  X → Y does NOT mean Y → X.
   Student_ID → Name, Program  ✔   ·   Name → Student_ID  ✘ (two students share a name).
   FDs are used to remove redundancy, avoid anomalies, find candidate keys, decompose tables.

ANOMALIES from one big table:  UPDATE (change in many rows) · INSERTION (can't add a course
   with no student) · DELETION (dropping the last row loses the course).

1NF: atomic values · NO repeating groups (Course_1..Course_3) · every row unique (define a PK).
2NF: 1NF + NO PARTIAL dependency (non-key depending on PART of a composite key).
     Single-attribute key ⇒ automatically 2NF.
3NF: 2NF + NO TRANSITIVE dependency (Key → Non-key → Non-key).
     FORMAL: for every X → Y, X is a SUPER KEY or Y is a PRIME attribute.
     Allowed: super→prime · super→non-prime · non-super→PRIME.
     NOT allowed: non-super key → non-prime attribute.
BCNF: for EVERY non-trivial X → Y, X must be a SUPER KEY (stronger than 3NF).
     A table can be in 3NF but NOT in BCNF (non-super key → prime attribute).

VALIDATION: lossless join (R1 ∩ R2 must be a key of R1 or R2) · dependency preservation ·
   normal-form checking.
MERITS: less redundancy · consistency · no update/insert/delete anomalies · integrity.
DEMERITS: more tables · more joins · slower queries · complex design · poor for reporting
   (→ denormalize for analytics).