OCR H446 1.3.2: Databases
The free BugBot lessons that teach this point, a robot to try it on, and the past-paper questions that test it.
What it covers
At GCSE (F7.2) you split one big table of robot runs into two linked tables, and met primary and foreign keys. The tables were given to you. At A level you design them yourself: start from what the users of a system need, model the data on paper, and only then build the database. This module follows the robotics club's data all the way: from a model, to a normalised database queried in SQL, to a database shared by many users at once, and finally to data too big for one machine.
- Conceptual data models
- Entity descriptions
- The degree of a relationship
- Resolving a many-to-many relationship
- Checking a degree against data
Or do it in the simulator, with tasks that mark themselves
Lessons that teach it
- A11.1 Data models and entity relationships Databases and big data
Model the data before building anything: entities, attributes, entity descriptions and the degree of each relationship, drawn as an entity… - A11.2 Relational databases and keys Databases and big data
Flat files against relational databases, and every kind of key: primary, composite, foreign and secondary, with indexes and how records… - A11.3 Normalisation to third normal form Databases and big data
Functional dependencies, update anomalies, and taking a flat score sheet through first, second and third normal form. - A11.4 SQL: defining tables and joining them Databases and big data
CREATE TABLE with types and keys, ALTER and DROP, then SELECT with INNER JOIN, wildcards, aggregates, GROUP BY and nested queries. - A11.5 SQL: changing data and referential integrity Databases and big data
INSERT, UPDATE and DELETE at A level, and how a database refuses orphan records. The robot logs its own moves. - A11.6 Transactions and ACID Databases and big data
A transaction is all or nothing: atomicity, consistency, isolation and durability, commit and rollback, and redundancy. - A11.7 Client server databases and concurrent access Databases and big data
Many clients, one database: the lost update, and the four ways to prevent it: record locks, serialisation, timestamp ordering and… - A11.8 Capturing, managing and exchanging data Databases and big data
How data gets in, how it is selected and managed, and how systems swap it as CSV, JSON and XML. The robot exports its own readings. - A11.10 Project: the run database Databases and big data
Design, normalise, build and fill a database of robot runs, log live runs in transactions, report with one joined query and export the…
Try it on a robot
The task from A11.1 Data models and entity relationships, running here. Change the program and press Run.
The specification is OCR's own document; this page says which of our lessons teach point 1.3.2 and is not endorsed by the board.