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

SALESPERSON table

Salesperson Salesperson Commission Year of Department Manager

Number Name Percentage Hire Number Name

PRODUCT table

Product Product Unit

Number Name Price

QUANTITY table

F IGURE 7.31
The salesperson and product attributes in
second normal form

Salesperson Product

Numbe r Number Quantity

the sole primary key attribute. Every non-key attribute of the table is fully defined
just by Salesperson Number, as can be verified in Figure 7.27. Similarly, the
PRODUCT Table has Product Number as its sole primary key attribute and the
non-key attributes of the table are dependent just on it. The QUANTITY Table has
the combination of Salesperson Number and Product Number as its primary key
because its non-key attribute, Quantity, requires both of them together to define it,
as indicated in the last defining association of Figure 7.27.
Figure 7.32 shows the sample salesperson and product data arranged in the
second normal form structure of Figure 7.31. Indeed, much of the data redundancy
visible in Figure 7.30 has been eliminated. Now, only once is salesperson 137's
name listed as Baker, his commission percentage listed as 10, and so forth. Only
once is product 19440's name listed as Hammer and its unit price listed as 17.50.
Second normal form is thus a great improvement over first normal form. But,
has all of the redundancy been eliminated? In general, that depends on the particular
list of attributes and defining associations. It is possible, and in practice it is often
the case, that second normal form is completely free of data redundancy. In such a
case, the second normal form representation is identical to the third normal form
representation.
A close look at the sample data of Figure 7.32 reveals that the second normal
form structure of Figure 7.31 has not eliminated all the data redundancy. At the
right-hand end of the SALESPERSON Table, the fact that Scott is the manager of
department 73 is repeated three times and this certainly constitutes redundant data.
How could this have happened? Aren't all the non-key attributes fully functionally
dependent on Salesperson Number? They are, but that is not the nature of the
problem. It's true that Salesperson Number defines both Department Number and
Manager Name and that's reasonable. If I'm focusing in on a particular salesperson,
I should know what department she is in and what her manager's name is. But,
as indicated in the next-to- last defining association of Figure 7.27, one of those
two attributes defines the other: given a department number, I can tell you who
the manager of that department is. In the SALESPERSON Table, one of the non-
key attributes, Department Number, defines another one of the non-key attributes,
Manager Name. This is what is causing the problem.