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 means we would lose data that we might not want to lose.
Update anomaly
Take a look at the table shown above again. 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, meaning that it is difficult to make changes to every record. Update anomalies lead to inaccuracy and inconsistency in a database.