AQA 7517 · Paper 2 · 4.10.2

Tables, Keys &
Referential Integrity

Primary keys · foreign keys · candidate keys · referential integrity

WHAT YOU'LL LEARN

Tables · records · fields · primary/foreign/candidate keys · referential integrity

AQA SPEC LINK

4.10.2 — Relational databases: tables, keys, referential integrity, data types

Tables & Records

Tables, Records & Fields

StudentID	FirstName	LastName	DOB
S001	Alice	Patel	2006-09-12
S002	Ben	Khan	2007-03-05

Table — stores data about one entity type (like a spreadsheet)

Record (row) — one instance of the entity (e.g. one student)

Field (column/attribute) — one piece of information about each record

Key Types

Types of Keys

Primary Key (PK)

Uniquely identifies each record. Must be unique, not null, and stable. E.g. StudentID.

Foreign Key (FK)

An attribute in one table that references the primary key of another table. Creates a link between tables. E.g. CourseID in Enrolment table references CourseID in Course table.

Candidate Key

Any attribute (or combination) that could serve as the primary key. One is chosen as PK; the rest are alternate keys.

Composite Key

Composite Primary Key

A composite key is a primary key made up of two or more attributes combined. Used when no single attribute uniquely identifies a record.

        Enrolment(StudentID, CourseID, EnrolDate)
      

Neither StudentID nor CourseID alone is unique in Enrolment — a student can enrol on many courses; a course has many students

Together they uniquely identify each enrolment record

Referential Integrity

Referential integrity ensures that a foreign key value always refers to an existing primary key record. The database will not allow you to create an orphaned record.

Cannot add an Enrolment record with a StudentID that does not exist in the Student table

Cannot delete a Student if there are Enrolment records referencing that student — unless CASCADE DELETE is set

Enforced by the DBMS automatically when foreign key constraints are defined

Data Types

Field Data Types in Databases

Data Type	Description	Example
INTEGER	Whole numbers	Age, Quantity
REAL/FLOAT	Decimal numbers	Price, Score
VARCHAR(n)	Variable-length text up to n chars	Name, Email
CHAR(n)	Fixed-length text	PostCode (7)
DATE	Calendar date	DOB, OrderDate
BOOLEAN	True/False	IsActive, Paid

Linking Tables

Relationships via Foreign Keys

Teacher
TeacherID	Name
T01	Mr Smith
T02	Ms Jones

Class
ClassID	Subject	TeacherID*
C01	Maths	T01
C02	Science	T01
C03	Art	T02

TeacherID* in Class references TeacherID in Teacher (1:M relationship); cannot set TeacherID=T99 if T99 doesn't exist in Teacher — referential integrity enforced

Indexes

Indexes & Efficiency

An index is a data structure that speeds up searches on a table by maintaining a sorted reference to one or more columns — like an index at the back of a textbook.

Primary key is automatically indexed by most DBMS

Can add additional indexes on frequently-searched columns (e.g. LastName)

Trade-off: faster reads but slower writes (index must be updated on INSERT/UPDATE/DELETE)

Without an index: full table scan O(n); with index: O(log n)

AQA Exam Style

Practice Question

AQA 7517 — Paper 2 Style

(a) Explain the difference between a primary key and a foreign key. [3]
(b) A student with StudentID S099 does not exist in the Student table. Explain what would happen if a user tried to add an Enrolment record with StudentID S099. [2]
(c) What is a composite primary key? Give an example. [2]

[7 marks]

3 marks

(a) A primary key uniquely identifies each record in its own table [1]; a foreign key is an attribute that references the primary key of another table [1]; foreign keys create links between tables and enforce referential integrity [1]

2 marks

(b) The DBMS would reject/refuse the insertion [1]; because referential integrity requires that the foreign key (StudentID) must match an existing primary key in the Student table [1]

2 marks

(c) A primary key made up of two or more attributes combined to uniquely identify a record [1]; example: (StudentID, CourseID) in an Enrolment table where neither alone is unique [1]

Summary

Key Points to Remember

Table — stores one entity type; rows = records; columns = fields/attributes

Primary key — unique, not null, stable; identifies each record uniquely

Foreign key — references PK of another table; creates links between tables

Referential integrity — FK must always reference a valid PK; enforced by DBMS

Composite key — two or more attributes combined as the PK

🎉 Lesson complete — move to the quiz!