| Lesson 8 || What does it mean when columns are functionally dependent? |
| Objective || Define functional dependencies. |
Define the Functional Dependencies of Normalization
In order for a relation to be in 2NF, the technical definition states that every non-key attribute must be functionally dependent on every column in the primary key.
Let us begin by defining functional dependencies.
Consider the following relation:
Customer (CustID, CustFirst, CustLast, Address, Phone)
In this relation, CustID is the primary key, which means that every record in the table will contain a unique CustID value.
It also means that a Customer relation's CustID field determines the values of the other fields in the relation.
In other words, the CustFirst, CustLast, Address, and Phone attributes are functionally dependent on the contents of the CustID field.
The field(s) that determine the values of other fields in a relation is known as the relation's determinant.
A determinant is always a candidate key for the relation.
Since more than one customer can have the same name, live at the same address, and have the same phone number,
the functional dependency does not work in the opposite direction.
In other words, the CustID field is not functionally dependent on the CustFirst, CustLast, Address, and Phone fields.
The next lesson describes the process of identifying functional dependencies.
Dependency and Determinants
- Functional dependency: Z is functionally dependent on X if the value of Y is determined by X.
In other words, if Y = X +1, the value of X will determine the resultant value of Y. Thus, Y is dependent on X as a function of the value of X.
- Determinant: The determinant in the description of functional dependency in the previous point is X because X determines the value Y, at least partially because 1 is added to X as well.
A determinant is the inversion or opposite of functional dependency.
- Transitive dependence: Z is transitively dependent on X when X determines Y and Y determines Z.
Transitive dependence thus describes that Z is indirectly dependent on X through its relationship with Y.
- Candidate key: A candidate key (potential or permissible key) is a field or combination of fields that can act as a primary key field for a table. Thus uniquely identifying each record in the table.
Another type of constraint is the functional dependency constraint, which establishes
a functional relationship among two sets of attributes X and Y. This constraint specifies
that the value of X determines a unique value of Y in all states of a relation; it is
denoted as a functional dependency X --> Y.