LOGICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 183

SALESPERSON table

Salesperson Salesperson Commission Year of Departmen t

Number Name Percentage Hire Numbe r

DEPARTMENT table

Department Manager

Number Name

PRODUCT table

Product Product Unit

Number Name Price

QUANTITY table

Salesperson Product Quantity

F IGURE 7.33
The salesperson and product attributes in
third normal form

Number Number

the DEPARTMENT Table, but a copy of the Department Number attribute (the
primary key attribute of the new DEPARTMENT Table) was left behind in the
SALESPERSON Table. If this had not been done, there no longer would have been
a way to indicate which department each salesperson is in.
The sample data for the third normal form structure of Figure 7.33 is shown
in Figure 7.34. Now, the fact that Scott is the manager of department 73 is shown
only once, in the second record of the DEPARTMENT Table. Notice that the
Department Number attribute in the SALESPERSON Table continues to indicate
which department a salesperson is in.
There are several important points to note about the third normal form structure
of Figure 7.33:

1. It is completely free of data redundancy.
2. All foreign keys appear where needed to logically tie together related tables.
3. It is the same structure that would have been derived from a properly drawn
entity-relationship diagram of the same business environment.

Finally, there is one exception to the rule that in third normal form, non-key
attributes are not allowed to define other non-key attributes. The rule does not hold
if the defining non-key attribute is a candidate key of the table. Let's say, just for the
sake of argument here, that the Salesperson Name attribute is unique. That makes
Salesperson Name a candidate key in Figure 7.33's SALESPERSON Table. But,
if Salesperson Name is unique, then it must define Commission Percentage, Year
of Hire, and Department Number just as the unique Salesperson Number attribute
does. Since it was not chosen to be the primary key of the table, Salesperson Name
is technically a non-key attribute that defines other non-key attributes. Yet it does