CS312 Highlighted Handouts by VU Gateway

A. What is Anomaly?

Module 12: Anomalies

Anomalies can be defined as the problems that can occur due to poor planning and designing of the databases. This usually occurs when a single table is created instead of creating multiple tables. An anomaly is an irregularity, or something which deviates from the expected or normal state.

PPPPP. Why Anomalies are Critical

As mentioned above, anomalies are critical as it can affect the end user experience. Besides, duplication or redundancy in the data is increased due to anomalies which in results create other serious issues. Anomalies may not have a huge impact on the small databases but a database with a large data size may have serious performance issues caused by anomalies. There are three types of anomalies which are explained below.

QQQQQ. Insert Anomaly

Insert anomaly occurs when certain data cannot be entered without entering unwanted data. In other words, in other words, some attributes cannot be entered without the presence of some other attributes. This results from poor design decision of storing data in just one table instead of multiple tables. As in the example below, if we want to enter a new product (pid, pname, price) then we have to add a customer (cid) against the product which might not be possible at the time of entering the product meaning to add a wanted row (product) we have to enter an unwanted (customer) row because CID is primary key and it can’t be left null.

Figure 7: Delete Anomaly

RRRRR.

Delete Anomaly

This anomaly occurs when the data is lost due to the deletion of some other data. The reasons of this anomaly are same as mentioned above i.e. poor design decision. Due to this anomaly certain attributes are lost due to the deletion of other attributes. If we want to delete pid : 4 then we will be deleting cid=2 also because when we delete complete row is deleted and there is only record for cid=2 meaning we might end up losing customer data when we delete product data which is a loss of unwanted data.

SSSSS.

Update Anomaly

An Update Anomaly exists when one or more instances of duplicated data are updated, but not all. An update anomaly occurs when the same data item has to be updated more than once. This can lead to errors and inconsistency of data. The same information can be expressed on multiple rows; therefore all the instances must be updates. If this is not done properly, update anomaly arises. If there are multiple records and we fail to update all the instances then it will result in data-inconsistency across database which will result in wrong reporting of data which is not acceptable. In the table given above if we decide to update Cname of cid=3 and we fail to update all the rows (cname) then it will end up in wrong information. Records with yellow color are showing that cid=3 is having different Cname and system will report you both Cname against CID=3 and its wrong. It is result that all the records are not updated.