The Relational Model
The data model underlying almost every commercial DBMS, introduced by E.F. Codd in 1970: a sound theoretical foundation built on set theory, with a single simple, uniform data structure called a relation. Introduced in 2026-03-09-the-relational-model-and-integrity-constraints; see relational-mapping-notation for how schemas are written down in this course, and er-to-relational-mapping for how a conceptual ER design becomes one.
The four basic concepts
| Concept | Definition |
|---|---|
| Domain | A set of atomic values, with a data type/format |
| Attribute | The name of a role played by some domain in a relation |
| Tuple | An ordered list of values, one per attribute |
| Relation | A set of tuples — informally, a table of rows and columns |
All four are visible at once in the anatomy figure, which is worth reading before the definitions below:
Figure 1: The four components of a relation, on an annotated Employee table. The relation name sits above the table; the attribute names are the header row; each body row is a tuple; and every value down one column — here Department — is drawn from that attribute’s single domain.
Domains
A domain \(D\) is a set of atomic values — atomic meaning indivisible as far as the relational model is concerned. Each domain carries a data type or format: integers, numbers/currency, fixed- or variable-length character strings, dates/timestamps, a sub-range of a data type (e.g. \(1 \le \text{grade} \le 7\)), or an enumerated type (e.g. \(\text{Gender} \in \{\text{Male}, \text{Female}, \text{Other}\}\)). Format-constrained domains count too — an Australian phone number (61 + 9 digits), or a car registration (6 alphanumeric characters, no Q).
The domain is what makes a value legal or illegal, which is why the domain constraint is the first of the five integrity-constraints: a value that isn’t a member of its attribute’s domain simply cannot be stored.
Attributes
Each attribute \(A\) names a role that some domain plays in a relation \(R\). Name and domain are independent in both directions:
- Same-named attributes in different relations need not share a domain —
Department.idandEmployee.idcan be drawn from entirely different domains despite the shared nameid. - Differently-named attributes can share a domain —
Employee.idandEmployee.managerIdare both drawn from the same “employee id” domain, which is precisely what makes a self-referencing foreign key possible (see foreign-keys).
The number of attributes in \(R\) is its degree — Employee [id, name, sex, salary, department] is of degree 5, and “degree of the relation” and “degree of the relation schema” are used interchangeably for the \(n\) in R [A1, …, An]. Degree is the only size measure Module 2 names. Unqualified “cardinality” in this course almost always means a relationship’s cardinality ratio on an ER diagram (relationship-constraints) or the “many cardinality” side of a relationship in the mapping rules; a relation’s row count only acquires a notation later, when Module 3 writes it as \(|R|\) to size a Cartesian product (\(|R_1 \times R_2| = m \cdot n\)) — see infs1200-diagram-notation.
Tuples
Each tuple \(t\) is an ordered list of \(n\) values \(t = \langle v_1, v_2, \ldots, v_n \rangle\), where each \(v_i\) either belongs to the domain of the corresponding attribute or is the special value NULL. A tuple with \(n\) values is an \(n\)-tuple.
Relation schema vs relation instance
The single most-tested distinction in this part of the course — the schema is metadata, the instance is data.
| Relation schema | Relation instance | |
|---|---|---|
| Written | R [A1, A2, …, An] |
r(R) = {t1, t2, …, tm} |
| Is | The relation’s name and attribute list | The actual set of tuples, at one point in time |
| Also called | Metadata — “data describing the data” | The data in the database |
| Comes from | Database design | Insert/update/delete operations |
| Changes | Rarely (a design decision) | Constantly |
Employee [id, name, sex, salary, department] is a schema of degree 5; the rows currently sitting in that table are its instance. Because the schema is fixed at design time and the instance changes with every update, an integrity constraint is a property of the schema that must hold on every instance of it.
Properties of relations
A relation is a mathematical set of tuples, and the consequences of that are what separate a relation from an arbitrary table.
- Tuple order is irrelevant. A set has no implied order. Two instances holding the same tuples in a different sequence are the same relation. Physically, tuples do sit in some order on blocks of secondary storage, but nothing semantic (queries included) may depend on it.
- Value order is a syntactic convention only. An \(n\)-tuple is syntactically an ordered list, so every tuple in one relation must list its values in the same attribute order — but which order is chosen carries no meaning, as long as the attribute-to-value correspondence is maintained consistently.
- Values are atomic. Each cell holds one indivisible value from its domain (or
NULL) — no repeating groups and no nested structure, which is why a multivalued attribute has to become a relation of its own during mapping. - Every relation is a table; not every table is a relation. A “pivoted” presentation table — a quarterly sales report with merged header cells, broken down by region and suburb — is a perfectly good table and not a valid relation at all. The word table is used interchangeably with relation in practice, but only relations obey the properties above.
One caveat for later: SQL does not enforce the set property. A SELECT returns a bag, duplicates and all, unless you ask for DISTINCT — see sql-select-statement. The properties above describe the model, not what a query result happens to look like.
Where the relational model sits
The relational model is the logical layer: what a DBMS actually implements, and the target of the mapping algorithm in er-to-relational-mapping (which is also where the conceptual/logical split is spelled out). The relational model is the middle box below: still a description of what is stored, not of how the bytes sit on disk (dbms-fundamentals has the three-schema architecture the right-hand column belongs to).
Figure 2: Where the relational model sits. Two design steps (gold) turn requirements into two schemas: conceptual design produces an ER schema, logical design — the mapping — produces a relational one. The green notes say what each model is for: the ER model is what conceptual design is done in, the relational model is what commercial DBMSs implement.
What that algorithm emits is relations plus their primary and foreign key constraints — from there, sql-ddl turns the schema into real CREATE TABLE statements, and normalisation (functional-dependencies, normal-forms) checks that the resulting relations are well-designed.