Anomalies in SQL

In SQL databases, anomalies refer to irregularities or inconsistencies in the data that can affect its integrity, accuracy, and reliability. There are several types of anomalies that can occur in SQL databases:

1. Insertion Anomaly

2. Update Anomaly

3. Deletion Anomaly

Relational Databases are affected by 3 different anomalies. They occur due to presence of data redundancy and inconsistent data. Anomalies can lead to incorrect query result and difficulty in managing the database.

Then, let’s dive in.

1. Insertion Anomaly

It arises when we are trying to insert some data into database, but we are unable to insert it.

Get NIRAJAN JHA’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Assume a new employee has joined the company as a trainee but not assigned to any department. Here, We will not be able to insert the data into the table if we don’t know the emp_department and also if emp_department does not allow Nulls.

2. Update Anomaly

It arises when we update some data in the database, but the data is partially updated which causes the data inconsistency.

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

From above table, we can see that there are two rows for an employee Rohan and he belongs to two different departments of company. If we need need to update Rohan’s address, we must update the same address in two rows. Otherwise, data will become inconsistent.

3. Deletion Anomaly

It arises when we delete some data from database, but some unrelated data is also deleted i.e. there will be loss of some necessary additional data due to deletion anomaly.

From the above table, assume if the company closes emp_department “A890”, then deleting the rows that have emp_department as A890 would also delete the information of employee Mamta since she is assigned only to this department.

Elimination of Anomalies

To prevent or reduce these anomalies, it’s important to design databases according to normalization principles. This approach involves structuring data into tables and implementing rules to maintain consistent and efficient data storage. By minimizing redundant data and upholding data integrity, normalization plays a crucial role in eliminating anomalies and enhancing database quality as a whole.