What are insertion, update and deletion anomalies?
They are problems in a badly designed table where redundant data forces you to add dummy values, edit many rows for one change, or lose unrelated facts when deleting a row. Normalization removes them.
Explain it simply
Imagine one register storing students and their department head's name on every line. Three problems appear: you cannot record a new department until a student joins it, changing a head means editing hundreds of lines, and deleting the last student of a department also erases the fact that the department exists.
Example
Table student_dept(roll_no, name, dept, hod):
| roll_no | name | dept | hod |
|---|---|---|---|
| 1 | Asha | CSE | Dr. Rao |
| 2 | Ravi | CSE | Dr. Rao |
| 3 | Neha | ECE | Dr. Iyer |
- Insertion anomaly: to add the new department MECH with its head, you need a student row, or you must insert NULL student data.
- Update anomaly: if Dr. Rao is replaced, update every CSE row. Miss one and the data contradicts itself.
- Deletion anomaly: deleting Neha, the only ECE student, also deletes Dr. Iyer and the ECE department.
Fix: split into students(roll_no, name, dept) and departments(dept, hod). Now each fact is stored once.
What to say in the interview
"Anomalies come from redundancy, where one table mixes facts about different things. I would decompose the table so each fact lives in one place, which is the goal of normalization."
Common mistakes
- Mixing up the three: insertion is about adding, update about changing many copies, deletion about losing other facts.