Worksheet

F7.2 Relational databases

Files and databases · GCSE · OCR J277 2.2.3, AQA 8525 3.7.1 · about 15 min

What this lesson is about

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

• Tables, records and fields
• The trouble with one big table
• Splitting into linked tables
• Keys
• A database in Python
• Following a foreign key

Questions 6 marks in all

1. [1 mark]In a database table, what is a record?

   1. AOne row: all the data about one item
   2. BOne column
   3. CThe whole table
   4. DThe primary key

2. [1 mark]What is a primary key?

   1. AA field whose value is unique for every record in the table
   2. BThe first field in any table
   3. CA password for the database
   4. DA field that links to another table

3. [1 mark]In the runs table, robot_id holds the primary key of a record in the robots table. What is robot_id in runs?

   1. AA foreign key
   2. BA primary key
   3. CA record
   4. DA table

4. [1 mark]Ada's colour is typed into every one of her runs. What is this called?

   1. AData redundancy
   2. BData validation
   3. CA primary key
   4. DNormal data

5. [1 mark]Why is storing Ada's colour once, in a robots table, better?

   1. AIt cannot become inconsistent: changing it once changes it everywhere
   2. BIt makes the database slower
   3. CIt removes the need for keys
   4. DIt hides the colour

6. [1 mark]Why would a robot's name make a poor primary key?

   1. ATwo robots could have the same name
   2. BNames are too long
   3. CNames cannot be stored in databases
   4. DNames are numbers

The 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()

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

Do it on the robot
www.bugbotlab.com/learn/f7-2-relational-databases/
The simulator checks it and tells you when it passes. Nothing to install, no account.

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.