Database Reference
In-Depth Information
FUNCTIONAL DEPENDENCE
Understanding functional dependence is crucial to learning the material in the rest of this chapter. Func-
tional dependence is a formal name for what is basically a simple idea. To understand functional depen-
dence, suppose the Rep table for Premiere Products contains an additional column named PayClass, as
shown in Figure 5-2.
157
Rep
RepNum
LastName
FirstName
Street
City
State
Zip
Commission
PayClass
Rate
0
20
Kaiser
Valerie
624 Randall
Grove
FL
33321
$20,542.50
1
.05
35
Hull
Richard
532 Jackson
Sheldon
FL
33553
$39,216.00
2
0
0
.07
65
Perez
Juan
1626 Taylor
Fillmore
FL
33336
$23,487.00
1
.05
FIGURE 5-2
Rep table with additional column, PayClass
Assume one of the policies at Premiere Products is that all sales reps in any given pay class earn the
same commission rate. How might you convey this fact to someone else? You might say that a sales rep
'
s pay
class determines his or her commission rate. Another way to convey this fact is to say that a sales rep
s com-
mission rate depends on his or her pay class. This phrasing uses the words determines and depends on
exactly the way you will use them in connection with database design. If you wanted to be more formal, you
would precede either expression with the word functionally. Thus, you might say,
'
“
A sales rep
'
s pay class
functionally determines his or her commission rate
”
or
“
A sales rep
'
s commission rate functionally depends
on his or her pay class.
”
The formal definition of functional dependence is as follows:
Definition:
A column (attribute) B is functionally dependent on another column A (or possibly a collection
of columns) when each value for A in the database is associated with exactly one value of B.
You can think of functional dependence as follows: If you are given a value for A in the database, do you
know whether it will be associated with exactly one value of B? If so, B is functionally dependent on A (writ-
ten as A
B). If B is functionally dependent on A, you can also say that A functionally determines B.
In the Rep table, LastName is functionally dependent on RepNum. If you are given a value of 20 for
RepNum, for example, you know that you will find a single LastName (in this case, Kaiser) associated with it.
(Note: You need to be concerned only with actual values of RepNum in the database. If you are given a value
of 21 for RepNum, for example, you will not find any names associated with it because there is no row in the
Rep table on which the rep number is 21.)
→
Q&A
Question: In the Customer table, is CustomerName functionally dependent on RepNum?
Answer: No. Rep number 20, for example, occurs on a row in which the customer name is Al
'
s Appliance and
Sport, on a row in which the customer name is Kline
s, and on a row in which the customer name is All Sea-
son. Thus, a rep number can be associated with more than one customer name.
'
Q&A
Question: In the OrderLine table, is QuotedPrice functionally dependent on OrderNum?
Answer: No. Order number 21617, for example, occurs on a row in which the quoted price is $794.95 and on
another row in which the quoted price is $150.00. Thus, an order number can be associated with more than
one quoted price.
