What Types Of Anomalies Are Found In Relational Databases?

There are several standard types of anomaly in a database. It doesn't need to be a relational database, the same anomalies are present in any database. A properly designed relational database specifically aims to eliminate these anomalies.

If the database is not properly normalised, it is susceptible to insertion, update and deletion anomalies.

The update anomaly occurs when the same data is stored in several records and a change has to be made. If only some of the records are updated, an update erors occurs - eg we could end up recording two teachers as giving the same class. If you are dealing with hundreds of records, this is quite likely to occur.

If you wish to record some info in advance of needing it, such as the name of a teacher for a class that at present has no students enrolled, and the teacher name is only recorded next to each student rather than in a separate table, then with no student enrolled, you can't add the teacher info. This is an insertion anomaly.

A deletion anomaly occurs not when you delete something by accident, but when deleting a record also removes the only instance of some other data. So if we recorded a class's details along with a student's, have only one student enrolled and then that student withdrew, we would be deleting the class info as well.

Normalisation of a relational database is designed to overcome these problems

Add Comment

Report