Relational Algebra: The Logic Behind Every Query

Open a fresh database for today:

duckdb day12/relational.duckdb

.mode box
.read day11/provinces.sql
.read day11/decisions.sql

12.1 The Relational Model (20 min)

In 1970, Edgar F. Codd proposed storing data as relations: sets of rows sharing the same columns. Every SQL database since is built on his ideas, and knowing the vocabulary helps you reason about queries precisely.

Keys: what makes a row unique?

A primary key is the column (or combination of columns) that identifies each row uniquely:

Remember Day 10's validate(), which rejected duplicate (series_id, date) rows? You were enforcing a composite primary key by hand.

Constraints: let the database enforce the rules

Create day12/constraints.sql:

CREATE OR REPLACE TABLE labour_market (
    ref_month         DATE    NOT NULL,
    province          VARCHAR NOT NULL,
    unemployment_rate DOUBLE  CHECK (unemployment_rate BETWEEN 0 AND 100),
    PRIMARY KEY (ref_month, province)
);

INSERT INTO labour_market VALUES ('2026-07-01', 'ON', 7.1);   -- ok
INSERT INTO labour_market VALUES ('2026-07-01', 'ON', 7.2);   -- duplicate key → error
INSERT INTO labour_market VALUES ('2026-07-01', 'QC', 540);   -- CHECK fails → error
INSERT INTO labour_market VALUES (NULL, 'BC', 5.9);           -- NOT NULL → error

Run each INSERT separately and read the error messages. The CHECK constraint is exactly the notebook curriculum's rule "unemployment rates remain between 0–100%". On Days 48–50 you'll express the same idea in Great Expectations.

Constraints guard data as it enters. Checks like Day 7's check_panel inspect data after the fact. Good pipelines use both.

12.2 The Core Operations: σ, π, ρ (25 min)

Relational algebra describes queries as operations that take tables in and return a table out. Because the output is always a table, operations can be chained, much like pandas methods.

Selection σ — keep rows meeting a condition

σ_{balance < 0}(provinces)

SELECT * FROM provinces WHERE balance_pct_gdp < 0;

Projection π — keep certain columns

π_{province, balance}(provinces)

SELECT province, balance_pct_gdp FROM provinces;

A subtle point: in pure relational algebra a relation is a set, so projection removes duplicate rows. SQL keeps duplicates unless you ask otherwise. Compare these two:

SELECT region FROM provinces;            -- 10 rows (a "bag")
SELECT DISTINCT region FROM provinces;   -- 4 rows  (a true set)

Rename ρ — rename columns or tables

ρ_{bal ← balance_pct_gdp}(provinces)

SELECT province, balance_pct_gdp AS bal FROM provinces AS p;

Composition — operations nest

π_province(σ_{region='Atlantic'}(provinces))

"Filter to Atlantic, then keep the province column":

SELECT province FROM provinces WHERE region = 'Atlantic';

Aggregation γ (an extension to the original algebra)

γ_{region; AVG(balance)}(provinces)

SELECT region, AVG(balance_pct_gdp) FROM provinces GROUP BY region;

Translation table

Try it 12.2

Write the relational-algebra expression and the SQL for: "the names of provinces in the West or Prairies with a surplus."

SELECT province
FROM provinces
WHERE region IN ('West', 'Prairies') AND balance_pct_gdp > 0;

12.3 Set Operations: ∪, ∩, − (23 min)

Set operations combine two tables with the same columns. Create day12/sets.sql:

CREATE OR REPLACE TABLE surplus_2026 AS
    SELECT province FROM provinces WHERE balance_pct_gdp > 0;

-- Illustrative: provinces that balanced their budget in the previous year
CREATE OR REPLACE TABLE surplus_2025 (province VARCHAR);
INSERT INTO surplus_2025 VALUES ('AB'), ('SK'), ('QC');

Union ∪ — in either year

SELECT province FROM surplus_2025
UNION                                   -- removes duplicates (a true set union)
SELECT province FROM surplus_2026;

SELECT province FROM surplus_2025
UNION ALL                               -- keeps duplicates; faster
SELECT province FROM surplus_2026;

Intersection ∩ — in both years

SELECT province FROM surplus_2025
INTERSECT
SELECT province FROM surplus_2026;

Difference − — in one but not the other

-- Lost their surplus
SELECT province FROM surplus_2025
EXCEPT
SELECT province FROM surplus_2026;

-- Newly in surplus
SELECT province FROM surplus_2026
EXCEPT
SELECT province FROM surplus_2025;

Order matters for EXCEPT, just as A − B ≠ B − A.

A DuckDB convenience: UNION BY NAME

Standard UNION matches columns by position, which is a common source of silent bugs. DuckDB can match by name instead:

SELECT 'boc' AS source, 2.25 AS rate
UNION ALL BY NAME
SELECT 3.63 AS rate, 'fed' AS source;      -- columns in a different order: still correct

This is what you want when stacking extracts from several sources, as in Day 10's pd.concat.

12.4 Products and Joins (35 min)

The Cartesian product × — every combination

CREATE OR REPLACE TABLE scenarios (scenario VARCHAR, gdp_growth DOUBLE);
INSERT INTO scenarios VALUES ('Base', 1.8), ('Downside', -0.5);

CREATE OR REPLACE TABLE rate_paths (path VARCHAR, terminal_rate DOUBLE);
INSERT INTO rate_paths VALUES ('Hold', 2.25), ('Cut', 1.75), ('Hike', 2.75);

SELECT * FROM scenarios CROSS JOIN rate_paths;     -- 2 × 3 = 6 rows

Cross joins are useful for scenario grids. They're also why a join with a missing or wrong join condition can explode your row count.

A join ⋈ is a product followed by a selection

R ⋈_{R.k = S.k} S = σ_{R.k = S.k}(R × S)

Set up two small tables of monthly rates (illustrative values). Note that the months don't fully overlap:

CREATE OR REPLACE TABLE boc_monthly (month DATE, overnight_rate DOUBLE);
INSERT INTO boc_monthly VALUES
    ('2026-03-01', 2.25), ('2026-04-01', 2.25), ('2026-05-01', 2.25),
    ('2026-06-01', 2.25), ('2026-07-01', 2.25), ('2026-08-01', 2.25);

CREATE OR REPLACE TABLE fed_monthly (month DATE, fed_funds DOUBLE);
INSERT INTO fed_monthly VALUES
    ('2026-02-01', 3.64), ('2026-03-01', 3.64), ('2026-04-01', 3.63),
    ('2026-05-01', 3.63), ('2026-06-01', 3.63), ('2026-07-01', 3.63);

Inner join — months in both

SELECT b.month,
       b.overnight_rate,
       f.fed_funds,
       b.overnight_rate - f.fed_funds AS boc_fed_diff
FROM boc_monthly AS b
JOIN fed_monthly AS f ON b.month = f.month
ORDER BY b.month;

b and f are table aliases (ρ again) that keep queries short. When both tables share the key's name, USING is a neater form:

SELECT month, overnight_rate, fed_funds
FROM boc_monthly JOIN fed_monthly USING (month);

Left, right and full outer joins

-- Every BoC month; Fed values where available (August has none yet)
SELECT * FROM boc_monthly LEFT JOIN fed_monthly USING (month) ORDER BY month;

-- Every month from either table
SELECT * FROM boc_monthly FULL OUTER JOIN fed_monthly USING (month) ORDER BY month;

Semi-join and anti-join: filtering by another table

A semi-join keeps rows of A that have a match in B, without adding B's columns. An anti-join keeps rows of A with no match. DuckDB supports both directly:

-- BoC months that have Fed data
SELECT * FROM boc_monthly SEMI JOIN fed_monthly USING (month);

-- BoC months MISSING Fed data — a classic data-quality check
SELECT * FROM boc_monthly ANTI JOIN fed_monthly USING (month);

The anti-join is what Day 6's merge(..., indicator=True) diagnostic was doing. Here it takes one line.

The fan-out trap: joining on a non-unique key

CREATE OR REPLACE TABLE regional_notes (region VARCHAR, note VARCHAR);
INSERT INTO regional_notes VALUES
    ('Atlantic', 'Fisheries exposure'),
    ('Atlantic', 'Ageing population'),     -- a second row for the same region!
    ('West',     'Energy exposure');

SELECT COUNT(*) FROM provinces;                                    -- 10
SELECT COUNT(*) FROM provinces JOIN regional_notes USING (region); -- more than you'd think

Each Atlantic province matched two notes, so it now appears twice. Any SUM over that result would double-count. Always know whether your join key is unique on the side you're joining to.

Habit: after every join, compare row counts before and after. If the count grew unexpectedly, you have a fan-out.

Try it 12.4

(a) Find regions that appear in provinces but have no entry in regional_notes. (b) Produce one row per province with all its notes combined into a single text field, so there is no fan-out. (Hint: aggregate first with string_agg(note, '; '), then join.)

-- (a)
SELECT DISTINCT region FROM provinces ANTI JOIN regional_notes USING (region);

-- (b)
SELECT p.province, p.region, n.notes
FROM provinces AS p
LEFT JOIN (
    SELECT region, string_agg(note, '; ') AS notes
    FROM regional_notes
    GROUP BY region
) AS n USING (region)
ORDER BY p.province;

12.5 How SQL Is Evaluated (8 min)

You write SQL in one order, but the database evaluates it in another:

This explains several puzzling rules:

• You can't use an aggregate like AVG(x) in WHERE, because groups don't exist yet at step 2. Use HAVING.
• In standard SQL, aliases defined in SELECT can't be used in WHERE. (DuckDB is friendlier and allows it, but don't count on this in other databases.)

Behind the scenes DuckDB rewrites your query into an efficient plan. You can inspect it:

EXPLAIN
SELECT region, AVG(balance_pct_gdp)
FROM provinces
WHERE balance_pct_gdp < 0
GROUP BY region;

You'll see the relational operators (filter, projection, aggregate) arranged as a tree.

12.6 Subqueries (8 min)

A query's result is a table, so it can be used inside another query.

-- Scalar subquery: provinces worse than the average
SELECT province, balance_pct_gdp
FROM provinces
WHERE balance_pct_gdp < (SELECT AVG(balance_pct_gdp) FROM provinces);

-- IN subquery: decisions taken at the peak rate
SELECT *
FROM boc_decisions
WHERE rate = (SELECT MAX(rate) FROM boc_decisions);

Nested subqueries quickly become hard to read. On Days 14–17 you'll learn Common Table Expressions (WITH ...), which name each step, like variables in Python.

12.7 Mini-Project: A Star Schema for Macro Series (18 min)

Analytical databases often separate:

• a fact table: many rows of measurements (series_id, date, value);
• dimension tables: few rows describing the facts (series names, units, sources).

Joining them gives labelled data without repeating descriptions millions of times. This is the design dbt marts use on Days 38–40.

Create day12/star_schema.sql:

-- Dimension: one row per series (the Day 10 catalogue, with more detail)
CREATE OR REPLACE TABLE dim_series (
    series_id  VARCHAR PRIMARY KEY,
    name       VARCHAR NOT NULL,
    source     VARCHAR NOT NULL,
    frequency  VARCHAR,
    unit       VARCHAR
);

INSERT INTO dim_series VALUES
    ('V39079',    'overnight_rate', 'boc',     'daily',   'percent'),
    ('FEDFUNDS',  'fed_funds',      'fred',    'monthly', 'percent'),
    ('v41690973', 'cpi_all_items',  'statcan', 'monthly', 'index 2002=100');

-- Fact: one row per series per date (illustrative values)
CREATE OR REPLACE TABLE fact_observations (
    series_id VARCHAR,
    obs_date  DATE,
    value     DOUBLE,
    PRIMARY KEY (series_id, obs_date)
);

INSERT INTO fact_observations VALUES
    ('V39079',   '2026-07-01', 2.25), ('V39079',   '2026-08-01', 2.25),
    ('FEDFUNDS', '2026-07-01', 3.63), ('FEDFUNDS', '2026-08-01', 3.63),
    ('v41690973','2026-06-01', 168.2),('v41690973','2026-07-01', 169.0),
    ('XYZ999',   '2026-07-01', 1.0);     -- orphan: no matching dimension row!

Tasks:

1. Join facts to the dimension to show name, source, unit, obs_date and value.
2. Count observations per source.
3. Find orphan facts with no matching series (anti-join).
4. Find series in the dimension with no observations.
5. Write the relational-algebra expression for task 1.

-- 1
SELECT d.name, d.source, d.unit, f.obs_date, f.value
FROM fact_observations AS f
JOIN dim_series AS d USING (series_id)
ORDER BY d.name, f.obs_date;

-- 2
SELECT d.source, COUNT(*) AS n_obs
FROM fact_observations AS f
JOIN dim_series AS d USING (series_id)
GROUP BY d.source;

-- 3
SELECT * FROM fact_observations ANTI JOIN dim_series USING (series_id);

-- 4
SELECT * FROM dim_series ANTI JOIN fact_observations USING (series_id);

-- 5: π_{name, source, unit, obs_date, value}( fact_observations ⋈_{series_id} dim_series )

Day 12 checklist

• I can identify primary keys (including composite keys) and add constraints
• I can express queries with σ, π, ρ, γ and translate between algebra, SQL and pandas
• I can use UNION, INTERSECT, EXCEPT and UNION BY NAME
• I can choose the right join, and use semi- and anti-joins for checks
• I check row counts after joins to catch fan-outs
• I understand SQL's logical evaluation order

git add .
git commit -m "Day 12: relational algebra, joins, star schema"
git push