University Tycoon: a board game modelled in SQLite

The question

University Tycoon is a Monopoly-style board game built from University of Manchester building names — Kilburn, AMBS, MECD, Whitworth Hall — set as the practical assignment for the MSc module Understanding Databases (DATA70141). The requirements note I kept in the project folder condenses the brief to six lines: six players maximum, six unique tokens, four location types, buildings with owners and tuition fees, an audit trail for each turn, and special cards. The assignment text itself is not in the folder — my defence notes cite numbered requirements up to at least ten, rules R0–R8 and a board image — so those six lines are my summary, not the brief.

The design question I set myself was whether the rules of the game could live in the schema rather than in the code above it. Could constraints, foreign keys and triggers keep the game legal on their own? The constraint that forced the issue is recorded in my defence notes: "SQLite doesn't support stored procedures, so all game logic must be implemented via INSERT/UPDATE/DELETE statements in the query files".

That question outlives the toy problem. Deciding what the database guarantees versus what the application is trusted to remember is the same decision on any production system.

The data

There is no observational data here and it would be dishonest to imply otherwise. The inputs are a six-line requirements summary and an initial game state supplied with the assignment.

That fixture is small: 20 board positions across four types (corner, building, hearing, rag), 12 buildings, 8 specials, 6 tokens and 4 players — Gareth on 430 credits, Stewart on 360, Emma on 470, Nadine on 400. Tuition fees take six values (15, 25, 30, 35, 40, 50), each mapping to one colour group and one shape marker. Three rounds of scripted play produce 14 audit rows.

Four players, three rounds and one hand-authored trajectory exercise almost none of the schema's edge cases. The brief is ambiguous in places, so I recorded my reading: on specials, "I interpreted this as referring to the game rules where only one special effect can apply to a player at a given moment", with the collectible-card reading considered and rejected. I also assumed "the provided initial state ... is accurate and complete".

Approach

The core choice was ISA inheritance: LOCATION as a supertype with BUILDING and SPECIAL as subtypes, implemented in SQLite by making each subtype's primary key its own foreign key to LOCATION. The alternative was two nullable foreign keys in the audit trail, one for buildings and one for specials, plus a CHECK that exactly one was non-NULL. I rejected it: a single polymorphic reference to LOCATION records any space without that logic.

I kept TOKEN as a one-column table rather than a CHECK on PLAYER, because a reference table makes the "six tokens exist" requirement visible in the schema and supports set-difference queries for which tokens are free.

The design changed mid-project, and the defence notes preserve the earlier version. They describe colour_group and shape_marker as BUILDING attributes; the submitted schema extracts them into a FEE_GROUPING table commented "added for 3NF", since both depend on the fee level rather than the building. Derived values went the same way: purchase price is not stored, because "purchase_price = 2 × tuition_fee".

Game logic then went into eleven triggers. Seven fire on inserts to AUDIT_LOG, so that writing one audit row moves the player, settles credits both ways, transfers ownership and sets suspension; an eighth writes the resulting balance back into the audit row, and the remaining three police PLAYER and BUILDING directly.

What I found

There is no statistical result to report — this is a schema-design exercise, and no mark is recorded in the source files.

What exists is a working artefact: 8 tables, 11 triggers and 1 view. Running create.sql, populate.sql, view.sql and the twelve turn scripts in order on a clean database reproduces the submitted state exactly — 14 audit rows and a leaderboard reading Emma 455 credits, Gareth 490, Stewart 385, Nadine 355. It is ordered by net worth (credits plus twice the tuition fees of buildings owned), which is why Emma leads on four buildings despite Gareth holding more cash.

The rules-in-schema question got a partial answer: movement, credit transfers, ownership and the six-player maximum are enforced by the database. Several other rules are not.

What this doesn't show

Three gaps, confirmed by testing the submitted schema rather than by reading it:

Bankruptcy is unhandled. credits INTEGER NOT NULL DEFAULT 0 CHECK (credits >= 0) means a turn that would push a player below zero aborts the whole audit insert rather than resolving anything. My own schema comment admits the uncertainty: "not sure how it would work in this game".

The suspension rule is not enforced. The special's description says a suspended player "Must roll a 6 to get out", but audit_exit_suspension clears the flag on any audit row with suspension_triggered = 0 that lands anywhere other than Suspension, with no reference to the roll — even though AUDIT_LOG already stores a roll_number on every row. That rule lives in whoever writes the INSERT — exactly the failure mode the project set out to avoid.

affected_player_id is typed TEXT because it can hold "a player ID, 'all', or NULL". Overloading it means it cannot carry a foreign key, so it accepts an id for a player who does not exist and the credit transfer silently affects nobody.

Beyond those: the LEADERBOARD view relies on an ORDER BY inside a subquery, which SQLite preserved here but which the SQL standard does not guarantee. credits_after_turn is written by a trigger guarded on the value still being 0, indistinguishable from a genuine zero balance. BUILDING.tuition_fee doubles as a foreign key into FEE_GROUPING, so the design breaks if two colour groups ever share a fee. And nothing was tested at scale.

Next I would make the suspension trigger read the roll_number column AUDIT_LOG already stores, and add a property-based test that plays random legal games and asserts total credits are conserved.