Concept 1 / 8
Why Good Schema Design Matters: Update Anomalies
Combining attributes from distinct real-world entities into a single relation causes three classes of problems, all rooted in data redundancy.
The EMP_DEPT anti-pattern — employee and department attributes crammed into one table:
| Ename | Ssn | Bdate | Dnumber | Dname | Dmgr_ssn |
|---|---|---|---|---|---|
| Smith | 123 | 1965 | 5 | Research | 333 |
| Wong | 333 | 1955 | 5 | Research | 333 |
| Zelaya | 999 | 1968 | 4 | Admin | 987 |
Department info (Dname, Dmgr_ssn) is repeated for every employee in that department — this is the redundancy.
Insert anomaly (new employee): Must correctly copy all department attribute values or risk inconsistency. If no department is assigned yet, must use NULL for every department column.
Insert anomaly (new department): Cannot insert a department with no employees without NULL-ing out the primary key (Ssn) — violates entity integrity.
Deletion anomaly: Deleting the last employee in a department destroys all knowledge of that department.
Modification anomaly: Renaming a department requires updating every employee tuple in that department — miss one and the database becomes inconsistent.
Root cause: The schema mixes two independent concepts (employees and departments) into one relation. The fix is normalization — guided by functional dependencies.