Answers

A11.2 Relational databases and keys

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

What this lesson is about

Flat files against relational databases, and every kind of key: primary, composite, foreign and secondary, with indexes and how records are found.

• Flat files and relational databases
• The vocabulary
• Keys
• Indexes
• How records are found in a file

Questions 6 marks in all

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

   1. AA primary key made of two or more attributes that together are unique
   2. BA primary key that is also a foreign key
   3. CA key that is indexed for searching
   4. DA key made by joining two tables
   Answer: A. No single attribute is unique, but the combination is, such as (RobotID, AttemptNo).

2. [1 mark]Which describes a secondary key?

   1. AAn attribute other than the primary key that is indexed so records can be searched or sorted by it quickly
   2. BThe second attribute of a composite key
   3. CA backup copy of the primary key
   4. DA foreign key in a link table
   Answer: A. A secondary key is indexed for fast searching and need not be unique.

3. [1 mark]Which are disadvantages of a flat file database compared with a relational database?

   Tick every answer that is true.

   1. AThe same facts are stored many times (data redundancy)
   2. BA missed edit can leave the data inconsistent
   3. CIt needs a DBMS to be set up
   4. DIt cannot be opened by a spreadsheet
   Answer: A, B. Repeating facts wastes space and invites inconsistency. A flat file is simpler to set up, not harder.

4. [1 mark]Adding an index on the task field of a runs table will usually...

   1. Amake searches by task faster, but take more storage and slow down inserts, updates and deletes
   2. Bmake every operation on the table faster
   3. Cstop two records having the same task
   4. Dremove the need for a primary key
   Answer: A. The index must be kept up to date on every change, which costs time and space.

5. [1 mark]Records are stored at an address calculated from their key. Which file organisation is this?

   1. ADirect (hashed) access
   2. BSerial
   3. CSequential
   4. DIndexed sequential
   Answer: A. A hash function turns the key into an address, so one record can be read without searching.

6. [1 mark]What does this print?

   SLOTS = 7
   table = [None] * SLOTS
   for key in [9, 16, 4]:
       address = key % SLOTS
       while table[address] is not None:
           address = (address + 1) % SLOTS
       table[address] = key
   print(table)

   Answer:

   [None, None, 9, 16, 4, None, None]

   9 and 16 both hash to 2, so 16 moves on to 3; 4 hashes to 4.

The task: a composite key

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

import sqlite3

attempts = [(1, 1, 42.0), (1, 2, 45.5), (2, 1, 38.0), (1, 2, 44.0), (3, 1, 76.5), (2, 1, 39.0)]
db = sqlite3.connect(":memory:")

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

import sqlite3

attempts = [(1, 1, 42.0), (1, 2, 45.5), (2, 1, 38.0), (1, 2, 44.0), (3, 1, 76.5), (2, 1, 39.0)]
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE attempts (robot_id INTEGER, attempt INTEGER, cm REAL, PRIMARY KEY (robot_id, attempt))")

for robot_id, attempt, cm in attempts:
    try:
        db.execute("INSERT INTO attempts VALUES (?, ?, ?)", (robot_id, attempt, cm))
        print(f"added robot {robot_id} attempt {attempt}")
    except sqlite3.IntegrityError:
        print(f"rejected robot {robot_id} attempt {attempt}: already logged")

count = db.execute("SELECT COUNT(*) FROM attempts").fetchone()[0]
print("records:", count)