| Lesson 8 || What does it mean when columns are functionally dependent? |
| Objective || Define functional dependencies. |
Define Functional Dependencies
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.
Functional Dependency Direction
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.
An attribute A
is functionally dependent on a "set of attributes X" if and only if
a "Value of X" uniquely determines a value of A.
A functional dependency says:
If you give me the value of the primary key, that will give me the rest of the fields in the relationship.
All the fields are dependent on the primary key
Using a biological example, the mother is functionally dependent on the child.
Functional dependency is a 1 to 1 relationship. For every child, there is only one mother. That is why the relationship goes from child to mother.
It is denoting a 1 to 1 relationship where the fields ("set of attributes X") are dependent on the primary key.
The primary key is the determinant.
X --> A
child --> mother
mother --> child
- The value of child implies the value of mother
- Value of mother does NOT imply value of child
- Child is the determinant (For every child, there is only one mother)
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.
- 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.
The functional dependency constraint 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.
The next lesson describes the process of identifying functional dependencies.
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.
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.
A field (or combination of fields) that uniquely identifies a record in a table.