Worksheet

A11.5 SQL: changing data and referential integrity

Databases and big data · A level · OCR H446 1.3.2, AQA 7517 4.10.2, Eduqas A500QS 2.5 · about 55 min

What this lesson is about

INSERT, UPDATE and DELETE at A level, and how a database refuses orphan records. The robot logs its own moves.

• INSERT
• UPDATE
• DELETE
• Referential integrity
• The robot logs its own runs

Questions 5 marks in all

1. [1 mark]What does referential integrity mean?

   1. AEvery foreign key value matches a primary key value in the table it refers to
   2. BEvery table has a primary key
   3. CNo two records are the same
   4. DEvery field has a value

2. [1 mark]Runs refer to robots with ON DELETE CASCADE. What happens when robot 2 is deleted?

   1. ARobot 2's runs are deleted too
   2. BThe delete is refused
   3. CRobot 2's runs keep robot_id 2
   4. DRobot 2's runs get robot_id null

3. [1 mark]What does this print?

   import sqlite3
   db = sqlite3.connect(":memory:")
   db.execute("PRAGMA foreign_keys = ON")
   db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY)")
   db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, robot_id INTEGER REFERENCES robots(robot_id))")
   db.execute("INSERT INTO robots VALUES (1)")
   db.execute("INSERT INTO runs (robot_id) VALUES (1)")
   try:
       db.execute("INSERT INTO runs (robot_id) VALUES (5)")
       print("added")
   except sqlite3.IntegrityError:
       print("refused")
   print(db.execute("SELECT COUNT(*) FROM runs").fetchone()[0])

4. [1 mark]What does this print?

   import sqlite3
   db = sqlite3.connect(":memory:")
   db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, robot_id INTEGER, cm REAL)")
   db.executemany("INSERT INTO runs VALUES (?, ?, ?)", [(1, 1, 40.0), (2, 2, 38.0), (3, 1, 50.0)])
   db.execute("UPDATE runs SET cm = cm + 5 WHERE robot_id = 1")
   db.execute("DELETE FROM runs WHERE cm < 45")
   print(db.execute("SELECT run_id, cm FROM runs").fetchall())

5. [1 mark]Why should a program insert values with ? placeholders rather than joining them into the SQL string?

   1. AThe values are passed separately, so typed text cannot change the SQL (SQL injection)
   2. BPlaceholders make the query run without a database
   3. CPlaceholders are required for numbers
   4. DJoining strings is not allowed in Python

The task: log the legs, keep the links

The starter builds two tables, robots and runs. runs.robot_id refers to robots(robot_id) with ON DELETE CASCADE. Robot 1 is Ada (the robot on the mat) and robot 2 is Bolt, who already has run 1. legs is a list of (move, cm), where move is "forward", "right" or "backward" and cm is a whole number of centimetres. 1. Switch foreign key checking on. 2. For each leg in order, drive it at speed 40, measure how far the robot really moved from position() before and after (the straight-line distance, rounded to a whole number), and INSERT it as a run for robot 1 with a field list and ? placeholders, letting the database number it. 3. Try to insert a run for robot 9. When the database refuses with sqlite3.IntegrityError, print refused: robot 9 does not exist. 4. UPDATE the runs whose move is right so their move is strafe. 5. DELETE Bolt from robots. 6. With a query that joins runs to robots, print every run in run order as run <run_id> <name> <move> <cm>, then print runs: <n>, the number of records left in runs. Five lines in all, starting with the refusal.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
db.execute("""CREATE TABLE runs (run_id INTEGER PRIMARY KEY,
              robot_id INTEGER NOT NULL REFERENCES robots(robot_id) ON DELETE CASCADE,
              move TEXT, cm INTEGER)""")
db.executemany("INSERT INTO robots VALUES (?, ?)", [(1, "Ada"), (2, "Bolt")])
db.execute("INSERT INTO runs VALUES (1, 2, 'forward', 30)")
db.commit()

legs = [("forward", 20), ("right", 15), ("backward", 10)]

Plan your program here, then type it in and press Run.

Do it on the robot
www.bugbotlab.com/learn/a11-5-changing-data-and-referential-integrity/
The simulator checks it and tells you when it passes. Nothing to install, no account.

Challenges

1. Change the runs table so deleting a robot sets its runs' robot_id to null instead. What must you remove from the table definition first?
2. Using club.py, give every robot in Hawks 1 cm extra on its wall runs with one UPDATE and a nested SELECT.
3. Why do most designers choose restrict rather than cascade for a table of exam results?