Update Anomaly

Setting the Scene

The same StudentCourse table, same rows as before. Dr. Rao (I1) currently sits in Room 204, and three rows reference him — the enrollments of S1, S2, and S3, all in course C1.

The Failure Scenario

Dr. Rao moves to Room 210. This is one real-world fact changing. But because InstructorOffice is duplicated across every row that mentions I1, fixing it correctly requires updating all three rows:

UPDATE StudentCourse SET InstructorOffice = 'Room 210' WHERE InstructorID = 'I1';

If the application (or a careless analyst running manual SQL) instead does something narrower — say it updates only the row for S1 because that's the row visible on the current screen:

UPDATE StudentCourse SET InstructorOffice = 'Room 210'
WHERE StudentID = 'S1' AND CourseID = 'C1';

...the table now says Dr. Rao's office is Room 210 in S1's row, but still Room 204 in the S2 and S3 rows. The database cannot answer "what is I1's office?" with one consistent value — it depends entirely on which row happens to be read. This is the update anomaly: a single logical fact requires updating multiple physical rows, and any partial update leaves the data self-contradictory.

Why This Happens

Because InstructorOffice is functionally dependent on InstructorID alone (see 21.5), but it is stored inside a table keyed by (StudentID, CourseID), the same InstructorID value appears in N rows — one per enrolled student — so the same office value is physically copied N times. Updating "the fact" really means updating "all N copies of the fact," and a system correctness property (atomicity of one fact) has been turned into an operational multi-row synchronization problem.

Edge Cases

• Even a "correct," fully-qualified UPDATE ... WHERE InstructorID = 'I1' that does update all matching rows is still doing more physical work than necessary (3 row rewrites for 1 logical change) — a properly normalized Instructor table would need exactly 1 row updated.
• This is distinct from the insertion anomaly (which blocks a valid insert) and the deletion anomaly (which causes accidental information loss on delete) — the update anomaly is specifically about inconsistency risk during a modification of an existing fact.
• Concurrent updates make it worse: if two different transactions each update a subset of I1's rows around the same time, a race condition can leave the office value in a genuinely ambiguous state even with correct application logic.

Key Takeaways / Interview Angle

• Q: Define the update anomaly in one line. A single real-world fact change requires updating multiple duplicated rows, and any missed row creates inconsistent, contradictory data.
• Q: How do you tell it apart from data redundancy itself? Redundancy (21.1) is the static condition — the fact is stored many times. The update anomaly is the dynamic consequence — what goes wrong when you try to change that fact later.
• Q: What is the permanent fix? Move InstructorOffice into an Instructor(InstructorID, InstructorName, InstructorOffice) table (3NF, see 21.14), so moving Dr. Rao's office is a single-row UPDATE by InstructorID, with zero risk of partial-update inconsistency.