In relational databases, different types of functional dependencies help in understanding relationships between attributes. These dependencies guide database normalization, ensuring data consistency, reducing redundancy, and maintaining data integrity.

Let’s go over four main types of functional dependencies using examples based on a Student and Course table.

1. Trivial Functional Dependency

A trivial functional dependency occurs when an attribute (or set of attributes) in the dependent set is a subset of the determinant set. In simple terms, if an attribute on the right side of a dependency is part of the left side, it is considered trivial.

• Notation: X → Y is trivial if Y is a subset of X.

• Example: Consider the Student table with attributes {Student_ID, Course_ID, Name}.

Here:

• {Student_ID, Course_ID} → {Course_ID} is a trivial functional dependency because Course_ID is a subset of {Student_ID, Course_ID}.
• {Student_ID} → {Student_ID} is also a trivial functional dependency since Student_ID is dependent on itself.

2. Non-Trivial Functional Dependency

A non-trivial functional dependency occurs when the dependent attribute is not a subset of the determinant attribute. This type of dependency provides meaningful information about the relationships between attributes in a table.

• Notation: X → Y is non-trivial if Y is not a subset of X.

• Example: In the Student table above:

  • {Student_ID} → {Name} is a non-trivial functional dependency because Name is not a subset of Student_ID.
  • This implies that each Student_ID uniquely determines the Name of the student, ensuring that Student_ID is unique to each name.

3. Multivalued Functional Dependency

A multivalued functional dependency exists when one attribute determines a set of values for another attribute independently of other attributes. In this case, the dependent attributes are not dependent on each other.

• Notation: X →→ {Y, Z} implies a multivalued dependency if Y and Z are independent.

• Example: Consider a modified Student_Course table where each student can have multiple courses and multiple contact numbers.

Here:

• Student_ID →→ {Course_ID, Contact_Number} is a multivalued functional dependency because Course_ID and Contact_Number are independently associated with Student_ID. The contact number does not depend on the course, and vice versa.

4. Transitive Functional Dependency

A transitive functional dependency occurs when there is an indirect relationship between attributes. If we have two dependencies, X → Y and Y → Z, then by transitivity, X → Z also holds. This type of dependency should be eliminated in normalization to achieve the third normal form (3NF).

• Notation: If X → Y and Y → Z, then X → Z is a transitive dependency.

• Example: Consider the following Student_Department table.

In this case:

• Student_ID → Department_ID and Department_ID → Department_Name.
• By transitivity, Student_ID → Department_Name also holds, which is a transitive dependency. This dependency implies that Department_Name can be derived from Student_ID through Department_ID, resulting in redundant information. Transitive dependencies are often eliminated during normalization.

Summary Table of Functional Dependencies

Understanding these dependencies is essential for effective database normalization and ensures that data remains consistent, non-redundant, and easy to maintain.