Database Reference
In-Depth Information
machine was a Lathe and that its AcquisitionPrice was 4750.00. When we delete one row, the
structure of this table forces us to lose facts about two different things, a machine and a repair.
This condition is called a deletion anomaly .
Now suppose we want to enter the first repair for a piece of equipment. To enter re-
pair data, we need to know not just RepairNumber, RepairDate, and RepairCost, but also
ItemNumber, EquipmentType, and AcquisitionCost. If we work in the repair department, this
is a problem because we are unlikely to know the value of AcquisitionCost. The structure of
this table forces us to enter facts about two entities when we just want to enter facts about
one. This condition is called an insertion anomaly .
Finally, suppose we want to change existing data. If we alter a value of RepairNumber,
RepairDate, or RepairCost, there is no problem. But if we alter a value of ItemNumber,
EquipmentType, or AcquisitionCost, we may create a data inconsistency. To see why, suppose
we update the last row of the table in Figure 3-10 using the data (100, 'Drill Press', 5500, 2500,
'08/17/13', 275).
Figure 3-11 shows the table after this erroneous update. The drill press has two different
AcquisitionCosts. Clearly, this is an error. Equipment cannot be acquired at two different costs.
If there were, say, 10,000 rows in the table, however, it might be very difficult to detect this er-
ror. This condition is called an update anomaly .
Figure 3-10
the EQUIPMENt_
rEPaIr relation
By The WAy Notice that the EQUIPMENT_REPAIR table in Figures 3-10 and 3-11
duplicates data. For example, the AcquisitionCost of the same item of
equipment appears several times. Any table that duplicates data is susceptible to up-
date anomalies like the one in Figure 3-11. A table that has such inconsistencies is said
to have data integrity problems.
As we will discuss further in Chapter 4, to improve query speed we sometimes
design a table to have duplicated data. Be aware, however, that any time we design a
table this way, we open the door to data integrity problems.
A Short history of Normal Forms
When Codd defined the relational model, he noticed that some tables had modification anomalies.
In his second paper, 4 he defined first normal form, second normal form, and third normal form.
He defined first normal form (1NF) as the set of conditions for a relation shown in Figure 3-4. Any
Figure 3-11
the EQUIPMENt_
rEPaIr table after an
Incorrect Update
4 E. F. Codd and A. L. Dean, “Proceedings of 1971 ACM-SIGFIDET Workshop on Data Description,” Access and
Control , San Diego, California, November 11-12, 1971 ACM 1971.
 
Search WWH ::




Custom Search