Data anomalies in DBMS

Anomalies means problems or inconsistency which happened during the operations performed on the table. There can be many reasons that anomaly occur for example,It occurs when data is stored multiple times unnecessarily in the database i.e. redundant data is present or it occur when all the data is stored in a single table. normalization is used to overcome the anomalies. the different type of anomalies are insertion,deletion and updation anomaly.

Input

The same input is used for all three anomalies.

Student

ID	Name	Age	Branch	Branch_Code	Hod_name
1	A	17	Civil	101	Aman
2	B	18	Civil	101	Aman
3	C	19	Civil	101	Aman
4	D	20	CS	102	Monu
5	E	21	CS	102	Monu
6	F	22	Electrical	103	Rakesh

With the help of this table, we are going to show the working of different anomalies.

Insertion Anomaly

When certain data or attributes cannot be inserted into the database without the presence of other data, it's called insertion anomaly.

For example, let's take a branch name petroleum, now the data regarding petroleum cannot be stored in the table unless we insert a student which is in petroleum. Practically, branch existence is not dependent on student existence i.e. we must have the capability that we can store the data of branch whether there are any student of that branch or not, but this can't be done because of insertion anomaly.

Code

Insert into student values(7, ‘G’,16, ‘PETROLEUM’,104, ‘NAMAN’)#Values get inserted
Select * from Student;#Data selected

Output

ID	Name	Age	Branch	Branch_Code	Hod_name
1	A	17	Civil	101	Aman
2	B	18	Civil	101	Aman
3	C	19	Civil	101	Aman
4	D	20	CS	102	Monu
5	E	21	CS	102	Monu
6	F	22	Electrical	103	Rakesh
7	G	16	Petroleum	104	Naman

Deletion anomaly

If we delete any data from the database and any other information which is required also gets deleted with that deletion, then it is called deletion anomaly.

For example, suppose a student of the electrical branch is leaving so now we have to delete the data of that student, but the problem is if we delete the student data, then branch data will also get deleted along with that as there is only one student present through which branch data is present.

Code

Delete from STUDENT WHERE BRANCH= ‘ELECTRICAL’;#data get deleted
Select * from STUDENT;#data selected

Output

ID	Name	Age	Branch	Branch_Code	Hod_name
1	A	17	Civil	101	Aman
2	B	18	Civil	101	Aman
3	C	19	Civil	101	Aman
4	D	20	CS	102	Monu
5	E	21	CS	102	Monu

Updation/modification anomaly

If we want to update any single piece of data then we have to update all other copies, it comes under insertion anomaly.

For example, suppose we need to change the hod name for civil branch, now as per requirement, only single data is to be changed, but we have to change the data at every other part so as to not make an inconsistent table

Algorithm

• Step 1 − Use update to make changes in the table

• Step 2 − Provide changes that are to be made

• Step 3 − Provide condition to where the task get performed

• Step 4 − Use select to check the output

Code

Update STUDENT #Table selected to preform task
Set HOD_NAME= ‘RAHUL’#changes to be made
WHERE BRANCH= ‘CIVIL’;#condition given
Select * from STUDENT;#Data selected

Output

ID	Name	Age	Branch	Branch_Code	Hod_name
1	A	17	Civil	101	Aman
2	B	18	Civil	101	Aman
3	C	19	Civil	101	Aman
4	D	20	CS	102	Monu
5	E	21	CS	102	Monu
6	F	22	Electrical	103	Rakesh

Conclusion

In this article, we have explained data anomalies in which a table is taken and then different anomalies are explained with reference to the table. The first anomaly is insertion where insertion is restricted without the presence of other data. The second anomaly is deletion where the deletion of any data leads to deletion of other useful data. The third is updation anomaly where every copies is updated so as to update a single piece of data.