Anomalies: Insertion, Deletion, Update

Anomalies are problems that arise in unnormalized or poorly structured database tables. They are usually the result of data redundancy and repetition in a relation. These anomalies can cause data inconsistencies, errors, and unintended data loss.

There are three main types of anomalies:

1. Insertion Anomaly

An insertion anomaly denotes that insertion of information in some parts required insertion of information in other parts also.

Problem: When you are unable to add data to the database because other data is missing.

Let’s assume that name of the above relation is Records, which contains records of Students, Course, and Faculties of some institute.
Sid is the primary attribute of Students.

Example:

In the above table, we can see that highlighted we want to insert the information for Faculties but because Sid is primary we also need to insert some dummy information for Students.

2. Deletion Anomaly

A deletion anomaly occurs when you delete a record that may contain attributes that shouldn’t be deleted. 

Problem: Deleting a record results in unintended loss of additional important data.

For instance, if we remove information about the last account at a branch, such as account A-101 at the Downtown branch in Figure 10.4, all of the branch information disappears.

Example:

The problem with deleting the A-101 row is we don’t know where the Downtown branch is located and we lose all information regarding customer 1313131. To avoid these kinds of update or deletion problems, we need to decompose the original table into several smaller tables where each table has minimal overlap with other tables.

3. Update Anomaly

Problem: When updating data in one place requires multiple changes in many rows, leading to inconsistency if even one is missed.

If a branch changes address, such as the Round Hill branch in Figure 10.3, we need to update all rows referring to that branch. Changing existing information incorrectly is called an update anomaly.

Summary Table: