Databases Reference
In-Depth Information
as the concept of functional dependency. Since Salesperson Name is functionally
dependent on Salesperson Number, given a particular Salesperson Number, say
137, only one Salesperson Name can be associated with it, Baker. Since Com-
mission Percentage is functionally dependent on Salesperson Number, given a
particular Salesperson Number, say 137, there can be only one Commission Per-
centage associated with it, 10.
First normal form is merely a starting point in the normalization process.
At this point, we have a great deal of data redundancy. Three records involve
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, two records involve 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 differ-
ent kinds of entities, salespersons and products, have been mixed together in
one table.
In some references, you will see the first normal form described differently,
stated that each table within a row must be uniquely identified. Data designers
who prefer that definition roll the requirement for atomic data to the second
normal form. In that case, the requirements of the second and third normal
forms, as described in this chapter, are combined as the third normal form. In
either case, the final goal and final result remains the same.
Normalizing to Second Normal Form
Since data normalization is a decomposition process, the next step will be to
decompose the table defined in Figure 4-26 into smaller tables to eliminate data
redundancy. And since we have established that at least some of the redundancy
is due to mixing attributes about salespersons and products, it seems reasonable
to want to separate them out. Informally, we will look at each of the nonkey
attributes and decide which attributes of the key are really needed to define it.
For example, Salesperson Name really only needs Salesperson Number to define
it. Product Name needs only Product Number to define it. Quantity needs both
attributes.
More formally, second normal form does not allow
partial functional
dependencies
where data is dependent on part of the primary key. That is, in
a table in
second normal form,
every nonkey attribute must be fully func-
tionally dependent on the entire key of that table. In plain language, a nonkey
attribute cannot depend on only part of the key, the way that Salesperson Name,
Product Name, and most of the other nonkey attributes of Figure 4-26 violate
this restriction.
Figure 4-28 shows the salesperson and product attributes arranged in the
second normal form. There is a SALESPERSON table in which Salesperson
Number is the sole primary key attribute. Every nonkey attribute of the table
