Chapter 12 · Normalization and Practical Schema Design
Functional Dependencies and Update Anomalies
Normalization begins by asking what facts determine other facts. Functional dependencies make those assumptions explicit, while anomalies reveal the operational cost of storing unrelated facts together.
Learning outcomes
Express functional dependencies using determinant and dependent attribute sets.
Compute simple attribute closures and recognize candidate keys.
Distinguish trivial, partial, full, and transitive dependencies.
Identify insertion, update, and deletion anomalies in a wide relation.
Use SQL profiling queries to test whether observed data violates a proposed dependency.
Facts, determinants, and dependencies
A functional dependency \(X \rightarrow Y\) means that any two valid rows agreeing on attributes \(X\) must also agree on attributes \(Y\). The dependency is a rule about every legal database state—not merely a coincidence in today’s sample.
Determinant
The attribute set on the left side. It identifies or determines another fact.
Dependent
The attribute set whose value is fixed by the determinant.
Candidate key
A minimal determinant whose closure contains every attribute in the relation.
Closure
All attributes that can be derived from X using the known dependencies.
Reusable wide-table laboratory
This intentionally redundant relation stores student, course, instructor, offering, and enrollment facts together. It is useful for detecting dependencies and anomalies before decomposition.
DROP TABLE IF EXISTS enrollment_sheet;CREATE TABLE enrollment_sheet ( student_id INTEGER NOT NULL, student_name TEXT NOT NULL, student_email TEXT NOT NULL, course_id INTEGER NOT NULL, course_code TEXT NOT NULL, course_title TEXT NOT NULL, instructor_id INTEGER NOT NULL, instructor_name TEXT NOT NULL, term_code TEXT NOT NULL, grade TEXT, PRIMARY KEY (student_id, course_id, term_code)) STRICT;INSERT INTO enrollment_sheet VALUES(101, 'Ava Chen', 'ava@example.edu', 501, 'SQL-101', 'SQL Foundations', 31, 'Nadia Rahimi', '2026-S1', 'A'),(102, 'Liam Ortiz','liam@example.edu', 501, 'SQL-101', 'SQL Foundations', 31, 'Nadia Rahimi', '2026-S1', 'B'),(101, 'Ava Chen', 'ava@example.edu', 502, 'DB-201', 'Database Design', 32, 'Omar Haddad', '2026-S1', NULL),(103, 'Mina Park', 'mina@example.edu', 502, 'DB-201', 'Database Design', 32, 'Omar Haddad', '2026-S1', 'A-'),(103, 'Mina Park', 'mina@example.edu', 503, 'DE-220', 'Data Engineering', 31, 'Nadia Rahimi', '2026-S2', NULL);Dependencies in the enrollment sheet
student_id -> student_name, student_emailcourse_id -> course_code, course_titleinstructor_id -> instructor_name(course_id, term_code) -> instructor_id(student_id, course_id, term_code) -> gradeThe composite key identifies an enrollment fact, but many non-key attributes depend on only part of that key or on another non-key attribute. That redundancy is the source of anomalies.
| Dependency type | Example | Why it matters |
|---|---|---|
| Trivial | (student_id, course_id) → student_id | The right side is already contained in the determinant. |
| Full | (student_id, course_id, term_code) → grade | Removing any determinant attribute loses the guarantee. |
| Partial | student_id → student_name | A non-key fact depends on part of the composite enrollment key. |
| Transitive | course_id → instructor_id → instructor_name | A non-key fact is reached through another non-key determinant. |
Attribute closure and candidate keys
Start with \(K=\{student\_id, course\_id, term\_code\}\). Repeatedly add attributes implied by dependencies:
Because the closure contains all attributes and no proper subset determines the enrollment grade, K is a candidate key.
closure := Xrepeat for each dependency A -> B if A is contained in closure add B to closureuntil closure no longer changesArmstrong’s inference rules
| Rule | Form | Interpretation |
|---|---|---|
| Reflexivity | If Y ⊆ X, then X → Y | A set determines its own subsets. |
| Augmentation | If X → Y, then XZ → YZ | Adding the same context preserves a dependency. |
| Transitivity | If X → Y and Y → Z, then X → Z | Dependencies can be chained. |
| Union | If X → Y and X → Z, then X → YZ | Combine attributes determined by the same left side. |
| Decomposition | If X → YZ, then X → Y and X → Z | Split a multi-attribute right side. |
The three update anomalies
When several independent facts share one row grain, every write must coordinate redundant copies.
| Anomaly | Example | Consequence |
|---|---|---|
| Update | Rename Nadia in every enrollment row. | Missing one row creates contradictory instructor names. |
| Insertion | Add a new course before its first student enrolls. | The course cannot be represented without inventing enrollment data. |
| Deletion | Delete the last enrollment in DE-220. | The only stored copy of the course and instructor assignment disappears. |
Profile proposed dependencies with SQL
SELECT student_id, COUNT(DISTINCT student_name) AS distinct_names, COUNT(DISTINCT student_email) AS distinct_emailsFROM enrollment_sheetGROUP BY student_idHAVING COUNT(DISTINCT student_name) > 1 OR COUNT(DISTINCT student_email) > 1;SELECT course_id, COUNT(DISTINCT course_code) AS distinct_codes, COUNT(DISTINCT course_title) AS distinct_titlesFROM enrollment_sheetGROUP BY course_idHAVING COUNT(DISTINCT course_code) > 1 OR COUNT(DISTINCT course_title) > 1;Zero returned rows means the current sample is consistent with the dependency. The dependency must still come from business meaning, authoritative rules, or an enforced constraint.
Inject and detect a contradiction
UPDATE enrollment_sheetSET student_email = 'ava.changed@example.edu'WHERE student_id = 101 AND course_id = 502;SELECT student_id, student_name, student_emailFROM enrollment_sheetWHERE student_id = 101ORDER BY course_id;The database now stores two email values for the same student identifier. A separate student table with one row per student makes this contradiction structurally impossible.
Checkpoint
Reason about dependencies
- Why is a functional dependency stronger than a pattern in sample data?
- What makes a candidate key minimal?
- Which dependency in the wide table is partial?
- How can deleting one enrollment erase an unrelated fact?
- What does an empty SQL violation query prove—and what does it not prove?
Review the answers
Dependencies describe every legal state. A candidate key determines all attributes and has no redundant attribute. student_id → student_name is partial relative to the composite enrollment key. Deleting the last row can remove the only copy of a course fact. An empty profile query supports but does not establish a business dependency.
Summary and references
- Functional dependencies state which facts determine other facts.
- Closures help identify superkeys and candidate keys.
- Partial and transitive dependencies create repeated facts.
- Redundancy causes insertion, update, and deletion anomalies.
- SQL profiling can reveal violations, but semantics define the rule.