#250 Data modelling 6.6 min
Data Modelling Basics: Entities, Keys, Relationships
With foreign key checks off, SQLite accepted a repair for bike 99, which does not exist.
Predict both commands
Copy the code folder first. Read schema.py and run.py, then write down what you expect python run.py diagram and python run.py keys to print, including the totals for Asha and Ben. Run both and compare. Asha should show 3 jobs and 125 pounds, and Ben 1 job and 15 pounds.
A hint
Add up the repair prices for the bikes owned by each customer.
Change a price
In schema.py, change the price of the service repair from 60 to 80. Predict Asha's new total, then run python run.py keys. You should see Asha jobs 3, total 145 pounds, and Ben unchanged.
A hint
The service repair is on bike 2, which belongs to Asha.
Make the orphan repair valid
In run.py, change the orphan repair so it points at bike 3 instead of bike 99 (the VALUES line starts with 5, 99, 1). Predict what happens with checks off and checks on, then run python run.py keys. Both lines should now say accepted, because the bike exists. The printed labels still say bike 99 because they are only text, so edit them if you like.
A hint
A foreign key is only a problem when the row it points at is missing.
Add a part table
In schema.py, add a table called part to the DDL text, after the repair table, with part_id INTEGER PRIMARY KEY, repair_id INTEGER NOT NULL REFERENCES repair(repair_id) and name TEXT NOT NULL. Run python run.py diagram. You should see a part line with 0 rows, and a new line saying repair 1 ---< many part via part.repair_id.
A hint
Copy the layout of the repair table and keep the semicolon after the closing bracket.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
A made-up four-table bike shop in SQLite; it does not cover many-to-many relationships, deletes or a graphical diagram.
