Notes and Study Materials

Functional Dependence in DBMS

The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B.


Example: PROJ_NUM -- -->PROJ_NAME
(read as “PROJ_NUM functionally determines PROJ_NAME”)

In this case, the attribute PROJ_NUM is known as the “determinant” attribute, and the attribute PROJ_NAME is known as the “dependent” attribute.   

 

Partial Dependency

 

A partial dependency exists when there is a functional dependence in which the determinant is only part of the primary key (remember we are assuming there is only one candidate key). 


For example, if (A, B) --> (C,D), B---->C, and (A, B) is the primary key, then the functional dependence   B---> C  is a partial dependency because only part of the primary key (B) is needed to determine the value of C. Partial dependencies tend to be rather straightforward and easy to identify.

A transitive dependency exists when there are functional dependencies such that X--->Y, Y--->Z, and X is the primary key. In that case, the dependency X--->Z is a transitive dependency because X determines the value of Z via Y. Unlike partial dependencies, transitive dependencies are more difficult to identify among a set of data.

Fortunately, there is an easier way to identify transitive dependencies. A transitive dependency will occur only when a functional dependence exists among nonprime attributes.

In the previous example, the actual transitive dependency is X--->Z.  However, the dependency Y--->Z signals that a transitive dependency exists.

Multivalued dependency

The type of dependency that exists when there are at least three attributes (e.g., A, B, and C) in a relation, with a well-defined set of B and C values for each A value, but those Band C values are independent of each other.

Find Other DBMS Questions