Python · Course navigation

Normalization and update anomalies

Split a redundant table so each fact is stored once and updates cannot contradict themselves.

On this page

Before you start

Joins and keys.

Split a redundant table so each fact is stored once and updates cannot contradict themselves.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

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.

When a table mixes facts about different things, three anomalies appear. An update anomaly: changing one customer's city requires changing many rows, and missing one leaves contradictory data. An insertion anomaly: you cannot record a customer who has not ordered yet. A deletion anomaly: deleting a customer's last order also loses the customer's city.

The normal forms formalize the repair. First normal form requires atomic values and no repeating groups. Second normal form removes dependencies on part of a composite key, and third normal form removes dependencies of non-key columns on other non-key columns. Boyce–Codd normal form is a stricter version of third. The example moves city into a customer table; one update then changes it everywhere. Normalization is not always the goal: reporting systems sometimes denormalize on purpose to avoid joins.

Code example

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()
)

Output

[('Cambridge',), ('London',)]
[('Cambridge',)]

Run code Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1import sqlite3

3db = sqlite3.connect(":memory:")

4db.execute("CREATE TABLE flat (order_id INTEGER, customer TEXT, city TEXT, item TEXT)")

5db.executemany(

6 "INSERT INTO flat VALUES (?, ?, ?, ?)",

7 [(1, "Ada", "London", "pen"), (2, "Ada", "London", "ink"), (3, "Bo", "Paris", "pad")],

8)

9db.execute("UPDATE flat SET city = 'Cambridge' WHERE order_id = 1")

10print(db.execute("SELECT DISTINCT city FROM flat WHERE customer = 'Ada' ORDER BY city").fetchall())

12db.executescript(

13 """

14 CREATE TABLE customer (name TEXT PRIMARY KEY, city TEXT NOT NULL);

15 CREATE TABLE purchase (

16 order_id INTEGER PRIMARY KEY,

17 customer TEXT NOT NULL REFERENCES customer(name),

18 item TEXT NOT NULL

19 );

20 INSERT INTO customer VALUES ('Ada', 'London'), ('Bo', 'Paris');

21 INSERT INTO purchase VALUES (1, 'Ada', 'pen'), (2, 'Ada', 'ink'), (3, 'Bo', 'pad');

22 """

23)

24db.execute("UPDATE customer SET city = 'Cambridge' WHERE name = 'Ada'")

25print(

26 db.execute(

27 "SELECT DISTINCT c.city FROM purchase p "

28 "JOIN customer c ON c.name = p.customer WHERE p.customer = 'Ada'"

29 ).fetchall()

30)

Follow the example

Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.

View the complete dry-run table

Prepared trace of the published program. Some rows group repeated iterations.
What happens	State after step	Output so far
The city is stored on every order row, so this update changes only one of Ada's two rows: an update anomaly.	`Ada: Cambridge (order 1), London (order 2)`	—
Ada now appears to live in two cities: the data contradicts itself.	`Ada: Cambridge (order 1), London (order 2)`	[('Cambridge',), ('London',)]
After normalization the city is stored once, in customer, so one update is enough.	`customer Ada: Cambridge`	[('Cambridge',), ('London',)]
Every order for Ada now joins to the same single city.	`customer Ada: Cambridge`	[('Cambridge',), ('London',)] [('Cambridge',)]

Behind the scenes

How Python processes this code

Python runs this source through its interpreter. It creates objects for values and resolves names as each statement runs.

Cost and common mistakes

Normalization costs extra joins on reads (about O(log n) per lookup with an index) and saves storage and update work, which becomes one row per fact.

Normalization is driven by real dependencies in the data, not by splitting tables mechanically. Check what actually determines what.
Over-normalizing can force many joins for simple reads. Denormalization is a deliberate, measured trade-off, not an error.
Using a name as a primary key is fragile because names change and repeat. The example keeps it short; real designs usually use surrogate keys.

Try it yourself

Which dependency does the flat table contain that makes it violate third normal form?

Reveal answer and reasoning

customer determines city, but customer is not a key of the flat table, so city depends on a non-key column. That is a transitive dependency on the key order_id.