AQA A-Level Computer Science (7517)

## Learning Outcomes

- Understand what a conceptual data model is and why it is used before database implementation
- Identify entities, attributes, relationships and entity identifiers from a written scenario
- Draw and interpret entity-relationship diagrams for simple multi-entity systems
- Distinguish clearly between one-to-one, one-to-many and many-to-many relationships
- Explain why many-to-many relationships are usually resolved using an associative entity
- Write accurate entity descriptions with underlined identifiers in the format required by AQA
- Avoid common mistakes such as choosing non-unique identifiers or confusing entities with attributes

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

For the AQA A-Level Computer Science (7517), you are expected to understand conceptual data models and entity-relationship modelling, with a focus on the following syllabus points:

- Know that a conceptual data model represents the data needed by a system and the relationships between data items
- Identify entities, attributes and relationships from a simple real-world scenario
- Produce entity descriptions in the form `Entity(Attribute1, Attribute2, ...)`
- Understand that the entity identifier is the attribute or combination of attributes that uniquely identifies each occurrence
- Represent relationships between entities on an entity-relationship diagram
- Distinguish between one-to-one, one-to-many and many-to-many relationships
- Recognise that many-to-many relationships are often resolved by adding an extra linking or associative entity
- Use clear notation, including underlining the identifier attribute or attributes

## 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 is the difference between an entity and an attribute?
2. How can you tell whether a relationship is one-to-many or many-to-many?
3. Why is a field such as surname usually a poor choice for an entity identifier?
4. What is the purpose of an associative entity in an ER model?
5. Write an entity description for a simple `Student` entity with a suitable identifier.

## Introduction

Conceptual data modelling is the stage where you work out what data a system needs before worrying about SQL, table creation, or full database implementation. In exam questions, you are often given a short scenario such as a school, hospital, hotel, or booking system. Your job is to turn that description into a clear data model.

This subtopic matters because it sits at the bridge between a real-world problem and a relational database design. If you misidentify the entities or relationships at this stage, the whole database structure becomes weak. AQA questions often test whether you can move from words to a correct entity description or ER diagram, not just whether you can recite definitions.

> **Key Term: conceptual data model**
> A high-level description of the data needed by a system and the relationships between items of data.

### Identifying entities, attributes and identifiers

A strong ER model begins with identifying the right **entities**. An entity is not just any noun in the scenario. It is a distinct type of object, person, place, event, or concept that the system needs to store data about. In a school system, likely entities might be `Student`, `Teacher`, `Lesson`, and `Subject`. In a hotel system, likely entities might be `Customer`, `Room`, and `Booking`.

> **Key Term: entity**
> A distinct real-world object or concept about which data is stored.

Each entity has **attributes**, which are the individual pieces of information you need to record about that entity. For example:

- `Student` might have `StudentID`, `FirstName`, `Surname`, `DateOfBirth`
- `Room` might have `RoomNumber`, `Type`, `PricePerNight`
- `Booking` might have `BookingDate`, `StartDate`, `EndDate`
  > **Key Term: attribute**
  > A property or characteristic of an entity.

A common mistake is to confuse entities and attributes. For example, in a school system, `Teacher` is usually an entity because you may need many details about each teacher. But `TeacherName` on its own might be only an attribute if the system does not need a separate teacher record. The right choice depends on how much data the system must store and whether the object has its own identity in the model.

A useful method in exam questions is this:

1. Read the scenario and highlight nouns
2. Ask which nouns need their own stored records
3. For each one, list the facts you need to store
4. Turn those facts into attributes

The next step is choosing an **entity identifier**. This is the attribute, or set of attributes, that uniquely identifies each occurrence of the entity. For `Student`, `StudentID` is a better identifier than `Surname`, because several students can share the same surname.

> **Key Term: entity identifier**
> The attribute, or combination of attributes, that uniquely identifies each occurrence of an entity.

In relational database language, this becomes the **primary key**, but in conceptual modelling you should focus first on uniqueness and clarity.

> **Key Term: primary key**
> An attribute used to uniquely identify each record in a table.

Suppose you are modelling a library member:

`Member(MemberID, FirstName, Surname, Email, DateJoined)`

The identifier should be `MemberID`, not `FirstName` or `Email` unless the scenario guarantees uniqueness. Exam questions reward sensible choices, not risky ones.

Sometimes one attribute is not enough. A school timetable entry might only be unique when more than one value is combined. That gives a **composite primary key**.

> **Key Term: composite primary key**
> A primary key made from two or more attributes whose combined values are unique.

For example:

`Booking(FacilityID, BookingDate, StartTime, CustomerID)`

If one facility can be booked more than once on different dates and times, then `FacilityID` alone is not unique. But the combination of `FacilityID`, `BookingDate`, and `StartTime` may be unique.

> **Test Tip:** If you are choosing an identifier, test it mentally against the question. Ask: "Could two records share this value?" If yes, it is not a safe identifier.

Entity descriptions should be written in the AQA-friendly form:

- `Doctor(DoctorID, FirstName, Surname, Specialism)`
- `Patient(PatientID, FirstName, Surname, DateOfBirth)`
- `Appointment(AppointmentDate, AppointmentTime, DoctorID, PatientID, Notes)`

The identifier is normally shown by underlining it. If the identifier is composite, underline all attributes that together form it.

### Modelling relationships and cardinality

Once you know the entities, you must decide how they are related. A **relationship** is an association between entities. In a written scenario, relationships are often hidden in verbs such as "places", "books", "teaches", "contains", or "attends".

> **Key Term: relationship**
> An association between two entities.

There are three relationship types you must know.

#### One-to-one

A **one-to-one** relationship means one record in entity A is linked to one record in entity B, and vice versa.

> **Key Term: one-to-one (1:1)**
> A relationship where each occurrence of one entity is linked to only one occurrence of the other.

Example:

- One passport belongs to one person
- One person has one passport

This kind of relationship is less common in simple exam questions, but it can appear.

#### One-to-many

A **one-to-many** relationship means one record in entity A can be linked to many records in entity B, but each record in B links back to only one record in A.

> **Key Term: one-to-many (1:M)**
> A relationship where one occurrence of an entity can be linked to many occurrences of another entity.

Example:

- One customer places many orders
- Each order is placed by one customer

This is one of the most common relationships in AQA questions.

#### Many-to-many

A **many-to-many** relationship means many records in entity A can be linked to many records in entity B.

> **Key Term: many-to-many (M:N)**
> A relationship where many occurrences of one entity can be linked to many occurrences of another.

Example:

- One student takes many courses
- One course has many students

This is easy to recognise in words, but students often stop there. The real exam skill is seeing what this means for later database design.

The term **cardinality** is often used for these relationship quantities.

> **Key Term: cardinality**
> The number of instances of one entity that may be associated with instances of another entity.

AQA does not require one named diagram style only. Some diagrams use crow's foot notation, some use labels, and some use wording. What matters is that your meaning is clear.

Worked example:

A college keeps records of tutors and students.

- Each tutor is responsible for many students.
- Each student has one tutor.

This is `Tutor 1:M Student`.

Why? Start from one tutor:

- Can one tutor be linked to several students? Yes.
  Now start from one student:
- Can one student be linked to several tutors? No, only one.

That two-way test is the safest way to decide.

A frequent error is reversing the relationship. Students may know there is a one-to-many link, but put the "many" marker on the wrong side. The wording must drive the diagram.

> **Test Tip:** Say the relationship out loud in both directions: "One tutor has many students; each student has one tutor." Then place the many side on the correct entity.

### Resolving many-to-many relationships with an associative entity

Many-to-many relationships are fine in a conceptual model, but when moving toward a relational database they are not usually implemented directly. Instead, you introduce a new entity between them.

> **Key Term: associative entity**
> An extra entity used to replace a many-to-many relationship with two one-to-many relationships.

Consider this scenario:

- A customer can buy many products
- A product can be bought by many customers

At conceptual level, `Customer` and `Product` have an M:N relationship. But a relational database needs more structure. You add a new entity such as `OrderLine`, `Purchase`, or `Enrolment`, depending on the context.

So instead of:

- `Customer M:N Product`

you create:

- `Customer 1:M Order`
- `Order 1:M OrderLine`
- `Product 1:M OrderLine`

or in a simpler model:

- `Student 1:M Enrolment`
- `Course 1:M Enrolment`

Why does this help? Because the linking entity can store details about the relationship itself. For example, in `Enrolment`, you may need:

- date enrolled
- grade
- status

Those are not attributes of `Student` alone or `Course` alone. They belong to the relationship between them.

Worked example:

A gym system stores members and classes.

- Each member can attend many classes.
- Each class can have many members.

Initial entities:

- `Member(MemberID, Name, Phone)`
- `Class(ClassID, ClassName, Day, Time)`

This is M:N. To resolve it, add:

- `Booking(MemberID, ClassID, DateBooked)`

Now the relationships are:

- One member can have many bookings
- One class can have many bookings
- Each booking refers to one member and one class

The entity description might be:

- `Member(_MemberID_, Name, Phone)`
- `Class(_ClassID_, ClassName, Day, Time)`
- `Booking(_MemberID_, _ClassID_, DateBooked)`

Here `Booking` may use a composite identifier of `MemberID` and `ClassID`, though in some systems an extra `BookingID` could be created instead.

This is also where the idea of a **foreign key** begins to matter in later relational design.

> **Key Term: foreign key**
> An attribute in one table that refers to the primary key of another table to create a link between them.

In conceptual modelling, do not rush too early into implementation detail. But you should understand the bridge:

- entity becomes table
- attribute becomes field
- identifier becomes primary key
- relationship becomes foreign key or linking table

Students often make these mistakes with M:N relationships:

- leaving the many-to-many relationship unresolved when the question clearly expects a database-ready structure
- inventing a linking entity with no sensible purpose
- forgetting that the linking entity may need its own attributes
- missing that the link entity often contains the identifiers of both main entities

### A worked method for AQA-style questions

Suppose the question says:

"A music school stores details of students, teachers and lessons. Each teacher teaches many lessons. Each student attends many lessons. Each lesson is taught by one teacher and can be attended by many students."

Step 1: Identify likely entities

- `Student`
- `Teacher`
- `Lesson`

Step 2: Add sensible attributes

- `Student(StudentID, FirstName, Surname)`
- `Teacher(TeacherID, FirstName, Surname, Instrument)`
- `Lesson(LessonID, Date, Time, Room)`

Step 3: Identify relationships

- One teacher teaches many lessons
- One lesson is taught by one teacher
- One student attends many lessons
- One lesson is attended by many students

So:

- `Teacher 1:M Lesson`
- `Student M:N Lesson`

Step 4: Resolve the M:N relationship
Add `Attendance` or `LessonBooking`

Entity descriptions become:

- `Student(_StudentID_, FirstName, Surname)`
- `Teacher(_TeacherID_, FirstName, Surname, Instrument)`
- `Lesson(_LessonID_, Date, Time, Room, TeacherID)`
- `Attendance(_StudentID_, _LessonID_)`

Step 5: Check your logic

- Can one teacher teach many lessons? Yes
- Can one lesson have many teachers? No
- Can one student attend many lessons? Yes
- Can one lesson have many students? Yes

This checking stage is where many marks are saved.

Another important exam issue is scope. Conceptual modelling is about the data structure, not every interface detail. If the scenario says a hotel records customer addresses, room types, and booking dates, you should model those data items. You should not start adding irrelevant things such as login screens or printing reports.

You also need to avoid repeated groups hidden inside attributes. For example, if a `Customer` can have many phone numbers, `Phone1`, `Phone2`, `Phone3` is usually a sign that you may need another entity, depending on the question. Repetition inside one record often suggests that the structure has not been thought through properly.

> **Test Tip:** In an exam, if you are unsure whether something is an entity or an attribute, ask: "Would this item have multiple records of its own, with several facts stored about each one?" If yes, it is probably an entity.

## Key Point Checklist

_This article has covered the following key knowledge points:_

- A conceptual data model is a high-level representation of the data a system needs and how items of data relate
- An entity is a distinct object or concept about which data is stored
- An attribute is a property or characteristic of an entity
- A relationship is an association between two entities
- Entity descriptions are written in the form `Entity(Attribute1, Attribute2, ...)`
- The entity identifier uniquely identifies each occurrence and is usually shown by underlining
- A primary key is the relational database term for the unique identifier of a table record
- A composite primary key uses more than one attribute together to ensure uniqueness
- The three main relationship types are one-to-one, one-to-many and many-to-many
- Cardinality describes how many instances of one entity can be linked to another
- Many-to-many relationships are usually resolved by introducing an associative entity
- Clear logic matters more than using one specific ERD house style

## Key Terms and Concepts

- conceptual data model
- entity
- attribute
- entity identifier
- primary key
- composite primary key
- relationship
- one-to-one (1:1)
- one-to-many (1:M)
- many-to-many (M:N)
- cardinality
- associative entity
- foreign key


Databases - Conceptual data models and entity-relationship modelling