The Problem of Redundancy

Insertion Anomaly

Let us consider an employee table in which we have employee_id, name, age, department_id, department_name, department_head, and department_phone. And amongst all, the employee_id is the primary key.

And let’s say we have two records of two employees named Haider and Rohan. And you can see the remaining details in the following table.

After giving a look at the details, we can summarise that the data in dept_id, dept_name, dept_head, and dept_phone is redundant in both the tuples. And now we will see how the redundant data can cause problems in the insertion of data which is called an insertion anomaly.

• Now, a Production department is introduced in the company, which has zero employees as it is a new department. 
• So, the data we have is dept_id, dept_name, dept_head, and dept_phone. While the remaining details are not there as there is no employee data.
• So as due to the lack of employee data, the problem that occurs while inserting this data is primary key cannot be null and empty. In order to insert the new data, we must have an employee_id associated with that particular department. 
• This causes the Insertion Anomaly here.

Also read anomalies in database

Let us now have a look at the deletion anomalies: 

Deletion Anomaly

As we got a good understanding of the Insertion Anomaly, let’s move on to the Deletion Anomaly.

We will consider the above table with the same data. But this time, we’ve added an employee for the production department, while the remaining employees are the same.

• As we can see this table contains data of all the employees as well as the department associated with each of them. 
• Let’s say that the employee “John” of the Production department decides to leave his job.
• And now as there is no other employee in the production department, we have to delete the employee data as well as the department data.

• So, after applying the delete query and deleting that particular row, we saw that there is no particular information left about the department.
• All the department details like dept_id, dept_name, dept_head, and dept_phone also get deleted along with the employee data.
• So, we lost the data which was not intended to be lost.
• And this is how Deletion Anomaly occurs because of the redundant data.

Updation Anomaly

Now, at last, let’s see the updation anomaly: 

And here again, let’s consider the same table but with more employees and departments. This time we have three departments ( Design, Production, and R & D ) and six employees associated accordingly. Each department has two employees as you can see in the above table.

• Now, Consider a situation if the head of the R & D department is changed.
• Initially, the head was “Christen” which was changed to “Lee”.

• In order to correct the table, we need to update the department head in every employee which is associated with the R & D department which can be a very time-consuming and hectic task to do.
• In this example, we need to update two rows. Now, the table we considered is very small and needs updation.
• We can easily identify which rows need updating, but this is not the case when we deal with big databases.
• There are tons of records in real situations and this type of updation in data becomes a burden.
• Just in case, if any row does not get updated. It can cause inconsistencies as a single department cannot have two different heads.
• And that was the Updation Anomaly due to redundancy.

How does data redundancy affect the efficiency of a database?

1. It causes Data inconsistency.
2. Unnecessarily expands the database size.
3. Sometimes data corruption is also possible.
4. Decreasing the time efficiency of the database is also the case. 

Now that we’ve understood each anomaly with an example, how to overcome this is the main task to accomplish. Thus, let us now discuss the approach to overcome such anomalies. 

How to Solve Data Redundancy?

• The use of Normalization is preferred.
• The purpose of Normalization is to define relationships.
• Normalizing data includes structuring a database's columns and tables to ensure that their dependencies are appropriately enforced.
• The term "normal form" refers to the set of rules used to normalize data, and a database is said to be "normalized" if it is free of delete, update, and insert anomalies.

Frequently Asked Questions

Q.1 ) Are there any advantages associated with Data Redundancy?

Although data redundancy may appear to be a bad event, many businesses may gain from it when it is actively embedded into everyday operations. Advantages like - Alternative data backup, Better data security, Faster data access and updates, Improved data reliability, etc.

Q.2 ) How does data redundancy result in data corruption?

When data is corrupted, it happens as a consequence of mistakes in writing, reading, storing, or processing. Data corruption occurs when the same data fields are duplicated in a database or file storage system.

Q.3 ) How is data redundancy handled?

DBMS, on the other hand, manages redundancy by keeping a single repository of data that is specified once and accessible by multiple users. Data remains constant because there is no or little redundancy. The file system does not enable data exchange, or sharing is too complicated.

Key Takeaways

To summarize the article, we learned how data redundancy leads to different inconsistencies and makes the database work inefficient. We discussed all the anomalies and the problems associated with them. And at last, we worked on how to solve the problem of redundancy.

But the knowledge never stops, so to better understand the Database management system, you can go through many articles on our platform. 

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.