In this post, we will see what are the different types of anomalies in DBMS. We will see anomalies in dbms caused to different operations – insertion, deletion, update etc.

Redundancy in a database is storing the same information in more than one place in the database. Storing information redundantly can lead to several types of problems.

• i. Redundant storage: Same information is stored repeatedly in more than one place leading
  to need of larger storage space.
• ii. Update anomalies: If one copy of such repeated data is updated, an inconsistency is
  created; unless all copies are similarly updated (inconsistency is when two copies of the
  same data do not match. In such cases we cannot decide which copy is right and the data is
  said to become inconsistent).
• iii. Insertion anomalies: It may not be possible to store some information unless some other
  information is stored well.
• iv. Deletion anomalies: It may not be possible to delete some information without losing some
  other information as well.

Consider the following relation of library database having the attributes.

Library = {card no, name, address, class, books_allowed, books issued}

The key of library database is card_no. In addition the books_allowed attribute is determined by the class attribute i.e. for a given class there is only one permissible value for books_allowed. This is a functional dependency which leads to possible redundancy in the relation as show in the following figure.

If the same value appears in the class column two rows (tuples), we can be sure that same value will appear in the books_allowed column of the two rows as well. This is redundancy. It has several negative consequences.

If the same value appears in the class column two rows (tuples), we can be sure that same value will appear in the books_allowed column of the two rows as well. This is redundancy. It has several negative consequences.

• Some information is stored multiple times.
  For example: For the class ‘normal’ the number of ‘books_allowed’ is two. This information is repeated two times in the above instance. This not only increases the space but might also lead to inconsistency.
  For example: if books_allowed for the first tuple is updated to three; without making similar change into several rows, then it results in consistency. This is an example of updation anomaly.
• Also we cannot insert the details of a new customer unless we know his class or suppose we decide to add a new class called ‘Super Gold’ for which books_allowed will be 10, but if we do not have a customer of this new class, we cannot insert the information that for class ‘Super Gold’ books_allowed should be 10. These are examples as insertion anomaly.
• If we decide all tuples with a given class value (for example: if we delete the row for card_no 107) then we loose the association between that class and its corresponding books_allowed. In the above example we will loose the information that for the class ‘Gold’ the number of books_allowed is 7. This is an example of deletion anomaly.

That’s end of post on anomalies in dbms.