Databases Reference
In-Depth Information
Normalizing to Third Normal Form
In
third normal form,
nonkey attributes are not allowed to define other nonkey
attributes. Stated more formally, third normal form does not allow
transitive
dependencies
in which one nonkey attribute is functionally dependent on another.
In Figure 4-29, you see that in the SALESPERSON table, Department Num-
ber, and Manager Name are both nonkey attributes. Department Number defines
Manager Name. Figure 4-30 shows the third normal form representation of the
attributes. Note that the SALESPERSON table in Figure 4-28 has been further
decomposed into the SALESPERSON and DEPARTMENT tables in Figure 4-30.
The Department Number and Department Manager attributes, which were the
cause of redundant data in the second normal form, were split off to form the
DEPARTMENT table, but a copy of the Department Number attribute (the pri-
mary 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 each salesperson's department, and you would have lost
that relationship. Keep in mind, the goal is
non-loss
decomposition. In other
words, no data is lost in the process.
The sample data for the third normal form structure in Figure 4-30 is shown
in Figure 4-31. Now, the fact that Scott is the manager of department 73 is shown
Figure 4-30
SALESPERSON table
Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of
Hire
Department
Number
DEPARTMENT table
Department
Number
Manager
Name
PRODUCT table
Product
Number
Product
Name
Unit
Price
QUANTITY table
Salesperson
Number
Product
Number
Quantity
Relational tables in the third normal form.
