Project: the run database

Design, normalise, build and fill a database of robot runs, log live runs in transactions, report with one joined query and export the report as JSON.

A11.10Databases and big dataA level75 min

This project puts the whole module into one program. You design a database for the club's competition, build it in third normal form with its keys and referential integrity, let BugBot add its own runs inside transactions, answer the organisers' question with one joined query, and export the answer in a format their system can read.

The brief

The club's competition has two challenges: the sprint, 30 cm forwards, and the reverse, 15 cm backwards. A run's error is how far its distance was from the challenge's target, in either direction. Bolt and Cog have already made their runs. BugBot, which is Ada, robot 1 of the Hawks, now makes one run at each challenge. Each run is measured and logged in its own transaction. The organisers want, for each team, the number of runs and the mean error, printed and saved as report.json.

Design

The data lists in the starter hold the facts. Working from the brief, the entities and their relationships are:

• Team (team_id, team_name)
• Robot (robot_id, name, team_id*)
• Challenge (code, name, target_cm)
• Run (run_id, robot_id*, code*, cm)

Check it is in 3NF: every table has a primary key and atomic values (1NF); every key is a single attribute, so there are no partial dependencies (2NF); and no non-key attribute depends on another, since the team's name lives in Team, not in Robot, and the target lives in Challenge, not in Run (3NF). A run's error is not stored at all: it depends on the run's distance and the challenge's target, so it is worked out in the query, and can never disagree with them.

Plan

Step 1: tables that refer to each other

Tables must be created parents first, and foreign key checking switched on before anything else happens on the connection:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.execute("CREATE TABLE teams (team_id INTEGER PRIMARY KEY, team_name TEXT NOT NULL)")
db.execute("""CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT NOT NULL,
              team_id INTEGER NOT NULL REFERENCES teams(team_id))""")
with db:
    db.executemany("INSERT INTO teams VALUES (?, ?)", [(1, "Hawks"), (2, "Owls")])
    db.executemany("INSERT INTO robots VALUES (?, ?, ?)", [(1, "Ada", 1), (2, "Bolt", 1), (3, "Cog", 2)])

try:
    with db:
        db.execute("INSERT INTO robots VALUES (4, 'Dot', 7)")
except sqlite3.IntegrityError as e:
    print("refused:", e)
print(db.execute("SELECT * FROM robots").fetchall())

Run this in the simulator

Step 2: measure a run

The robot's commands say how far it should go; position() says how far it did go. Measure the straight-line distance between the positions before and after:

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

def measured(drive, cm):
    """Drive cm with the given command and return the distance really covered, to the nearest cm."""
    x0, y0 = position()
    drive(50, distance=cm)
    x1, y1 = position()
    return round(((x1 - x0) ** 2 + (y1 - y0) ** 2) ** 0.5)

print("forward 20:", measured(forward, 20))
print("backward 10:", measured(backward, 10))

Run this in the simulator

Step 3: an error in SQL

ABS gives a number's size without its sign, so a run 3 cm short and a run 3 cm long both have an error of 3. An aggregate can work on an expression, not just a field:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE attempts (cm INTEGER, target_cm INTEGER)")
db.executemany("INSERT INTO attempts VALUES (?, ?)", [(27, 30), (33, 30), (30, 30)])
for row in db.execute("SELECT cm, target_cm, ABS(cm - target_cm) FROM attempts"):
    print(*row)
print("mean error:", db.execute("SELECT ROUND(AVG(ABS(cm - target_cm)), 1) FROM attempts").fetchone()[0])

Run this in the simulator

In the full report the distance is in runs and the target is in challenges, so the query needs the join before it can work out the error, and the team's name is two joins away from runs.

Task: the run database

The starter gives the data as lists of tuples: teams holds (team_id, team_name), robots holds (robot_id, name, team_id), challenges holds (code, name, target_cm) and earlier_runs holds (robot_id, code, cm). Ids and distances are whole numbers; names and codes are text.

1. Create the four tables of the design, with a primary key on each and the three foreign keys declared with REFERENCES, and switch on foreign key checking.
2. Insert the data lists. Let the database number the runs.
3. For each challenge, in the order of challenges, the robot (Ada, robot 1) makes one run at speed 50: S drives forward the target distance, R drives backward the target distance. Measure the straight-line distance really covered, rounded to a whole number, and insert the run inside its own transaction (with db:).
4. With one query that joins runs, robots, teams and challenges, groups by team, and uses COUNT and AVG, print one line per team in name order: <team_name>: <n> runs, mean error <error> cm, where the error is the mean of the absolute differences between each run's cm and its challenge's target_cm, rounded to 1 decimal place. For example Owls: 2 runs, mean error 2.5 cm.
5. Save the report to report.json with json.dump, as a list with one object per team, each with the keys team (text), runs (a whole number) and mean_error (a number). Then print saved report.json.

Three lines in all.

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

import sqlite3
import json

teams = [(1, "Hawks"), (2, "Owls")]
robots = [(1, "Ada", 1), (2, "Bolt", 1), (3, "Cog", 2)]
challenges = [("S", "sprint", 30), ("R", "reverse", 15)]
earlier_runs = [(2, "S", 27), (2, "R", 16), (3, "S", 33), (3, "R", 13)]

Challenges

1. Add each robot's best run at each challenge to the report, with a nested SELECT or a second GROUP BY.
2. A second robot logs runs to the same database at the same time. Which records could suffer a lost update, and which concurrency method would you choose for this system?
3. The organisers' system only accepts XML. Write the report as XML instead, and explain one advantage and one disadvantage of the change.