Pythonnormalization_demo.py
Copy
1
import sqlite3
2
 
3
conn = sqlite3.connect(":memory:")
4
conn.row_factory = sqlite3.Row
5
 
6
# ── 0. Denormalized (UNF) ─────────────────────────────────────────
7
# OrderID | CustomerName | CustomerCity | Products (multi-value) | Supplier
8
# Problems: multi-value column, transitive deps
9
conn.executescript("""
10
-- Final 3NF schema:
11
-- Customers(customer_id PK, name, city)
12
-- Suppliers(supplier_id PK, name)
13
-- Products(product_id PK, name, supplier_id FK)
14
-- Orders(order_id PK, customer_id FK, order_date)
15
-- OrderItems(order_id FK, product_id FK, quantity, unit_price)
16
 
17
CREATE TABLE customers (
18
    customer_id INTEGER PRIMARY KEY,
19
    name        TEXT NOT NULL,
20
    city        TEXT NOT NULL
21
);
22
 
23
CREATE TABLE suppliers (
24
    supplier_id INTEGER PRIMARY KEY,
25
    name        TEXT NOT NULL
26
);
27
 
28
CREATE TABLE products (
29
    product_id  INTEGER PRIMARY KEY,
30
    name        TEXT NOT NULL,
31
    supplier_id INTEGER NOT NULL REFERENCES suppliers(supplier_id)
32
);
33
 
34
CREATE TABLE orders (
35
    order_id    INTEGER PRIMARY KEY,
36
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
37
    order_date  TEXT NOT NULL
38
);
39
 
40
CREATE TABLE order_items (
41
    order_id   INTEGER NOT NULL REFERENCES orders(order_id),
42
    product_id INTEGER NOT NULL REFERENCES products(product_id),
43
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
44
    unit_price REAL    NOT NULL CHECK (unit_price >= 0),
45
    PRIMARY KEY (order_id, product_id)
46
);
47
""")
48
 
49
# Seed data
50
conn.executescript("""
51
INSERT INTO customers VALUES (1, 'Alice', 'London'), (2, 'Bob', 'Paris');
52
INSERT INTO suppliers VALUES (1, 'Acme Corp'), (2, 'TechMakers');
53
INSERT INTO products VALUES (1, 'Widget', 1), (2, 'Gadget', 2), (3, 'Doohickey', 1);
54
INSERT INTO orders VALUES (1, 1, '2024-01-10'), (2, 2, '2024-01-11');
55
INSERT INTO order_items VALUES (1, 1, 2, 9.99), (1, 2, 1, 29.99), (2, 3, 5, 4.99);
56
""")
57
 
58
# Query proving no anomalies
59
rows = conn.execute("""
60
    SELECT o.order_id, c.name AS customer, c.city,
61
           p.name AS product, s.name AS supplier,
62
           oi.quantity, oi.unit_price,
63
           ROUND(oi.quantity * oi.unit_price, 2) AS line_total
64
    FROM orders o
65
    JOIN customers c ON c.customer_id = o.customer_id
66
    JOIN order_items oi ON oi.order_id = o.order_id
67
    JOIN products p ON p.product_id = oi.product_id
68
    JOIN suppliers s ON s.supplier_id = p.supplier_id
69
    ORDER BY o.order_id, p.name
70
""").fetchall()
71
 
72
for r in rows:
73
    print(dict(r))
74
 
75
conn.close()
76