Deletion Anomaly

Setting the Scene

Look again at the S2 row for course C3:

Ravi (S2) is the only student currently enrolled in Computer Networks (C3), taught by Dr. Singh (I3).

The Failure Scenario

Suppose Ravi drops the course:

DELETE FROM StudentCourse WHERE StudentID = 'S2' AND CourseID = 'C3';

This single DELETE was meant to record one fact: "Ravi is no longer enrolled in C3." But because that row was the only row mentioning C3, the delete also silently erases:

• The fact that course C3 ("Computer Networks") exists at all.
• The fact that I3 ("Dr. Singh") teaches, and that his office is Room 118.

The database has no other row anywhere that stores "Computer Networks" or "Dr. Singh, Room 118," so once this row is gone, so is that information — even though Computer Networks the course and Dr. Singh the instructor both still exist in the real world. This is the deletion anomaly: removing one legitimate fact (an enrollment) accidentally destroys other, unrelated facts (a course's and an instructor's existence) purely because they happened to be stored in the same row.

Why This Happens

The table conflates three logically independent facts into one row: "this student is enrolled," "this course exists," and "this instructor exists/works here." Deleting an enrollment is a normal, frequent operation (students drop courses constantly); it should never have the power to erase course or instructor master data. That power only exists because course/instructor data isn't kept in its own relation with its own lifecycle.

Edge Cases

• The anomaly only manifests when the deleted row is the last row referencing that CourseID/InstructorID. If two other students were still enrolled in C3, deleting Ravi's row would just remove his enrollment and C3/Dr. Singh's data would survive in the other rows — but this is fragile: the schema's correctness by accident depends on there always being at least one other row, which is not guaranteed.
• This is different from an intentional DELETE FROM Course WHERE CourseID = 'C3' in a properly normalized design — that is a deliberate, explicit operation on the Course entity, not a side effect of an unrelated enrollment deletion.
• Cascading deletes (ON DELETE CASCADE) in a normalized design are a controlled, explicit version of "deleting one thing removes related things" — the anomaly here is the opposite: an uncontrolled, implicit loss of unrelated master data.

Key Takeaways / Interview Angle

• Q: Define the deletion anomaly in one line. Deleting a row that represents one fact (an enrollment) unintentionally destroys other, independent facts (course/instructor existence) because they were never given their own table.
• Q: How is it different from the insertion anomaly? Insertion anomaly blocks you from adding an independent fact; deletion anomaly destroys an independent fact as an unwanted side effect of removing something else.
• Q: What's the permanent fix? Separate Course and Instructor into their own relations (2NF/3NF, see 21.13/21.14) so that deleting an Enrollment row can never touch Course or Instructor rows — each entity's lifecycle is now independent.