Anomalies in DBMS with Example

A Database is a very complex mechanism to handle and control a large amount of data. As we all know the overall design of the database is known as database schema which is further divided into three categories as given below:

  1. Physical schema
  2. Logical schema
  3. View schema

The database or relational schema looks quite simpler and effective from the top but as we go to the bottom, we find a massive collection of data. A database has to face so many problems which we may call anomalies. An anomaly is like an unwanted situation, which may impact the integrity or consistency of a database.

Anomalies a database may face are listed as given below:

  1. Redundancy
  2. Update Anomalies
  3. Insertion Anomalies
  4. Deletion Anomalies
SIDName (Not Null)Subject (Not Null)Mobile
1RajEnglish65468154
2JyotiHome science87668545
3VikashMaths26865948
1RajMathsNull
3VikashScienceNull

Redundancy

Duplicate data storage in a database is called redundancy. As we can see in the above table student’s name and subject’s name are repeated. This is called redundancy. Due to this lots of memory space will be wasted. Also, Redundancy creates other three anomalies.

Update Anomalies

Anomalies generated during update of a database. If a record has multiple copies, and if we make updates in a few copies and leave the remaining copies with old values, then the search result for that record may be misled the information. This may create inconsistency. Like in above given table student “Raj” is repeated twice so if we update the new mobile number for Raj at the second time then Raj has two mobile numbers. This may create confusion that which one is actually the correct mobile number. 

Insertion Anomalies

Anomalies generated during insert data into a database. In the given above table If a new student enrolled in a college but not selects any subject yet. Then we can’t insert that student’s record in the college database as we can’t leave the subject column blank for that student if NULL is not allowed. It means the student is enrolled but no data will be found for the same.

Deletion Anomalies

Anomalies generated during delete data into a database. As in above given table if we delete the subject “Home Science” since it’s no longer available then the record of student “Jyoti” is also deleted whereas Jyoti is still an active student of the college.

Leave a Comment

Your email address will not be published.