In this course, we will study functional dependency, its types, rules for using them and their advantages.

A functional dependency is the dependency of one attribute on another attribute in the database system. It is used to reduce redundancy and to improve the design of the database. We represent functional dependency using an arrow symbol (->).

Let us take an example A->B, here attribute B is dependent on an attribute A or we can say that A can uniquely identify B.

**Trivial functional dependency****Non-trivial functional dependency****Multivalued dependency****Transitive dependency**

If in a relational schema R containing attribute sets X and Y, X is a superset of Y or Y is a subset of X then Y is trivial functional dependent on X.

For example, let us consider a table named student in which there are two attributes namely student_name and student_id. Here in {student_name,student_id} -> student_id, student_id is a subset of {student_name,student_id} and hence it is trivial functional dependent.

If in a relational schema R containing attribute sets X and Y, X is a not superset of Y or Y is not a subset of X then Y is non-trivial functional dependent on X. In other words, we can say that if X can not uniquely identify Y then it is called non-trivial dependency.

For example, let us consider a table named student in which there are two attributes namely student_name and student_id. Here in {student_name} -> student_id, student_id is not a subset of {student_name} and hence it is a non-trivial functional dependent.

If in a relation or table there is more than one independent multivalued attribute then it is called multivalued dependency.

For example, let us consider a table of a car manufacturing company, which produces cars of two colors red and black in each model every year.

Car model | Manufacturing year | Color |
---|---|---|

A1001 | 2005 | Black |

A2012 | 2006 | Red |

A2012 | 2006 | Black |

A2222 | 2008 | Red |

In this example, the columns manufacturing year and color are both independent of each other but are dependent on the car model. In these types of cases, the columns are said to be multivalued dependent and these are represented as car model->>manufacturing year.

If in a relationship a functional dependency is formed indirectly by two functional dependencies.

For example, P->R is a transitive dependency if and only if the following functional dependencies hold true:

P->Q Q does not ->P Q-> |

Note a transitive dependency only occurs if there are three or more than three attributes in a relation. We have already studied it in normalization where we used this dependency for normalizing databases into third normal form.

These are the set of rules which when applied repeatedly generate closure of functional dependencies. There are various axioms:

If B⊆C and B⊆A then A->B

If A->B holds and X is attribute set then AX->BX also holds.

If P->Q and Q->R then P->R.

**The following rules can be derived from the axioms given:**

Union | If X->Y holds and X->Z holds, then X->YZ holds. |
---|---|

Composition | If P->Q and X->Y holds, then PX->QY holds. |

Decomposition | If X-YZ holds then X->Y and X->Z holds. |

Pseudo Transitivity | If P->Q holds and QR->S holds, then PR->S holds. |

- Avoids redundancy.
- Improves data quality.
- The meanings and constraints can be well defined in the database.
- The identification of bad design becomes easy.