Database anomalies explained
There are three types of anomalies in a database. They are :
- Insertion anomaly
- Deletion anomaly
- Update anomaly
These anomalies are explained through the following table as an example.
Insertion anomaly: Suppose, in the university database, the primary key is ‘Student ID’, without it we cannot insert any new record into the table. In the table, there are 3 Departments(Math, Physics and Chemistry). If there is a new department ‘Geology’ is to be added into the table which has no student currently enrolled into this department then how will we enter the Head of the department name, Dept code of Geology department? (The existence of a department does not necessarily need to depend on the admitted students into this department !) But we should be able to insert department details irrespective of whether there is any student is in this department or not.
As long as there is no student in the geology department,we cannot insert the dept code of Geology in this table. This problem is called insertion anomaly.
Deletion anomaly: If we want to delete the only student of Chemistry department (i.e. Amer) then we will lose the information of Chemistry department’s Dept code and head of the department, as we did not store this information in a separate table.
We just want to erase the record of a particular student, we do not want delete the information of Chemistry department. But unwittingly, the information of department gets deleted, although we do not want it to happen. This problem is called deletion anomaly.
Modification/Updation anomaly: Suppose the name of the head of the Math department needs to be changed. For doing that we have to modify all three records with department Math.
Making change in so many columns just for changing a single information is inefficient and time consuming. This problem is known as updation anomaly.
