Types of Functional Dependencies

Functional dependencies describe how the values of some attributes are determined by the values of others. Sorting them into types is what lets us reason about redundancy and drive normalization. This lesson covers the four types you meet most often — trivial, non-trivial, multivalued, and transitive — using a small running example of students, courses, and departments.

Throughout, X → Y reads “X functionally determines Y”: any two rows that agree on all attributes of X must also agree on all attributes of Y. The arrow always points from the determinant (left) to the dependent (right).

1. Trivial functional dependency

A functional dependency X → Y is trivial when Y is a subset of X. Such a dependency holds automatically in every relation, because a row cannot disagree with itself on attributes it already contains — it carries no information about the data.

Rule: X → Y is trivial if and only if Y ⊆ X.

Consider a Student table with attributes {Student_ID, Course_ID, Name}:

• {Student_ID, Course_ID} → {Course_ID} is trivial because Course_ID is a subset of the determinant.
• {Student_ID} → {Student_ID} is trivial because any attribute set determines itself.

2. Non-trivial functional dependency

A functional dependency X → Y is non-trivial when Y is not a subset of X — that is, Y contains at least one attribute outside X. These are the dependencies that carry real information about the data and that constraint design must account for.

Rule: X → Y is non-trivial if and only if Y ⊈ X (some attribute of Y lies outside X). It is sometimes called completely non-trivial when X and Y share no attributes at all, i.e. X ∩ Y = ∅.

In the Student table above, {Student_ID} → {Name} is non-trivial: Name is not part of Student_ID. It asserts a genuine constraint — every row with a given Student_ID must carry the same Name.

3. Multivalued dependency

A multivalued dependency (MVD), written X ↠ Y, captures a different shape of constraint: for a fixed value of X, the set of Y values is independent of the rest of the row. It arises when one entity (here, a student) is associated with two independent multi-valued facts — for example several courses and several phone numbers — that have nothing to do with each other. Forcing both into one table multiplies the rows, because every course must be paired with every phone number.

Formal definition. Let the relation’s attributes be partitioned into three disjoint groups X, Y, and Z (where Z is everything not in X or Y). The MVD X ↠ Y holds when, for any two rows that agree on X but may differ on both Y and Z, the relation also contains the two rows formed by swapping their Y and Z parts. Concretely, if rows (x, y, z) and (x, y′, z′) both exist (same x, but y ≠ y′ and z ≠ z′), then the swapped rows (x, y, z′) and (x, y′, z) must also exist. The Y values attach to X regardless of which Z they appeared with — that is what “independent” means.

Consider a Student_Course table where each student may take several courses and have several contact numbers, and the two are unrelated:

Here Student_ID ↠ Course_ID (and, symmetrically, Student_ID ↠ Contact_Number). With X = Student_ID, Y = Course_ID, Z = Contact_Number, take the two rows (101, C101, 123-456-7890) and (101, C102, 098-765-4321) — same student, different course and different number. The definition then forces the swapped rows (101, C101, 098-765-4321) and (101, C102, 123-456-7890) to exist as well, which is exactly why the table must hold the full cross-product of courses against numbers. That redundancy is what fourth normal form (4NF) removes, by splitting the two independent facts into separate tables. Note that every functional dependency is also a multivalued dependency, but not the reverse.

4. Transitive dependency

A transitive dependency is an indirect determination: if X → Y and Y → Z hold, then X → Z follows by transitivity. When the middle attribute Y is neither a key nor part of one, this indirection is exactly the redundancy that third normal form (3NF) targets.

Precise condition (3NF). A non-trivial dependency X → Z violates 3NF when it is transitive in the technical sense: there is an attribute set Y such that X → Y and Y → Z, where Y is not a superkey (so Y → X does not hold) and Z is a non-prime attribute (Z belongs to no candidate key). Both clauses matter: if Y were a superkey the dependency would be acceptable, and if Z were prime 3NF would tolerate it. Loosely saying “X reaches Z through a middle attribute” misses these two precise conditions.

Consider a Student_Department table:

The candidate key is Student_ID. We have Student_ID → Department_ID and Department_ID → Department_Name, so by transitivity Student_ID → Department_Name. Check the condition against this dependency: the middle set Department_ID is not a superkey (knowing the department does not identify the student — rows 101 and 103 share D01), and Department_Name is non-prime (it is in no candidate key). Both clauses are met, so this is a genuine transitive dependency. The visible cost is redundancy: “Science” is stored once per student in D01, and renaming the department means updating every such row. Normalization to 3NF removes it by moving {Department_ID, Department_Name} into its own table.

Summary

Classifying a dependency tells you what normal form it threatens and how to repair it: trivial and non-trivial describe whether a dependency carries information, while multivalued and transitive dependencies pinpoint the specific redundancies that 4NF and 3NF respectively eliminate.

Sources

• Silberschatz, A., Korth, H. F., & Sudarshan, S. Database System Concepts, 7th ed., McGraw-Hill, 2019 — functional dependencies, multivalued dependencies, and the formal definitions of 3NF and 4NF (Chapters 7–8).
• Elmasri, R., & Navathe, S. B. Fundamentals of Database Systems, 7th ed., Pearson, 2016 — dependency types, the prime/non-prime attribute distinction, and normalization through 4NF.
• Garcia-Molina, H., Ullman, J. D., & Widom, J. Database Systems: The Complete Book, 2nd ed., Pearson, 2008 — the row-swap characterization of multivalued dependencies.
• Codd, E. F. “Further Normalization of the Data Base Relational Model”, IBM Research Report RJ909, 1971 — original treatment of transitive dependencies and third normal form.