Pace SQL Project — ACID Transactions on Product / Depot / Stock

Six transactions implemented in Python + PostgreSQL with full ACID guarantees and reactive (cascading) foreign-key constraints.

Tables

Table	Columns	Key
Product	prodid, name, price	prodid
Depot	depid, addr, volume	depid
Stock	prodid, depid, qty	(prodid, depid) — FKs to Product and Depot, both ON DELETE CASCADE / ON UPDATE CASCADE

The six transactions

1. Delete product p1 from Product and Stock.
2. Delete depot d1 from Depot and Stock.
3. Rename product p1 → pp1 in Product and Stock.
4. Rename depot d1 → dd1 in Depot and Stock.
5. Add product (p100, cd, 5) to Product and (p100, d2, 50) to Stock.
6. Add depot (d100, Chicago, 100) to Depot and (p1, d100, 100) to Stock.

Each transaction is wrapped in a BEGIN ... COMMIT / ROLLBACK block — see transactions.py. For 1–4 the code exposes both a reactive version (relying on the cascade clauses) and a manual version (explicit Stock update inside the same transaction).

ACID

• Atomicity — commit() / rollback() in transactions._run.
• Consistency — primary keys, foreign keys, and NOT NULL in schema.sql.
• Isolation — SERIALIZABLE isolation level set in db.py.
• Durability — PostgreSQL WAL.

Running the demo

# 1. Create the database
createdb pace_sql

# 2. Configure credentials
cp .env.example .env   # then edit if needed

# 3. Install dependencies
pip install -r requirements.txt

# 4. Run
python demo.py            # uses reactive (cascade) versions for T1–T4
python demo.py manual     # uses explicit Stock updates for T1–T4

The demo reloads schema.sql before each transaction so it can be run repeatedly and the table state is always shown before and after.

Files

File	Purpose
schema.sql	DDL + seed data + reactive FK constraints
db.py	Connection helper (SERIALIZABLE isolation)
transactions.py	The six transactions, with helper _run
demo.py	Runs all six and prints table state before / after