Anomalies in Relational Database : Update, Delete and Insertion Anomalies

Anomalies in Relational Database :

If a database design is not perfect, it may contain anomalies, which are like a bad dream for any database administrator. Managing a database with anomalies is next to impossible. Anomalies are inconvenient or error-prone situation arising when we process the tables. There are three types of anomalies:

1. Update Anomalies : An Update Anomaly exists when one or more instances of duplicated data is updated, but not all. If data items are scattered and are not linked to each other properly, then it could lead to strange situations. For example, when we try to update one data item having its copies scattered over several places, a few instances get updated properly while a few others are left with old values. Such instances leave the database in an inconsistent state. For example, consider the following table 'student':

By observing the above table, we identify that in case if we have to update City of John, then we need to update all the entries of John in the above table. Else his data will become inconsistent, leading to update anomaly.

2. Delete Anomalies : A Delete Anomaly exists when certain attributes are lost because of the deletion of other attributes. In Delete Anomaly, if we tried to delete a record, than parts of it was left undeleted because of unawareness, the data is also saved somewhere else. For example, consider the above table 'student',  if any of the students drops out any of the subject and we need to delete his entry, entire entry of his would be deleted from above table. But he is still part of that college and whole of information is lost.   But this should not happen. We should have his information stored in database. This is called leading to delete anomaly.

3. Insert Anomalies : An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes. For example, in the above table 'student', if we need to enter any new student detail, which has not yet opted for any subject, then we need to enter all of his information with subject as NULL. Also, if a particular field, say City of a student is present in more than one table, we need to insert the data in all the tables. But for some of the tables, these data would be irrelevant at that point of time. All these situations lead to insertion anomaly.

No comments:

Post a Comment