Functional Dependency in DBMS with Examples

Every normal form covered in the rest of this module — 1NF, 2NF, 3NF, BCNF — is defined in terms of a single underlying concept: functional dependency. Without a solid grasp of what it means for one column to 'functionally depend' on another, the normal forms become abstract rules to memorize rather than logical consequences you can reason through yourself.

Key Definitions

• Functional dependency (FD): A relationship between two sets of attributes, written A → B, meaning that the value of A uniquely determines the value of B.
• Determinant: The attribute (or set of attributes) on the left side of a functional dependency, the one that determines the other's value.
• Partial dependency: A functional dependency where a non-key attribute depends on only part of a composite primary key, rather than the whole key.
• Transitive dependency: A functional dependency where a non-key attribute depends on another non-key attribute, rather than directly on the primary key.

What You'll Learn

• Define functional dependency and its standard notation.
• Identify functional dependencies within a sample table.
• Distinguish between full, partial, and transitive functional dependencies.
• Understand why functional dependency is the foundation of normalization theory.

Detailed Explanation

The notation A → B is read as 'A determines B,' meaning that for any two rows sharing the same value of A, they must also share the same value of B. In the doctors table, doctor_id → doctor_name holds true, since knowing a specific doctor_id uniquely tells you exactly one doctor_name — no two rows can have the same doctor_id but different names. This is the most fundamental kind of functional dependency: a key determining a non-key attribute.

Things get more interesting with composite keys. Imagine a table tracking which doctors work in which departments, with a composite primary key of (doctor_id, department_id), and an additional column department_location. Here, department_id → department_location is a partial dependency, because department_location depends on only part of the composite key (department_id alone), not the full (doctor_id, department_id) pair — this is precisely the kind of dependency that Second Normal Form (Lesson 8.8) is designed to eliminate.

A transitive dependency occurs when a non-key column depends on another non-key column rather than directly on the primary key. For example, if a doctors table includes both department_id and department_location, and department_location's value is actually determined by department_id (not by doctor_id directly), then doctor_id → department_id → department_location forms a transitive chain — this is exactly the kind of dependency that Third Normal Form (Lesson 8.9) is designed to eliminate. Recognizing these dependency patterns is the analytical skill that makes every subsequent normal form immediately intuitive rather than a memorized rule.

Visual Summary

Three labeled arrow diagrams stacked vertically. First: [doctor_id] --→--> [doctor_name], labeled 'Simple FD: key determines non-key attribute'. Second: [doctor_id, department_id] (composite key) with an arrow from only [department_id] --→--> [department_location], labeled 'Partial dependency — depends on only part of the composite key'. Third: [doctor_id] --→--> [department_id] --→--> [department_location], labeled 'Transitive dependency — non-key determines non-key'.

Quick Reference

Dependency Type	Example	Problem It Signals
Full functional dependency	doctor_id → doctor_name	None — this is the healthy, expected pattern
Partial dependency	(doctor_id, department_id) composite key, but department_id alone → department_location	Signals a 2NF violation
Transitive dependency	doctor_id → department_id → department_location	Signals a 3NF violation

SQL Example

-- Illustrative table with a transitive dependency (not yet normalized)
-- doctor_id | doctor_name | department_id | department_location
-- 101       | Dr. Verma   | 1              | Building A
-- 104       | Dr. Khan    | 1              | Building A   <-- department_location repeated!

-- Here: doctor_id -> department_id -> department_location
-- department_location is transitively dependent on doctor_id via department_id,
-- NOT directly dependent on doctor_id. This redundancy (Building A repeated)
-- is the practical symptom that reveals the transitive dependency.

department_location's value is fully determined by department_id alone — every doctor in department_id 1 will always show 'Building A', regardless of which doctor it is. This means department_location does not directly depend on doctor_id; it depends on doctor_id only indirectly, through department_id. The repeated 'Building A' value across multiple doctor rows is the visible symptom of this transitive dependency, and it's exactly the kind of redundancy that Third Normal Form eliminates by moving department_location into its own departments table.

Real-World Examples

• Database design tools and normalization advisors analyze functional dependencies automatically to suggest how a flat table should be split into properly normalized tables.
• Data warehouse architects document functional dependencies explicitly as part of formal schema design reviews before implementation.
• Database courses and certification exams (like those covering DBMS theory) test functional dependency identification as a core prerequisite skill for understanding normal forms.

Common Mistakes to Avoid

• Confusing partial dependency (depends on part of a composite key) with transitive dependency (depends on another non-key attribute).
• Not checking for functional dependencies before attempting to apply normal form rules, treating normalization as memorized steps rather than logical analysis.
• Assuming every relationship between columns is a functional dependency, without verifying the 'uniquely determines' condition actually holds.

Interview Questions

Q1. What is a functional dependency?

A functional dependency, written A → B, means that the value of attribute A uniquely determines the value of attribute B — any two rows with the same A value must also share the same B value.

Q2. What is the difference between a partial dependency and a transitive dependency?

A partial dependency occurs when a non-key attribute depends on only part of a composite primary key rather than the whole key. A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than directly on the primary key.

Q3. Why is functional dependency considered the foundation of normalization?

Every normal form is defined in terms of eliminating a specific type of problematic functional dependency — 2NF eliminates partial dependencies, and 3NF eliminates transitive dependencies — so understanding functional dependency is a prerequisite to understanding why each normal form's rules exist.

Practice MCQs

1. What does the notation A → B mean in functional dependency?

1. A and B are always equal
2. The value of A uniquely determines the value of B
3. A is a foreign key referencing B
4. B must be a primary key

Answer: B. The value of A uniquely determines the value of B

Explanation: A → B means that for any two rows with the same value of A, the value of B must also be the same, indicating A determines B.

2. What kind of dependency does 2NF specifically eliminate?

1. Transitive dependency
2. Partial dependency
3. Multi-valued dependency
4. Full functional dependency

Answer: B. Partial dependency

Explanation: Second Normal Form specifically addresses and eliminates partial dependencies, where a non-key attribute depends on only part of a composite primary key.

Quick Revision Points

• Functional dependency notation: A → B means 'A determines B.'
• Partial dependency relates to composite keys; transitive dependency relates to non-key-to-non-key chains.
• Every normal form (2NF, 3NF, BCNF) is defined by eliminating a specific category of problematic functional dependency.

Conclusion

• A functional dependency means one attribute's value uniquely determines another's.
• Partial dependencies involve composite keys; transitive dependencies involve chains through non-key attributes.
• Understanding functional dependency makes every subsequent normal form logical rather than memorized.

Functional dependency is the foundational concept underlying all of normalization theory: A → B means the value of A uniquely determines the value of B. Recognizing partial dependencies (where a non-key attribute depends on only part of a composite key) and transitive dependencies (where a non-key attribute depends on another non-key attribute rather than directly on the primary key) is essential, since Second Normal Form and Third Normal Form are defined precisely as the elimination of these two dependency patterns, respectively.