Foundations — Keys — primary, candidate, super, foreign, natural vs surrogate

Before you can even read the parent note, you must own a small toolbox of words and symbols. Below, every single one is built from zero: plain meaning → the picture → why the topic needs it. Read top to bottom; each rung rests on the one above.

1. What is a table? (the picture behind everything)

Look at the figure. The whole subject lives inside this rectangle — keys are questions we ask about the rows of this rectangle.

WHY the topic needs it: you cannot talk about "identifying a row" until you can see what a row and a column are. The red row in the figure is the thing a key must let us point at.

2. What is a set? (why duplicates are forbidden)

The symbol $\{\}$ (curly braces) means "the set containing…". When the parent writes $\{roll\_no,email\}$ it means "the set of the two columns roll_no and email" — braces because order and repetition don't matter, we only care which columns are in the group.

WHY the topic needs it: because a table is a set of rows, no two rows can be truly identical — and that is exactly why we must be able to tell any two rows apart. The "no duplicates" rule of a set is the seed of every kind of key.

3. Uniquely identifies — the heart of a key

Picture a crowd where everyone wears a name tag. If every tag is different, the tag uniquely identifies a person. If two people wear "Sam", the name does not uniquely identify.

WHY the topic needs it: the words "super key", "candidate key", "primary key" are all just degrees of this one property. Master this and the rest is bookkeeping.

4. Attribute sets and the subset symbols $\subseteq$ and $\subset$

The parent writes things like $\{roll\_no\}\subseteq$ Super Key. You must own these two symbols.

WHY the topic needs it: the definition of candidate key is literally "a super key with no proper subset that is also a super key." You cannot parse that sentence without $\subset$.

5. NULL — the value that means "unknown"

WHY the topic needs it: the whole "PRIMARY KEY = unique + NOT NULL" rule, and the difference between PRIMARY KEY and UNIQUE, hinges on what NULL does.

6. Minimal — the idea of "can't remove anything"

Picture a three-legged stool. Remove one leg → it falls. That stool is minimal: every leg is load-bearing. A four-legged chair where you could saw off a leg and still stand is not minimal.

WHY the topic needs it: candidate key = minimal super key. The red "spare" leg in the figure is exactly the redundant column (name in $\{roll\_no,name\}$) that disqualifies a super key from being a candidate.

7. The four key words, defined at last

You now own every ingredient — set, uniquely identifies, subset, minimal, NULL. With those in hand, the four central terms of the parent topic become one-line definitions. Here they are as standalone entries so you never have to infer them.

8. Reference and the arrow of a foreign key

The parent's FOREIGN KEY (roll_no) REFERENCES Student(roll_no) draws exactly one such arrow. The Enrolment table borrows the Student name-tag system. See Referential Integrity for the enforcement rules and Entity-Relationship Model for where these links come from in design.

WHY the topic needs it: the foreign-key half of the topic is this arrow. If the arrow can point at a non-existent row, the whole model of linked tables collapses.

9. Natural vs Surrogate — where the key's value comes from

All the words above ask "which columns identify a row?" This last pair asks a different question: "where does the identifying value come from — the real world, or the machine?"

WHY the topic needs it: the parent devotes a whole section and a comparison table to this trade-off. It is orthogonal to super/candidate/primary — any of those can be either natural or surrogate underneath.

10. Reading the SQL words

The parent uses SQL. Here is the minimum vocabulary, each mapped to a concept above.

WHY the topic needs it: every idea above is abstract — a rule about rows. SQL is how you tell the database to actually enforce that rule. Re-encoding "this candidate key is my primary key" as the words PRIMARY KEY is the moment thought becomes a working constraint the machine will police for you. Learn the keyword and you can read (and write) the parent's code blocks without guessing.

How these foundations feed the topic

Read this map as a build order: an arrow means "the idea at the tail must be understood before the idea at the head." Each box corresponds to a numbered section above (e.g. "Set" = §2, "Super key" = §7). Trace any key back through its arrows and you land on the raw ingredients — table, set, uniqueness — that we built from zero. If a box ever feels shaky, follow its incoming arrows back to that section and re-read.

The links Functional Dependencies and Normalization build on top of this same "uniquely identifies" idea, and Indexes are how the database physically speeds up finding a row by its key.

Equipment checklist

Test yourself — each line is prompt ::: answer. If any answer surprises you, re-read that section before opening the parent note.