Database Reference
In-Depth Information
Both determinants are candidate keys (they both determine all the other attributes in
the relation). Thus, every determinant is a candidate key, and the relationship
is
i n B C N F.
At this point, we need to check the BUYER relation to determine if it is in BCNF. Work
through the steps yourself for BUYER to check your understanding of the “Step-by-Step”
method. You will find that BUYER
is
in BCNF, and therefore our normalized relations, as
shown with the sample data in Figure 3-21, are:
SKU_DATA_2 (
SKU
, SKU_Description,
Buyer
)
BUYER (
Buyer
, Department)
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 a
referential integrity constraint (note that this is 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.
Normalization Example 1: The “Straight-to-BCNF” Method
Now let's rework this example using the “Straight-to-BCNF” method. SKU and SKU_
Description determine all of the columns in the table, so they are candidate keys. Buyer is a de-
terminant, but it does not determine all of the other columns, and hence it is not a candidate
key. Therefore, SKU_DATA has a determinant that is not a candidate key and is therefore not
in BCNF. It will have modification anomalies.
To remove such anomalies, in step 3A in Figure 3-19, we move the columns of functional
dependency whose determinant is not a candidate key into a new table. In this case, we place
Buyer and Department into a new table:
BUYER (Buyer, Department)
Next, in step 3B in Figure 3-19, we make the determinant of the functional dependency the
primary key of the new table. In this case, Buyer becomes the primary key:
BUYER (
Buyer
, Department)
SKU_DATA_2
Figure 3-21
the Normalized SKU_
Data_2 and BUYEr
relations
BUYER
Search WWH ::
Custom Search