Modification Anomalies

Speaker: Let’s look again at the table that tracks the relocation of employees of the Acme Spacecraft Company. It qualifies as a relation, but that may not be enough to maintain data integrity as new rows are added or existing rows deleted. Suppose Joyce Brown resigns to take a job with a competitor. Deleting the row with employee ID=3, removes Ms. Brown from the table, but that’s not all that it does. It also removes the fact that launch engineers work at Cape Huntington. It even removes the fact that there is a job category named Launch Engineer and that the company has operations at Cape Huntington. This problem is called a deletion anomaly. In the process of removing information that you no longer want in the table, you have also removed information that you want to keep. What has caused this problem and what can be done about it?

What has caused the problem is that a single table is being used to hold information about two different sets of facts. One set of facts is the job classification of the company’s employees. The other set of facts is the location at which various jobs are performed. These two sets of facts are related to each other, but they are not tightly related. Rather than putting all the information in one table, it makes more sense to break that table up into two related but separate tables. Let’s break it up into an employee table and a location table. Now, when Joyce Brown leaves the company, her row in the employee table can be removed. The fact that launch engineer’s work at Cape Huntington is preserved in the location table. The preceding example, shows how breaking up tables so they refer to only one set of facts prevents the occurrence of a deletion anomaly.

Another possible modification anomaly is the insertion anomaly. Let’s take another look at the original table. Suppose management now wants to activate Building 2 for inspecting rockets after they had been manufactured but before they are tested. With this table, we cannot activate building to until we have an employee assigned to work in that building. However, Building 1 is running out of room and we need to move completed rocket somewhere. After we break the table in two, the problem disappears. The employee table holds only employees and the location table can hold locations even if there is no employee assigned to work at them. The process of breaking up a table that deals with multiple ideas or facts into multiple tables that will be less susceptible to modification anomalies is called normalization. In the next segment, I’ll give an overview of normalization that will help you to design more reliable databases.