Insertion, Deletion, and Modification Anomalies Are Considered Bad

An insertion anomaly is a situation that arises when one is prevented from inserting data into a relation because the complete primary key is not available. No part of a primary key can be null. Harrington (2009) points out that insertion anomalies usually occur in first-normal form relations that do not exist in the higher normal forms.

Insertion anomalies occur because there is usually data about more than one entity in the relation. An example of customer, item, and orders will be used to show an example of an insertion anomaly.

1Customer (customer_number, first_name, last_name, street, city, state, zip, phone)
2
3Item (item_number, item_type, title, distributor_number, retail_price, release_date, genre, quantity_in_stock)
4
5Orders (order_number , item_number,customer_number, first_name, last_name,city, state, zip, phone, title, price, street, , order_date, has_shipped)

Take the orders table, for example; the primary keys for this table are order_number and iterm_number. The customer_number by itself would not make the table unique. The item_number would not make it unique either. So, in this example, order_number and item_number are concatenated primary keys. An order cannot be added into this table until a customer or customer_number exists. A merchandise item or item_number must also exist before a row can be inserted into the orders table.

These two examples are examples of insertion anomalies. The insertion anomalies force an insert of data to have occurred in the item and customer table before an insert into orders cahhappen. Another issue that can happen in the first normal form is when relation data is deleted, as Harrington(2009) discussed.

Using the example of a customer, item, and orders above, what happens if a customer cancels an item in the order? This means that if the item was the only item in the order, the order and item data is deleted.

If the order was the only order which the item appeared then one would only use data about the item. In the case where the deleted item was the item ordered by a customer, loss of the data about the customer

The deletion anomalies can occur because part of the primary key of a tuple or row becomes null when the item data is deleted, forcing you to remove the entire row or tuple. The result of a deletion anomaly is the loss of data that one would like to keep. In practical terms, one is forced to remove data about an unrelated entity when one deletes data about another entity in the same table.

The third type of anomaly is the modification or update anomaly. The modification anomaly in the orders relation is unrelated to the primary key. The order relation or table has unnecessary duplicated data or information about customers. If a customer changes, the customer’s data is changed in every row for each associated item on every order ever placed by the customer. If every row is not modified or updated, then data that should be the same are no longer the same. The potential for this type of inconsistent data is called a modification anomaly.

References

Harrington, J. L. (2009). Relational Database Design and Implementation. : Elsevier Science. Retrieved from http://www.ebrary.com