The Relational Model

The previous lesson ended by saying there is a difference deeper than formatting between the rows of a text file and the rows of a database. This lesson names that difference: what mathematical structure stands under a table, and which of the details visible when looking at a table does that structure say do not belong to the data?

The answer collects into a single concept. In the relational model, data is held in something that looks like a table but is not one: a relation. Every rule in this lesson is a direct consequence of the relation’s definition, which comes from set theory.

Domain

The set of values a column may take is called that column’s domain. A branch’s province draws from a text domain, a book’s publication year from an integer domain, and a book’s loanability from a two-element truth domain.

A domain is narrower than a type. The publication year’s type is integer; its domain might be “the integers between 1450 and 2100.” A type says how a value is stored, a domain which values are meaningful. In the schema, a domain is written as a constraint:

sqlite3 :memory: <<'SQL'
CREATE TABLE book (
  isbn             TEXT NOT NULL,
  title            TEXT NOT NULL,
  publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1450 AND 2100)
);
INSERT INTO book VALUES ('978-975-0000-01-1', 'Lost Time', 2019);
INSERT INTO book VALUES ('978-975-0000-02-8', 'Sea Lighthouses', 20199);
SQL

Runtime error near line 7: CHECK constraint failed: publication_year BETWEEN 1450 AND 2100 (19)

A slip of the finger entered the year as 20199, and the engine turns it back. Not defining the domain in the schema means expecting every program that writes to the data to perform this check on its own.

Relation

Given $n$ domains $D_1, D_2, \dots, D_n$, their Cartesian product is the set of all $n$-tuples whose components are each drawn from the corresponding domain. A relation is a subset of that product:

$$R \subseteq D_1 \times D_2 \times \dots \times D_n$$

The Cartesian product gives “everything that could be written”; a relation selects, from among those, “what is actually true.” If the branch relation holds the triple ('CEN', 'Central', 'Ankara'), this states that a branch coded CEN, named Central, in Ankara exists in this library. Triples absent from the relation are considered false. A table is a list of facts.

A relation has two parts. The heading is the set of attribute-name-to-domain pairings and does not change over time. The body is the set of rows matching the heading and changes on every insert, delete, and update. The number of attributes in the heading is called the relation’s degree; the number of rows in the body is called its cardinality.

Row and Column

Each element of the body is a row, or in the theoretical term, a tuple. A row is a mapping from attribute names to values: the branch_code attribute maps to 'CEN', the name attribute maps to 'Central'.

Each item in the heading is a column and corresponds to an attribute. A column has a name and a domain, not a position number. In the branch relation, there is no such concept as “the second column” — there is a name column.

Two consequences follow from this, and both are commonly forgotten in practice.

Row Order Does Not Belong to the Data

The body is a set, and the elements of a set are unordered. Whatever order a query returns rows in, that order comes not from the data but from the access path the engine chooses. Adding an index to the same table, without changing a single row, is enough to change the order of the result:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE branch (branch_code TEXT, name TEXT, city TEXT);
INSERT INTO branch VALUES ('CEN', 'Central', 'Ankara'),
                          ('BHC', 'Bahcelievler', 'Ankara'),
                          ('KDK', 'Kadikoy', 'Istanbul');
SELECT branch_code, name FROM branch;
CREATE INDEX branch_name ON branch (name, branch_code);
SELECT branch_code, name FROM branch;
SQL

┌─────────────┬──────────────┐
│ branch_code │     name     │
├─────────────┼──────────────┤
│ CEN         │ Central      │
│ BHC         │ Bahcelievler │
│ KDK         │ Kadikoy      │
└─────────────┴──────────────┘
┌─────────────┬──────────────┐
│ branch_code │     name     │
├─────────────┼──────────────┤
│ BHC         │ Bahcelievler │
│ CEN         │ Central      │
│ KDK         │ Kadikoy      │
└─────────────┴──────────────┘

The two queries are identical, the data is identical, the order differs. The second query now reads not the table but the index kept ordered by name. Which access path gets chosen depends on the engine; as a rule, an ordered result must be requested explicitly. A program that leaves the order unrequested has left its correctness to chance.

Repeated Rows and the Multiset

In set theory, an element either belongs to a set or does not; it cannot be present twice. By definition, a relation holds no repeated row. A SQL table departs from a relation on exactly this point: if no key is defined, the same row can be written twice.

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE branch (branch_code TEXT, name TEXT, city TEXT);
INSERT INTO branch VALUES ('CEN', 'Central', 'Ankara');
INSERT INTO branch VALUES ('CEN', 'Central', 'Ankara');
SELECT COUNT(*) AS row_count, COUNT(DISTINCT branch_code) AS distinct_code FROM branch;
SQL

┌───────────┬───────────────┐
│ row_count │ distinct_code │
├───────────┼───────────────┤
│ 2         │ 1             │
└───────────┴───────────────┘

A SQL table is not a set but a multiset: the same value can be present more than once. This is not a deficiency of the model but a deliberate departure from the theory — eliminating repeats requires sorting or hashing, and that costs something. The consequence is that a table behaving like a relation does not happen on its own; it must be requested in the schema, and defining a key is that request. Keys are the subject of the next lesson.

Relation Variable and Schema

The branch table above holds a value that changes over time: three rows today, four tomorrow. The theory names this distinction. A relation variable is a name, and that name’s value at any instant is a relation. CREATE TABLE creates a relation variable; INSERT changes its value.

From here, two separate planes of database work follow. The schema states the headings of relation variables and the constraints placed on them; it rarely changes, and changing it requires a migration. The instance is the totality of values at a given instant; it changes constantly. A design error lives in the schema, a data error lives in the instance; fixing the former is more expensive than fixing the latter.

The Indivisibility of a Value

The model’s final rule is that where a row and a column meet, there must be a single value. Writing a branch’s phone numbers as one piece of text, '0312-000-0001, 0312-000-0002', means squeezing a list into one place. The system treats that text as indivisible: no query can be written to find the second number inside it, removing one number turns into text editing, and no constraint can be placed on a number.

The remedy is moving the multivalued fact into a separate relation — each phone number in its own row, together with the branch code. This rule will be formalized under the name first normal form in the Normalization topic; for now it stands as a requirement of the model.

Summary

• A relation is a subset of the Cartesian product of attribute domains; its heading is fixed, its body is variable.
• A domain is a column’s set of meaningful values and is narrower than a type; it is written into the schema as a constraint.
• Because the body is a set, row order does not belong to the data; an ordered result must be requested explicitly.
• A SQL table accepts repeated rows because it is a multiset; behaving like a relation depends on a key being defined in the schema.
• The schema states relation variables and their constraints; the instance states the values at a given moment.

Next Step

This lesson arrived at the same place twice: rows need to be distinguishable from one another, and that distinguishing does not happen unless the schema says so. The next lesson defines the column set that identifies a row on its own, discusses which one is chosen when several candidates exist, and builds the key that binds one relation’s rows to another relation’s rows.