A situation where a change in data causes inconsistency or lose information.
Insertion Anomaly
Occurs when certain data cannot be inserted into a database without the presence of other, unrelated data.
Example:
Cannot add a new student unless they are enrolled in a course, because student and course details are stored in the same table.
Before
| StudentID | Student | CourseID | Course |
|---|---|---|---|
| 1 | Ann | DB | Databases |
| 2 | Bob | OS | Operating Systems |
After
| StudentID | Student | CourseID | Course |
|---|---|---|---|
| 1 | Ann | DB | Databases |
| 2 | Bob | OS | Operating Systems |
| 3 | Cathy | NULL | NULL |
CourseID is part of the key, so it cannot be NULL. Cathy cannot be stored.
Underlined columns form the primary key.
Update Anomaly
Happens when data redundancy causes inconsistent updates across records.
Example:
If an instructor’s department name changes, it must be updated in multiple rows; missing one row leads to inconsistent data.
Before
| CourseID | Instructor | Dept |
|---|---|---|
| DB | Lee | CS |
| OS | Lee | CS |
| NW | Kim | EE |
After
| CourseID | Instructor | Dept |
|---|---|---|
| DB | Lee | Computing |
| OS | Lee | CS |
| NW | Kim | EE |
1 row was missed. Lee now belongs to 2 departments.
Underlined columns form the primary key.
Deletion Anomaly
Occurs when deleting a record unintentionally removes valuable related information.
Example:
Deleting the last student enrolled in a course also deletes the course information itself.
Before
| StudentID | Student | CourseID | Course |
|---|---|---|---|
| 1 | Ann | DB | Databases |
| 2 | Bob | OS | Operating Systems |
After
| StudentID | Student | CourseID | Course |
|---|---|---|---|
| 1 | Ann | DB | Databases |
| 2 | Bob | OS | Operating Systems |
Ann was the last student in DB, so the course Databases is lost.
Underlined columns form the primary key.
