Well-Structured Relations & the Three Anomalies

The test for a good relation

A well-structured relation contains *minimal redundancy* and lets users insert, modify, and delete rows without errors or inconsistencies. The one-line test: *can every fact be changed in exactly one place?* If updating one salary requires touching many rows, the relation is not well structured.

Redundancy is the disease; the three anomalies are its symptoms. They are the entire *reason normalization exists*.

Worked example: one badly-structured table

Consider an EMP_COURSE table that crams employee facts and course-completion facts together, keyed on (EmpID, CourseTitle):

EmpID  Name      DeptName     Salary   CourseTitle  DateCompleted
100    Simpson   Marketing    48000    SPSS         6/19
100    Simpson   Marketing    48000    Surveys      10/19
140    Smith     Accounting   52000    Tax Acc      12/19
110    Lucero    Info Systems 43000    SPSS          1/19
110    Lucero    Info Systems 43000    C++           3/19

Employee 100's name, department, and salary are repeated on every course row. That redundancy spawns three anomalies:

- Insertion anomaly — you *cannot record a new employee* who has taken no course yet, because the key needs a CourseTitle. You are forced to invent fake data or wait. - Deletion anomaly — delete the only row for employee 140 (the *Tax Acc* row) and you lose both the fact that 140 exists and the existence of the *Tax Acc* course. One delete, two facts destroyed. - Modification (update) anomaly — give employee 100 a raise and you must update the salary in every row for that employee. Miss one and the database now reports two different salaries for the same person.

Why this matters

Each anomaly traces back to the same root cause: facts about two different things (employees and courses) are stored in one relation. Normalization is the formal cure — split the table so each fact lives in exactly one place. EMPLOYEE1 (one row per employee) is well structured; the merged EMP_COURSE above is not.