Functional Dependencies in a Relational Database

The preceding lesson introduced Second Normal Form and stated that a relation is in 2NF when every non-key attribute is fully functionally dependent on the entire primary key. That definition rests on a concept that has not yet been formally defined: functional dependency. This lesson provides that definition and explains how functional dependencies apply to the tables of a relational database.

A functional dependency is a constraint between two sets of attributes in a relation. It exists when the value of one attribute or set of attributes uniquely determines the value of another attribute.

The formal definition: attribute A is functionally dependent on a set of attributes X if and only if a value of X uniquely determines a value of A. This is written using arrow notation:

X --> A

Read as: "X functionally determines A" or "A is functionally dependent on X." For any two rows in the table that share the same value of X, those rows must also share the same value of A.

The Customer relation provides a concrete illustration:

Customer (CustID, CustFirst, CustLast, Address, Phone)

CustID is the primary key. Every record in the table contains a unique value for CustID. Knowing the value of CustID uniquely determines the values of every other field in the row - there is exactly one customer with that ID, and therefore exactly one first name, last name, address, and phone number associated with it. In functional dependency notation:

CustID --> CustFirst, CustLast, Address, Phone

The attributes CustFirst, CustLast, Address, and Phone are all functionally dependent on CustID.

Functional dependency is directional - it does not necessarily work in both directions. The dependency runs from the determining attribute to the dependent attribute, not the other way around.

In the Customer relation, the reverse dependency does not hold:

CustFirst, CustLast, Address, Phone --> CustID  (NOT valid)

Multiple customers can share the same first name, last name, address, or phone number. Knowing a customer's name and address does not uniquely identify which customer record to retrieve. The dependency only works in the direction from the primary key to the other fields - not from the other fields back to the primary key.

A functional dependency can therefore be summarized as:

If you give me the value of the primary key, that will give me the rest of the fields in the relationship.

All fields in a row are dependent on the primary key. The primary key is the determinant^[1] - the attribute whose value determines the values of all other attributes in the row.

The Child-Mother Analogy

A biological analogy makes the directional nature of functional dependency intuitive. Consider the relationship between a child and their biological mother:

X   --> A
child --> mother   (valid)
mother --> child   (NOT valid)

1. The value of child implies the value of mother - every child has exactly one biological mother, so knowing the child uniquely determines the mother.
2. The value of mother does NOT imply the value of child - one mother can have multiple children, so knowing the mother does not uniquely determine which child.
3. Child is the determinant - for every child, there is only one mother; the dependency runs from child to mother.

Functional dependency is a one-to-one relationship between the determining value and the determined value. The determinant uniquely identifies the dependent value. A determinant is always a candidate key^[2] for the relation - it is a column or set of columns that could serve as a primary key because its values are unique within the table.

Consider a table tracking which instructor teaches each course:

StudentID	Course	Instructor
101	Database	Dr. Smith
102	Database	Dr. Smith
103	Networks	Dr. Johnson

Two functional dependencies can be identified in this data:

• Course → Instructor - each course has only one instructor. Knowing the course name uniquely determines the instructor.
• StudentID → Course is not a valid functional dependency - a student can take multiple courses, so knowing the StudentID does not uniquely determine the Course.

This example shows that functional dependencies describe business rules as well as data structure. The rule "each course is taught by exactly one instructor" is expressed as a functional dependency Course → Instructor. Identifying these dependencies is the analytical step that precedes normalization decisions.

Two types of dependency are relevant to normalization:

1. Functional dependency. Y is functionally dependent on X when the value of X uniquely determines the value of Y across all rows in the relation. Formally: knowing X tells you Y. Notation: X → Y
2. Transitive dependence. Z is transitively dependent on X when X determines Y and Y determines Z - so Z is indirectly determined by X through its relationship with Y. Notation: X → Y → Z, which means X → Z transitively.

The functional dependency constraint establishes that the value of X determines a unique value of Y in all states of a relation:

X --> Y

Transitive dependence is relevant to Third Normal Form. A relation is in 3NF when it is in 2NF and contains no transitive dependencies among non-prime attributes. If a non-key column determines another non-key column, that transitive dependency must be resolved by decomposition - just as partial dependencies are resolved to achieve 2NF.

Functional dependencies are the analytical foundation of the entire normalization process. Each normal form is defined in terms of which functional dependencies are and are not permitted:

Normal Form	Functional Dependency Requirement
1NF	Basic atomicity - no repeating groups. Functional dependencies are not the primary concern but help identify structural problems.
2NF	No partial dependencies - every non-key attribute must depend on the entire primary key, not just part of a composite key.
3NF	No transitive dependencies - no non-key attribute may depend on another non-key attribute.
BCNF	Every determinant must be a candidate key. Handles edge cases that 3NF does not cover.

Functional dependencies also help identify candidate keys and primary keys. A candidate key is any minimal set of attributes that functionally determines all other attributes in the relation. By mapping out which attributes determine which others, the database designer can identify all candidate keys and select the most appropriate one as the primary key.

Functional dependencies describe business rules as constraints on the data. The rule "each employee works in exactly one department" is expressed as EmployeeID → DeptID. The rule "each department has exactly one manager" is expressed as DeptID → ManagerID. Together, these two dependencies imply EmployeeID → ManagerID transitively - the employee's manager is determined by the employee's department, which is determined by the employee ID.

Functional dependency analysis has practical limitations that database designers should be aware of:

• Complexity in large schemas. Identifying and managing all functional dependencies in a large database with dozens of tables and hundreds of attributes is a substantial analytical task. Dependencies can be subtle and non-obvious, particularly when they involve combinations of columns rather than individual attributes.
• Dynamic business rules. Functional dependencies reflect the business rules in effect at the time of design. Business rules change over time - a department that previously had one manager might be restructured to have co-managers. When business rules change, the functional dependencies in the schema may need to be re-evaluated and the schema may need to be restructured.
• Sample data versus rules. Functional dependencies should be derived from business rules, not inferred from sample data alone. A small sample dataset might appear to support a dependency that the business rules do not actually guarantee. Always verify dependencies against the intended business logic, not just the data currently in the table.

[1] Determinant: The attribute or set of attributes that determines the value of one or more other attributes in a functional dependency. In the notation X → Y, X is the determinant. A determinant is always a candidate key for the relation.

[2] Candidate key: A field or combination of fields that can act as a primary key for a table, uniquely identifying each record. A relation may have multiple candidate keys; one is designated as the primary key and the others become alternate keys.

[3] Primary key: A field or combination of fields that uniquely identifies each record in a table. The primary key is the designated determinant for the relation.