Module 12: Databases

A relational schema describes tables, columns and constraints. A primary key identifies a row; a foreign key refers to a key in another table. An author table plus book.author_id represents ownership without repeating the author's name everywhere. NOT NULL rejects missing required values, UNIQUE rejects duplicates and CHECK enforces local predicates. SQL tables are not ordered sequences: add ORDER BY when order matters. SQLite foreign-key enforcement must be enabled for each connection with PRAGMA foreign_keys=ON before relying on it.

SELECT chooses columns, WHERE filters rows and JOIN combines rows satisfying a relationship. JOIN books to authors on author_id=id, not on coincidentally equal names. COUNT counts rows; GROUP BY forms groups before aggregation. NULL represents missing or unknown information, so use IS NULL rather than =NULL. A join can multiply rows when multiple matches exist: inspect key cardinalities before assuming one output per book. Parameter placeholders bind data values; they are not substitutes for arbitrary table or column names.

Repeated facts invite update anomalies: changing one author's name in many book rows can leave contradictory values. Store the author fact once and refer to it. A many-to-many book-author relationship needs a linking table with a composite key, not a comma-separated field. Model editions and physical copies separately if their identities differ. Normalisation helps consistency, but practical denormalisation may improve a measured workload if the update policy preserves correctness. Begin with the facts and dependencies, not the desired screen layout.

An index is an additional lookup structure maintained alongside table data. It can reduce exact and range lookup work but consumes space and adds update cost. The database chooses a plan based on available structures and statistics; do not assume a declared index is used for every predicate. EXPLAIN QUERY PLAN shows the chosen access strategy. An index on title supports our exact title lookup; a leading-wildcard substring query may still scan. Measure the actual workload and include write costs before adding many indexes.

A transaction groups changes so they commit together or roll back. Atomicity prevents a loan existing without its stock update; isolation controls interactions between concurrent transactions; durability concerns committed data surviving failures under the database's guarantees. Our conditional UPDATE decreases stock only when copies>0, and the loan insert belongs to the same transaction. Bind user values with ? placeholders so input remains data instead of SQL syntax. Input validation and query parameterisation are complementary; neither replaces authorisation.

Download each script and run it in a terminal with Python 3.11 or later: python m12_database.py. On Windows, py -3 is an alternative; on some systems use python3. The labs use only the standard library. Predict the result before running, then complete the variations. Run without -O so assertions remain enabled. Outputs below were captured by the builder. Code and output are identical in both language editions.

Create an author/book schema, join it, inspect an index and reopen the file to verify persistence across connections.

Download m12_database.py

"""Persist records, join relations and inspect an indexed query."""
import sqlite3, tempfile
from pathlib import Path
with tempfile.TemporaryDirectory() as folder:
    path = Path(folder) / "catalogue.sqlite"
    db = sqlite3.connect(path)
    try:
        db.execute("PRAGMA foreign_keys = ON")
        db.executescript("""
            CREATE TABLE authors(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
            CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER NOT NULL REFERENCES authors(id));
            CREATE INDEX book_title ON books(title);
        """)
        with db:
            db.execute("INSERT INTO authors VALUES (?, ?)", (1, "Frank Herbert"))
            db.execute("INSERT INTO books VALUES (?, ?, ?)", (1, "Dune", 1))
        query = "SELECT books.title, authors.name FROM books JOIN authors ON authors.id=books.author_id WHERE books.title=?"
        print("joined:", db.execute(query, ("Dune",)).fetchall())
        plan = db.execute("EXPLAIN QUERY PLAN SELECT id FROM books WHERE title=?", ("Dune",)).fetchall()
        indexed = any("INDEX" in row[3] for row in plan)
        print("index used:", indexed); assert indexed
        attack = "' OR 1=1 --"
        assert db.execute("SELECT id FROM books WHERE title=?", (attack,)).fetchall() == []
    finally:
        db.close()
    reopened = sqlite3.connect(path)
    try:
        print("reopened rows:", reopened.execute("SELECT COUNT(*) FROM books").fetchone()[0])
        assert reopened.execute("SELECT COUNT(*) FROM books").fetchone()[0] == 1
    finally:
        reopened.close()

joined: [('Dune', 'Frank Herbert')]
index used: True
reopened rows: 1

1. Add another book for the same author.
2. Try an invalid author_id.
3. Run a grouped book count per author.

A duplicate request fails after an attempted decrement, which must roll back. The lab rejects duplicates; Module 14 implements idempotent replay instead.

Download m12_transactions.py

"""A stock decrement and loan creation commit together or roll back together."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys=ON")
db.executescript("""
 CREATE TABLE books(id INTEGER PRIMARY KEY, copies INTEGER NOT NULL CHECK(copies>=0));
 CREATE TABLE loans(request_id TEXT PRIMARY KEY, book_id INTEGER REFERENCES books(id));
 INSERT INTO books VALUES(1, 2);
""")
def borrow(request_id, book_id):
    with db:
        changed = db.execute("UPDATE books SET copies=copies-1 WHERE id=? AND copies>0", (book_id,)).rowcount
        if changed != 1: raise ValueError("unavailable")
        db.execute("INSERT INTO loans VALUES (?, ?)", (request_id, book_id))
try:
    borrow("request-a", 1)
    try:
        borrow("request-a", 1)
    except sqlite3.IntegrityError:
        print("duplicate request rolled back")
    assert db.execute("SELECT copies FROM books").fetchone()[0] == 1
    borrow("request-b", 1)
    try:
        borrow("request-c", 1)
    except ValueError:
        print("out of stock rejected")
    print("copies:", db.execute("SELECT copies FROM books").fetchone()[0], "loans:", db.execute("SELECT COUNT(*) FROM loans").fetchone()[0])
    assert db.execute("SELECT COUNT(*) FROM loans").fetchone()[0] == 2
finally:
    db.close()

duplicate request rolled back
out of stock rejected
copies: 0 loans: 2

1. Remove transaction grouping and identify the broken invariant.
2. Test an unknown book.
3. Explain why a conditional UPDATE is better than an unprotected check followed by decrement.

Try before opening the solution. ★ applies an idea; ★★ combines ideas; ★★★ asks for design or proof.

Choose an answer for feedback; reset to retry. A text answer key is available without JavaScript.

• SQLite transactions — Read commit/rollback and write transactions; describe a partial-update failure.
• Python sqlite3 tutorial — Read placeholders and connection management; explain closure versus rollback.

Draw the author/book schema and explain a failed loan transaction. Module 13 explores how programs themselves become structured input and where computation has limits.