Key Taxonomy

## The single idea underneath the vocabulary A relation is a set of tuples, each describing one thing. To refer to "that row" you need a set of attributes whose value combination never repeats. Every key term is a refinement of that one question: which attribute combinations identify a row uniquely? Uniqueness here is a property of the **schema**, not of the rows that happen to be stored today. If two customers currently have different phone numbers, that does not make phone number a key. It is a key only if the business rule says two customers can never share one. Deriving keys from a data sample is one of the most common candidate mistakes. ## Superkey A set of attributes K is a superkey of relation R if, in every legal instance of R, no two distinct tuples agree on all of K. Equivalently, K functionally determines every attribute of R. Two consequences follow immediately: - The set of **all** attributes is always a superkey, because a relation is a set and sets contain no duplicate elements. - Any **superset** of a superkey is a superkey. Adding attributes can never create a collision that was not already there. That is why superkeys are cheap and numerous, and why the concept alone is not useful for design. ## Candidate key: superkey plus minimality A candidate key is a superkey with no proper subset that is also a superkey. This property is called minimality or irreducibility. For `Employee(emp_id, national_id, email, dept_id, name)` where employee number, national ID and email are each individually unique: - `{emp_id}`, `{national_id}`, `{email}` are candidate keys. - `{emp_id, name}` and `{email, dept_id}` are superkeys but not candidate keys: they contain a smaller superkey. - `{dept_id, name}` is neither, since a department can hold two people with the same name. Minimality is about the number of attributes, not their storage width. A four-byte integer superkey of two columns is not "more minimal" than a single wide text column that is already unique. Minimality also matters because a non-minimal key drags irrelevant attributes into every referencing table and into the definition of the normal forms. ## Primary key and alternate keys All candidate keys are logically equal: each one identifies rows just as well. The **primary key** is the one the designer nominates as the row's canonical identity, typically the one that is stable, narrow, always known, and convenient to reference from other relations. The relational model adds one rule to it, entity integrity: no attribute of the primary key may be null, because a row identified by an unknown value is not identified at all. The candidate keys not chosen become **alternate keys**. They are not decoration. If `email` is a real candidate key and you only enforce the primary key, the database will happily store two rows with the same email, and every downstream assumption about email identifying a person is now false. Each candidate key you believe in should be enforced as a uniqueness rule; each one you do not enforce is not actually a key, just a hope. ## Practical framing Interviewers ask this to check that you can reason about identity rather than recite syntax. Strong answers usually include: the nesting (superkey to candidate key to primary key), minimality as the discriminator, the fact that multiple candidate keys can coexist, the non-null rule on the primary key, and the reminder that keys come from business rules. A short worked example with three columns is faster and more convincing than a definition read out from memory.