Types of Functional dependencies in DBMS

In relational database management, functional dependency is a concept that specifies the relationship between two sets of attributes where one attribute determines the value of another attribute. It is denoted as X → Y, where the attribute set on the left side of the arrow, X is called Determinant, and Y is called the Dependent. 

What is Functional Dependency?

A functional dependency occurs when one attribute uniquely determines another attribute within a relation. It is a constraint that describes how attributes in a table relate to each other. If attribute A functionally determines attribute B we write this as the A→B.

Functional dependencies are used to mathematically express relations among database entities and are very important to understanding advanced concepts in Relational Database Systems.

Example:

From the above table we can conclude some valid functional dependencies:

• roll_no → { name, dept_name, dept_building }→  Here, roll_no can determine values of fields name, dept_name and dept_building, hence a valid Functional dependency
• roll_no → dept_name , Since, roll_no can determine whole set of {name, dept_name, dept_building}, it can determine its subset dept_name also.
• dept_name → dept_building ,  Dept_name can identify the dept_building accurately, since departments with different dept_name will also have a different dept_building
• More valid functional dependencies: roll_no → name, {roll_no, name} ⇢ {dept_name, dept_building}, etc.

Here are some invalid functional dependencies:

• name → dept_name   Students with the same name can have different dept_name, hence this is not a valid functional dependency.
• dept_building → dept_name    There can be multiple departments in the same building. Example, in the above table departments ME and EC are in the same building B2, hence dept_building → dept_name is an invalid functional dependency.
• More invalid functional dependencies: name → roll_no, {name, dept_name} → roll_no, dept_building → roll_no, etc.

Read more about What is Functional Dependency in DBMS ?

Types of Functional Dependencies in DBMS

1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e. If X → Y and Y is the subset of X, then it is called trivial functional dependency.

Symbolically: A→B is trivial functional dependency if B is a subset of A.

The following dependencies are also trivial: A→A & B→B

Example 1 :

• ABC -> AB
• ABC -> A
• ABC -> ABC

Example 2:

Here, {roll_no, name} → name is a trivial functional dependency, since the dependent name is a subset of determinant set {roll_no, name}. Similarly, roll_no → roll_no is also an example of trivial functional dependency. 

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant. i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.

Example 1 :

• Id -> Name
• Name -> DOB

Example 2:

Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not a subset of determinant roll_no. Similarly, {roll_no, name} → age is also a non-trivial functional dependency, since age is not a subset of {roll_no, name} 

3. Semi Non Trivial Functional Dependencies 

A semi non-trivial functional dependency occurs when part of the dependent attribute (right-hand side) is included in the determinant (left-hand side), but not all of it. This is a middle ground between trivial and non-trivial functional dependencies. X -> Y is called semi non-trivial when X intersect Y is not NULL. 

Example:

Consider the following table:

Functional Dependency:

{StudentID,CourseID}→CourseID

This is semi non-trivial because:

• Part of the dependent attribute (Course_ID) is already included in the determinant ({Student_ID, Course_ID}).
• However, the dependency is not completely trivial because {StudentID}→CourseID is not implied directly.

4. Multivalued Functional Dependency

In Multivalued functional dependency, entities of the dependent set are not dependent on each other. i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is called a multivalued functional dependency.

Example:

In this table:

• X: bike_model
• Y: color
• Z: manuf_year

For each bike model (bike_model):

1. There is a group of colors (color) and a group of manufacturing years (manuf_year).
2. The colors do not depend on the manufacturing year, and the manufacturing year does not depend on the colors. They are independent.
3. The sets of color and manuf_year are linked only to bike_model.

That’s what makes it a multivalued dependency.

In this case these two columns are said to be multivalued dependent on bike_model. These dependencies can be represented like this:

Read more about Multivalued Dependency in DBMS.

5. Transitive Functional Dependency

In transitive functional dependency, dependent is indirectly dependent on determinant. i.e. If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive functional dependency.

Example:

Here, enrol_no → dept and dept → building_no. Hence, according to the axiom of transitivity, enrol_no → building_no is a valid functional dependency. This is an indirect functional dependency, hence called Transitive functional dependency.

6. Fully Functional Dependency

In full functional dependency an attribute or a set of attributes uniquely determines another attribute or set of attributes. If a relation R has attributes X, Y, Z with the dependencies X->Y and X->Z which states that those dependencies are fully functional.

Read more about Fully Functional Dependency.

7. Partial Functional Dependency

In partial functional dependency a non key attribute depends on a part of the composite key, rather than the whole key. If a relation R has attributes X, Y, Z where X and Y are the composite key and Z is non key attribute. Then X->Z is a partial functional dependency in RBDMS.

Read more about Partial Dependency.

For more details you can refer Database Normalization and Difference between Fully and Partial Functional Dependency articles.