Functional Dependencies in a Relational Database

A functional dependency X → Y means that any two rows agreeing on X must also agree on Y. X and Y can each contain one or several attributes. The rule applies to every permitted state of a relation, not just to the rows currently stored.

Read the notation as “X determines Y” or “Y is functionally dependent on X.” This concept underlies the partial-dependency test in second normal form and helps determine how facts should be grouped into tables.

Suppose a Customer relation stores one current contact record per customer:

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

CustID → CustFirst, CustLast, Address, Phone

Under this model, one CustID cannot be associated with two different current phone values in the same valid relation state. The dependency does not mean the phone number can never change. Updating a customer's number changes the stored state while preserving the rule.

The model explicitly permits one current phone value per customer. If multiple phone numbers must be recorded, that is a different requirement, normally represented by related rows. Do not assume a dependency merely because an attribute name sounds plausible.

Functional dependency expresses a constraint, not necessarily a calculation. A customer ID need not mathematically encode the person's name; it determines which name is associated with that customer.

From CustID → Phone, you cannot conclude Phone → CustID. Two customers may share a household phone number. Similarly, names and addresses need not uniquely identify people.

A dependency is therefore not necessarily a one-to-one mapping. Each determinant value has at most one associated dependent value in the relation, but several determinant values can share that same result. A reverse dependency requires its own business rule.

For example, assume each department has one current manager:

DeptID → ManagerID

A manager may lead several departments, so ManagerID → DeptID need not hold. This departmental example makes the direction explicit without relying on an ambiguous real-world analogy.

For this simplified snapshot, assume each course has one instructor, students may take several courses, and each student-course pair occurs once. Course and Instructor are labels for uniquely identified courses and instructors within this example.

StudentCourse: illustrative snapshot

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

StudentCourse(StudentID, Course, Instructor)

Candidate key: (StudentID, Course)
Course → Instructor

Course is a determinant because it determines Instructor. It is not a key of StudentCourse: the Database course occurs in rows for students 101 and 102. The repeated course value is legitimate; it identifies two different enrollments.

StudentID happens to be unique in the displayed sample, but the rule allowing a student to take several courses means StudentID → Course is not a schema constraint. A later row for student 101 taking Networks would be valid.

For a real teaching schedule, a course may have several sections, terms, and instructors. SectionID or a composite offering identifier may be the appropriate determinant. Instructor names are also poor person identifiers when names can coincide. Establish that scope before applying the simplified dependency to a production design.

These terms describe properties of functional dependencies; transitive dependence is not an alternative to functional dependence.

• Full dependency: X determines Y, and no proper subset of X determines Y. A grade may depend on the complete pair (StudentID, Course), assuming one enrollment per pair.
• Partial dependency: some proper subset of X already determines Y. In StudentCourse, Course alone determines Instructor, so Instructor depends on only part of the candidate key.
• Transitive implication: if X determines Y and Y determines Z, then X determines Z. The resulting dependency follows through Y.

EmployeeID → DeptID
DeptID → ManagerID
Therefore: EmployeeID → ManagerID

Assume each employee belongs to one department, each department has one manager, and several employees can share a department. In an EmployeeDepartment(EmployeeID, DeptID, ManagerID) relation, DeptID is not a superkey, and ManagerID is non-prime if EmployeeID is the only candidate key. DeptID → ManagerID then violates 3NF.

Transitivity itself is a valid inference rule, not inherently a design error. Whether a dependency violates a normal form depends on the determinants and candidate keys involved.

Trivial and nontrivial dependencies

A dependency is trivial when its right-hand attributes are already included in its left-hand set. For example, (StudentID, Course) → Course always holds. Course → Instructor is nontrivial because Instructor is not part of Course. This distinction matters in the formal definitions of 3NF and BCNF.

Dependency tests used in normalization

Normal form	Relevant requirement
1NF	Represent individual domain values and avoid repeating groups in this course's conventional relational model. This does not eliminate partial or transitive dependencies.
2NF	Be in 1NF; no non-prime attribute depends on a proper subset of any candidate key.
3NF	For every nontrivial dependency X → A, X is a superkey or A is prime.
BCNF	For every nontrivial functional dependency, the determinant is a superkey.

A prime attribute belongs to at least one candidate key; a non-prime attribute belongs to none. BCNF requires a superkey, not necessarily a minimal candidate key. Higher forms such as 4NF and 5NF examine multivalued and join dependencies, so not every normal form is defined solely through functional dependencies.

In StudentCourse, Instructor is non-prime and depends on Course, part of the composite candidate key. A suitable decomposition is Enrollment(StudentID, Course) and CourseInstructor(Course, Instructor). Joining on Course reconstructs the instructor for each enrollment under the stated dependency.

Sample data can reveal a counterexample, but an absence of counterexamples does not prove a schema constraint. Ask whether the business permits the apparently conflicting records.

SELECT Course
FROM StudentCourse
GROUP BY Course
HAVING COUNT(DISTINCT Instructor) > 1;

This query finds courses with more than one distinct non-null instructor value. Such rows contradict the proposed rule for the defined snapshot, or indicate that the table mixes different offerings or periods. COUNT(DISTINCT) ignores nulls, so inspect missing instructors separately; text comparisons also follow the database's collation rules.

Classical dependency reasoning assumes ordinary attribute values. SQL nulls, incomplete source data, and changing historical records need explicit treatment. When storing history, include the relevant time or version identifiers instead of assuming a current-state dependency applies across all dates.

A foreign key enforces a reference to another table; it does not by itself enforce every functional dependency within a wide table. Appropriate decomposition and keys can make important dependencies enforceable without repeating the same fact independently.

Determinant: the attribute set on the left of a functional dependency.

Candidate key: a minimal attribute set that determines the entire relation.

Primary key: the selected candidate key used as the principal row identifier.