Anomalies in DBMS – Insert, Update, Delete Anomalies

Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners. Also, if you are interested in content writing, you can mail us at tutorialwing@gmail.com.

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.

Card_no Name Address Class Books_allowed Books_issued
101 Mr. Joshi Kothrud, Pune Normal 2 1
103 Ms. Deepa Karve Road Normal 2 2
104 Dr.Bhide Deccan, Pune Silver 5 3
107 Mr. Amit Tilak Road Gold 7 6
109 Ms. smith Laxmi Road Silver 5 5

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.

Support Us

If you like Tutorialwing and would like to contribute, you can email an article on any educational topic at tutorialwing@gmail.com. We would love to publish your article. See your article on Tutorialwing and help others with your knowledge. Follow Facebook, LinkedIn, Google+, Twitter, Youtube for latest updates.
Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners