📄 Paper 2 · 4.10 Databases
4.10.2 Relational Databases & Referential Integrity
AQA 7517 · A-Level Computer Science · ~14 min read

Relational Model — Codd's Rules

The relational model (proposed by Edgar Codd, 1970) organises data into relations (tables). Each table has:

  • Rows (tuples/records): each row is a unique instance of the entity
  • Columns (attributes/fields): each column represents one attribute
  • All values in a column are of the same data type
  • Each row is unique (enforced by the primary key)
  • Order of rows and columns does not matter

Data Types in Databases

Data typeDescriptionExample
INTEGER / INTWhole numberAge: 17
FLOAT / REALDecimal numberPrice: 9.99
VARCHAR(n)Variable-length text up to n charactersName: 'Smith'
CHAR(n)Fixed-length text, exactly n charactersGender: 'M'
DATECalendar dateDOB: 2005-03-14
BOOLEANTrue or falseIsActive: TRUE
TEXTLong-form text (no set limit)Description

Referential Integrity

Referential integrity: every foreign key value must match an existing primary key in the related table — or be null (if the relationship is optional). The DBMS enforces this automatically.

Violations of referential integrity:

  • Inserting a row with a foreign key value that doesn't exist in the parent table
  • Deleting a parent row when child rows still reference it
  • Updating a primary key to a value referenced by foreign keys

ON DELETE CASCADE: if a parent record is deleted, all child records referencing it are automatically deleted.

ON DELETE SET NULL: if a parent record is deleted, child foreign keys are set to NULL.

Integrity Constraints

ConstraintMeaning
PRIMARY KEYUnique identifier — automatically NOT NULL and UNIQUE
FOREIGN KEYMust match a primary key in referenced table (referential integrity)
NOT NULLField must have a value — cannot be empty
UNIQUEAll values in the column must be different
CHECKValue must satisfy a condition (e.g. Age >= 16)
DEFAULTProvides a default value if none is supplied

Validation vs Verification

Validation: checking data is reasonable and meets defined rules (type check, range check, format check, presence check). Done by software automatically.

Verification: checking data was entered correctly — done by humans (double entry, proofreading).

Worked Example: School Database Design

Tables: Student(StudentID PK, FirstName, LastName, DOB, TutorGroupID FK) — TutorGroup(TutorGroupID PK, RoomNumber, TeacherID FK) — Teacher(TeacherID PK, TeacherName) — Enrolment(StudentID FK, CourseID FK — composite PK) — Course(CourseID PK, CourseName, TeacherID FK)

  • TutorGroupID in Student → FK references TutorGroup
  • StudentID + CourseID in Enrolment → composite PK (resolves M:M Student-Course)
  • TeacherID in Course → FK references Teacher
Exam tip: AQA 7517 may give you a scenario and ask you to identify tables, primary keys, foreign keys, and relationships. Practise reading a database schema. Know all six integrity constraints and be ready to explain referential integrity: every FK must match an existing PK, or the DBMS rejects the operation. Know ON DELETE CASCADE vs ON DELETE SET NULL.
▶
Click through the slides at your own pace. Use arrow keys or click to advance.
Click slide or press arrow keys to navigate

Worksheet — 4.10.2 Relational Databases

8 questions · instantly marked · AQA 7517 standard

Q1Explain what referential integrity means in a relational database. State two operations that would violate it.[3]
✅ Mark scheme
Mark scheme
Referential integrity: every foreign key value must match an existing primary key in the referenced table [1]. Violations (any 2): inserting a child record with a FK that doesn't exist in the parent table [1]; deleting a parent record when child records still reference it [1]; updating a PK to a value that foreign keys already reference [1].
Q2State appropriate data types for the following fields in a Student table: (a) StudentID, (b) DateOfBirth, (c) GradeAverage, (d) IsEnrolled, (e) LastName.[3]
✅ Mark scheme
Mark scheme
(a) StudentID: INTEGER or INT [1]; (b) DateOfBirth: DATE [1]; (c) GradeAverage: FLOAT or REAL [1]; (d) IsEnrolled: BOOLEAN [1]; (e) LastName: VARCHAR(n) [1].
Q3Explain the difference between ON DELETE CASCADE and ON DELETE SET NULL. State when each would be appropriate.[3]
✅ Mark scheme
Mark scheme
ON DELETE CASCADE: deleting the parent record automatically deletes all child records referencing it [1]; appropriate when child records have no meaning without the parent (e.g. delete order lines when the order is deleted) [1]. ON DELETE SET NULL: deleting the parent sets the child FK to NULL [1]; appropriate when the child record can exist independently but temporarily has no parent (e.g. employee's manager is deleted — employee remains but manager FK set to null) [1].
Q4Describe three integrity constraints used in a relational database other than PRIMARY KEY.[2]
✅ Mark scheme
Mark scheme
Any 3: NOT NULL — field must contain a value [1]; UNIQUE — all values in the column must be different [1]; FOREIGN KEY — enforces referential integrity [1]; CHECK — value must meet a condition (e.g. Salary > 0) [1]; DEFAULT — provides a default value if none given [1].
Q5A database has a Customer table and an Order table. A customer can be deleted but their orders must remain. What referential action should be used? Explain why.[2]
✅ Mark scheme
Mark scheme
ON DELETE SET NULL [1]; when the customer is deleted, the CustomerID FK in the Order table is set to NULL [1]; the order records are preserved for audit/financial purposes even though the customer no longer exists [1].
Q6Explain the difference between validation and verification. Give an example of each.[3]
✅ Mark scheme
Mark scheme
Validation: automatic software check that data meets defined rules [1]; e.g. age must be between 0 and 120 (range check), email must contain '@' (format check) [1]. Verification: human check that data was entered correctly [1]; e.g. double-entry of a password — typed twice and compared [1].
Q7List the four main properties of a relation (table) in the relational model.[2]
✅ Mark scheme
Mark scheme
Each row is unique (enforced by primary key) [1]; all values in a column are the same data type [1]; order of rows does not matter [1]; order of columns does not matter [1].
Q8Explain why VARCHAR is preferred over CHAR for storing names, but CHAR(1) might be used for a gender field.[2]
✅ Mark scheme
Mark scheme
VARCHAR: variable-length — only uses space equal to actual string length [1]; names vary in length so VARCHAR avoids wasting storage [1]. CHAR(1): fixed length, exactly 1 character — suitable when the field always has exactly one character (M/F) [1].
Topic Quiz
Question 1 of 15
You scored
out of 15
Card 1 of 8
Click to reveal definition
🎉
All cards reviewed!
TermDefinition
🎯

Mini Test — Relational Databases

10 questions · 10 minutes

← 4.10.1 DB Concepts
64 of 70 · AQA 7517
4.10.3 SQL →