The Relational Model and Normalization - Database Processing: Fundamentals, Design, and Implementation - page 141

Next, following step 3C in Figure 3-19, we leave a copy of the determinant as a foreign key in
the original relation. Thus, SKU_DATA becomes SKU_DATA_2:
SKU_DATA_2 ( SKU , SKU_Description, Buyer )
The resulting tables are thus:
SKU_DATA_2 ( SKU , SKU_Description, Buyer )
BUYER ( Buyer , Department)
where SKU_DATA_2.Buyer is a foreign key to the BUYER table.
Both of these tables are now in BCNF and will have no anomalies due to functional de-
pendencies. For the data in these tables to be consistent, however, we also need to define the
referential integrity constraint in step 3D in Figure 3-19:
SKU_DATA_2.Buyer must exist in BUYER.Buyer
This statement means that every value in the Buyer column of SKU_DATA_2 must also exist
as a value in the Buyer column of BUYER. Sample data for the resulting tables is the same as
shown in Figure 3-21.
Note that both the “Step-by-Step” method and the “Straight-to-BCNF” method produced
exactly the same results. Use the method you prefer; the results will be the same. To keep this
chapter reasonably short, we will use only the “Straight-to-BCNF” method for the rest of the
normalization examples.
Normalization example 2
Now consider the EQUIPMENT_REPAIR relation in Figure 3-10. The structure of the table is:
EQUIPMENT_REPAIR (ItemNumber, EquipmentType, AcquisitionCost, RepairNumber,
RepairDate, RepairCost)
Examining the data in Figure 3-10, the functional dependencies are:
ItemNumber S (EquipmentType, AcquisitionCost)
RepairNumber S (ItemNumber, EquipmentType, AcquisitionCost, RepairDate, RepairCost)
Both ItemNumber and RepairNumber are determinants, but only RepairNumber is a can-
didate key. Accordingly, EQUIPMENT_REPAIR is not in BCNF and is subject to modification
anomalies. Following the procedure in Figure 3-19, we place the columns of the problematic
functional dependency into a separate table, as follows:
EQUIPMENT_ITEM ( ItemNumber , EquipmentType, AcquisitionCost)
and remove all but ItemNumber from EQUIPMENT_REPAIR (and rearrange the columns so
that the primary key RepairNumber is the first column in the relation) to create:
REPAIR ( RepairNumber , ItemNumber , RepairDate, RepairCost)
We also need to create the referential integrity constraint:
REPAIR.ItemNumber must exist in EQUIPMENT_ITEM.ItemNumber
Data for these two new relations are shown in Figure 3-22.