Module 15 · Data modelling · Lesson 2 of 5
The relational model
Codd's idea that powers nearly every database: data as relations of tuples over typed attributes. Relations, tuples, domains, degree and cardinality, why order and duplicates do not matter in theory, how NULL complicates logic.
Why it matters
Before 1970 databases were navigated by following pointers from record to record, so every query was a program and every change to the structure broke programs. Edgar Codd proposed representing all data as simple tables, mathematically relations, and querying them with set operations. That idea is why SQL exists, why the same query works whatever the physical layout, and why the vocabulary in interviews ("tuple", "degree", "domain") comes from mathematics. Getting the model straight makes keys, normalization and relational algebra much easier.
The idea
A relation is a set of tuples that all share the same attributes. Each attribute draws its values from a domain (the set of allowed values, such as integers or dates). In SQL terms: a relation is a table, a tuple is a row, an attribute is a column, and a domain is roughly a column's type plus its constraints.
The vocabulary
| Term | Meaning | SQL equivalent |
|---|---|---|
| Relation | A set of tuples with the same attributes | Table |
| Tuple | One element of the relation: a value for each attribute | Row |
| Attribute | A named column with a domain | Column |
| Domain | The set of atomic values an attribute may take | Type plus CHECK constraints |
| Relation schema | The relation's name and its attributes, R(A1, ..., An) | CREATE TABLE definition |
| Degree (arity) | Number of attributes | Number of columns |
| Cardinality | Number of tuples | Number of rows |
| Database schema | The set of relation schemas | All table definitions |
Properties that theory guarantees and SQL relaxes
Because a relation is a set, in theory tuples have no order and there are no duplicate tuples; attributes have no order either, because they are referred to by name. Every value is atomic (indivisible: no lists or nested tables inside a cell), which is the first normal form you will meet in the next module. SQL tables relax two of these: they are multisets (duplicates allowed unless a key prevents them, which is why DISTINCT exists) and ORDER BY lets you request an order, though storage order is still never guaranteed. Many SQL databases also allow arrays and JSON in a column, a deliberate escape from strict atomicity.
NULL and three-valued logic
The model needs a way to say "unknown" or "not applicable", and SQL uses NULL. NULL is not a value and not zero or the empty string; it is the absence of one. Comparisons with NULL return UNKNOWN, so SQL uses three-valued logic (TRUE, FALSE, UNKNOWN), and a WHERE clause keeps only rows where the condition is TRUE.
| Expression | Result | Consequence |
|---|---|---|
| NULL = NULL | UNKNOWN | Use IS NULL, never = NULL |
| salary > 1000 when salary is NULL | UNKNOWN | The row is excluded from both 'salary > 1000' and 'salary <= 1000' |
| x NOT IN (1, 2, NULL) | UNKNOWN for every x | Returns no rows: a classic bug with subqueries that can return NULL |
| COUNT(column) | Counts non-NULL values only | COUNT(*) counts rows; they differ when NULLs exist |
| UNKNOWN AND FALSE / UNKNOWN OR TRUE | FALSE / TRUE | Logic still resolves when the other side decides it |
Integrity rules
The model comes with two rules that the next lessons develop: entity integrity (every relation has a primary key, and no part of it may be NULL, so every tuple is identifiable) and referential integrity (a foreign key value must match an existing key in the referenced relation, or be NULL). Together with domain constraints, they let the DBMS reject data that could never be correct.
Trade-offs
The relational model buys a simple uniform structure, a mathematical query language and independence from physical storage, at the cost of joins to reassemble entities spread across tables and an impedance mismatch with object-oriented code. NULL buys a way to express missing information at the cost of three-valued logic that surprises everyone eventually. Document and graph models trade some of these guarantees for a closer fit to particular data shapes, as the System Design track's databases module discusses.
In an interview
Define precisely and connect to SQL: "A relation is a set of tuples over a fixed set of attributes, each with a domain; degree is the number of attributes and cardinality the number of tuples. SQL tables are close but are multisets, which is why DISTINCT exists." Then show you know the NULL trap: "NOT IN against a subquery that can return NULL returns nothing; NOT EXISTS is the safe form."
Check yourself
A query 'SELECT name FROM employees WHERE dept_id NOT IN (SELECT dept_id FROM closed_departments)' returns no rows at all, although most employees work in open departments. What is the most likely cause?