Database Reference
In-Depth Information
Q&A
Question: Is QuotedPrice functionally dependent on PartNum?
Answer: No. A given part number, such as DR93, can occur on more than one row in the OrderLine table.
The quoted price can be different on each row. With current data, the two rows on which the part number is
DR93 actually have the same price, $495.00. What is important, however, is not that they happen to be the
same now, but whether there is a guarantee that they will always be the same. That is certainly not the case.
In fact, the reason for storing quoted prices in the OrderLine table is to allow two rows with the same part
number to have different prices.
158
Q&A
Question: On which columns is QuotedPrice functionally dependent?
Answer: For any combination of an order number and a part number, there can be only one row in the
OrderLine table. Thus, any combination of an order number and part number in the OrderLine table is asso-
ciated with exactly one quoted price. Consequently, QuotedPrice is functionally dependent on the combina-
tion (formally called the concatenation) of OrderNum and PartNum.
At this point, a question naturally arises: How do you determine functional dependencies? Can you
determine them by looking at sample data, for example? The answer is no.
Consider the Rep table shown in Figure 5-3, in which all last names are unique. It is very tempting to say
that LastName functionally determines Street, City, State, and Zip (or equivalently that Street, City, State,
and Zip are all functionally dependent on LastName). After all, given the last name of a rep, you can find his
or her address.
Rep
RepNum
LastName
FirstName
Street
City
State
Zip
Commission
Rate
20
Kaiser
Valerie
624 Randall
Grove
FL
33321
$20,542.50
0.05
35
Hull
Richard
532 Jackson
Sheldon
FL
33553
$39,216.00
0.07
65
Perez
Juan
1626 Taylor
Fillmore
FL
33336
$23,487.00
0.05
FIGURE 5-3
Rep table
What happens when you add rep 85, whose last name also is Kaiser, to the database? Now you have the
situation illustrated in Figure 5-4. If the last name you are given is Kaiser, you no longer can find a single
address. Thus, you were misled by the original sample data. The only way to determine the functional
dependencies that exist is to examine users
'
policies through discussions with users, an examination of user
documentation, and so on.
Rep
RepNum
LastName
FirstName
Street
City
State
Zip
Commission
Rate
20
Kaiser
Valerie
624 Randall
Grove
FL
33321
$20,542.50
0.05
35
Hull
Richard
532 Jackson
Sheldon
FL
33553
$39,216.00
0.07
65
Perez
Juan
1626 Taylor
Fillmore
FL
33336
$23,487.00
0.05
85
Kaiser
Wil
172 Bahia
Norton
FL
39281
$0.00
0.05
FIGURE 5-4
Rep table with second rep named Kaiser added
