Relational databases — AQA GCSE Computer Science

Verified courseTeaching note checked against the official specification.

A relational database stores data across multiple linked tables containing records and fields, using primary and foreign keys to establish relationships. This structure minimizes data redundancy, preserves referential integrity, and enables precise data querying via SQL.

Relational databases explained

In a relational database, a field is one column of a table that stores a single attribute for every record, such as CustomerID, Surname or Email.

Read the full explanation

Each field has a defined data type and holds one piece of information about the entity. Within a single record, one field holds one value, for example the surname 'Patel'. A field is not the whole row (that is a record) and not the whole table. Fields define the structure of a table: every record in the table has the same set of fields. A field can also be a key, such as a primary key that uniquely identifies each record. Recognising a field as one attribute column is the skill tested here.

Read the Relational databases study guideFull revision notes for AQA GCSE Computer Science

Your focus

Identify a field as a single attribute column in a table.
Distinguish a field from a record and from a table.
Explain how a field has a data type and can act as a key.

Which command word is this?

Name or select a specific database component, field, or key from the given stimulus without providing an explanation.

Relational databases exam tips

Topic Overview

A relational database organizes data into one or more structured tables (relations) made up of rows (records) and columns (fields). Rather than storing all information in a single, error-prone flat file, data is logically separated into distinct entities connected by primary and foreign keys.

In AQA GCSE Computer Science, understanding relational databases is essential for Paper 2. Students must be able to define key terms, interpret entity relationships, explain the benefits of relational structures over flat files, and write simple SQL queries to retrieve and manipulate stored data.

Key Concepts

→Entity, Record, and Field: An entity is a real-world object or concept being modelled; a record is an individual row representing one instance of an entity; a field is an individual column representing a specific attribute.
→Primary Key: A field that uniquely identifies every record within a table, preventing ambiguity and duplicate entity entries.
→Foreign Key: A field in one table that links directly to the primary key in another table to enforce referential integrity between related entities.
→Data Redundancy and Integrity: Relational architecture reduces the duplication of data (redundancy) and ensures data remains accurate and consistent across all linked tables (integrity).

Marking Points

A field is a single column in a table storing one attribute.
Each field has a defined data type, such as text, number or date.
Every record in a table contains the same set of fields.
A field can act as a key, for example a primary key that uniquely identifies a record.
A field is not the whole row (a record) nor the whole table.

Examiner Tips

💡Look for the word 'column' or 'attribute' when identifying a field in a table diagram.
💡Check the data type of a field to confirm it stores one kind of value.
💡Eliminate options describing a whole row, because those describe a record, not a field.
💡Always reference field and table names exactly as they are printed in the exam question, preserving capitalisation and spelling.
💡When explaining the drawbacks of flat-file databases, explicitly reference 'update anomalies' (inconsistent updates) and 'data redundancy' to access the top mark band.
💡Memorise the exact syntax sequence for SQL: SELECT (fields), FROM (tables), WHERE (conditions/joins), and ORDER BY (sorting).

Common Mistakes

Confusing a field with a record: a field is one attribute column, whereas a record is a whole row of fields.
Thinking a field holds all the data about an entity; it holds only one attribute of that entity.
Assuming fields can vary between records in the same table; all records share the same field structure.
Misconception: Foreign keys must share the exact same column name as the primary key they reference. Correction: While it is standard practice to name them identically for clarity, the database requires matching data types and valid references, not identical names.
Misconception: A primary key must always consist of just a single field. Correction: A primary key can be a composite key made up of two or more fields that together form a unique identifier for a record.
Misconception: Relational databases eliminate all duplicate data entirely. Correction: Relational databases eliminate unnecessary data duplication (redundancy), but key fields (foreign keys) must still be repeated to establish the relationships.

Revision Plan

1Day 1-2: Master core vocabulary by drawing and labelling a two-table relational schema with fields, records, primary keys, and foreign keys.
2Day 3-4: Practice identifying anomalies (insertion, deletion, update) in unnormalised flat-file spreadsheets to appreciate the benefits of relational tables.
3Day 5-6: Write and test fundamental SQL statements (SELECT, FROM, WHERE, ORDER BY) incorporating single-table queries and simple relational joins.
4Day 7: Complete timed AQA past-paper questions focusing on database structure design and key identification.

Exam Question Types

📋Key Identification Questions: Identifying primary keys, foreign keys, or suitable data types from a provided table schema.
📋Comparison Questions: Comparing flat-file databases to relational databases in terms of efficiency, integrity, and storage.
📋SQL Query Writing: Constructing valid SQL commands using SELECT, FROM, WHERE, and ORDER BY across one or two related tables.

Command Word Expectations (AQA)

Identify

Name or select a specific database component, field, or key from the given stimulus without providing an explanation.

Explain

State a valid point about relational database behaviour or structure and expand upon it with an explanation of its cause, mechanism, or consequence.

State

Provide a brief, factual answer without calculation, elaboration, or justification.

How Students Lose Marks (Examiner Pitfalls)

Pitfall: Confusing the definitions and purposes of primary keys and foreign keys.

❌ Weak Answer (Loses Marks):A primary key is the main field and a foreign key links tables together.

Example improved answer:A primary key is an attribute or combination of attributes that uniquely identifies each record in a table. A foreign key is an attribute in one table that references the primary key in another table to create a relational link between them.

Examiner Tip: Always state that a primary key provides 'unique identification' for records and explicitly state that a foreign key links directly to another table's primary key.

Pitfall: Vaguely explaining why relational databases are superior to flat-file databases.

❌ Weak Answer (Loses Marks):Relational databases are better because they are faster, modern, and do not make mistakes.

Example improved answer:Relational databases reduce data redundancy by ensuring repeated data is not duplicated across rows. This prevents update and deletion anomalies, improves data consistency, and saves storage space.

Examiner Tip: Use specific exam terminology: state 'reduces data redundancy' and 'maintains data integrity' rather than informal phrases like 'saves time' or 'is less messy'.

Step-by-Step Worked Solutions

Question: A school database tracks Students and Clubs. Each student can join one Club, and each Club has multiple students. Table: Student(StudentID, FirstName, LastName, DateOfBirth, ClubID) Table: Club(ClubID, ClubName, RoomNumber) (a) Identify the primary key for the Student table. (b) Identify the foreign key in this design and explain its purpose.

1.Step 1: Examine the Student table attributes to find the attribute that uniquely identifies each student entity. StudentID is the unique identifier, making it the primary key.
2.Step 2: Look for an attribute present in the Student table that also acts as a primary key in another table. ClubID exists in both tables.
3.Step 3: State the attribute and describe its relational role: ClubID in the Student table acts as a foreign key that references ClubID in the Club table to link a student to their specific club.

Final Answer: (a) Primary key: StudentID. (b) Foreign key: ClubID in the Student table; it links each student record to their corresponding club record in the Club table.

Question: A doctor's surgery uses a relational database with two tables: Patient(PatientID, FullName, Postcode) Appointment(AppointmentID, Date, Time, Fee, PatientID) Write an SQL statement to retrieve the FullName of all patients who have an appointment with a Fee greater than 50.00.

1.Step 1: Identify the required fields for display. The question requests FullName, which comes from the Patient table.
2.Step 2: Identify the tables involved. Both Patient and Appointment are needed to match patients with appointment fees.
3.Step 3: Define the JOIN condition linking the primary key of Patient (Patient.PatientID) with the foreign key of Appointment (Appointment.PatientID).
4.Step 4: Formulate the WHERE clause filter to restrict records where Fee > 50.00.

Final Answer: SELECT Patient.FullName FROM Patient, Appointment WHERE Patient.PatientID = Appointment.PatientID AND Appointment.Fee > 50.00;

Active Recall Memory Test

What is the technical term for a single row in a database table?

Key Fact: A record (or tuple).

What is the purpose of a primary key in a relational database?

Key Fact: To uniquely identify each record within a table.

What connects a record in one table to a record in another table?

Key Fact: A foreign key referencing a primary key.

What problem occurs when the same data item is stored in multiple locations and updated in only one?

Key Fact: An update anomaly (causing data inconsistency).

Frequently Asked Questions

What is the difference between a flat-file database and a relational database?

A flat-file database stores all information in a single table or file, which causes significant data duplication and increases the risk of data entry errors. In contrast, a relational database splits information across multiple specialized tables linked by primary and foreign keys. This division prevents unnecessary data redundancy, saves storage, and keeps information consistent when updates are made.

Can a database table have more than one primary key?

A table can only have one primary key, but that primary key can consist of more than one column. When two or more fields are combined to create a unique identifier for each record, it is called a composite primary key. In addition, a table can contain multiple foreign keys that connect it to several other tables.

What is referential integrity in GCSE Computer Science?

Referential integrity is a database rule ensuring that relationships between tables remain valid and consistent. It dictates that a foreign key value in one table must always match an existing primary key value in the related table, or be set to null. This prevents orphaned records, such as an order existing for a customer ID that does not exist.

Why is data redundancy such a big issue in flat-file systems?

Data redundancy means storing the same piece of information more than once. When redundant data needs to be modified, it must be updated manually in every location. If an update is missed, the database suffers from an update anomaly, leaving conflicting data in the system and ruining overall data integrity.

How do you resolve a many-to-many relationship in a relational database?

Relational databases cannot directly link many-to-many relationships without causing heavy duplication. To resolve this, database designers break the many-to-many relationship into two one-to-many relationships using an intermediate junction table (also called a linking table). The junction table typically contains the primary keys of both connected tables as foreign keys.

Relational databases explained

Your focus

Which command word is this?

Relational databases exam tips

More AQA GCSE Computer Science topics

Cookies on MasteryMind