Keys in Relational Databases

One idea, layered: every key is a superkey with a constraint added

"Key" is overloaded, but the terms nest cleanly. Build them up:

• Superkey — any set of attributes whose values are unique across rows, so it identifies a row. {StudentID}, {StudentID, Email}, even {StudentID, Email, Name} are all superkeys if StudentID is unique. Most superkeys carry dead weight.
• Candidate key — a minimal superkey: remove any attribute and it stops being unique. This is the real notion of "a key"; a table can have several.
• Primary key — the one candidate key you choose to identify rows (NOT NULL, unique, one per table). The others become alternate keys (still enforced UNIQUE).
• Composite key — a key with more than one attribute. Foreign key — attribute(s) in one table whose values must match a candidate/primary key in another (covered next, in integrity constraints).

Finding candidate keys mechanically — attribute closure

You don't eyeball candidate keys; you derive them from the functional dependencies. The tool is the closure X⁺: the set of all attributes determined by X. If X⁺ = every attribute, X is a superkey; if no proper subset of X is also a superkey, X is a candidate key.

Take R(StudentID, Email, CourseID, Grade) with FDs: StudentID→Email, Email→StudentID (email is unique per student), {StudentID, CourseID}→Grade.

{StudentID, CourseID}⁺
   start: {StudentID, CourseID}
   + StudentID→Email           → add Email
   + {StudentID,CourseID}→Grade → add Grade
   = {StudentID, Email, CourseID, Grade}  = ALL attributes  → superkey
   drop StudentID or CourseID → closure no longer covers all  → MINIMAL
   → {StudentID, CourseID} is a CANDIDATE KEY

Because Email→StudentID too, {Email, CourseID} has the same closure → it's a second candidate key. But {StudentID, Email, CourseID} is only a superkey: Email is redundant (StudentID already implies it), so it isn't minimal and isn't a candidate key. Pick one candidate as primary (say {StudentID, CourseID}); the other is an alternate key you still declare UNIQUE.

Why it matters beyond vocabulary

• Candidate keys are the inputs to normalization — 2NF/3NF/BCNF are defined relative to "prime" attributes (those in some candidate key). You can't check a normal form without first finding the candidate keys.
• Choosing the primary key drives physical layout: in InnoDB the PK is the clustered index (rows stored in PK order), so a wide or random PK (e.g. a UUID) has real write-amplification costs.

Takeaways

• superkey ⊇ candidate key (minimal) ⊇ {primary (chosen), alternate (the rest)}.
• Derive candidate keys by computing attribute closures from the FDs, not by guessing.
• A relation can have several candidate keys; exactly one becomes the primary key.

---

Deepened for this guide (the prior version only listed definitions). Closure algorithm per Silberschatz, "Database System Concepts". See also: Functional Dependency & Closure, Normalization, Indexing & Storage.

I just read a lesson on **Keys in Relational Databases** (Databases) and want to truly understand it. Explain Keys in Relational Databases from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Keys in Relational Databases** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Keys in Relational Databases** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Keys in Relational Databases** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.