learning-it by example

#252 Data modelling 6.9 min

Normalisation 1NF to 3NF on a Messy Spreadsheet

Three ordinary changes broke a flat table of 5 rows; four tables took all three.

1. In a flat SQLite orders table where every order row repeats the customer's city, what happened when Asha moved to York and the update touched only her first order?
2. In a flat SQLite sales table keyed by order and product, why was a new product (a clip at 1 pound) rejected before anyone had ordered it?
3. In a flat SQLite sales table of three orders and two customers, how many customers were left after order A2, Ben's only order, was deleted?
4. In a SQLite order design split into customer, product, orders and order_line tables, which table is keyed by email and holds the name and city?
5. In a four-table order design (customer, product, orders, order_line) where the price lives only on the product table, changing a price would also change the totals of old orders. What is the advice to avoid this?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Predict the anomalies

    Copy the code folder first. Read flat.py and normal.py, then write down what you expect python run.py flat and python run.py normal to print, especially the cities, the clip insert and the customers left. Run both and compare. The flat run should show 2 cities, a rejected clip and 1 of 2 customers.

    A hint

    Check which rows the update in flat_anomalies touches.

  2. Fix the update by touching every row

    In flat.py, change the flat update so that it sets the city for all of Asha's rows, by using WHERE name = 'Asha' instead of WHERE order_id = 'A1'. Run python run.py flat. The update line should now report 1 cities. Think about why this fix is fragile, and what the customer table does instead.

    A hint

    On the flat table you have to remember to change every row. In the four tables there is one row to change.

  3. Add a product to an order

    In flat.py, add a third item to order A3 in the SHEET, so the cell reads: pen x1 at 2; ink x2 at 5; cap x4 at 1. Predict the numbers, then run python run.py flat and python run.py normal. You should see 6 rows in first normal form, 4 products, 6 order lines, and order A3 totalling 16 with match the sheet: True.

    A hint

    Each item in the cell becomes one row of the flat table.

  4. Remove the cascade

    In normal.py, delete the words ON DELETE CASCADE from the order_line table. Predict what happens when the run deletes order A2, then run python run.py normal. The script should stop with an error, FOREIGN KEY constraint failed, because the order's lines still point at it. Put the words back afterwards.

    A hint

    Foreign keys are switched on in normal_tables, so SQLite refuses to leave the lines pointing at a missing order.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

A made-up three-order sheet in SQLite; it stops at third normal form and does not measure query speed.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos