Part 0 · 2 chapters · ~12 min

The Relational Model

Codd's 1970 model and his 12 rules (numbered 0 to 12, so 13), relations, tuples, attributes and domains defined precisely, superkeys, candidate, primary, foreign and surrogate keys, the closed-world assumption and how NULL breaks it with three-valued logic, and the formal gap between the relational model and SQL.

1

Relations and keys

the model saysSQL doesconsequence
relations are sets (no duplicates)tables are bags; SELECT returns duplicates unless DISTINCTCOUNT(*) after a join can double-count
tuples are unorderedORDER BY is the only guarantee; without it order is arbitrarypagination without ORDER BY is a bug
attributes are unordered, namedcolumns have positions (SELECT *, INSERT without names)schema changes break positional code
two-valued logic, no NULLNULL and three-valued logicnext chapter

Codd's rules (1985) numbered 0 to 12 tested whether a product was "truly relational": rule 0 (manage data entirely through relational capabilities), information as values in tables, guaranteed access by table, key and column, systematic NULL treatment, a relational catalogue, a comprehensive data language, view updating, set-level insert, update and delete, physical and logical data independence, integrity independence, distribution independence and non-subversion. No mainstream SQL database satisfies all of them; view updating (rule 6) is the one most systems only partly meet.

THE RELATIONAL MODEL, PRECISELY
Codd, 1970
relationA set of tuples over a fixedheading: no order, no duplicates.attribute and domainA named column drawn from a set ofallowed values.superkeyAny attribute set that determinesthe whole tuple.candidate keyA minimal superkey; the primarykey is the one you choose.foreign keyAttributes whose values must existas a key elsewhere.surrogate vs naturalGenerated ids vs real-worldidentifiers (NUBAN, BVN, email).
swipe the figure sideways, or tap expand for full screen
1/4
sets of tuples
A relation is a set: tuples are unordered and unique. SQL tables are bags (duplicates allowed, rows ordered physically), the first gap between SQL and the model.
sets, not listsSQL tables are bags
2

NULL and the closed world

code
-- closed-world assumption: what is not in the database is false. NULL means "unknown" instead,
-- so SQL uses three-valued logic: TRUE, FALSE, UNKNOWN
SELECT NULL = NULL;                       -- NULL (unknown), not true
SELECT * FROM accounts WHERE closed_at <> '2026-01-01';     -- rows with closed_at NULL are excluded
SELECT * FROM t WHERE id NOT IN (SELECT ref FROM s);          -- if any s.ref IS NULL, returns no rows at all
SELECT * FROM t WHERE NOT EXISTS (SELECT 1 FROM s WHERE s.ref = t.id);   -- the safe form

-- aggregates skip NULLs: AVG over (100, NULL, 200) is 150, not 100
-- UNIQUE allows many NULLs in most databases (Postgres 15+: UNIQUE NULLS NOT DISTINCT to forbid)

NOT IN with a NULL in the subquery is the most common NULL bug in production SQL: every comparison with NULL is UNKNOWN, so NOT IN can never be true. Prefer NOT EXISTS, and declare columns NOT NULL unless unknown is a real state.