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:
- Physical schema
- Logical schema
- 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:
- Redundancy
- Update Anomalies
- Insertion Anomalies
- Deletion Anomalies
| SID | Name (Not Null) | Subject (Not Null) | Mobile |
| 1 | Raj | English | 65468154 |
| 2 | Jyoti | Home science | 87668545 |
| 3 | Vikash | Maths | 26865948 |
| 1 | Raj | Maths | Null |
| 3 | Vikash | Science | Null |
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.