Chapter 4: The Relational Model — Tables, Rows, and Math

The relational model was invented by Edgar F. Codd at IBM in 1970. It's based on mathematical set theory and predicate logic. Despite being 50+ years old, it remains the dominant data model because it's simple, powerful, and mathematically sound.

Core Terminology

// C struct = relation schema
struct Employee {       // ← This is like a table definition
    int  id;             // ← attribute (column)
    char name[64];       // ← attribute
    int  dept_id;        // ← attribute (foreign key!)
    int  salary;         // ← attribute
};

struct Employee employees[1000]; // ← relation instance (table data)
// employees[0] = one tuple (row)

The Key Insight: Relationships

The "relational" in relational database doesn't mean "tables are related" (though they are). It comes from the mathematical term relation (a set of tuples). But in practice, the power comes from relating tables to each other via keys:

Primary Key (PK) — uniquely identifies each row. departments.id, employees.id.

Foreign Key (FK) — references a primary key in another table. employees.dept_id → departments.id.

Relational Algebra (The Math Behind SQL)

SQL is based on relational algebra — a set of operations on tables that produce new tables:

Selection (σ) — Filter rows

-- σ(salary > 90000)(employees)
SELECT * FROM employees WHERE salary > 90000;
-- Result: Alice (95000), Dave (91000)

Projection (π) — Pick columns

-- π(name, salary)(employees)
SELECT name, salary FROM employees;
-- Result: only name and salary columns

Join (⋈) — Combine tables

-- employees ⋈ departments ON dept_id = id
SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON e.dept_id = d.id;

Set Operations

-- Union: all rows from both (no duplicates)
SELECT name FROM employees UNION SELECT name FROM contractors;

-- Intersection: rows in both
SELECT name FROM employees INTERSECT SELECT name FROM managers;

-- Difference: rows in first but not second
SELECT name FROM employees EXCEPT SELECT name FROM fired;

Properties of Relations

• No duplicate rows — each row is unique (enforced by primary key)
• Row order doesn't matter — a table is a SET, not a list
• Column order doesn't matter — columns are identified by name
• Each cell contains one atomic value — no arrays, no nested structs (in pure relational model)

NULL: The Billion-Dollar Mistake?

NULL represents "unknown" or "not applicable." It's NOT zero, NOT empty string, NOT false.

-- NULL behaves strangely:
NULL = NULL   → NULL  (not TRUE!)
NULL != NULL  → NULL  (not TRUE either!)
NULL + 5      → NULL
NULL AND TRUE → NULL

-- You must use IS NULL / IS NOT NULL:
SELECT * FROM employees WHERE manager_id IS NULL;

Constraints: Rules the Database Enforces

CREATE TABLE employees (
    id        SERIAL PRIMARY KEY,          -- unique, not null, auto-increment
    name      VARCHAR(100) NOT NULL,       -- must have a value
    email     VARCHAR(255) UNIQUE,         -- no duplicates
    dept_id   INTEGER REFERENCES departments(id), -- must exist in departments
    salary    INTEGER CHECK (salary > 0),  -- custom validation
    hire_date DATE DEFAULT CURRENT_DATE     -- default value
);

The database rejects any operation that violates these constraints. This is data integrity enforced at the storage level — no matter what buggy application code does, the data stays valid.

Why the Relational Model Won

• Data independence — physical storage can change without affecting queries
• Declarative queries — say WHAT you want, not HOW to get it
• Mathematical foundation — provably correct optimizations
• Constraints — database enforces data integrity regardless of application bugs
• Ad-hoc queries — ask any question without pre-planning access patterns