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

But that is the same thing as saying that each of the non-key attributes is
defined by or is functionally dependent on the primary key! For example, consider
the first record of the table in Figure 7.30.

Sales-person Product Sales-person Commission Year of Department Manager Product Unit

Number Number Name Percentage Hire Number Name Name Price Quantity

137 19440 Baker 10 1995 73 Scott Hammer 17.50 473

The combination of Salesperson Number 137 and Product Number 19440 is
unique. There is only one record in the table that can have that combination of
Salesperson 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
as the concept of functional dependency. Since Salesperson Name is functionally
dependent on Salesperson Number, given a particular Salesperson Number, say
137, there can be only one Salesperson Name associated with it, Baker. Since
Commission Percentage is functionally dependent on Salesperson Number, given
a particular Salesperson Number, say 137, there can be only one Commission
Percentage associated with it, 10. And so forth.
First normal form is merely a starting point in the normalization process. As
can immediately be seen from Figure 7.30, there is a great deal of data redundancy
in first normal form. There are three records involving salesperson 137 (the first
three records) and so there are three places in which his name is listed as Baker, his
commission percentage is listed as 10, and so on. Similarly, there are two records
involving product 19440 (the first and fifth records) and this product's name is listed
twice as Hammer and its unit price is listed twice as 17.50. Intuitively, the reason
for this is that attributes of two different kinds of entities, salespersons and products,
have been mixed together in one table.

Second Normal Form Since data normalization is a decomposition process, the
next step will be to decompose the table of Figure 7.29 into smaller tables to
eliminate some of its data redundancy. And, since we have established that at least
some of the redundancy is due to mixing together attributes about salespersons
and attributes about products, it seems reasonable to want to separate them out at
this stage. Informally, what we are going to do is to look at each of the non-key
attributes of the table in Figure 7.29 and, on the basis of the defining associations
of Figure 7.27, decide which attributes of the key are really needed to define it. For
example, Salesperson Name really only needs Salesperson Number to define it; it
does not need Product Number. Product Name needs only Product Number to define
it; it does not need Salesperson Number. Quantity indeed needs both attributes,
according to the last defining association of Figure 7.27.
More formally, second normal form, which is what we are heading for, does
not allow partial functional dependencies . That is, in a table in second normal form,
every non-key attribute must be fully functionally dependent on the entire key of
that table. In plain language, a non-key attribute cannot depend on only part of
the key, in the way that Salesperson Name, Product Name, and most of the other
non-key attributes of Figure 7.29 do.
Figure 7.31 shows the salesperson and product attributes arranged in second
normal form. There is a SALESPERSON Table in which Salesperson Number is