How to Identify Functional Dependencies

Identify a functional dependency by asking: if two rows have the same values for X, must they also have the same values for Y? If the business rules require the answer to be yes, write X → Y. X is the determinant; Y is the dependent attribute or set of attributes.

The previous lesson defined functional dependencies. This lesson shows how to discover and document them. The goal is to establish rules that hold for every valid state of a relation, rather than describe coincidences in its current rows.

For example, CustID → CustFirst, CustLast, Address, Phone is appropriate for a customer relation that stores one current name, address, and phone per customer. If customers can have several phone numbers, those numbers need a different representation. The dependency must match the information the relation actually records.

Before looking for determinants, state what one row represents and which period it covers. A row might describe a student, a particular course offering, or one student's enrollment in that offering. These are different facts with different keys.

Also distinguish a current snapshot from a history. A student number can determine one current address without determining every address the student has ever used. A historical address relation needs a way to distinguish address records or effective periods. Similarly, a course may have different professors in different semesters.

Correct dependencies reveal facts repeated across rows and help prevent update, insertion, and deletion anomalies when tables are decomposed appropriately. Repeated values alone do not prove a design error: repeating a foreign key is normal. Nor does identifying dependencies automatically enforce them; the schema and its constraints must implement the rules.

1. Write the business rules. Ask domain experts whether each identifier refers to one object, whether names may repeat, whether assignments can change, and whether multiple values are allowed. Record exceptions as carefully as the usual case.
2. Propose determinants. Translate each rule into an arrow. A determinant may contain one attribute or several. Test the reverse direction independently: StudNr → StudName does not imply StudName → StudNr.
3. Identify candidate keys. A candidate key is a minimal set of attributes that determines all attributes of the relation. Every candidate key is a determinant, but a determinant need not be a key. A course number may determine a course name without identifying an enrollment row.
4. Look for counterexamples. Inspect sample and historical data for identical determinant values paired with different dependent values. One legitimate counterexample disproves a proposed rule. A data-entry error instead shows that a valid rule has been violated.
5. Check composite determinants for unnecessary attributes. Remove each attribute in turn and ask whether the smaller set still determines the dependent value under the business rules. This distinguishes full from partial dependencies.
6. Document and review the result. Keep each dependency beside its meaning, scope, and supporting rule. Use dependency diagrams or a data dictionary to review the result with users. Revisit the dependencies when the rules change.

Sample data can disprove a dependency but cannot establish that it will hold for all future valid rows. Schema-analysis tools can suggest patterns; business knowledge is needed to decide which patterns are requirements.

Step 1: State the Assumptions

Consider a university enrollment system with these deliberately simplified rules:

• StudNr identifies a student with one recorded current name and mailing address. Student names are not assumed unique.
• CourseNr identifies a catalog course and its current name. Different courses may have the same name.
• There is at most one offering of a course per semester and year. Thus (CourseNr, Semester, Year) identifies an offering.
• Each offering has exactly one professor, identified by ProfID. Professor names need not be unique.
• A student has at most one enrollment in a given offering and receives at most one final grade for that enrollment.

If the university permits parallel sections, add a section identifier to the offering key or introduce an OfferingID. If it permits co-teaching, model professor assignments separately. These are changes to the rules, not exceptions to ignore.

Step 2: Translate the Rules into Dependencies

StudNr → StudName, StudAddr
CourseNr → CourseName
ProfID → ProfName
CourseNr, Semester, Year → ProfID
StudNr, CourseNr, Semester, Year → Grade

The last dependency describes a relationship attribute. A grade belongs to a particular enrollment, not to a student alone or a course alone. In an enrollment relation containing these attributes, the four-column combination is a candidate key under the stated rules. Removing StudNr loses the identity of the student; removing part of the offering key can mix different offerings.

Do not infer CourseNr → ProfID: the same course can have a different professor next semester. Do not infer ProfID → CourseNr: a professor may teach several courses. The direction and the complete determinant both matter.

Step 3: Derive Additional Dependencies

Known dependencies allow logical deductions. Because an offering determines its professor ID and a professor ID determines a professor name, the offering also determines that name:

CourseNr, Semester, Year → ProfID
ProfID → ProfName
Therefore: CourseNr, Semester, Year → ProfName

This is transitivity. Armstrong's axioms also include reflexivity (a set determines its subsets) and augmentation (adding the same attributes to both sides preserves a dependency). These rules derive consequences of established dependencies; they do not validate the original business assumptions.

To check a proposed key, compute its attribute closure: start with the proposed key attributes and repeatedly add attributes determined by those already available. Reaching every attribute makes the set a superkey. It is a candidate key only if no proper subset also determines every attribute.

A relation is in 2NF when it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key. A non-prime attribute belongs to no candidate key. Testing only the selected primary key can miss another candidate key's partial dependencies.

Suppose one wide enrollment relation contains student details, course details, professor details, and the grade. Under our rules, its candidate key is (StudNr, CourseNr, Semester, Year). The following distinctions explain why that wide relation needs decomposition:

Whether a column is a foreign key or sounds like an attribute of the table's subject is not a sufficient 2NF test. Ask which attributes actually determine it. A foreign key can itself depend on only part of a composite candidate key.

The insurance example follows the same logic. In PolicyCar(PolicyNo, CarID, Deductible, CoveragePlan), assume the pair identifies a covered vehicle. If the plan is chosen per policy, then PolicyNo → CoveragePlan is a partial dependency. If the plan is chosen separately per vehicle on the policy, the pair may be required. Confirm which rule applies before moving the attribute.

This diagnostic query looks for conflicting course names in a proposed wide enrollment table:

SELECT CourseNr
FROM EnrollmentDraft
GROUP BY CourseNr
HAVING COUNT(DISTINCT CourseName) > 1;

A returned course number has more than one distinct non-null name in the inspected rows. Investigate whether that is an error, a historical name change, or evidence that the proposed dependency is inappropriate. No returned rows means only that this check found no conflict in the current data.

COUNT(DISTINCT ...) excludes nulls, so check missing names separately. Text comparisons also follow the database's comparison and collation rules. The query is a diagnostic aid, not a substitute for defining required values and enforcing the intended schema.

For relationships, a many-side row's key determines its single referenced parent when that parent assignment is single-valued. A many-to-many relationship generally requires the participating keys together to identify an association. Even then, repeated events may require another attribute: a student taking the same offering twice would invalidate our one-enrollment-per-offering assumption.

Finish with a reviewed dependency list and explicit assumptions. In the next lesson, use that list to decompose a relation into Second Normal Form. Later checks address transitive dependencies and stricter normal forms.

Practice distinguishing a full key from a partial determinant in the Functional Dependencies Exercise. For each proposed arrow, state the business rule that makes it valid and a possible counterexample if that rule does not apply.