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

much to check. As one example, consider the General Hardware Co. database of
Figure 7.19.
The basic idea in checking the structural worthiness of relational tables with
the data normalization rules is to:
1. Check to see if there are any partial functional dependencies. That is, check
whether any non-key attributes are dependent on or are defined by only part of
the table's primary key.
2. Check to see if there are any transitive dependencies. That is, check whether
any non-key attributes are dependent on or are defined by any other non-key
attributes (other than candidate keys).
Both of these can be verified by the business environment's list of defining
associations or functional dependencies.
In the SALESPERSON Table of Figure 7.19, there is only one attribute,
Salesperson Number, in the primary key. Therefore there cannot be any partial
functional dependencies. By their very definition, partial functional dependencies
require the presence of more than one attribute in the primary key, so that a non-key
attribute can be dependent on only part of the key ! As for transitive dependencies,
are any non-key attributes determined by any other non-key attributes? No! And,
even if Salesperson Name is assumed to be a unique attribute and therefore it defines
Commission Percentage and Year of Hire, this would be an allowable exception
because Salesperson Name, being unique, would be a candidate key. The same
analysis can be made for the other General Hardware tables with single-attribute
primary keys: the CUSTOMER, PRODUCT, and OFFICE tables of Figure 7.19.
Figure 7.19's CUSTOMER EMPLOYEE Table has a two-attribute primary
key because Employee Number is unique only within a customer. But then, by
the very same logic, the non-key attributes Employee Name and Title must be
dependent on the entire key, because that is the only way to uniquely identify who
we are talking about when we want to know a person's name or title. Analyzing
this further, Employee Name cannot be dependent on Employee Number alone
because it is not a unique attribute. Functional dependency requires uniqueness
from the determining side. And, obviously, Employee Name cannot be dependent
on Customer Number alone. A customer company has lots of employees, not just
one. Therefore, Employee Name and Title must be dependent on the entire primary
key and the rule about no partial functional dependencies is satisfied. Since the
non-key attributes Employee Name and Title do not define each other, the rule
about no transitive dependencies is also satisfied and thus the table is clearly in third
normal form.
In the SALES Table of Figure 7.19, there is a two-attribute primary key
and only one non-key attribute. This table exists to represent the many-to-
many relationship between salespersons and products. The non-key attributes, just
Quantity in this case, constitute intersection data. By the definition of intersection
data these non-key attributes must be dependent on the entire primary key. In
any case, there would be a line in the functional dependency list indicating that
Quantity is dependent on the combination of the two key attributes. Thus, there are
no partial functional dependencies in this table. Interestingly, since there is only one