Anomalies in Relational Model
Database anomaly is a flaw in databases because of poor planning and storing everything in a flat database. Anomalies occur when there is too much redundancy in the database. Poor table design has related data scattered over various tables. Any new change in the database should be updated in many places. It is also possible that the information is only half present. It's there in one table, but missing in another one. Let's understand this by an example.
Assume manufacturing company stores employee details in a table called as an employee having four attributes:
- Emp_id for employee's id.
- Emp_name for employee's name.
- Emp_address for employee's address.
- Emp_dept for the department details in which the employee works.
The table will look like this. The table given below is not normalized. We will see how problems arise when a table is not normalized.
Types of Anomalies
If a tuple is inserted in referencing relation and referencing attribute value is not present in referenced attribute, it will not allow inserting in referencing relation.
Assume that a new employee is joining the company under training and not assigned to any department. Then, we would not insert the data into the table if the emp_dept field doesn't allow nulls.
If a tuple is updated from referenced relation and the referenced attribute value is used by referencing attribute in referencing relation. In that case, it will not allow updating the tuple from referenced relation.
In the given table, we have two rows for an employee named Rick, and he belongs to two different departments of the company. If we need to update Rick's address, we must update the same address in two rows. Otherwise, the data will become inconsistent.
If, in some way, we can update the correct address in one department but not the other, then according to the database, Rick will have two different addresses, which is not correct and would lead to inconsistent data.
If a tuple is deleted from referenced relation and the referenced attribute value is used by referencing attribute in referencing relation, it will not allow deleting the tuple from referenced relation.
Assume that if the company closes the department D890, then deleting the rows that have emp_dept as D890 would also delete the information of employee Maggie since she is assigned only to this department.
Removal of Anomalies
To prevent anomalies, we need to normalize the database by efficiently organizing the data in a database. Normalization is a systematic approach to eliminate data redundancy and Insertion, Modification, and Deletion Anomalies by decomposing tables. The database designer organizes the data to eliminate unnecessary duplications and provides a quick search path to all necessary information.
According to Edgar F Codd, the inventor of relational databases, the goals of normalization include:
- removing all redundant (or repeated) data from the database
- removing undesirable insertions, updates, and deletion dependencies
- reducing the need to restructure the entire database every time new fields are added to it
- making the relationships between tables more useful and understandable.
Frequently Asked Questions
Q1. What causes anomalies in the database?
Anomalies are caused due to much redundancy in the database. Anomalies can also be caused when the database's tables suffer from poor construction.
Q2.What is normalisation?
Normalization is the method of organizing data in a database. It is the process of specifying and defining tables, keys, columns, and relationships to create an efficient database. This process includes creating tables and establishing relationships according to the rules. Normalization protects the database's data and makes the database flexible by eliminating redundancy and inconsistent dependency.
Q3. Why can normalization reduce or eliminate update anomalies?
Normalization is used to remove duplicate data and database anomalies from the table. Normalization helps to reduce complexity and redundancy by examining new data types used in the table. It reduces the chances of anomalies occurring in a database.
We learned how to eliminate anomalies in the database by using normalization. We have also known about deleting, updating, and inserting anomalies and the situation in which they can occur. Normalization is the solution to all kinds of anomalies. It helps to remove anomalies and give structured data.
Visit here to learn more about different topics related to database and management systems. Ninjas don’t stop here. Check out the Top 100 SQL Problems to master frequently asked questions in big companies and land your dream job. Also, try CodeStudio to practice a wide range of DSA questions asked in lots of interviews.