Functional Dependencies and Their Types
A functional dependency is a relationship between columns where knowing the value of column A always determines the value of column B, written A -> B. It is the mathematical foundation on which every normal form from 1NF to BCNF is defined, and its types include trivial, non-trivial, full, partial and transitive dependencies.
A functional dependency is a relationship between columns in a table: if knowing the value of column A always tells you the value of column B, then B is functionally dependent on A, written A -> B. This concept is the mathematical foundation that every normal form (1NF through BCNF) is formally defined in terms of.
Reading a functional dependency
student_id -> student_name means: for any given student_id, there is exactly one corresponding student_name. Knowing the student ID tells you, unambiguously, what the name is. This doesn't mean the reverse is true, two different students could share the same name, so student_name -> student_id wouldn't necessarily hold.
Types of functional dependencies
Trivial functional dependency. A -> B is trivial if B is a subset of A. For example, {student_id, name} -> student_id is trivial, of course knowing {student_id, name} tells you the student_id, it's part of what you already know. Trivial dependencies aren't useful for design purposes since they're automatically true.
Non-trivial functional dependency. A -> B where B is not a subset of A. student_id -> student_name is non-trivial: name isn't part of what "student_id" already contains, it's a genuinely new piece of information determined by it.
Full functional dependency. B depends on the entire set of columns in A, not just part of it. If {student_id, course_id} -> grade, and removing either student_id or course_id from the left side would break the dependency (a student_id alone doesn't determine a grade, since a student has different grades in different courses), then this is a full functional dependency.
Partial functional dependency. B depends on only part of a composite key A, not the whole thing. If {student_id, course_id} -> student_name, notice that student_name really only depends on student_id alone, course_id is irrelevant to it. This is a partial dependency, and it's specifically what Second Normal Form (2NF) is designed to eliminate.
Transitive functional dependency. A determines B, and B (which is not a key) determines C, meaning A indirectly determines C through B. If student_id -> department_id and department_id -> department_head, then student_id transitively determines department_head, through department_id as the intermediate step. This is specifically what Third Normal Form (3NF) is designed to eliminate.
A worked example
Take this table:
| student_id | course_id | student_name | grade |
|---|---|---|---|
| 1 | 101 | Asha | A |
| 1 | 102 | Asha | B |
| 2 | 101 | Ravi | A |
Here's what the functional dependencies actually look like:
{student_id, course_id} -> gradeis a full functional dependency: a grade genuinely depends on the combination of which student and which course, neither column alone determines it.student_id -> student_nameholds on its own, meaning{student_id, course_id} -> student_nameis a partial functional dependency:student_nameonly really depends onstudent_id, thecourse_idhalf of the key is irrelevant to it.
That partial dependency is exactly why this table isn't in 2NF, and exactly why it should be split into a students table (student_id -> student_name) and a separate enrollments table holding {student_id, course_id} -> grade.
Where this matters in real systems
Functional dependencies aren't just an academic exercise, they're the actual diagnostic tool used to decide how a table should be split during real schema design. Every "why is this in 2NF but not 3NF" question ultimately comes down to identifying exactly which functional dependency (partial or transitive) is present, and restructuring the table specifically to remove it.
What interviewers actually ask about functional dependencies
- "What is a functional dependency, in your own words?" If knowing column A's value always tells you column B's value, B is functionally dependent on A.
- "What's the difference between a partial and a full functional dependency?" A full dependency requires the entire composite key to determine the value; a partial dependency means only part of the composite key is actually needed.
- "What's a transitive dependency, and why does it matter?" When A determines B, and B (a non-key attribute) determines C, so A indirectly determines C. It matters because it's exactly the condition 3NF is defined to eliminate.
- "Identify the functional dependencies in this table." Interviewers often give a small table and ask the candidate to write out its actual dependencies, this is a very common practical check.
Key takeaways
- A functional dependency
A -> Bmeans knowing A's value always determines B's value. - Trivial dependencies are automatically true (B is part of A) and aren't useful for design decisions; non-trivial ones are.
- A partial dependency (a value depends on only part of a composite key) is what 2NF eliminates.
- A transitive dependency (A determines B, B determines C) is what 3NF eliminates.
- Identifying functional dependencies is the actual practical method for deciding how to normalize a table.
More in RDBMS Design
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
What is a functional dependency, in your own words?
If knowing column A's value always tells you column B's value, B is functionally dependent on A.
What's the difference between a partial and a full functional dependency?
A full dependency requires the entire composite key to determine the value; a partial dependency means only part of the composite key is actually needed.
What's a transitive dependency, and why does it matter?
When A determines B, and B (a non-key attribute) determines C, so A indirectly determines C. It matters because it's exactly the condition 3NF is defined to eliminate.
Identify the functional dependencies in this table.
Interviewers often give a small table and ask the candidate to write out its actual dependencies, this is a very common practical check.