Candidate Key Derivation

Definition

An attribute set K is a candidate key of a relation if and only if:

1. K+ (the closure of K) equals the entire set of attributes of the relation (K is a superkey), and
2. No proper subset of K also has this property (K is minimal / irreducible).

This turns "what is the key?" from a guess into a formal, checkable computation using closures (21.9).

Deriving the Candidate Key of StudentCourse

The relation is StudentCourse(StudentID, StudentName, CourseID, CourseName, InstructorID, InstructorName, InstructorOffice, EnrollmentDate) — 8 attributes — with the FD set from 21.9 (F1-F6).

Step 1 — test single attributes. From 21.9's computations: {CourseID}+ has only 5 attributes, and {StudentID}+ has only 2 ({StudentID, StudentName}). Neither reaches all 8, so no single attribute is a superkey. InstructorID+ similarly only reaches {InstructorID, InstructorName, InstructorOffice} — 3 attributes. No single-attribute superkey exists.

Step 2 — test the composite pair {StudentID, CourseID}. From 21.9: {StudentID, CourseID}+ = all 8 attributes. So this pair is a superkey.

Step 3 — check minimality. Is either proper subset also a superkey? {StudentID}+ (2 attributes) and {CourseID}+ (5 attributes) both fall short of all 8. Neither subset works alone, so {StudentID, CourseID} cannot be reduced further.

Conclusion: {StudentID, CourseID} satisfies both conditions — it is a candidate key.

Step 4 — check for other candidate keys. Could {StudentID, InstructorID} also be one? From 21.9, {StudentID, InstructorID}+ = {StudentID, InstructorID, StudentName, InstructorName, InstructorOffice} — only 5 attributes, missing CourseID, CourseName, EnrollmentDate. Not a superkey, so it's not a candidate key. Systematically testing every other combination of the 8 attributes turns up no other superkey smaller than or unrelated to {StudentID, CourseID}, so `{StudentID, CourseID}` is the unique candidate key of this (unnormalized/1NF) relation.

Why This Formal Process Matters

Guessing a key by "it looks unique in the sample data" is unreliable (see 21.5's warning about coincidental uniqueness). Closure-based derivation is the only way to be certain, because it's grounded in the declared functional dependencies (the actual business rules), not the accidental contents of a few sample rows.

Edge Cases

• A relation can have multiple candidate keys (e.g., if StudentName were guaranteed globally unique by policy, {StudentName, CourseID} might also become a superkey — though minimality would still need checking). This relation happens to have exactly one.
• Prime attributes are those that belong to at least one candidate key (StudentID and CourseID here). This distinction becomes essential for the subtle difference between 3NF and BCNF (21.15).
• Testing every subset of a relation with many attributes is exponential in the worst case; in practice, schema designers use the known FDs to prune obviously-insufficient candidates quickly (as done in Steps 1-2 above) rather than brute-forcing all 2^n subsets.

Key Takeaways / Interview Angle

• Q: State the two-part formal definition of a candidate key. Its closure must equal all attributes (superkey), and no proper subset may also satisfy that (minimality).
• Q: Why isn't {StudentID, InstructorID} a candidate key even though it contains "enough" attributes to feel key-like? Its closure only reaches 5 of the 8 attributes — it never determines CourseID, CourseName, or EnrollmentDate, so it fails the superkey requirement outright.
• Q: Why not just eyeball the sample data to find the key? Because uniqueness in a handful of sample rows can be coincidental; only computing closures from the declared FDs guarantees correctness for all valid data, forever.