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).