Anomalies in DBMS

The term anomaly is used to describe a discrepancy between two parts of a database. In a retail database, for example, you may have a customer and an invoice table. If you are no longer selling to customers, you may want to purge them from your database periodically. If you delete a customer but keep their invoices for the purchases they have made, you have an anomaly. There is an invoice for a customer that doesn't exist anymore. By deleting all of a customer's invoices when you delete a customer, this anomaly can be avoided. Be sure to close the book on the year before you do this.

What are the Anomalies in DBMS?

A database anomaly is an inconsistency in the data resulting from an operation like an update, insertion, or deletion. There can be inconsistencies when a record is held in multiple places and not all of the copies are updated.

Anomalies occur when the data present in the database has too much redundancy and if the tables making up the database are poorly constructed.

How are Anomalies Caused in DBMS?

Poorly constructed tables in a database are often the reason behind anomalies. How do you define "poor construction"? A poorly designed table becomes apparent if, when a designer is creating the database, he fails to identify the entities which are interdependent, such as rooms of a hostel and the hostel, and then minimizes the chances of one entity being independent of another.

A database anomaly is a fault within a database, which can occur because of poor planning or when everything is stored in a flat database. A normalization procedure, which combines and splits tables, is usually sufficient to remove this. By normalizing the database, we reduce the likelihood of creating tables that generate anomalies.

Type of Anomalies in DBMS

There are different types of anomalies that can occur in a database. Redundancy anomalies, for instance, can cause problems during tests if you are a student, and during job interviews, if you are looking for work. However, they are easily spotted and fixed. These are the ones we need to pay attention to:

1. Update
2. Insert
3. Delete

Database anomalies fall into these three major categories:

1. Update anomaly:

Consider a college database that keeps student information in a table called student, which contains four columns: stu_id for the student's id, stu_name for the student's name, stu_address for the student's address, and stu_club for the student's club. Eventually, the table will appear as follows:

For student Muthu, we have two columns in the above table as he belongs to two clubs at the college. If we want to change Muthu's address, we must update it twice otherwise the data will be inconsistent.

When the correct address gets updated in one club but not in another, Muthu would possess two different addresses, which is not acceptable and could result in inconsistent data.

2. Insert anomaly:

Example

We use the same table in the previous example with modified data

For example, in the above table if a new student named Nanda has joined the college and he has no department affiliation as the club allows intake of students only from second year. Then we can't insert the data of Nanda into the table since the stu_club field cannot accept null values.

3. Delete anomaly:

Example In this example, we use modified data from the previous example

Suppose, for instance, the college at some point closes the club crypto, then deleting the rows that contain s_club as crypto would also delete the information of student Mukesh since he belongs only to this department.