Community resourceWorksheet

OCR H446 1.3.2 Database foundations, tables and keys

Part 1 of 13 · H446 1.3.2 · Databases

OCR H446 1.3.2 opens with the vocabulary everything else depends on: database, table, field, record, and the primary, foreign and secondary keys that link them. This worksheet builds that model from a ticket-booking scenario so students can justify a key choice rather than recite a definition.

Students will:

  • distinguish a flat file design from a set of linked relational tables
  • identify primary, foreign and secondary keys from where a field appears in a schema
  • explain the job each key does in the table it sits in
  • propose tables and keys for an unfamiliar equipment-loan scenario
  • recall the core definitions from memory with no answer bank available

Inside: 7 explanation cells, 2 multiple-choice questions, 2 fill-in-the-blanks cells and 3 written answers. 27 marks, about 25 to 35 minutes.

Series: H446 1.3.2 · Databases, part 1 of 13.

Shared by Coding PathwayVerified teacher

  • 14 cells
  • About 30 minutes
  • CC BY-SA 4.0
  • Shared 31 Aug 2026
  • Updated 3 Sept 2026

Preview

The whole resource, exactly as a class sees it. Answers and marking are held back.

Database foundations, tables and keys

A ticket company must store customers, events and bookings without losing which facts belong together. A database is an organised collection of data that can be stored, searched and changed.

By the end, you will be able to

  • distinguish a flat file from a relational database;
  • identify fields, records and tables;
  • select and justify primary, foreign and secondary keys;
  • explain how keys support links and reliable retrieval.

Reactivate: a program record groups related fields for one item. A database record is one row in a table; the two ideas are related but not identical.

Tables organise facts by meaning

Table anatomy and database keysCUSTOMERcustomer_id (PK)nameemail (secondary key)townBOOKINGbooking_id (PK)customer_id (FK)event_id (FK)quantityreferences PKA row is a record; a named column is a field; the whole grid is a table.

A field is a named item of data, such as email. A record is one row about one instance, such as one customer. A table stores records with the same fields.

A primary key uniquely identifies each record. A foreign key stores a value that refers to a primary key in another table. A secondary key is an additional field used for searching; it need not be unique.

Worked comparison: one wide file or linked tables?

A flat file could store one row per booking with customer name, email, event title and venue repeated on every row. It is simple to create and may suit a small, single-purpose list.

A relational design stores Customer, Event and Booking separately and links them with keys. Repetition is reduced, facts can be updated in one place and related data can be selected in combinations. The trade-off is greater design complexity and the need to follow relationships.

A flat file is not necessarily plain text. The important distinction is one table/file structure rather than several related tables.

Multiple choice1 mark

Which statement about database keys is accurate?

  • ASeveral bookings may contain the same customer_id foreign key
  • BEvery foreign key value must be unique in its table
  • CA secondary key is a second primary key
  • DA primary key may identify several different records
Multiple choice1 mark

The field customer_id is the primary key in Customer and appears in Booking. Which statement is accurate?

  • Acustomer_id remains a primary key everywhere it appears
  • BBooking is the parent table because it contains the foreign key
  • Ccustomer_id is a foreign key in Booking because it references the Customer primary key
  • DEvery customer_id value in Booking must be unique
Fill in the blanks3 marks
In a table, a named column is a gap 1, one row is a gap 2, and the field chosen to identify each row uniquely is the gap 3.
  • field
  • record
  • table
  • primary key
  • foreign key

Guided checkpoint: choose keys by testing their job

CUSTOMER(customer_id, email, name, town)

  1. Ask which field is guaranteed unique and stable for every record.
  2. Use that field as the primary key.
  3. Ask which other field users may search by.
  4. Treat that as a possible secondary key only if the scenario supports searching it.

Worked decision: customer_id is a strong primary key because the system assigns one unique value to each customer. Email may be a secondary key for retrieval, but it can change and a policy would be needed before assuming it is unique.

Written answer4 marks

For EVENT(event_id, title, event_date, venue_id), identify the primary key and foreign key and explain the job of each.

Use unique identifier and reference to another table.

Students type their answer here.

Written answer6 marks

Compare a flat file and relational database for a ticket company with thousands of repeat customers and events.

Cover both approaches on shared criteria before recommending one.

Students type their answer here.

Independent transfer: equipment loans

A college records students, equipment and loans. Students may borrow many items, and one item may be loaned many times over the year.

Design only the table/key foundation. Relationship cardinalities are developed in DB02.

Written answer8 marks

Propose three tables for the equipment-loan database. Give a primary key for each and show two required foreign keys in the Loan table. Justify one key choice.

Use Student, Equipment and Loan or defensible equivalents.

Students type their answer here.

Closed-book checkpoint

Complete each sentence from memory. There is no answer bank and correctness is held for teacher review.

Fill in the blanks4 marks
A single-table design is a completion 1. A database containing linked tables is completion 2. A good identifier is both completion 3 and stable. A foreign key completion 4 a parent-table identifier.

Review your understanding

Before submitting, check that you can explain the main distinction in your own words, apply it in an unfamiliar context and justify each consequence rather than only naming a feature.