#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.
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.
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.
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.
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.
