Database Insertion, Update, and Deletion Anomalies

A non-normalized database has the potential to have three different types of anomaly errors: Update, Insert, or Delete. Normalizing the database to third normal form should avoid those errors. Duplicated fields in a table as described in Understanding Database Normalization are the primary cause of the errors below.

Update Anomaly

When there are duplicated fields, an update to one entry must be copied to potentially many fields and defeats one of the benefits of a database over a spreadsheet.

Insertion Anomaly

Without properly normalized data tables it is possible to have a situation where you are unable to add new data because of dependent linked tables. For instance, you could have an Orders table where you are unable to add an order for a new Client because there are no orders already in the table.

Deletion Anomaly

A deletion anomaly is orphan data. In a non-normalized database if you delete a Client whose ClientID number is 123 and the Orders for that Client remain in the database. In the Order table, you will still show ClientID number 123 for that Client, but have no way to identify the associated Client. When referential integrity is properly enforced, all dependent tables are deleted.