What Is Insertion Anomaly?

Insertion anomaly indicates that we cannot insert a fact about one entity until we have an additional fact about another entity. Suppose we want to store the information that the cost of car is Rs. 14,00,000, but we cannot enter this data into the relation until the data about the car is entered into the relation.

This restriction seems undesirable. Why should we have to wait until someone buys the car (although its price is known). We can record its price. This restriction is called an insertion anomaly. This problem can be solved by dividing the relation into two relations, each one is used to store different facts.

For example, to insert a new staff tuple (record) into the 'Staff-Branch' relation, we must include the details of the branch at which the staff is to be located. Suppose you enter the details of new staff located at branch number 3891, you must enter the correct details of this branch number.

It is difficult to insert details of a new branch that has no members of staff into the 'Staff-Branch' relation. The only way to do this is to place null values in the attributes for staff, such as Staff-No. It creates integrity problem because Staff-No is the primary key of Staff-Branch relation. Therefore, you cannot enter a row for a new branch into the Staff-Branch relation with a null value for Staff-No attribute. When Staff-Branch relation is divided into Staff and Branch relation, the above-mentioned problem will not occur. It is because, the branch details can be entered in the Branch relation. The details of staff located at that branch can be entered later into the Staff relation.