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

associated with a given order. For the same reasons, it does not determine Quantity, Price, or
ExtendedPrice.
What about SKU? SKU does not determine OrderNumber because several OrderNumbers
are associated with a given SKU. It does not determine Quantity or ExtendedPrice for the same
reason.
What about SKU and Price? From this data, it does appear that
SKU S Price
but that might not be true in general. In fact, we know that prices can change after an order
has been processed. Further, an order might have special pricing due to a sale or promotion. To
keep an accurate record of what the customer actually paid, we need to associate a particular
SKU price with a particular order. Thus:
(OrderNumber, SKU) S Price
Considering the other columns, Quantity, Price, and ExtendedPrice do not determine anything
else. You can decide this by looking at the sample data. You can reinforce this conclusion by
thinking about the nature of sales. Would a Quantity of 2 ever determine an OrderNumber or
a SKU? This makes no sense. At the grocery store, if I tell you I bought two of something, you
have no reason to conclude that my OrderNumber was 1010022203466 or that I bought car-
rots. Quantity does not determine OrderNumber or SKU.
Similarly, if I tell you that the price of an item was $3.99, there is no logical way to conclude
what my OrderNumber was or that I bought a jar of green olives. Thus, Price does not deter-
mine OrderNumber or SKU. Similar comments pertain to ExtendedPrice. It turns out that no
single column is a determinant in the ORDER_ITEM table.
What about pairs of columns? We already know that
(OrderNumber, SKU) S Price
Examining the data, (OrderNumber, SKU) determines the other two columns as well.
Thus:
(OrderNumber, SKU) S (Quantity, Price, ExtendedPrice)
This functional dependency makes sense. It means that given a particular order and a particu-
lar item on that order, there is only one quantity, one price, and one extended price.
Notice, too, that because ExtendedPrice is computed from the formula ExtendedPrice =
(Quantity * Price) we have:
(Quantity, Price) S ExtendedPrice
In summary, the functional dependencies in ORDER_ITEM are:
(OrderNumber, SKU) S (Quantity, Price, ExtendedPrice)
(Quantity, Price) S ExtendedPrice
No single skill is more important for designing databases than the ability to identify func-
tional dependencies. Make sure you understand the material in this section. Work problems
3.58 and 3.59, the Regional Labs case questions, and The Queen Anne Curiosity Shop and
Morgan Importing projects at the end of the chapter. Ask your instructor for help if necessary.
You must understand functional dependencies and be able to work with them.
When Are Determinant Values Unique?
In the previous section, you may have noticed an irregularity. Sometimes the determinants of
a functional dependency are unique in a relation, and sometimes they are not. Consider the
SKU_DATA relation, with determinants SKU, SKU_Description, and Buyer. In SKU_DATA, the