S035 · The Relational Model — Tables, Keys, Normalisation

"""rel_lab.py — feel the relational model in your fingers."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys = ON")     # SQLite quirk — off by default
cur = conn.cursor()
 
# ---------- 1. Define a normalised schema with real constraints ----------
schema = """
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL UNIQUE
);
 
CREATE TABLE products (
    product_id  INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL CHECK (price_cents > 0)
);
 
CREATE TABLE orders (
    order_id           INTEGER PRIMARY KEY,
    customer_id        INTEGER NOT NULL REFERENCES customers(customer_id),
    product_id         INTEGER NOT NULL REFERENCES products(product_id),
    qty                INTEGER NOT NULL CHECK (qty > 0),
    unit_price_cents   INTEGER NOT NULL,       -- snapshot at order time
    order_date         TEXT NOT NULL
);
 
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_product  ON orders(product_id);
"""
cur.executescript(schema)
 
# ---------- 2. Seed data ----------
cur.executemany("INSERT INTO customers VALUES (?,?,?)", [
    (1, "Alice", "a@x.com"),
    (2, "Bob",   "b@x.com"),
])
cur.executemany("INSERT INTO products VALUES (?,?,?)", [
    (10, "T-shirt", 50000),
    (20, "Mug",     25000),
    (30, "Cap",     30000),
])
cur.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?)", [
    (1, 1, 10, 2, 50000, "2026-01-15"),
    (2, 1, 20, 1, 25000, "2026-01-20"),
    (3, 2, 10, 1, 50000, "2026-01-22"),
    (4, 1, 30, 1, 30000, "2026-02-05"),
])
 
# ---------- 3. Show Alice's orders — email lives in ONE place ----------
print("Alice's orders (single-source email):")
for row in cur.execute("""
    SELECT o.order_id, c.email, p.name, o.qty, o.unit_price_cents, o.order_date
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
    WHERE c.name = 'Alice'
    ORDER BY o.order_id;
"""):
    print(" ", row)
 
# ---------- 4. Update Alice's email ONE row; all joins reflect it ----------
cur.execute("UPDATE customers SET email = 'alice@new.com' WHERE customer_id = 1")
 
# ---------- 5. Change product price; historical orders KEEP their snapshot ----------
cur.execute("UPDATE products SET price_cents = 60000 WHERE product_id = 10")
 
print("\nAfter email + price change:")
for row in cur.execute("""
    SELECT o.order_id, c.email, p.name AS product,
           p.price_cents AS current_price,
           o.unit_price_cents AS paid_price
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
    WHERE c.name = 'Alice'
    ORDER BY o.order_id;
"""):
    print(" ", row)
 
# ---------- 6. Referential integrity: try to insert a bad order ----------
print("\nAttempt: insert order for non-existent customer_id=999")
try:
    cur.execute("INSERT INTO orders VALUES (99, 999, 10, 1, 50000, '2026-03-01')")
except sqlite3.IntegrityError as e:
    print("  ✓ blocked by FK:", e)
 
# ---------- 7. UNIQUE constraint: duplicate email rejected ----------
print("\nAttempt: insert duplicate email")
try:
    cur.execute("INSERT INTO customers VALUES (3, 'Carol', 'alice@new.com')")
except sqlite3.IntegrityError as e:
    print("  ✓ blocked by UNIQUE:", e)
 
# ---------- 8. CHECK constraint: negative qty rejected ----------
print("\nAttempt: insert order with qty = -5")
try:
    cur.execute("INSERT INTO orders VALUES (100, 1, 10, -5, 50000, '2026-03-01')")
except sqlite3.IntegrityError as e:
    print("  ✓ blocked by CHECK:", e)
 
# ---------- 9. Aggregate — revenue per customer ----------
print("\nRevenue per customer:")
for row in cur.execute("""
    SELECT c.name,
           SUM(o.qty * o.unit_price_cents) / 100.0 AS revenue_rupees
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
    ORDER BY revenue_rupees DESC;
"""):
    print(" ", row)
 
conn.close()