Access Database Design and Programming, Second Edition

Functional Dependencies

Before we can discuss the other normal forms, we need to discuss the concept of functional dependency , which is used to define these normal forms. This concept is quite simple, and we have actually been using it for some time now. As an example, we have remarked that, for the Publishers table scheme, the PubName attribute depends completely on the PubID attribute. (More properly, we should say that the value of the PubName attribute depends completely on the value of the PubID attribute, but the above shorthand is convenient.) Thus, we can say that the functional dependency from PubID to PubName, written:

PubID → PubName

holds for the Publishers table scheme. This can be read “PubID determines PubName” or “PubName depends on PubID.”

More generally, suppose that {A₁,...,A_k} are attributes of a table scheme and that {B₁,...,B_n} are also attributes of the same table scheme. We do not require that the Bs be different from the As. Then the attributes B₁,...,B_n depend on the attributes A₁,...,A_k, written:

{A1,. . .,Ak} → {B1,. . .,Bn}

if the values of A₁,...,A_k completely determine the values of B₁,...,B_n. Our main interest is when there is only one attribute on the right:

{A1,. . .,Ak} → {B}

For instance, it is probably safe to say that:

{PubName,PubPhone} → {PubID}

which is just another way of saying that there is only one publisher with a given name and phone number (including area code).

It is very important to understand that a functional dependency means that the attributes on the left completely determine the attributes on the right for now and for all time to come, no matter what additional data may be added to the database. Thus, just as the concept of a key relates to entity classes (table schemes) rather than individual entity sets (tables), so does functional dependency. Every table scheme has its set of associated functional dependencies, which are based on the meaning of the attributes.

Recall that a superkey is a set of attributes that uniquely determines an entity. Put another way, a superkey is a set of attributes upon which all other attributes of the table scheme are functionally dependent.

Some functional dependencies are obvious. For instance, an attribute functionally depends upon itself. Also, any set of attributes functionally determines any subset of these attributes, as in:

{A,B,C} → {A,B}

This just says that if we know the values of A, B, and C, then we know the value of A and B! Such functional dependencies are not at all interesting, and are called trivial dependencies . All other dependencies are called nontrivial.