Anomalies
| ID | Forename | Surname | Department | Department ID | Phone number |
| 1 | Colin | Arthur | ICT | 001 | 300 |
| 2 | Laura | Brown | ICT | 001 | 300 |
| 3 | Stephen | MacLeod | ICT | 001 | 300 |
| 4 | Scott | Sinclair | English | 002 | 301 |
| 5 | Michelle | Wie | English | 002 | 301 |
| 6 | Ross | Dyett | PE | 003 | 302 |
| 7 | Ian | Anderson | PE | 003 | 302 |
| 8 | Betty | Flood | Geography | 004 | 303 |
| ID | 1 |
|---|---|
| Forename | Colin |
| Surname | Arthur |
| Department | ICT |
| Department ID | 001 |
| Phone number | 300 |
| ID | 2 |
|---|---|
| Forename | Laura |
| Surname | Brown |
| Department | ICT |
| Department ID | 001 |
| Phone number | 300 |
| ID | 3 |
|---|---|
| Forename | Stephen |
| Surname | MacLeod |
| Department | ICT |
| Department ID | 001 |
| Phone number | 300 |
| ID | 4 |
|---|---|
| Forename | Scott |
| Surname | Sinclair |
| Department | English |
| Department ID | 002 |
| Phone number | 301 |
| ID | 5 |
|---|---|
| Forename | Michelle |
| Surname | Wie |
| Department | English |
| Department ID | 002 |
| Phone number | 301 |
| ID | 6 |
|---|---|
| Forename | Ross |
| Surname | Dyett |
| Department | PE |
| Department ID | 003 |
| Phone number | 302 |
| ID | 7 |
|---|---|
| Forename | Ian |
| Surname | Anderson |
| Department | PE |
| Department ID | 003 |
| Phone number | 302 |
| ID | 8 |
|---|---|
| Forename | Betty |
| Surname | Flood |
| Department | Geography |
| Department ID | 004 |
| Phone number | 303 |
Insert anomaly
In the above example, it is not possible to add a new department to the database without also having to add a member of staff at the same time. The table expects a teacher鈥檚 details and the details of a department to be stored together as one record.
At the moment, there is no way to add the Maths department without also having to add a Maths teacher. This problem is known as an insert anomaly.
Delete anomaly
A delete anomaly is the opposite of an insert anomaly. When a delete anomaly occurs it means that you cannot delete data from the table without having to delete the entire record.
For example, if we want to remove Betty Flood from the table, we would also need to remove all data that is stored about the Geography department. This is all stored as one record so must all be deleted together.
Delete anomalies lead to the loss of data that we might not want to lose.
Update anomaly
In the table above, if the phone number for the English department changed to 307 instead of 301 it would need to be changed in two different records.
If the change only happened in one of the two records, then an update anomaly would have taken place.
In small tables it can be easy to spot update anomalies and make sure that changes are made everywhere. However, large flat file tables would often contain thousands of records. This makes it difficult to make changes to every record.
Update anomalies lead to inaccuracy and inconsistency in a database.