10.1: Functional Dependencies

To understand normalization theory (first, second, third and Boyce-Codd normal forms), we must understand what is meant by the term functional dependency. There is another type of dependency called a multi-valued dependency but that is important to the understanding of higher normal forms not covered in this text.

A functional dependency is an association between two attributes. We say there is a functional dependency from attribute A to an attribute B if and only if for each value of A there can be at most one value for B. We can illustrate this by writing:

• • • A functional determines B, or
    • B is functionally determined by A, or
    • by a drawing such as: 

When we have a functional dependency from A to B, we refer to attribute A as the determinant.

EXAMPLE 1.

Consider a company collects information about each employee such as the employee’s identification number (ID), their first name, last name, salary and gender. As is typical, each employee is given a unique ID which serves to identify the employee. Hence for each value of ID there is at most one value for first name, last name, salary and gender.

Therefore, we have four functional dependencies where ID is the determinant; we can show this as a list or graphically:

If you think about this case, there cannot be any other FDs. For example, consider the gender attribute – we need to allow for more than one employee for a given gender, and so we cannot have a situation where gender functionally determines ID. So, gender  ID cannot exist. Now consider the first name attribute. Again, we need to allow for more than one employee to have the same first name and so first name cannot determine anything. Similarly, for other attributes.

EXAMPLE 2.

Recall the Department and Course tables introduced in Chapter 2 – sample data is shown below:

Recall the primary keys (underlined above) of these two tables:

Consider the Department table where deptCode is the primary key. For each value of deptCode there is at most one value for deptName, deptLocn, deptPhone, and chairName. You should agree the following FDs exist:

• • • deptCode –> deptName
    • deptCode –> deptLocn
    • deptCode –> deptPhone
    • deptCode –> chairName

Each row of the Course table has one value for title, one value for description, and one value for credit hours. The primary key of Course consists of two attributes, deptCode and courseNo. The following FDs exist for the Course table:

• • • deptCode, courseNo –> title
    • deptCode, courseNo –> description
    • deptCode, courseNo –> credit hours

In this case we have a determinant comprising two attributes; the determinant is composite.

We can draw the functional dependencies as:

Could there be other functional dependencies in this situation?

These examples demonstrate that there is a FD from the primary key to each of the other attributes in a table.

EXAMPLE 3.

The following ERD is shown in the Chen notation. There is one entity type named Employee that has 4 attributes. In this design there are two keys (id and sin) and two descriptive attributes (firstName and lastName):

Each symbol in an ERD contains information about a model. From the above we know there are two keys, id and sin. An id value, or a sin value, will uniquely identify an employee and so we have the six FDs:

This example shows that an ERD carries information that can be expressed in terms of FDs.