ER case study: a train reservation system

postgres/berth_ranges.sql

-- The same berths on PostgreSQL 18.6, with the no-overlap rule declared instead of programmed.
SHOW server_version;
-- btree_gist lets a GiST index compare plain columns, such as text and integers, with =.
CREATE EXTENSION btree_gist;

CREATE TABLE berth (
  run_id     integer NOT NULL,
  coach_code text NOT NULL,
  berth_no   integer NOT NULL,
  PRIMARY KEY (run_id, coach_code, berth_no)
);
CREATE TABLE allocation (
  run_id     integer NOT NULL,
  coach_code text NOT NULL,
  berth_no   integer NOT NULL,
  passenger  text NOT NULL,
  journey    int4range NOT NULL CHECK (NOT isempty(journey)),
  FOREIGN KEY (run_id, coach_code, berth_no) REFERENCES berth (run_id, coach_code, berth_no),
  EXCLUDE USING gist (run_id WITH =, coach_code WITH =, berth_no WITH =, journey WITH &&)
);
INSERT INTO berth SELECT 1, 'B1', n FROM generate_series(21, 24) AS n;

INSERT INTO allocation VALUES (1, 'B1', 23, 'Anu', int4range(1, 4));
-- int4range(4, 7) is [4,7): it starts where Anu's [1,4) ends, so the two do not overlap.
INSERT INTO allocation VALUES (1, 'B1', 23, 'Ravi', int4range(4, 7));
-- expect: error (overlaps both journeys on berth 23)
INSERT INTO allocation VALUES (1, 'B1', 23, 'Sara', int4range(3, 5));
INSERT INTO allocation VALUES (1, 'B1', 22, 'Sara', int4range(3, 5)), (1, 'B1', 21, 'Tom', int4range(2, 6));

-- Free berths from stop 5 to stop 7.
SELECT b.coach_code, b.berth_no
FROM berth AS b
WHERE b.run_id = 1
  AND NOT EXISTS (SELECT 1 FROM allocation AS a
                  WHERE (a.run_id, a.coach_code, a.berth_no) = (b.run_id, b.coach_code, b.berth_no)
                    AND a.journey && int4range(5, 7))
ORDER BY b.berth_no;

-- PostgreSQL 18 can state the same rule as a temporal primary key: its last column is checked for overlaps.
CREATE TABLE allocation_pk (
  run_id     integer NOT NULL,
  coach_code text NOT NULL,
  berth_no   integer NOT NULL,
  journey    int4range NOT NULL,
  passenger  text NOT NULL,
  PRIMARY KEY (run_id, coach_code, berth_no, journey WITHOUT OVERLAPS)
);
INSERT INTO allocation_pk VALUES (1, 'B1', 23, int4range(1, 4), 'Anu'), (1, 'B1', 23, int4range(4, 7), 'Ravi');
-- expect: error
INSERT INTO allocation_pk VALUES (1, 'B1', 23, int4range(3, 5), 'Sara');

postgres/pg_session.py

"""Throwaway PostgreSQL 18.6 servers for the examples: run SQL files statement by statement and print each statement
with what it returned, or keep several connections open at once to show what concurrent transactions do.

The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file. It is
created in a new folder under the run's temporary folder (TMPDIR), listens only on a Unix socket in that folder (no
network port), and is stopped and removed afterwards. learn:verify's run sandbox cannot read the home folder, where
mise keeps PostgreSQL, so these sessions are recorded with LEARN_VERIFY_NETWORK=allow; they read only this folder and
the toolchain, and they open no network connection.

Statements that create or change tables, and long INSERTs, are printed as their first line only (the SQL files on the
page show them in full). A statement whose comment says "expect: error" must fail, and every other statement must
succeed; otherwise the session exits with status 1, so a run that went wrong is never recorded as if it had worked.
"""

import os
import shutil
import subprocess
import sys
import tempfile
from pathlib import Path

PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"


def _statements(sql):
    """Splits a SQL file into statements: each ends with a semicolon at the end of a line. Comment lines stay with
    the statement that follows them."""
    out, lines = [], []
    for line in sql.splitlines():
        if not line.strip() and not lines:
            continue
        lines.append(line)
        if line.rstrip().endswith(";") and not line.lstrip().startswith("--"):
            out.append("\n".join(lines).strip())
            lines = []
    if any(not line.lstrip().startswith("--") and line.strip() for line in lines):
        sys.exit("the file ends with a statement that has no semicolon")
    return out


def _shown(statement):
    """The lines of a statement as the session prints them: a statement that creates or changes a table, or a long
    INSERT, by its first line only (the SQL file on the page shows it in full)."""
    lines = statement.splitlines()
    code = [line for line in lines if not line.lstrip().startswith("--")]
    if len(code) > 1 and code[0].split()[0].upper() in ("CREATE", "ALTER"):
        return [code[0].rstrip() + " …"]
    if len(code) > 1 and code[0].split()[0].upper() == "INSERT":
        return [line for line in lines if line.lstrip().startswith("--")] + [code[0].rstrip() + " …"]
    return lines


def _run(args, **kw):
    return subprocess.run(args, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, text=True, **kw)


def run_file(name):
    """Runs one SQL file on a server of its own."""
    with Server() as pg:
        pg.play(name)


class Server:
    """A throwaway PostgreSQL 18.6 server for sessions that need several open connections at once."""

    def __enter__(self):
        if not (BIN / "postgres").exists():
            sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="pg"))
        if len(str(self.base / ".s.PGSQL.5432")) > 103:
            sys.exit(f"the socket path in {self.base} is longer than Unix allows (103 bytes): use a shorter TMPDIR")
        self.data = self.base / "data"
        self.env = {**os.environ, "PGHOST": str(self.base), "PGUSER": "learner", "PGDATABASE": "postgres"}
        done = _run([BIN / "initdb", "-D", self.data, "-U", "learner", "--auth=trust", "--no-instructions", "--no-sync",
                     "-E", "UTF8", "--locale=C"], env=self.env)
        if done.returncode != 0:
            sys.exit(f"initdb failed:\n{done.stdout}")
        options = f"-c listen_addresses='' -c unix_socket_directories='{self.base}'"
        options += " -c fsync=off -c full_page_writes=off"  # a throwaway server: no need to survive a power cut
        log = self.base / "server.log"
        done = _run([BIN / "pg_ctl", "-D", self.data, "-o", options, "-l", log, "-w", "start"], env=self.env)
        if done.returncode != 0:
            sys.exit(f"the server did not start:\n{(self.base / 'server.log').read_text()}")
        self.connections = []
        return self

    def __exit__(self, *exc):
        for c in self.connections:
            c.close()
        _run([BIN / "pg_ctl", "-D", self.data, "-m", "immediate", "-w", "stop"], env=self.env)
        shutil.rmtree(self.base, ignore_errors=True)

    def play(self, name, quiet=False):
        """Runs a SQL file statement by statement and prints each with its result; quiet prints only a summary line
        (and any statement that did not do what its comment expects)."""
        failed, count = False, 0
        for statement in _statements(Path(name).read_text()):
            expect_error = "expect: error" in statement
            done = _run([BIN / "psql", "-X", "-v", "VERBOSITY=default", "-c", statement], env=self.env)
            count += 1
            unexpected = (done.returncode != 0) != expect_error
            if not quiet or unexpected:
                print("\n".join(("=> " if i == 0 else "   ") + line for i, line in enumerate(_shown(statement))))
                print(done.stdout.rstrip("\n"))
                print()
            if unexpected:
                failed = True
                print(f"[the statement above {'succeeded' if expect_error else 'failed'}, which was not expected]")
        if quiet and not failed:
            print(f"-- {name}: {count} statements, each as expected")
            print()
        if failed:
            sys.exit(1)

    def sql(self, statement):
        """Runs one statement on a connection of its own and returns what psql printed."""
        return _run([BIN / "psql", "-X", "-c", statement], env=self.env).stdout.rstrip("\n")

    def connect(self, name):
        c = Connection(name, self.env)
        self.connections.append(c)
        return c


class Connection:
    """An open psql session: one connection, so a transaction stays open between statements."""

    MARK = "-- statement done --"

    def __init__(self, name, env):
        self.name = name
        self.p = subprocess.Popen([BIN / "psql", "-X", "-v", "ON_ERROR_STOP=0"], stdin=subprocess.PIPE,
                                  stdout=subprocess.PIPE, stderr=subprocess.STDOUT, text=True, bufsize=1,
                                  env={**env, "PGAPPNAME": name})

    def send(self, statement):
        """Sends a statement without waiting for its result."""
        self.p.stdin.write(f"{statement}\n\\echo '{self.MARK}'\n")
        self.p.stdin.flush()

    def result(self):
        """Waits for the result of the statement sent last."""
        lines = []
        for line in self.p.stdout:
            if line.rstrip("\n") == self.MARK:
                break
            lines.append(line.rstrip("\n"))
        return "\n".join(lines)

    def run(self, statement):
        self.send(statement)
        return self.result()

    def close(self):
        if self.p.poll() is None:
            self.p.stdin.close()
            self.p.wait(timeout=10)

postgres/reservation_schema.sql

-- The final design on PostgreSQL 18.6: the tables, one train, one run and one coach of four berths.
CREATE EXTENSION btree_gist;
CREATE TABLE station (
  station_code text PRIMARY KEY,
  name         text NOT NULL
);
CREATE TABLE train (
  train_no text PRIMARY KEY,
  name     text NOT NULL
);
-- STOP is a weak entity of TRAIN: the train's key plus the stop's place on the route.
CREATE TABLE stop (
  train_no     text NOT NULL REFERENCES train (train_no),
  seq          integer NOT NULL CHECK (seq > 0),
  station_code text NOT NULL REFERENCES station (station_code),
  PRIMARY KEY (train_no, seq)
);
-- A run is one train on one date: a surrogate key keeps the keys below short, UNIQUE keeps the natural key.
CREATE TABLE run (
  run_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  train_no text NOT NULL REFERENCES train (train_no),
  run_date date NOT NULL,
  UNIQUE (train_no, run_date)
);
CREATE TABLE coach (
  run_id     integer NOT NULL REFERENCES run (run_id),
  coach_code text NOT NULL,
  class      text NOT NULL CHECK (class IN ('sleeper', 'ac3', 'ac2')),
  PRIMARY KEY (run_id, coach_code)
);
CREATE TABLE berth (
  run_id     integer NOT NULL,
  coach_code text NOT NULL,
  berth_no   integer NOT NULL CHECK (berth_no > 0),
  PRIMARY KEY (run_id, coach_code, berth_no),
  FOREIGN KEY (run_id, coach_code) REFERENCES coach (run_id, coach_code)
);
-- A booking: an internal key, a random reference for the passenger, one run and one journey of stops [from, to).
CREATE TABLE booking (
  booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_ref text NOT NULL UNIQUE CHECK (public_ref ~ '^[A-Z0-9]{8}$'),
  run_id     integer NOT NULL REFERENCES run (run_id),
  journey    int4range NOT NULL CHECK (lower(journey) >= 1 AND NOT isempty(journey)),
  UNIQUE (booking_id, run_id, journey)
);
CREATE TABLE passenger (
  booking_id   bigint NOT NULL REFERENCES booking (booking_id),
  passenger_no integer NOT NULL CHECK (passenger_no BETWEEN 1 AND 6),
  name         text NOT NULL,
  PRIMARY KEY (booking_id, passenger_no)
);
-- An allocation repeats its booking's run and journey, because an exclusion constraint compares columns of one
-- table; the three-column foreign key keeps the copy equal to the booking.
CREATE TABLE allocation (
  booking_id   bigint NOT NULL,
  passenger_no integer NOT NULL,
  run_id       integer NOT NULL,
  journey      int4range NOT NULL,
  coach_code   text NOT NULL,
  berth_no     integer NOT NULL,
  PRIMARY KEY (booking_id, passenger_no),
  FOREIGN KEY (booking_id, passenger_no) REFERENCES passenger (booking_id, passenger_no),
  FOREIGN KEY (booking_id, run_id, journey) REFERENCES booking (booking_id, run_id, journey),
  FOREIGN KEY (run_id, coach_code, berth_no) REFERENCES berth (run_id, coach_code, berth_no),
  EXCLUDE USING gist (run_id WITH =, coach_code WITH =, berth_no WITH =, journey WITH &&)
);
CREATE TABLE waitlist_entry (
  run_id       integer NOT NULL REFERENCES run (run_id),
  class        text NOT NULL,
  position     integer NOT NULL,
  booking_id   bigint NOT NULL,
  passenger_no integer NOT NULL,
  PRIMARY KEY (run_id, class, position),
  UNIQUE (booking_id, passenger_no),
  FOREIGN KEY (booking_id, passenger_no) REFERENCES passenger (booking_id, passenger_no)
);
-- The history: every change of a passenger's state is a new row.
CREATE TABLE passenger_event (
  event_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  booking_id   bigint NOT NULL,
  passenger_no integer NOT NULL,
  status       text NOT NULL CHECK (status IN ('confirmed', 'waitlisted', 'cancelled')),
  FOREIGN KEY (booking_id, passenger_no) REFERENCES passenger (booking_id, passenger_no)
);

INSERT INTO station VALUES ('ARN', 'Arunagar'), ('BKP', 'Bakulpur'), ('CDP', 'Chandrapuri'), ('DVN', 'Devnagar'),
  ('EKB', 'Ekamba'), ('FLP', 'Fulpur'), ('GRN', 'Girinagar');
INSERT INTO train VALUES ('99001', 'Lesson Express');
INSERT INTO stop SELECT '99001', n, (ARRAY['ARN', 'BKP', 'CDP', 'DVN', 'EKB', 'FLP', 'GRN'])[n] FROM generate_series(1, 7) AS n;
INSERT INTO run (train_no, run_date) VALUES ('99001', '2026-12-20');
INSERT INTO coach VALUES (1, 'B1', 'ac3');
INSERT INTO berth SELECT 1, 'B1', n FROM generate_series(21, 24) AS n;

postgres/bookings.sql

-- A day of bookings on run 1 of train 99001, coach B1, berths 21 to 24.
INSERT INTO booking (public_ref, run_id, journey) VALUES ('K7Q2M9XW', 1, '[1,4)'), ('P3ZT8HNA', 1, '[4,7)'),
  ('H8D4RW2E', 1, '[2,5)'), ('U6M2LJ9T', 1, '[1,6)'), ('W5N6C3QY', 1, '[1,3)');
INSERT INTO passenger VALUES (1, 1, 'Anu'), (1, 2, 'Ravi'), (2, 1, 'Sara'), (3, 1, 'Tom'), (4, 1, 'Uma'), (5, 1, 'Dev');
INSERT INTO allocation VALUES (1, 1, 1, '[1,4)', 'B1', 23), (1, 2, 1, '[1,4)', 'B1', 24), (2, 1, 1, '[4,7)', 'B1', 23);

-- expect: error (Tom's journey overlaps Anu's on berth 23)
INSERT INTO allocation VALUES (3, 1, 1, '[2,5)', 'B1', 23);

-- expect: error (an allocation whose journey differs from its booking's)
INSERT INTO allocation VALUES (3, 1, 1, '[2,4)', 'B1', 22);

INSERT INTO allocation VALUES (3, 1, 1, '[2,5)', 'B1', 22), (4, 1, 1, '[1,6)', 'B1', 21);
INSERT INTO passenger_event (booking_id, passenger_no, status)
  VALUES (1, 1, 'confirmed'), (1, 2, 'confirmed'), (2, 1, 'confirmed'), (3, 1, 'confirmed'), (4, 1, 'confirmed');

-- Dev asks for stops 1 to 3: no berth of B1 is free over [1,3), so he joins the waitlist.
SELECT count(*) AS free_berths FROM berth AS b
WHERE b.run_id = 1 AND NOT EXISTS (SELECT 1 FROM allocation AS a WHERE a.run_id = b.run_id
  AND a.coach_code = b.coach_code AND a.berth_no = b.berth_no AND a.journey && '[1,3)');

INSERT INTO waitlist_entry VALUES (1, 'ac3', 1, 5, 1);
INSERT INTO passenger_event (booking_id, passenger_no, status) VALUES (5, 1, 'waitlisted');

-- Ravi cancels: his allocation goes, an event records it, and berth 24 is free over [1,4), which fits Dev.
DELETE FROM allocation WHERE booking_id = 1 AND passenger_no = 2;
INSERT INTO passenger_event (booking_id, passenger_no, status) VALUES (1, 2, 'cancelled');
INSERT INTO allocation VALUES (5, 1, 1, '[1,3)', 'B1', 24);
DELETE FROM waitlist_entry WHERE booking_id = 5 AND passenger_no = 1;
INSERT INTO passenger_event (booking_id, passenger_no, status) VALUES (5, 1, 'confirmed');

-- Each passenger's current state, the latest event, and their berth.
SELECT DISTINCT ON (p.booking_id, p.passenger_no) b.public_ref, p.name, e.status, a.coach_code, a.berth_no
FROM passenger AS p
JOIN booking AS b ON b.booking_id = p.booking_id
JOIN passenger_event AS e ON e.booking_id = p.booking_id AND e.passenger_no = p.passenger_no
LEFT JOIN allocation AS a ON a.booking_id = p.booking_id AND a.passenger_no = p.passenger_no
ORDER BY p.booking_id, p.passenger_no, e.event_id DESC;