AQA A-Level Computer Science (7517)

## Learning Outcomes

- Explain what a relational database is and why it is used instead of a flat-file database in many situations
- Identify the difference between entity, table, attribute, field, record and relation
- Define primary key, composite primary key and foreign key with precision
- Explain how tables are linked together in a relational database
- Interpret and write simple entity descriptions for related tables
- Apply relational database structure to simple SQL table definitions and JOIN queries
- Avoid common exam mistakes such as confusing relation with relationship or row with column

## AQA A-Level Computer Science (7517) Syllabus

For the AQA A-Level Computer Science (7517), you are expected to understand relational databases, with a focus on the following syllabus points:

- Explain the concept of a relational database
- Define the terms attribute, primary key, composite primary key and foreign key
- Know that data in a relational database is stored in multiple related tables
- Identify how records and fields represent data in tables
- Understand how foreign keys create links between tables
- Distinguish between a single-field primary key and a composite primary key
- Recognise that SQL can be used to define tables and retrieve data from multiple related tables

## Test Your Knowledge

_Attempt these questions before reading this article. If you find some difficult or cannot remember the answers, look more closely at that area during your revision._

1. What makes a database relational rather than flat-file?
2. What is the difference between a primary key and a foreign key?
3. Why might a table need a composite primary key instead of a single attribute?
4. In a table, what is the difference between an attribute and a record?
5. How does a JOIN query depend on the structure of a relational database?

## Introduction

A relational database stores data in separate but linked tables. This matters because most real systems contain several types of data that connect to each other. A school stores students, teachers and classes. A shop stores customers, products and orders. If all of that is forced into one big table, duplication quickly appears and updates become error-prone.

For AQA, you need more than a short definition. You need to explain how the model works: entities become tables, attributes become fields, records store individual items, and keys create links between tables. You also need to use the exact technical language, because exam questions often test small distinctions.

> **Key Term: relational database**
> A database made up of two or more related tables, where links between tables are created using keys.

### From entities to tables: how the relational model stores data

A relational database starts with a data model of the real world. In that model, you identify the things you want to store data about. These are **entities**. In implementation, each entity usually becomes a table.

For example, imagine an online cinema booking system. The entities might be:

- Customer
- Film
- Booking

In the database, these become tables. Each table stores data about one type of thing only. That is the first big strength of the relational model: it separates different kinds of data into logical structures.

A table is also called a **relation**. This wording can catch students out. In database theory, relation means table. It does not mean the same thing as relationship. A relationship is the link between entities or tables.

Within each table, the columns are the **attributes** or **fields**. They describe the properties of that entity. In a Customer table, likely attributes include CustomerID, FirstName, LastName and Email. The rows are **records**. Each row stores the data for one actual customer.

So this vocabulary lines up like this:

- entity -> table
- attribute -> field -> column
- record -> row
- relation -> table

That sounds simple, but many exam errors come from mixing up row and column. If you say an attribute is a row, the meaning is wrong.

A small example:

Customer table

| CustomerID | FirstName | LastName | Email          |
| ---------- | --------- | -------- | -------------- |
| 101        | Asha      | Khan     | asha@email.com |
| 102        | Ben       | Patel    | ben@email.com  |

Here:

- **Customer** is the entity or table
- **CustomerID**, **FirstName**, **LastName**, **Email** are attributes
- each horizontal row is one record
  > **Key Term: attribute**
  > A property of an entity, represented as a field or column in a table.
  >
  > **Key Term: record**
  > A row in a table representing one instance of an entity.

Why not just use one large table? Because one-table designs often repeat the same data many times. Suppose every booking row stored the customer's full name and email again and again. If that customer changes email address, every row would need editing. That wastes storage and increases the chance of inconsistent data.

A relational design reduces duplication by storing a customer's details once in the Customer table, then linking bookings to that customer by key.

> **Test Tip:** If a question asks you to "explain the concept" of a relational database, do not stop at "it has tables". State that it uses multiple related tables, stores different entities separately, and links them using keys to reduce duplication and support accurate retrieval.

### Keys: how records are identified and tables are linked

Keys are the core of relational databases. Without them, tables would just be separate lists.

A **primary key** uniquely identifies each record in a table. That means no two records can have the same primary key value. If two customers were both given CustomerID 101, the database could not reliably tell them apart.

> **Key Term: primary key**
> An attribute, or minimal set of attributes, that uniquely identifies each record in a table.

A good primary key must do one job above all others: uniqueness. Names usually make poor primary keys because two people can share a name. That is why systems often use an ID number such as CustomerID, StudentID or OrderID.

Example:

Customer(CustomerID, FirstName, LastName, Email)

If CustomerID is underlined in an entity description, that shows it is the primary key.

Now consider an Orders table:

Order(OrderID, CustomerID, OrderDate, Total)

Here, OrderID is the primary key for the Orders table. But CustomerID appears here too. In this table, CustomerID is not acting as the unique identifier for the order. Instead, it points to the customer who made the order. That makes it a **foreign key**.

> **Key Term: foreign key**
> An attribute in one table that references the primary key of another table, creating a link between the tables.

This distinction is critical:

- **Primary key**: uniquely identifies a record in its own table
- **Foreign key**: refers to a record in another table

A foreign key does not need to be unique. In fact, in one-to-many structures it usually repeats. If one customer places five orders, the Orders table will contain the same CustomerID five times. That is correct.

Worked schema:

Customer(CustomerID, FirstName, LastName, Email)

Order(OrderID, CustomerID, OrderDate, Total)

How the link works:

1. CustomerID is the primary key in Customer.
2. CustomerID appears again in Order.
3. In Order, CustomerID is a foreign key.
4. This lets each order be linked back to the correct customer.

This design is better than storing customer name and email inside every order row, because the customer details are entered once and then reused through the key.

Students often ask: why not use the customer's name as the foreign key? Because names can change, may be duplicated, and are less reliable than an ID.

> **Test Tip:** In definitions, be exact. A foreign key is not "a field used to connect tables" on its own. A stronger exam answer says it is an attribute in one table that references the primary key of another table.

### Composite primary keys and why one field is sometimes not enough

Some tables cannot be uniquely identified by one attribute alone. In those cases, the primary key is made from two or more attributes used together. This is a **composite primary key**.

> **Key Term: composite primary key**
> A primary key made from two or more attributes that together uniquely identify a record.

A common case is a linking table between two entities in a many-to-many situation.

Take students and courses:

- one student can take many courses
- one course can contain many students

If you try to store this directly in one table by putting several CourseIDs in each student record, you create repeating groups and a poor structure. A better design uses a third table:

Student(StudentID, Name)

Course(CourseID, Title)

Enrollment(StudentID, CourseID, DateJoined)

Now think about the Enrollment table. Can StudentID be the primary key? No, because one student may appear in several enrollment records. Can CourseID be the primary key? No, because many students can be on the same course.

But the combination of StudentID and CourseID can identify each enrollment uniquely, assuming a student cannot enroll on the same course twice. That pair becomes the composite primary key.

Worked reasoning:

- StudentID alone is not unique in Enrollment
- CourseID alone is not unique in Enrollment
- StudentID + CourseID together are unique
- therefore the table needs a composite primary key

This is a standard exam pattern. AQA may give you a table and ask what kind of key is needed. If no single field is unique, check whether a combination is.

In the Enrollment table:

- StudentID is part of the composite primary key
- CourseID is part of the composite primary key
- both are also foreign keys to other tables

That dual role is allowed. A field can be part of a composite primary key and also be a foreign key.

A common mistake is to say a composite key means "several separate primary keys". That is wrong. A table has one primary key, but that key may consist of several attributes.

Another example is a Loan table in a library:

Member(MemberID, Surname, FirstName)

Book(BookID, Title, Author)

Loan(MemberID, BookID, LoanDate, DueDate)

If the system allows only one active loan per member-book pair at a time, MemberID and BookID together may identify the loan. If the same member could borrow the same book again later, the design might also need LoanDate or a unique LoanID. The point is not to guess blindly; the key must match the rules of the situation.

### Relational databases in practice: entity descriptions, SQL and exam technique

In exams, you may need to move between three representations:

- plain English scenario
- entity description
- SQL

You must be able to see that they describe the same database structure.

Suppose a school stores students and classes, and records which students attend which classes.

Entity descriptions might be:

Student(StudentID, FirstName, Surname)

Class(ClassID, Subject, Room)

Attendance(StudentID, ClassID, Date)

Here, Attendance is the linking table. StudentID and ClassID might form a composite primary key, or the table might use all three fields depending on whether attendance is stored per day. The exact key depends on what makes each record unique.

This structure then supports SQL table creation:

```sql
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(30),
    Surname VARCHAR(30)
);
```

```sql
CREATE TABLE Class (
    ClassID INT PRIMARY KEY,
    Subject VARCHAR(30),
    Room VARCHAR(10)
);
```

```sql
CREATE TABLE Attendance (
    StudentID INT,
    ClassID INT,
    Date DATE,
    PRIMARY KEY (StudentID, ClassID, Date),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
);
```

The database is relational because the tables are linked through foreign keys. That structure also allows multi-table queries with JOIN.

Example query:

```sql
SELECT Student.FirstName, Student.Surname, Class.Subject
FROM Student
JOIN Attendance ON Student.StudentID = Attendance.StudentID
JOIN Class ON Attendance.ClassID = Class.ClassID;
```

Step-by-step logic of the JOIN:

1. Start with Student.
2. Match each student to rows in Attendance using StudentID.
3. Match each Attendance row to Class using ClassID.
4. Return the requested fields from the linked tables.

Without foreign-key links, this query would be much harder and less reliable.

Students often treat SQL and relational databases as separate topics. In reality, the SQL depends on the relational structure. A wrong key choice often leads to awkward or incorrect queries.

Common student errors in this topic:

- confusing **relation** with **relationship**
- saying an **attribute** is a row instead of a column
- thinking a **foreign key** must be unique
- assuming every table can use a single-field primary key
- forgetting that a foreign key references a primary key in another table
- joining on the wrong fields, such as Name instead of StudentID
- writing `UPDATE` or `DELETE` without `WHERE`, affecting all records
  > **Key Term: relation**
  > Another term for a table in the relational model.
  >
  > **Key Term: relationship**
  > The link between entities or tables in a database.
  >
  > **Test Tip:** When asked to identify keys in a schema, test uniqueness table by table. Ask: "What identifies one record in this table?" Then ask: "Which field here points to another table?" That separates primary key thinking from foreign key thinking.

For longer explain questions, a strong structure is:

1. Define the term precisely.
2. Give a small example table or schema.
3. Explain why that feature matters in practice.

For example, if asked why relational databases are preferable to flat-file databases in complex systems, a high-quality answer would say that separate linked tables reduce duplication, make updates more consistent, and allow related data to be queried together. That is stronger than simply saying "they are more organised".

## Key Point Checklist

_This article has covered the following key knowledge points:_

- A relational database stores data in two or more related tables
- A table is also called a relation
- An entity in a data model is usually implemented as a table
- An attribute is a field or column describing one property of an entity
- A record is a row representing one instance of an entity
- A primary key uniquely identifies each record in a table
- A composite primary key uses two or more attributes together to create uniqueness
- A foreign key is an attribute in one table that references the primary key of another table
- Foreign keys create the links between tables in a relational database
- A foreign key does not have to be unique, but a primary key must be unique
- Linking tables makes it possible to reduce duplicated data and query across multiple tables
- SQL statements such as CREATE TABLE and JOIN depend on the relational structure of the database

## Key Terms and Concepts

- relational database
- attribute
- record
- primary key
- foreign key
- composite primary key
- relation
- relationship


Databases - Relational databases