MySQL + SQL · Lesson 25

Functional Dependency in DBMS

In this lesson: Functional dependency explained simply — X→Y, trivial, non-trivial, partial, transitive — with examples for normalization.

What is functional dependency

A functional dependency means one column's value determines another's. If knowing column A always tells you column B, then B functionally depends on A. This simple idea is the theory behind keys and the whole process of normalization.

💡 A -> B means: "if you know A, you always know B" — A determines B.

The A -> B notation

We write a functional dependency as A -> B:

A is the determinant (the column you know).
B is the dependent (the column it determines).

It reads "A determines B". For each value of A, there is exactly one value of B.

A simple example

roll	name	dept
1	Aman	Science
2	Diya	Arts

Here roll -> name and roll -> dept: each roll number determines exactly one name and one department. So roll determines the other columns — which is why it makes a good primary key.

Full dependency

A full functional dependency means a column depends on the whole key, not just part of it. If the primary key is (student_id, course_id) and grade needs both to be known, then grade is fully dependent on the composite key. This is exactly what 2NF requires.

Partial dependency

A partial dependency is when a column depends on only part of a composite key.

Key: (student_id, course_id)
student_name depends only on student_id  -- partial!

Here student_name needs only student_id, not the full key. Partial dependencies cause redundancy and are removed in 2NF by splitting the table.

Transitive dependency

A transitive dependency is an indirect one, through another non-key column.

roll -> dept       (roll determines dept)
dept -> dept_head  (dept determines dept_head)
so roll -> dept_head  (indirectly, transitive)

dept_head depends on dept, not directly on roll. Transitive dependencies are removed in 3NF by moving the dependent column to its own table.

Common mistakes

Reversing the arrow. A -> B does not mean B -> A; direction matters.
Confusing partial and transitive. Partial = part of a composite key; transitive = via another non-key column.
Ignoring dependencies when designing. They tell you how to split tables correctly.
Assuming every column depends on the key. Some depend on other columns — that is the problem to fix.

Summary

A functional dependency A -> B means A determines B.
A is the determinant; B is the dependent.
Full dependency: depends on the whole (composite) key.
Partial dependency: depends on part of a composite key (fixed in 2NF).
Transitive dependency: depends via another non-key column (fixed in 3NF).

Next, apply these in normalization, or push further with BCNF.

Frequently Asked Questions

What is a functional dependency in DBMS?

A functional dependency means one column's value determines another column's value. Written A -> B, it says "if you know A, you know B". For example, roll -> name means each roll number determines exactly one student name.

What does A -> B mean?

A -> B means A functionally determines B: for each value of A there is exactly one value of B. A is called the determinant and B the dependent. It is the core idea behind keys and normalization.

What is a partial dependency?

A partial dependency happens when a non-key column depends on only part of a composite primary key, not the whole key. Removing partial dependencies is what Second Normal Form (2NF) does.

What is a transitive dependency?

A transitive dependency is when a non-key column depends on another non-key column, which in turn depends on the key. For example roll -> dept and dept -> dept_head, so roll -> dept_head indirectly. Third Normal Form (3NF) removes these.

Why are functional dependencies important?

They are the foundation of good database design. Identifying dependencies tells you which columns belong together, which should be keys, and how to split tables during normalization to avoid redundancy.