SQL / RELATIONAL FOUNDATIONS
What a relational database is and why tables won
Explain what makes a database relational, how value matching replaces stored links, and why one set of tables can answer questions nobody planned for.
What you will learn
- State what makes a store relational and what the word 'relational' actually names
- Link two tables by matching values and query that link from either direction
- Nest a query inside another, because every result is itself a table
- Spot data shapes, like a list stuffed into one column, that block future questions
Understanding What a relational database is and why tables won
A relational database keeps every fact in the same shape: a table, which is a set of rows that all share the same named columns. There are no pointers from one record to another, no records nested inside other records, and no traversal order baked into the store. The word relational does not refer to the connections between tables; it names the table itself, which is what mathematics calls a relation over a list of columns.
Because nothing is linked, relationships have to be worked out while a query runs, by comparing values. A row in work belongs to a row in composer for exactly one reason: its composer_id column holds the same integer as that composer's id column, and the join checks that at the moment you ask. Nothing in the stored data favours one direction, which is why the same two tables answer both "what did she write" and "who wrote this".
That property is what won the argument. The hierarchical and network databases that came before, such as IMS and the CODASYL systems, stored the access paths, so programs had to walk them, and a question the designer had not anticipated meant new pointers or a reload of the data. A relational query names only the result it wants, which leaves the engine free to choose how to find it today and choose differently once the table has grown; and because every query consumes tables and returns a table, results stack, so you can filter a summary or join the output of a join.
-- Two tables, with no stored link between them.
CREATE TABLE composer (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE work (
id INTEGER PRIMARY KEY,
composer_id INTEGER NOT NULL,
title TEXT NOT NULL,
premiered INTEGER
);
INSERT INTO composer (id, name) VALUES
(1, 'Clara Schumann'),
(2, 'Florence Price'),
(3, 'Erik Satie');
INSERT INTO work (id, composer_id, title, premiered) VALUES
(1, 1, 'Piano Concerto in A minor', 1836),
(2, 2, 'Symphony No. 1 in E minor', 1932),
(3, 2, 'Violin Concerto No. 2', 1952),
(4, 3, 'Gymnopedies', 1888);
-- Asked one way: how many works per composer?
SELECT c.name, COUNT(w.id) AS works
FROM composer c
JOIN work w ON w.composer_id = c.id
GROUP BY c.id, c.name
ORDER BY works DESC, c.name;
-- Same tables, untouched, asked the other way: who wrote the early works?
SELECT w.title, c.name
FROM work w
JOIN composer c ON c.id = w.composer_id
WHERE w.premiered < 1900
ORDER BY w.premiered;The only thing a relational database stores is values in tables, and relationships are recomputed at query time by matching those values, which is why questions nobody planned for are still answerable.
Worked examples
A result is itself a table
Shows that a query can be used as the input of another query, because its output has the same shape as a stored table.
CREATE TABLE reading (
station TEXT,
celsius INTEGER
);
INSERT INTO reading (station, celsius) VALUES
('Oslo', -3), ('Oslo', 1),
('Lima', 19), ('Lima', 28);
SELECT station, spread
FROM (
SELECT station, MAX(celsius) - MIN(celsius) AS spread
FROM reading
GROUP BY station
) AS s
WHERE spread > 5;Example explained
Line 1The inner SELECT turns four stored rows into one row per station; that intermediate table is never stored anywhere.
Line 2AS s gives the derived table a name, which PostgreSQL requires and SQLite merely tolerates omitting.
Line 3WHERE spread > 5 filters on a column that no CREATE TABLE ever mentioned, yet it behaves like any other column.
Line 4Oslo's spread is 1 - (-3) = 4 and fails the test; Lima's is 28 - 19 = 9 and survives.
Changing how, not what
Run in the same session as the tables above, this shows that adding an access path leaves the query text and the answer untouched.
CREATE INDEX work_premiered_idx ON work (premiered);
SELECT COUNT(*) AS early_works
FROM work
WHERE premiered < 1900;Example explained
Line 1CREATE INDEX adds a way to locate rows, adds no facts, and returns no rows of its own.
Line 2The SELECT is character for character the same query you would have written before the index existed, because it names a condition rather than a search method.
Line 3Whether the index is actually used is the engine's decision; the count is 2 either way.
Line 4Drop the index and the answer is still 2, only the work done to reach it differs.
Important notes
Boxes, pipes, alignment and colours come from whichever client you run; the database returns column names and rows, so never treat the drawing as part of the data.
SQL tables are close to relations but not identical: they permit duplicate rows and NULLs, neither of which a relation as originally defined allows.
Common mistakes
Reading relational as "has relationships between tables": a single table with no foreign key is already a relation, and a document store full of id references is still not relational, so the reader ends up looking for a link object that does not exist in the model.
Believing row order is part of the data because the first run happened to return insertion order; as soon as an index appears or the engine picks another plan the order shifts, and reports built on that assumption go wrong without any error.
Storing a list in one column, such as works = 'Gymnopedies,Sarabandes', which reads fine until the first query that must count or filter individual items, at which point every question becomes string surgery that the engine cannot optimise.
Try it yourself
Change, predict, then run
In a browser SQL editor, create city(id, name) and visit(id, city_id, year), insert three cities and five visits with two visits pointing at the same city. Then write one query counting visits per city and a second listing each visit with its city name, without changing either table in between.
Open the SQL workspaceCheck your understanding
You have composer and work as two tables, linked because work.composer_id holds composer.id values. The schema was designed for listing composers with their works, and now someone wants works listed with their composer. What must change?
- The tables have to be recreated with work as the parent, since joins follow declaration order
- Nothing in storage changes; you write a different query, because the link is a value either side can match on
- A reverse reference from work back to composer must be added before the query can run
- An index on work.composer_id is required, or the reverse query returns no rows
Show answer
The relationship is not stored anywhere, it is recomputed by comparing composer_id with id, so neither direction is privileged and only the query text differs. The index option is tempting because an index really can make that lookup faster, but it affects how quickly rows are found, never which rows come back.