Explain the difference between a superkey, a candidate key and a primary key in the relational model, and what makes an attribute 'prime'.

## Superkey A **superkey** of relation R is any attribute set X such that the functional dependency `X -> R` holds - X determines the value of every attribute in the relation, so no two distinct rows can share the same X value. The set of all attributes is trivially a superkey (two rows equal on everything are the same row under set semantics). Any superset of a superkey is again a superkey, so a relation typically has many. Operationally you test a superkey with attribute closure: compute X+ under the FD set and check whether it contains every attribute of R. ## Candidate key A **candidate key** is a *minimal* superkey: X determines all attributes, and for every attribute a in X, `X - {a}` no longer does. Minimality is the only difference from a superkey, and it is the part candidates forget. If `(student_id, course_id)` is a candidate key of an enrollment relation, then `(student_id, course_id, enrolled_on)` is a superkey but not a candidate key, because dropping `enrolled_on` still identifies rows. A relation may have **several** candidate keys. Consider `employee(emp_id, national_id, email, name)` where all three of `emp_id`, `national_id` and `email` are individually unique: there are three candidate keys, each a single attribute. Candidate keys may also overlap partially, sharing some attributes without either containing the other. The empty set can even be a candidate key, in the degenerate case of a relation constrained to hold at most one row. ## Primary key and alternate keys The **primary key** is whichever candidate key the designer nominates as the official identifier. The remaining candidate keys are **alternate keys** and normally get their own uniqueness constraints. Relational theory attaches no special power to the primary key; the choice is a design decision about stability, width and what other tables will reference. What theory does insist on is that all candidate keys are enforced, not just the nominated one - a very common practical omission, where a surrogate identifier is declared primary and the genuine business uniqueness is left unconstrained, allowing duplicates. ## Prime and non-prime attributes An attribute is **prime** if it is a member of at least one candidate key; otherwise it is **non-prime**. Note the quantifier: membership in *some* key suffices, not all of them. In `enrollment(student_id, course_id, term, seat_no, grade)` where `(student_id, term)` and `(seat_no, term)` are both candidate keys, the prime attributes are `student_id`, `term` and `seat_no`; `course_id` and `grade` are non-prime. This classification exists because the higher normal forms are stated with it: the rules that restrict dependencies of non-prime attributes on parts of keys, and the rule that allows certain dependencies only when the determined attribute is prime, both hinge on this label. Get the candidate keys wrong and every subsequent normalization judgement is wrong too. ## Working it out The practical procedure is: 1. Classify attributes by their appearance in the FD set. An attribute that never appears on any right-hand side must be in **every** candidate key - nothing can derive it. An attribute that appears only on right-hand sides can be in none. 2. Take the mandatory attributes as a seed and compute their closure. If it is already all of R, that seed is the unique candidate key and you are done. 3. Otherwise extend the seed with one additional attribute at a time, computing closures, and keep the sets that reach all of R. Discard any that contain a smaller solution, since candidate keys must be minimal. ## Keys versus their implementation A candidate key is a logical property implied by the FDs. A `PRIMARY KEY` or `UNIQUE` declaration is the mechanism that makes the engine reject violations, and an index is the structure that usually enforces it. The three are different levels: a key exists in the design even if nobody declared it, and declaring a uniqueness constraint that does not correspond to a real domain rule breaks legitimate data. Which candidate key is nominated as primary, and whether it is a business attribute or a generated one, is a separate design conversation from identifying what the candidate keys are.