Free A-Level Computer Science guide

Relational databases: from one table to a sound design

Learn why tables are linked, how keys preserve those links, and how to explain a design in a longer exam answer.

Content owner: Michael Print · Written for A-Level learners · Checked against official specifications

The short answer

A relational database stores related data in tables. A primary key uniquely identifies a row in its own table. A foreign key in another table refers to it. Separating different entities can reduce repeated facts and the inconsistencies that follow from updating only some copies.

Four ideas to get right

Entity and table

An entity is a type of thing the system records, such as a volunteer. A table stores records of that entity.

Primary key

A field, or combination of fields, that uniquely identifies a row.

Foreign key

A field referring to a key in a related table.

Referential integrity

The database keeps references valid, so a child row cannot point to a missing parent when the constraint is enforced.

Worked example

Volunteers and habitat surveys

One volunteer can take part in several surveys, and each survey can involve several volunteers. A Participation table links them:

Volunteer(VolunteerID PK, VolunteerName)

Survey(SurveyID PK, SurveyDate, SiteName)

Participation(VolunteerID PK/FK, SurveyID PK/FK, Hours)

The pair (VolunteerID, SurveyID) is the composite primary key of Participation under the stated rule that a volunteer appears only once per survey. Each field is also a foreign key. The rule matters: if repeated sessions must be recorded, the key design needs changing.

Why this works: a name or site is stored with the entity it describes. The Participation rows record the relationship and hours without repeating those details.

A method for design questions

Find the entities in the scenario.
State whether each relationship is one-to-one, one-to-many or many-to-many.
Choose keys and, where needed, a linking table.
Check whether non-key details describe the chosen key or would be repeated elsewhere.
Explain the practical effect in the given scenario, such as correcting a telephone number once.

Original A-Level practice questions

The board labels show curriculum alignment. These are independently written questions with indicative schemes, not reproduced papers.

Question 1

6 marks

OCR H446-aligned · evaluation. A repair charity keeps one row per tool loan in a spreadsheet. Each row repeats the borrower's name and telephone number alongside tool code and loan dates. A borrower can have several loans. The charity expects hundreds of loans and must sometimes correct a phone number. Explain one benefit and one drawback of its single-table design, then recommend whether separate Borrower and Loan tables linked by a key would suit it better. Justify your view.

Show solution and marking guidance

Model answer: A single table is easy to start and export, but every loan repeats the borrower's telephone number. Correcting some rows and missing others creates inconsistent values. Separate Borrower and Loan tables would put each phone number in one Borrower row; a BorrowerID foreign key in Loan would preserve the link. That is more suitable as the charity's loan volume grows, despite the extra design work.

Indicative marks: single-table benefit (1); developed repetition/update problem (2); linked tables and key (1); recommendation (1); scenario-specific justification (1).

Question 2

6 marks

AQA 7517-aligned · entity modelling. A music centre records performers and concerts. A performer can appear at many concerts and each concert has many performers. It records the minutes performed at each appearance. Describe a three-table design using Performer, Concert and a linking table. Give each primary key and the linking table's foreign keys. Assume each performer appears at most once at a concert.

Show solution and marking guidance

One valid three-table designSchema

Performer(PerformerID PK, PerformerName)
Concert(ConcertID PK, ConcertDate)
Appearance(PerformerID PK/FK, ConcertID PK/FK, MinutesPerformed)

Performer primary key (1); Concert primary key (1); linking Appearance table (1); two correctly directed foreign keys (2); composite primary key matching the “at most once” rule (1). A surrogate key can work with an additional uniqueness constraint.

Question 3

6 marks

Cambridge International 9618-aligned · 3NF reasoning. A community archive stores Borrowing(BorrowingID, ItemID, ItemTitle, MemberID, MemberName, BorrowedOn). Each ItemID has one title, each MemberID has one name, and BorrowingID uniquely identifies a borrowing. Explain why ItemTitle and MemberName should move out of Borrowing. Give a three-table design and identify the foreign keys.

Show solution and marking guidance

ItemTitle depends on ItemID, not directly on BorrowingID (1); MemberName similarly depends on MemberID (1). Use Item(ItemID PK, ItemTitle) (1), Member(MemberID PK, MemberName) (1), and Borrowing(BorrowingID PK, ItemID FK, MemberID FK, BorrowedOn) (1). Both foreign keys refer to the matching parent primary keys (1). Explain the update risks caused by repeating names or titles.

Question 4

8 marks

Extended design explanation. A repair business has RepairJob(JobID, DeviceID, ModelName, TechnicianID, TechnicianName, Fault, Hours). JobID identifies a job; each DeviceID has one ModelName and each TechnicianID one TechnicianName. Devices and technicians occur in many jobs. Explain why the table may become inconsistent. Propose a design that avoids repeated names, label its primary and foreign keys, and explain how each job remains linked to its device and technician.

Show solution and marking guidance

Model answer: ModelName and TechnicianName repeat across jobs, so updating only some copies can leave conflicting values. Use Device(DeviceID PK, ModelName), Technician(TechnicianID PK, TechnicianName) and RepairJob(JobID PK, DeviceID FK, TechnicianID FK, Fault, Hours). RepairJob.DeviceID links to Device; RepairJob.TechnicianID links to Technician. Foreign-key constraints keep those references valid.

Indicative marks: two specific repetition/update risks (2); valid Device and Technician tables with keys (2); RepairJob with job fields and JobID primary key (1); two correctly directed foreign keys (2); explanation of preserved links or referential integrity (1).

Further syllabus coverage and sources

These questions focus on keys and relationships. OCR H446, AQA 7517 and Cambridge International 9618 also include normalisation to 3NF and more advanced database work; the exact SQL and transaction requirements differ. Use your examination-year specification for the full scope.

These are independently written explanations and practice questions. CompSciTutoring.co.uk is not affiliated with or endorsed by an examination board. The marking guidance is indicative; always check the syllabus for your examination year.

Found an error? Email a correction.

Need help applying this to unfamiliar questions?

Use the free examples first, then explore one-to-one tuition if guided practice would help.

Explore tuition Email Michael

Four ideas to get right

Entity and table

An entity is a type of thing the system records, such as a volunteer. A table stores records of that entity.

Primary key

A field, or combination of fields, that uniquely identifies a row.

Foreign key

A field referring to a key in a related table.

Referential integrity

The database keeps references valid, so a child row cannot point to a missing parent when the constraint is enforced.

Volunteers and habitat surveys

One volunteer can take part in several surveys, and each survey can involve several volunteers. A Participation table links them:

Volunteer(VolunteerID PK, VolunteerName)

Survey(SurveyID PK, SurveyDate, SiteName)

Participation(VolunteerID PK/FK, SurveyID PK/FK, Hours)

Why this works: a name or site is stored with the entity it describes. The Participation rows record the relationship and hours without repeating those details.

A method for design questions

Find the entities in the scenario.

State whether each relationship is one-to-one, one-to-many or many-to-many.

Choose keys and, where needed, a linking table.

Check whether non-key details describe the chosen key or would be repeated elsewhere.

Explain the practical effect in the given scenario, such as correcting a telephone number once.

Original A-Level practice questions

The board labels show curriculum alignment. These are independently written questions with indicative schemes, not reproduced papers.

Question 1

6 marks

Show solution and marking guidance

Indicative marks: single-table benefit (1); developed repetition/update problem (2); linked tables and key (1); recommendation (1); scenario-specific justification (1).

Question 2

6 marks

Show solution and marking guidance

One valid three-table designSchema

Performer(PerformerID PK, PerformerName)
Concert(ConcertID PK, ConcertDate)
Appearance(PerformerID PK/FK, ConcertID PK/FK, MinutesPerformed)

Question 3

6 marks

Show solution and marking guidance

Question 4

8 marks

Show solution and marking guidance

Further syllabus coverage and sources