Relational Model & Keys
The vocabulary that every exam question assumes. Learn the three synonyms and the four key types cold.
| ID | name | dept_name | salary |
|---|---|---|---|
| 22222 | Einstein | Physics | 95000 |
| 12121 | Wu | Finance | 90000 |
| 32343 | El Said | History | 60000 |
| 45565 | Katz | Comp. Sci. | 75000 |
| 98345 | Kim | Elec. Eng. | 80000 |
| 76766 | Crick | Biology | 72000 |
Core terms
Schema vs Instance
A relation schema defines the structure: relation name, attribute names, and attribute domains.
INSTRUCTOR = (ID, name, dept_name, salary) ← schema R = (A₁, A₂, …, Aₙ)
Formally, given domains D₁…Dₙ, a relation r is a subset of D₁ × D₂ × … × Dₙ — a set of n-tuples. The current rows are the relation instance; one row is a tuple. Order of tuples is irrelevant.
Keys
Let K ⊆ R. Four kinds, each narrower than the last:
🔑 Super key
K is a super key if its values are sufficient to identify a unique tuple. May contain extra attributes.
e.g. {ID} and {ID, name} are both super keys of instructor.
🎯 Candidate key
A super key that is minimal — remove any attribute and it stops being a super key.
e.g. {ID} is a candidate key. {ID, name} is not (name is redundant).
⭐ Primary key
One candidate key chosen to identify tuples. If R = {ID, u_name, name, dept_name, salary} has candidate keys {ID} and {u_name}, the primary key is either {ID} or {u_name}.
🔗 Foreign key
An attribute whose value must appear as a key in another relation. The table with the FK is the referencing relation; the table it points to is the referenced relation.
e.g. dept_name in instructor is a foreign key referencing department.