How is data organised in a relational database?
Understand relational database concepts including tables, records, fields, primary keys and foreign keys, and why data is organised this way.
A focused answer to AQA GCSE Computer Science 3.7, covering relational database concepts including tables, records, fields, primary keys and foreign keys, and why data is organised this way.
Reviewed by: AI editorial process; not yet individually human-reviewed
Have a quick question? Jump to the Q&A page
Jump to a section
What this dot point is asking
AQA wants you to understand the core relational database concepts (tables, records, fields, primary keys and foreign keys) and explain why data is organised into related tables.
Tables, records and fields
A useful way to picture it is a spreadsheet: each table is a sheet, each row is a record, and each column is a field. The relational model just adds rules (keys) that let separate tables be joined together reliably.
Primary key
A primary key is essential because it gives every record a guaranteed-unique handle, so a particular record can always be found, referred to and linked to without ambiguity. Names and dates of birth are not reliable as keys because they can repeat.
Foreign key
For example, an Orders table might have a CustomerID foreign key that matches the CustomerID primary key in a Customers table. Each order then points to exactly one customer, and a single customer record serves all of that customer's orders.
Why use related tables
Choosing a primary key and a data type per field
When designing a table you choose a primary key and a data type for each field. The primary key should be a field guaranteed to be unique and unlikely to change, which is why an ID number is preferred over a name or date of birth that could repeat. Each field is given a data type matching its values: an integer for a quantity, a real for a price, a string for a name, a Boolean for a yes/no flag, and a date for a date of birth. Choosing types well keeps the data valid (you cannot store text in a number field) and saves storage, just as choosing variable types does in a program.
Try this
Q1. State what a primary key is. [2 marks]
- Cue. A field that uniquely identifies each record in a table, so no two records share the same value.
Q2. Explain how a foreign key links two tables. [2 marks]
- Cue. It is a field in one table that holds the primary key of another table, connecting the related records.
Exam-style practice questions
Practice questions written in the style of AQA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.
AQA 20194 marksA shop stores customer and order data. Explain why splitting the data into a Customers table and an Orders table linked by a key is better than storing everything in one table. Refer to redundancy and consistency.Show worked answer →
Storing everything in one table repeats each customer's details (name, address) on every order they place, which is data redundancy: it wastes storage and means the same fact is held many times.
Splitting into a Customers table (each customer stored once, with a primary key) and an Orders table (each order linked to a customer by a foreign key) stores each customer's details once. This improves consistency, because if a customer's address changes it is updated in one place, so there is no risk of some copies being updated and others left wrong.
Markers reward identifying redundancy (repeated data) and consistency (update once, no conflicting copies) as the reasons, linked to the two-table design with keys.
AQA 20214 marksDefine the terms primary key and foreign key, and using a Students table and a Classes table as an example, describe how a foreign key would link them.Show worked answer →
A primary key is a field that uniquely identifies each record in a table, so no two records share its value. A foreign key is a field in one table that holds the primary key of another table, creating a link between the two.
Example: the Classes table has a primary key ClassID. The Students table includes a ClassID field as a foreign key; each student's ClassID matches the primary key of the class they are in, so the two tables are linked and a student can be traced to their class.
Markers reward correct definitions of both keys and a clear example of the foreign key in one table matching the primary key of the other.
Related dot points
- Use SQL to retrieve data with SELECT, FROM, WHERE and ORDER BY, and to insert, update and delete records.
A focused answer to AQA GCSE Computer Science 3.7, covering SQL to retrieve data with SELECT, FROM, WHERE and ORDER BY, and to insert, update and delete records.
- Use one-dimensional and two-dimensional arrays and records to store collections of data, and access elements using indexes and field names.
A focused answer to AQA GCSE Computer Science 3.2.6, covering one-dimensional and two-dimensional arrays and records, and accessing elements using indexes and field names.
- Read from and write to a text file, including opening, reading line by line, writing and closing a file, so that data persists after the program ends.
A focused answer to AQA GCSE Computer Science 3.2.10, covering reading from and writing to a text file, including opening, reading line by line, writing and closing a file so data persists.
- Understand the ethical, legal and cultural issues raised by digital technology and how stakeholders are affected.
A focused answer to AQA GCSE Computer Science 3.8, covering the ethical, legal and cultural issues raised by digital technology and how different stakeholders are affected.
Sources & how we know this
- AQA GCSE Computer Science (8525) specification — AQA (2020)