The Relational Model: Relations, Keys & Nulls

What a relation actually is

A relation is a named, two-dimensional table of data: a fixed set of named columns and an arbitrary number of unnamed rows. The subtle point the exam rewards: a relation is defined by its structure (the column names and their domains), not by the data sitting in it right now. Add or delete rows and you still have the same relation; an empty STUDENT table is still STUDENT.

A proper relation must satisfy three properties:

1. Atomic cells — exactly one value at the intersection of every row and column (no lists, no repeating groups).
2. Unique rows — no two rows are identical; some column or combination uniquely identifies each row.
3. Order is irrelevant — the sequence of columns and the sequence of rows carries no meaning.

The key family

Every exam question about the relational model leans on this vocabulary. Get the distinctions crisp:

• Candidate key — any attribute (or combination) that uniquely identifies a row and is nonredundant (drop any part and it stops identifying). A relation can have several candidate keys.
• Primary key — the one candidate key the designer chooses as the official identifier. It is underlined in shorthand notation. Every relation has exactly one primary key.
• Composite key — a primary (or candidate) key made of more than one attribute. REGISTRATION(StudentID, SectionID) needs both columns to identify one enrollment.
• Foreign key — an attribute in one relation that is the primary key of another relation. It is the value-based link that makes the model 'relational'.
• Recursive foreign key — a foreign key that references the primary key of its own relation, used to implement a unary (recursive) relationship such as employee-manages-employee.

Worked example: reading keys off a real schema

Take the registrar/HR schema you will query later:

DEPARTMENTS(dept_id, dept_name, building, budget)
EMPLOYEES(emp_id, name, title, dept_id, manager_id, state, salary, hired)
REGISTRATIONS(student_id, section_id, grade)

• In EMPLOYEES, emp_id is the primary key. dept_id is a foreign key (it is DEPARTMENTS.dept_id). manager_id is a recursive foreign key — it points back at emp_id in the very same table, so a row can name its manager, who is another employee row. The top boss simply has manager_id = null.
• In REGISTRATIONS, the primary key is the composite (student_id, section_id) — one student can appear in many sections and one section holds many students, so neither column alone identifies a row.

Nulls

A null is a special marker meaning no value applies or the value is unknown — it is not zero and not an empty string. A foreign key may be null when the relationship is optional (an employee with no manager). A primary-key column may never be null (the entity-integrity rule, next lesson).