Relational Design AI Usage

Name of AI service/solution that was used

Claude Code (Anthropic)

• URL: https://claude.com/claude-code
• Type of service/subscription: Claude subscription, model Claude Opus 4.7 (1M context).

Final result

Diagram

relational_diagram_v4.png was exported by the student in DBeaver from the live project schema, with the tables in the same positions as the entity sets of ERModel_v05.png; see RelationalDesign (section "How to regenerate it") for instructions.

Results in details / description

The AI:

• Consolidated two inconsistent draft schemas (server/db/db.sql and server/db/schema.sql) into a single schema_creation.sql.
• Corrected foreign-key errors in the original (the crypto_id column in holdings, orders, and transactions had been pointed at both users(id) and crypto(id); the AI split it into separate user_id and crypto_id columns per ep-diagram.md).
• Added two convenience views, v_latest_prices and v_portfolio, to keep the Go CLI simple.
• Produced a sample-data script data_load.sql that TRUNCATEs and re-inserts deterministic rows, so the "must work on empty DB and on a DB that already has data" requirement from P2 is met.
• Documented normalisation up to 3NF and the intentional denormalisation of holdings.avg_price.

Summary of AI involvement

	Session 1 — 2026-04-21	Session 2 — 2026-08-06/07
What I brought	My own draft SQL (db.sql, schema.sql) and the model in ep-diagram.md	The schema as it stood after session 1
What the AI did	Reviewed my SQL, found the foreign-key errors, consolidated two inconsistent drafts into one script	Reviewed the schema again; one constraint change, plus documentation of the transformation
What I decided	Which corrections to adopt, to keep both balance columns, to drop the secret-question fields	To make avg_price NOT NULL rather than handle nulls in application code

The relational model in this phase is a transformation of my ER model, and the foreign-key errors the AI found in session 1 were errors in my draft SQL — that review is the single most useful thing the AI did on this phase.

Entire AI usage log

See ERModelAIUsage — the full transcript of the 2026-04-21 conversation covers both P1 and P2 work. The specific prompts that drove the relational-design output were the same "make it work and make it fill in or to follow all of the needed instructions" instruction and the student's subsequent "do everything that you need to do".

Student action required: append any future consultations where you asked the AI to refine the schema, tune constraints, or write additional queries.

Session 2 — 2026-08-06 / 2026-08-07

Prompts are logged in full in ERModelAIUsage (section "Session 2 — 2026-08-06 / 2026-08-07"); the one that drove this phase was "Also fix some database things or golang things if you think we can do it better". Changes to the P2 artefacts:

• holdings.avg_price changed from nullable to NOT NULL DEFAULT 0 CHECK (avg_price >= 0). Reason: it feeds the P/L arithmetic in v_portfolio, and SQL arithmetic involving NULL produces NULL, so a nullable average would have silently blanked the unrealised-P/L column of a real position. A related crash path in the Go code (scanning a NULL average into a non-nullable float64, which was reported to the user as "Insufficient holding") was fixed at the same time.
• RelationalDesign gained an explicit account of the partial transformation: which ER construct each foreign key comes from, that M:N relationships with attributes become tables whose foreign-key pair is a UNIQUE constraint, and that total participation becomes NOT NULL — which is why transactions.related_order is the one nullable foreign key.
• The candidate keys of holdings and watchlist_items are now documented as the relationship keys {user_id, crypto_id} and {watchlist_id, crypto_id}.

Both scripts were re-run end to end against PostgreSQL 16 after these changes.

Still outstanding: relational_schema.jpg must be exported from DBeaver against the faculty database. No AI involvement is possible there — it needs a live connection to your assigned database.

Session 3 — 2026-09-16

Driven by the same design review logged in full in ERModelAIUsage (section "Session 3 — 2026-09-16"): a sell order had nothing to check holdings.quantity against except itself, so nothing stopped two sell orders from being granted the same units.

Changes to the P2 artefacts:

• holdings gained reserved_quantity numeric(20,4) NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity) in schema_creation.sql.
• v_portfolio gained reserved_quantity and the derived available_quantity = quantity - reserved_quantity.
• RelationalDesign gained a "Reservation and the order lifecycle" section explaining why the check is enforced at the database level rather than trusted to application code, and why it does not conflict with the existing SELECT … FOR UPDATE locking on the sell path.
• data_load.sql needed no change — reserved_quantity defaults to 0, which is correct for every seeded holding.

Re-run end to end against the live database on localhost:5433 (-init then -load-data), and against a manually seeded 2 BTC holding to reproduce the exact scenario that motivated the change — see UseCase0005Implementation for the transcript.

What I decided: to add the CHECK constraint rather than rely on trade.go alone to keep the reservation consistent — the same reasoning already applied to avg_price NOT NULL in session 2.

Session 4 — 2026-09-29 (Claude Opus 5.5): correction after review

Prompt (student, excerpt): I pasted the reviewer's comments on P2:

the contains relationship from the model is implemented differently in the database the holds relationship from the model is implemented differently in the database there may be other inconsistencies that are difficult to spot; the Phase 2 diagram should have been created using DBeaver with a layout visually similar to the ERD

I suggest correcting only the Phase 1 model so that it matches Phase 2 [...] For Phase 2, simply update the image according to the requirements.

Response (AI, summarised):

• Explained the mismatch. In the model, Holds and Contains were M:N relationships, whose table key would be the pair {user_id, crypto_id} / {watchlist_id, crypto_id}. But holdings and watchlist_items have their own id primary key, which is how an entity set is implemented. Following the reviewer's advice, P1 was corrected (v05, entity sets Holdings and WatchlistItems), and the database was not changed.
• Found the other inconsistencies between this page and the live schema. The page was missing users.reserved_balance, orders.filled_quantity, the status partially_filled, market_trades.buy_order_id / sell_order_id and the whole order_events table. The "10 tables", "5 indexes" and "the one nullable foreign key" counts were also out of date (really 11 tables, 8 indexes in schema_creation.sql, and 3 nullable foreign keys).
• Rewrote RelationalDesign. Each relation is labelled with its entity set and each foreign key with its relationship, and the transformation is a table of all 15 relationships → 15 foreign keys, with NOT NULL following participation.
• Wrote export instructions with a table grid that mirrors ERModel_v05.png.

What I decided: to correct P1 instead of the database, as the reviewer suggested. I exported relational_diagram_v4.png from DBeaver myself, with the tables arranged like the ER diagram.