Relational databases

Tables, records, fields, primary and foreign keys, and avoiding redundancy.

F7.2Files and databasesGCSE15 min

A CSV file is fine for one small list. A school's records, a shop's orders, or a year of robot runs from a whole class need more: many kinds of data, linked together, searched quickly, and kept consistent. That is what a database is for. This lesson is about how a relational database organises data into linked tables, using the class's robot runs as the example.

Tables, records and fields

A database stores data in tables. Each row is a record, one item, such as one run. Each column is a field, one piece of information about every item, such as the distance. Every field has a data type.

The trouble with one big table

Look at Ada's colour: it is typed into every one of her runs. Storing the same data more than once is data redundancy. It wastes space, but worse, it invites data inconsistency: change Ada's colour to blue in one record and forget another, and the database now disagrees with itself about what colour Ada is.

Splitting into linked tables

The fix is to store each fact once, in its own table, and link the tables:

robots

runs

Now Ada's colour is stored in one place. Change it there, and every run is up to date.

Keys

• A primary key is a field that is unique for every record in its table, so each record can be found exactly. robot_id is the primary key of robots; run_id is the primary key of runs. Names make poor keys: two robots could both be called Ada.
• A foreign key is a field in one table that holds the primary key of a record in another table. robot_id in runs is a foreign key: it links each run to the robot that did it.

That link is what makes the database relational: tables related to each other through keys.

A database in Python

Python comes with a database engine, sqlite3. The file below builds the two tables on this page, so every cell can use them:

# logbook.py: the class's robot runs as a relational database
import sqlite3

ROBOTS = [(1, "Ada", "green"), (2, "Bolt", "red"), (3, "Cog", "blue")]
RUNS = [(1, 1, "wall", 42.0, 3.1), (2, 1, "square", 80.0, 9.4), (3, 2, "wall", 38.0, 2.7),
        (4, 1, "wall", 45.5, 3.0), (5, 3, "square", 76.5, 8.8), (6, 2, "square", 81.0, 10.2)]

def open_db():
    """A fresh database with the robots and runs tables."""
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT, colour TEXT)")
    db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, robot_id INTEGER REFERENCES robots(robot_id), "
               "task TEXT, cm REAL, seconds REAL)")
    db.executemany("INSERT INTO robots VALUES (?, ?, ?)", ROBOTS)
    db.executemany("INSERT INTO runs VALUES (?, ?, ?, ?, ?)", RUNS)
    return db

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

import logbook

db = logbook.open_db()
robots = db.execute("SELECT * FROM robots").fetchall()
runs = db.execute("SELECT * FROM runs").fetchall()
print("robots:", robots)
for run in runs:
    print(run)

Run this in the simulator

Each record comes back as a tuple of its fields, in order. The line with SELECT is SQL, the language for asking databases questions, and the next lesson is all about it. For now, use it as "give me every record in this table".

Following a foreign key

To say which robot did each run, look up the foreign key in the other table:

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

import logbook

db = logbook.open_db()
names = {}
for robot_id, name, colour in db.execute("SELECT * FROM robots"):
    names[robot_id] = name

for run_id, robot_id, task, cm, seconds in db.execute("SELECT * FROM runs"):
    print("run", run_id, "by", names[robot_id], ":", task, cm, "cm")

Run this in the simulator

The runs table only knows robot numbers. The link through robot_id turns them back into names. Next lesson, SQL does this join for you in one query.

Task: runs by robot

Using logbook.open_db(), print one line for every run, in run order, in the form run 1 by Ada: wall 42.0 cm. Look each robot's name up through the robot_id foreign key; do not type the names into your program.

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

import logbook

db = logbook.open_db()

Challenges

1. Add a tasks table with a primary key and a description of each task, and make runs use a foreign key to it.
2. Which field in runs could never be a primary key, and why?
3. Count how many runs each robot did, using the two tables and a dictionary.