Goals of Normalization: Eliminate All Three Types of Anomaly
An anomaly is an irregularity, or something which deviates from the expected or normal state. When designing databases, we identify three types of anomalies:Insert, Update and Delete.
As is often the case, it is probably easier to understand these concepts by looking at some examples.
An example of an Insert Anomaly might be a table which stores records for a company's Salespeople and the clients for whom they are responsible. Leaving aside for the moment the fact the table shown above has other design problems, let's say this table requires an entry in the client field.
Because client is a required field, it will not be possible to add records for newly hired sales reps until they have been assigned to one or more clients. If a newly hired sales rep must complete several weeks of training before being allowed to call on clients, it is not possible to record him in the table during training. Or, if we do add new hires while they are in training, we must create "dummy" clients as placeholders.
This table is also a good example of a design that leads to Update Anomalies. Note that sales rep's names are repeated for each client. Let's say, for example, that we recently learned Stan's last name should have been spelled "Crossley". This table design makes it very easy for a data entry person to update one of the two records for Stan, without noticing, and updating, the other. Moreover, it is also possible that there are really TWO Stan's working for this organization, and that Stan number 2 actually does spell his name "Crosley", while Stan number 3 spells his "Crossley". (Or is it the other way around?)
Finally, we can also use this table as an example of a Delete Anomaly. Let's say that Anne takes on a temporary research assignment that requires her to give up her existing clients for the next 6 months. Because we can not delete just the client value, we are faced with the choice of deleting Anne's record completely from the Sales Rep table, creating a "dummy"
As is often the case, it is probably easier to understand these concepts by looking at some examples.
An example of an Insert Anomaly might be a table which stores records for a company's Salespeople and the clients for whom they are responsible. Leaving aside for the moment the fact the table shown above has other design problems, let's say this table requires an entry in the client field.
Because client is a required field, it will not be possible to add records for newly hired sales reps until they have been assigned to one or more clients. If a newly hired sales rep must complete several weeks of training before being allowed to call on clients, it is not possible to record him in the table during training. Or, if we do add new hires while they are in training, we must create "dummy" clients as placeholders.
This table is also a good example of a design that leads to Update Anomalies. Note that sales rep's names are repeated for each client. Let's say, for example, that we recently learned Stan's last name should have been spelled "Crossley". This table design makes it very easy for a data entry person to update one of the two records for Stan, without noticing, and updating, the other. Moreover, it is also possible that there are really TWO Stan's working for this organization, and that Stan number 2 actually does spell his name "Crosley", while Stan number 3 spells his "Crossley". (Or is it the other way around?)
Finally, we can also use this table as an example of a Delete Anomaly. Let's say that Anne takes on a temporary research assignment that requires her to give up her existing clients for the next 6 months. Because we can not delete just the client value, we are faced with the choice of deleting Anne's record completely from the Sales Rep table, creating a "dummy"