learning-it by example

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

1. In SQLite, what must you do before the database refuses a repair row that points at a bike that does not exist?
2. In the SQLite bike shop demo, what happened when a second customer was inserted with Asha's email address?
3. What did the join in the SQLite bike shop demo report for the two customers?
4. How does the text diagram of tables and relationships in the SQLite bike shop demo stay in step with the real tables?
5. Why does the price sit on the repair table and not on the bike table in the bike shop model?

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

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

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

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

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

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos