Database Reference
In-Depth Information
For the data to be consistent, we must repeat the value of price for as many rows as each
kit has parts. For this example, we must add three rows for the Bike Repair kit and four rows
for the First Aid kit. The result is duplicated data that can cause data integrity problems.
Now you also know the problem with the relation in Figure 3-2. Anomalies exist in that
table because it contains two multivalued dependencies:
BuyerName
S
S
SKU_Managed
BuyerName
S
S
CollegeMajor
Fortunately, it is easy to deal with multivalued dependencies: Put them into a table of their
own. None of the tables in Figure 3-28 has modification anomalies because each table consists
of only the columns in a single, multivalued dependency. Thus, to fix the table in Figure 3-2, we
must move BuyerName and SKU_Managed into one table and BuyerName and CollegeMajor
into a second table:
PRODUCT_BUYER_SKU (
BuyerName
,
SKU_Managed
)
PRODUCT_BUYER_MAJOR (
BuyerName
,
CollegeMajor
)
The results are shown in Figure 3-31. If we want to maintain strict equivalence between these
tables, we would also add the referential integrity constraint:
PRODUCT_BUYER_SKU.BuyerName must be identical to
PRODUCT_BUYER_MAJOR.BuyerName
This referential integrity constraint may not be necessary, depending on the requirements of
the application.
Notice that when you put multivalued dependencies into a table of their own, they disap-
pear. The result is just a table with two columns, and the primary key (and sole candidate key)
is the composite of those two columns. When multivalued dependencies have been isolated in
this way, the table is said to be in fourth normal form (4NF).
The hardest part of multivalued dependencies is finding them. Once you know they exist
in a table, just move them into a table of their own. Whenever you encounter tables with odd
PRODUCT_BUYER_SKU
Figure 3-31
Placing the two Multivalued
Dependencies in Figure 3-2
into Separate relations
PRODUCT_BUYER_MAJOR
Search WWH ::
Custom Search