Functional Dependency in DBMS

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

What is a functional dependency in DBMS

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.

What are the types of functional dependencies in DBMS?

  1. Trivial functional dependency
  2. Non-trivial functional dependency
  3. Multivalued dependency
  4. Transitive dependency
Functional dependencies in DBMS

Trivial functional 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.

Non Trivial functional dependency

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.

Multivalued dependency

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.

Transitive dependency

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.

What are Functional dependency rules?

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

Axiom of reflexivity

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

Axiom of augmentation

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

Axiom of transitivity

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.

What are the Advantages of Functional dependency