Relational Foundations
Keys and Identity
A candidate key is a minimal set of attributes whose values uniquely identify every tuple in a relation. Primary keys are selected candidate keys, while superkeys, alternate keys, foreign keys, and surrogate identifiers serve distinct purposes.
- A superkey is unique; a candidate key is both unique and irreducible.
- A relation may have several candidate keys; choosing one as primary does not make the others cease to express identity.
- Minimal means no proper subset is a superkey, not “fewest columns among all keys.”
- A natural key derives from the problem domain; a surrogate key is introduced by the system and still needs domain uniqueness constraints where duplicates are forbidden.
- A foreign key expresses inclusion of referencing values in a referenced key and therefore models a relationship, not local tuple identity.
- Good identifiers are stable, mandatory, and independent of mutable descriptive data.
Worked candidate-key derivation
Consider ENROLLMENT(student_id, email, course_id, term, grade) with business rules student_id → email, email → student_id, and (student_id, course_id, term) → grade. A student can take the same course in different terms, and many students share a course and term.
| Start with | Closure under the stated rules | Conclusion |
|---|---|---|
{student_id, course_id, term} | Adds email, then grade; reaches all five attributes | Superkey |
Remove student_id | {course_id, term}; cannot obtain student or grade | Student component is necessary |
Remove course_id | Identifies a student and term, but not which enrollment | Course component is necessary |
Remove term | Identifies a student and course, but not a particular offering | Term component is necessary |
Replace student_id with email | {email, course_id, term} obtains student_id and grade | A second candidate key |