Databases Reference
In-Depth Information
Figure 4-27
SALESPERSON/PRODUCT table
Salesperson
Name
Commission
Percentage
Salesperson
Number
Product
Number
Year of
Hire
Department
Number
Manager
Name
Product
Name
Unit
Price
Quantity
137
137
137
186
186
186
186
204
204
361
361
361
19440
24013
26722
16386
19440
21765
24013
21765
26722
16386
21765
26722
Baker
Baker
Baker
Adams
Adams
Adams
Adams
Dickens
Dickens
Carlyle
Carlyle
Carlyle
10
10
10
15
15
15
15
10
10
20
20
20
1995
1995
1995
2001
2001
2001
2001
1998
1998
2001
2001
2001
73
73
73
59
59
59
59
73
73
73
73
73
Scott
Scott
Scott
Lopez
Lopez
Lopez
Lopez
Scott
Scott
Scott
Scott
Scott
Hammer
Saw
Pliers
Wrench
Hammer
Drill
Saw
Drill
Pliers
Wrench
Drill
Pliers
17.50
26.25
11.50
12.95
17.50
32.99
26.25
32.99
11.50
12.95
32.99
11.50
473
170
688
1475
2529
1962
3071
809
734
3729
3110
2738
Data normalized to the first normal form.
according to the list of defining associations or functional dependencies of Fig-
ure 4-24, every attribute in the table is either part of the primary key or is defined
by one or both attributes of the primary key. This is actually a requirement of
the second normal form, as we will see later. Salesperson Name, Commission Per-
centage, Year of Hire, Department Number, and Manager Name are each defined
by Salesperson Number. Product Name and Unit Price are each defined by Prod-
uct Number. Quantity is defined by the combination of Salesperson Number and
Product Number.
These two different ways of approaching the primary key selection are
equivalent. If the combination of a particular Salesperson Number and a par-
ticular Product Number is unique, then it identifies exactly one record of the
table. And if it identifies exactly one record of the table, then that record
shows the single value of each of the nonkey attributes that is associated with
the unique combination of the key attributes. But that is the same thing as
saying that each of the nonkey attributes is defined by or is functionally
dependent on the primary key. For example, consider the first record of the
table in Figure 4-27.
The combination of Salesperson Number 137 and Product Number 19440
is unique. Only one record in the table can have that combination of Salesper-
son Number and Product Number values. Therefore, if someone specifies those
values, the only Salesperson Name that can be associated with them is Baker,
the only Commission Percentage is 10, and so forth. But that has the same effect
