Homeschool Guide: These lesson plans are a guide for parents. Content may contain errors — always cross-reference with official exam board specifications.

relational databases

FoundationHigherAll Boards

4 detailed 50-minute lessons with teaching scripts, worked examples, parent guides, and assessment criteria.

Fastmail

Lesson Overview

Total Lessons: 4
Tier: Foundation and Higher
Duration: 50 minutes per lesson (200 minutes total)
Exam Boards: AQA, Edexcel, OCR, Eduqas, CCEA

Learning Objectives

Prerequisites

Materials & Equipment

Lesson 1: Introduction: relational databases

Duration: 50 minutes

Starter Activity (5 minutes)

Quick Recall

Write down everything you already know about relational databases. Then check against the key terms: Data Redundancy, Data Inconsistency, GCSE Computer Science Exam Tips. Use a mini-whiteboard or paper.

Main Content (35 minutes)

Parent/Teacher Guide:
Before lesson: Read the script below. Pre-teach key vocab: Data Redundancy, Data Inconsistency, GCSE Computer Science Exam Tips.
If stuck: Re-read the revision notes (link above), then break the content into smaller steps.
Extension: See the Stretch & Challenge ideas in Lesson 4.
Teaching Script (35 mins):
Mins 0-5 - Hook: "Today: relational databases. By the end you will be able to answer exam questions on it unaided. It connects to the rest of Computer Science because the ideas here recur across the spec."
Mins 5-20 - Direct Instruction: Work through the core ideas below one at a time; after each, ask your student to explain it back in their own words.
Mins 20-30 - Guided Practice: Model the worked example together, then let your student attempt the first practice question with guidance.
Mins 30-35 - Independent Practice: 2-3 practice questions from Lesson 3 below, with immediate feedback.
First Look

Start with the revision notes summary, then attempt: Define the terms field, record, and table in the context of a database.

Plenary (5 minutes)

Check Out

Your student states one thing they learned and one question they still have about relational databases.

Lesson 2: Core Concepts: relational databases

Duration: 50 minutes

Starter Activity (5 minutes)

Review Previous Lesson

Quick recap: write 3 key points from Lesson 1 on relational databases. Check them against the notes below.

Main Content (35 minutes)

Definition: A database is an organised, structured collection of data that can be easily accessed, managed, and updated. A relational database stores data in multiple linked tables that relate to each other.
Definition: A table is a structured set of data organised in rows and columns. Each table stores data about one type of entity (e.g. a table for Students, a table for Courses).
Definition: A field is a single category of data within a table. Each field has a name and a data type (e.g. text, integer, date). A field stores one piece of information for each record.
Definition: A record is a single, complete set of data in a table. Each row represents one item or entity, containing values for every field.
Definition: A primary key is a field (or combination of fields) that uniquely identifies each record in a table. No two records can have the same primary key value. A primary key cannot be left empty (null).
Definition: A foreign key is a field in one table that references the primary key of another table. It creates a link (relationship) between the two tables, allowing them to be connected.
TermMeaningExample
1001AliceSmith
1002BobJones
1003ClaraPatel
1001AliceSmith
1002BobJones
TG01Mr WilliamsR12
TG02Mrs KhanR15
One-to-One1 : 1One record in table A relates to exactly one record in table B

Practice (10 minutes)

Q: Define the terms field, record, and table in the context of a database.

Answer: A field is a single category of data in a table (a column). A record is a complete set of data for one entity in a table (a row). A table is a structured collection of related data organised in rows and columns.

Plenary (5 minutes)

Explain Back

Your student teaches the key points back to you without looking. Fill any gaps immediately.

Lesson 3: Application: relational databases

Duration: 50 minutes

Starter Activity (5 minutes)

Quick Recall

Recall the key terms: Data Redundancy, Data Inconsistency, GCSE Computer Science Exam Tips. Define each in one sentence.

Main Content (35 minutes)

Parent/Teacher Guide: Let your student attempt each question alone first, then compare with the model answer. Award method marks for correct working even if the final answer is wrong.

Q1: Define the terms field, record, and table in the context of a database.

Answer: A field is a single category of data in a table (a column). A record is a complete set of data for one entity in a table (a row). A table is a structured collection of related data organised in rows and columns.

Q2: What is a primary key? Why is it important?

Answer: A primary key is a field that uniquely identifies each record in a table. It must be unique (no two records can have the same value) and cannot be null. It is important because it allows each record to be uniquely identified and retrieved, and it enables relationships between tables via foreign keys.

Q3: Explain the difference between a primary key and a foreign key.

Answer: A primary key uniquely identifies each record in its own table. A foreign key is a field in one table that references the primary key of another table, creating a link between them. Primary keys must be unique; foreign keys can repeat. Every table has one primary key; foreign keys only exist when there is a relationship between tables.

Q4: What is data redundancy and how does a relational database eliminate it?

Answer: Data redundancy is when the same data is stored in multiple places, wasting space and causing inconsistency. A relational database eliminates redundancy by splitting data into separate related tables, so each piece of data is stored only once. When data needs to be linked, foreign keys are used instead of duplicating the data.

Q5: How would you resolve a many-to-many relationship in a relational database?

Answer: A many-to-many relationship is resolved by creating a linking (junction) table between the two entities. The linking table contains foreign keys referencing the primary keys of both tables, converting the M:N relationship into two 1:M relationships. For example, Students M:N Courses is resolved by creating an Enrolments table with StudentID (FK) and CourseID (FK).

Plenary (5 minutes)

Error Review

Review any questions answered incorrectly. Identify whether the error was knowledge, method, or reading the question.

Lesson 4: Exam Practice: relational databases

Duration: 50 minutes

Starter Activity (5 minutes)

Command Words

Review what these command words require: state (one point), describe (say what happens), explain (say why), compare (both sides), evaluate (judgement).

Main Content (35 minutes)

Extended Answer

Extended question: Full-Mark Response A school stores student data in a flat file with fields: StudentID, Name, TutorGroup, TutorName, TutorRoom. Explain two problems with this flat file design, and describe how a relational database would solve them. [5 marks] <div class="

Problem 1: Data redundancy — the TutorName and TutorRoom are repeated for every student in the same tutor group. If 30 students share a tutor, the tutor's name and room appear 30 times. Problem 2: Update anomaly — if a tutor changes room, every record for their students must be updated. Missing one record causes data inconsistency. Relational database solution: Students table: StudentID (PK), Name, TutorGroup (FK) Tutors table: TutorGroup (PK), TutorName, TutorRoom The TutorGroup field in Students is a foreign key linking to the Tutors table. Now each tutor's name and room are stored only once. If a tutor changes room, only ONE record in the Tutors table needs updating, eliminating redundancy and update anomalies.

Exam Tips: Know the definitions: table, field, record, primary key, foreign key | Primary key = unique identifier; Foreign key = link to another table | Always explain WHY primary keys must be unique (to identify each record) | For redundancy questions, give a concrete example of duplicated data | Remember: M:N relationships need a linking table to resolve | Be able to draw simple E-R diagrams showing 1:1, 1:M, and M:N relationships
Common Errors: ✗ Thinking a flat file and a relational database are the same ✓ A flat file stores all data in a single table with redundant data. A relational database splits data into linked tables, reducing redundancy and improving data integrity. ✗ Confusing primary key and foreign key ✓ A primary key uniquely identifies each record in a table. A foreign key is a field in one table that references the primary key of another table, creating the relationship between them. ✗ Forgetting that a primary key must be unique and cannot be NULL ✓ Every record must have a unique primary key value — no duplicates and no empty (NULL) values. This ensures each record can be uniquely identified. ✗ Not understanding wh
Stretch & Challenge (Grade 8-9):
  • Synoptic links: explain how relational databases connects to another Computer Science topic you have studied
  • Real-world: research one real-world use or example of relational databases
  • Critical: "What are the limitations of the models used in relational databases?"

Plenary (5 minutes)

Assessment Criteria
  • Got it: Confident explanation + correct worked examples
  • Getting there: Main points OK, needs support with detail
  • Not yet: Confused on key concepts - re-run Lesson 2

Homework & Consolidation

Recommended Resources

🎓 Smart Lesson (Guided)