Anomalies in Database with Example

Anomalies in Database are the main cause of Data Redundancy in DBMS. In this tutorial we will learn about anomalies in database with examples.

A person who design the database must have the knowledge about anomalies in database. In Database normalization approach we try to remove the database anomalies.

What is Anomaly in Database?

• When we perform insert, delete, update on a table, some problem may occur in executing these operations in the database.
• These problems are  known as an anomaly. Anomalies in database means inconsistency in database that may occurred during operation performed on relation table.
• Anomalies in Database also affects the Data Integrity.
• If the Database is not properly designed or poorly designed then it may lead to data delicacy or data loss problems.
• In this situation, when we try to insert a record in a table, then this may not be inserted.

There may be the following types of anomalies in the database.

Consider an example

There are two relations as shown in the following two tables named as STUDENT or STUDENT_COURSE.

Insertion Anomaly

Insertion anomaly refers to situation when a new row is added to a table and it causes an inconsistency

Consider the STUDENT_COURSE Relation. The value of STUD_ NO attribute of this Relation is referred from the STUD_NO attribute of STUDENT Relation.

If we try to insert a record with STUD_No value five, then it will not allow it.

Updation  Anomaly

Updation anomalies in DBMS means If there are some changes in the database, we have to apply that change in all the rows. If we miss any row, we will have one more field, creating an update anomaly in the database.

Deletion Anomaly

The term “deletion anomaly in the database” is used when we delete some rows from a table and any necessary additional information or data is also lost from the database due to this deletion.

This anomaly occurs when we want to delete or update a tuple form the Referenced Relation. Since the Referenced attribute value is used by Referencing Relation, then we are not allowed to delete the tuple from the referenced Relation.

Example

For example, in the above Relation when we try to delete or modify the tuple for which the value of STD_NO is 1, we are not allowed. This value is used in STUD_NO attribute in STUDENT_COURSE relation.

How to remove anomalies in Database ?

• Anomalies in Database can be countered through Database Normalization process.
• Normalization process is used to remove anomalies in database and to maintain the data integrity.
• Database normalization remove the Data Redundancy.