What are insertion, update, and deletion anomalies?
Interview preparation resource from Gate Smashers.
Insertion, update, and deletion anomalies are problems that arise when multiple independent facts are stored together in one poorly designed table. They cause unnecessary restrictions, repeated updates, or accidental loss of information. For example, in a StudentCourse table that stores Student, Course, and Teacher, course and teacher information get repeated across many rows. The three common anomalies are: • Insertion anomaly: you cannot store one fact without also having unrelated data (e.g., you may be unable to add a new course until at least one student enrolls). • Update anomaly: the same fact appears in many rows, so changing a teacher’s name requires updating every repeated row or data becomes inconsistent. • Deletion anomaly: deleting a row to remove one fact can accidentally remove another fact (e.g., deleting the last student enrolled in a course may also remove the only stored information about that course and its teacher). Normalization reduces these anomalies by separating independent facts into related tables.
Overview and motivating example
Insertion, update, and deletion anomalies are problems caused by storing multiple independent facts in one poorly designed table. They lead to unnecessary restrictions, repeated updates, or accidental loss of information.
A common motivating example is a StudentCourse table that contains Student, Course, and Teacher. If the same course is taken by many students, the course and teacher information is repeated across many rows, which creates the conditions for the three anomalies described below.
- Table example: StudentCourse table with Student, Course, and Teacher — course and teacher details repeated across rows.
Insertion anomaly
An insertion anomaly occurs when you cannot store one fact without also having unrelated data. The table design forces you to include data that may not yet exist or is irrelevant to the fact you want to record.
- Example: You may be unable to add a new course until at least one student enrolls in it.
Update anomaly
An update anomaly happens when the same fact appears in many rows. Changing that fact requires updating every repeated row; missing even one update creates inconsistent data across the table.
- Example: If a teacher's name changes, every row containing that teacher must be updated. Missing a row yields inconsistent data.
Deletion anomaly
A deletion anomaly arises when deleting a row to remove one fact inadvertently removes another independent fact because they were stored together in the same row.
- Example: Deleting the last student enrolled in a course may also remove the only stored information about that course and its teacher.
How normalization helps
Normalization reduces insertion, update, and deletion anomalies by separating independent facts into related tables. By storing each fact in the appropriate relation (for example: Students, Courses, Teachers, and Enrollment), repetition is minimized and the anomalies are avoided.