Normalization and update anomalies

Normalization is the process of organizing tables so that each fact is stored in one place. It is guided by functional dependencies: a column or set of columns A functionally determines B when every value of A is associated with exactly one value of B. Here customer determines city.

Python

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE flat (order_id INTEGER, customer TEXT, city TEXT, item TEXT)")
db.executemany(
    "INSERT INTO flat VALUES (?, ?, ?, ?)",
    [(1, "Ada", "London", "pen"), (2, "Ada", "London", "ink"), (3, "Bo", "Paris", "pad")],
)
db.execute("UPDATE flat SET city = 'Cambridge' WHERE order_id = 1")
print(db.execute("SELECT DISTINCT city FROM flat WHERE customer = 'Ada' ORDER BY city").fetchall())

db.executescript(
    """
    CREATE TABLE customer (name TEXT PRIMARY KEY, city TEXT NOT NULL);
    CREATE TABLE purchase (
        order_id INTEGER PRIMARY KEY,
        customer TEXT NOT NULL REFERENCES customer(name),
        item TEXT NOT NULL
    );
    INSERT INTO customer VALUES ('Ada', 'London'), ('Bo', 'Paris');
    INSERT INTO purchase VALUES (1, 'Ada', 'pen'), (2, 'Ada', 'ink'), (3, 'Bo', 'pad');
    """
)
db.execute("UPDATE customer SET city = 'Cambridge' WHERE name = 'Ada'")
print(
    db.execute(
        "SELECT DISTINCT c.city FROM purchase p "
        "JOIN customer c ON c.name = p.customer WHERE p.customer = 'Ada'"
    ).fetchall()
)

Read the full lesson