Concept of Redundancy and Anomalies

Concept of Redundancy and Anomalies

Definition: Redundancy is a condition created in database in which same piece of data is held at two different places.

Redundancy is at the root of several problems associated with relational schemas. 

Problems caused by redundancy: Following problems can be caused by redundancy-

i) Redundant storage: Some information is stored repeatedly.

ii) Update anomalies: If one copy of such repeated data is updated then inconsistency is created unless all other copies are similarly updated.

iii) Insertion anomalies: Due to insertion of new record repeated information get added to the relation schema.

iv) Deletion anomalies: Due to deletion of particular record some other important information associated with the deleted record get deleted and thus we may lose some other important information from the schema.

Example: Following example illustrates the above discussed anomalies or redundancy problems

Consider following Schema in which all possible information about Employee is stored.

1) Redundant storage: Note that the information about DeptID, DeptName and DeptLoc is repeated.

2) Update anomalies: In above table if we change DeptLoc of Pune to Chennai, then it will result inconsistency as for DeptID 101 the DeptLoc is Pune. Or otherwise, we need to update multiple copies of DeptLoc from Pune to Chennai. Hence this is an update anomaly.

3) Insertion anomalies: For above table if we want to add new tuple say (5, EEE,50000) for DeptID 101 then it will cause repeated information of (101, XYZ,Pune) will occur.

4) Deletion anomalies: For above table, if we delete a record for EmpID 4, then automatically information about the DeptID 102,DeptName PQR and DeptLoc Mumbai will get deleted and one may not be aware about DeptID 102. This causes deletion anomaly.