C11: Relational databases, transactions, and recovery

A. Prerequisites and observable goals#

Level: Degree core; SQLite is the concrete laboratory, not a claim that all databases behave identically.

B. The central question: which facts must remain consistent together?#

A relational schema describes tables, columns, keys, and constraints. A row represents an entity or relationship under a declared interpretation. A primary key identifies a row; a foreign key connects a stored reference to an existing row in another table. A uniqueness constraint protects an identity rule even when two application requests race. Application checks are useful for friendly errors, but they do not replace database-enforced invariants.

Consider players belonging to guilds. Storing guild name and region repeatedly in every player row creates update anomalies: renaming a guild requires updating many rows, and partial updates can leave contradictory names. A functional dependency $\text{guild\_id}\to\text{guild\_name,region}$ suggests separating guild facts into a guild table and storing only its key in each player. Normalization organizes dependencies to reduce redundancy and anomalies. It is not a rule that every query must use the maximum possible number of joins; deliberate denormalization can be appropriate when its consistency policy and measured benefit are explicit.

C. Queries operate on rows, duplicates, and unknown values#

SQL query results ordinarily have bag semantics: duplicate rows can remain unless eliminated deliberately. An inner join returns matching combinations; a left join also retains left-side rows without matches, filling right-side columns with NULL. NULL represents missing or unknown information under SQL’s three-valued logic, not a special integer zero. Use IS NULL, not = NULL, to test for it. A WHERE clause retains rows whose predicate is true, not unknown.

To summarize guild gold, join guilds to players, group by guild identity, and sum balances. COALESCE(SUM(...),0) gives zero for a guild with no players. COUNT(player.id) counts matched non-null player identifiers; COUNT(*) after a left join also counts the placeholder row for an empty guild. That distinction can cause a convincing but incorrect report.

SELECT g.name, COALESCE(SUM(p.gold), 0) AS total_gold
FROM guild AS g
LEFT JOIN player AS p ON p.guild_id = g.id
GROUP BY g.id, g.name
ORDER BY g.name;

Grouping by a stable identifier avoids accidentally combining different entities that happen to share a display name. Sorting the final output gives deterministic presentation, not a guarantee that the database stores rows physically in that order.

D. Indexes and query plans are workload-dependent tools#

An index stores an additional search structure over selected columns. It can reduce reads for selective lookups, joins, and ordered access, but costs storage and maintenance on writes. An index on player.guild_id supports finding players for a guild. An index is not automatically used: the planner considers available access paths and estimates. Inspect an execution plan and measure representative data rather than assuming the presence of an index proves a faster query.

A composite index’s column order matters because its ordering determines which prefixes can be searched efficiently. A covering index may avoid fetching full table rows for some queries. These are useful continuation topics, but the lab stays small enough to inspect every row and constraint. Avoid optimizing only the tiny fixture: its purpose is semantic verification, not a realistic performance benchmark.

E. Transactions establish a unit of change#

A transfer of four gold units from Ada to Cy must subtract and add together, or do neither. A transaction makes this a single commit-or-rollback unit. ACID summarizes atomicity, consistency with declared invariants, isolation between concurrent transactions, and durability under the system’s guarantees. “Consistency” does not mean the database knows every unstated business rule. A transaction can atomically commit the wrong calculation if the application specified it incorrectly.

The lab starts BEGIN IMMEDIATE, conditionally subtracts only when the source exists and has enough gold, adds to an existing target, and commits. Failure at either update rolls back the entire transfer. The conditional subtraction is important: a separate unlocked “check balance, then subtract” can race with another withdrawal. Parameters bind values into SQL expressions without concatenating user input into SQL syntax. Table or column names are not interchangeable with bound values; dynamic identifiers need a trusted allowlist or another explicit construction policy.

Transaction scope is the business operation, not one statement at a time.

Isolation behavior varies by database and configuration. Dirty reads expose uncommitted values; lost updates overwrite another change; write skew can violate a cross-row rule under some snapshot-isolation systems when concurrent transactions write different rows after reading a shared condition. Do not assume every anomaly exists in every engine. SQLite ordinarily serializes writers; WAL mode permits readers to retain snapshots while a writer proceeds. A deferred read transaction that later tries to write can encounter a busy/snapshot conflict. The lab deliberately uses an immediate write transaction and explicit SQL transaction control. [SQL][PYSQL]

F. Migrations, backup, and recovery are part of correctness#

A migration changes schema or data from a known version to another. Record the expected starting version, apply compatible changes transactionally where the engine supports them, and test both a fresh database and an upgrade fixture. CREATE TABLE IF NOT EXISTS is convenient for a teaching initializer but is not a general migration strategy: an old table with the wrong columns still exists. J03 adds explicit supported-version checking for its bounded schema.

A backup is useful only when it can be restored and its invariants checked. A live WAL database may have state outside its main file, so blindly copying only that file is not a general consistent-backup method. The tests use SQLite’s backup interface and query the restored copy. This verifies selected logical contents, not every possible power-failure or storage-corruption scenario. Durability also depends on configuration and the storage stack; no classroom test certifies it universally.

Nonrelational storage can suit key-value access, document-shaped aggregates, or specialized scalability requirements. It does not remove the need for schema meaning, validation, identity, consistency, and recovery. Select a storage model from the data and access patterns rather than treating SQL versus non-SQL as a contest with one universal winner.

G. Independent practice#

F foundation C standard course work T transfer S optional stretch L runnable lab or project. Attempt the complete set before opening its solutions.

H. Complete solutions: C11#

Compare the first step where your work differs. A corrected answer is not yet an independent success.

Open every solution

"""C11.05: small SQLite schema, constraints, joins, and an atomic transfer."""
import sqlite3

def connect(path: str = ":memory:") -> sqlite3.Connection:
    db = sqlite3.connect(path, isolation_level=None)
    db.execute("PRAGMA foreign_keys=ON")
    db.executescript("""
        CREATE TABLE IF NOT EXISTS guild(
            id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
        CREATE TABLE IF NOT EXISTS player(
            id INTEGER PRIMARY KEY, name TEXT NOT NULL,
            guild_id INTEGER REFERENCES guild(id),
            gold INTEGER NOT NULL CHECK(typeof(gold)='integer' AND gold >= 0));
        CREATE INDEX IF NOT EXISTS player_guild ON player(guild_id);
        PRAGMA user_version=1;
    """)
    return db

def seed(db: sqlite3.Connection) -> None:
    db.execute("BEGIN")
    try:
        db.executemany("INSERT INTO guild VALUES(?,?)", [(1, "North"), (2, "South")])
        db.executemany("INSERT INTO player VALUES(?,?,?,?)",
                       [(1, "Ada", 1, 12), (2, "Bo", 1, 8), (3, "Cy", 2, 5)])
        db.execute("COMMIT")
    except Exception:
        db.execute("ROLLBACK")
        raise

def guild_totals(db: sqlite3.Connection):
    return db.execute("""
        SELECT g.name, COALESCE(SUM(p.gold),0)
        FROM guild AS g LEFT JOIN player AS p ON p.guild_id=g.id
        GROUP BY g.id, g.name ORDER BY g.name
    """).fetchall()

def transfer(db: sqlite3.Connection, source: int, target: int, amount: int):
    if type(amount) is not int or amount <= 0 or source == target:
        raise ValueError("distinct accounts and positive integer amount required")
    db.execute("BEGIN IMMEDIATE")
    try:
        changed = db.execute(
            "UPDATE player SET gold=gold-? WHERE id=? AND gold>=?",
            (amount, source, amount)).rowcount
        if changed != 1:
            raise ValueError("missing source or insufficient gold")
        if db.execute("UPDATE player SET gold=gold+? WHERE id=?",
                      (amount, target)).rowcount != 1:
            raise ValueError("missing target")
        db.execute("COMMIT")
    except Exception:
        db.execute("ROLLBACK")
        raise

if __name__ == "__main__":
    db = connect()
    try:
        seed(db)
        transfer(db, 1, 3, 4)
        print(guild_totals(db))
    finally:
        db.close()

I. Retention and readiness#

Retrieve invariants from programming and atomic state changes from concurrency. Advance when you can name the transaction boundary from the business rule, distinguish SQL NULL from zero, and demonstrate a failed operation leaves the database unchanged.