How does a relational database organise data into tables, and how do keys link those tables together?
Describe the relational model in terms of tables, rows, attributes, primary keys and foreign keys, and explain referential integrity
A focused answer to the H2 Computing outcome on the relational model. Tables, rows and attributes, primary and foreign keys, relationships between tables, and how referential integrity keeps data consistent.
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
SEAB wants you to describe the relational model - data held in tables of rows and attributes - and to explain how primary keys identify rows, how foreign keys link tables, and how referential integrity keeps those links valid. The central idea is that storing each fact once in a well-keyed set of tables avoids the duplication and inconsistency of one giant flat table.
The answer
Tables, rows and attributes
A relational database organises data into tables (relations). Each table is about one kind of thing - members, books, loans. A row (record or tuple) is one instance, such as a single member. An attribute (column or field) is one property of that thing, such as a member's name, and every value in a column shares the same type.
Primary keys
A primary key uniquely identifies each row. It must be unique across rows and never null. A good primary key is stable (does not change) and minimal. Often a dedicated surrogate identifier such as MemberID is used because natural values like names can repeat or change.
Foreign keys and relationships
A foreign key is an attribute in one table that holds the primary-key value of a row in another table, creating a link between them. For example a Loan row stores the MemberID of the borrower:
Member(MemberID PK, Name, JoinDate)
Loan(LoanID PK, MemberID FK -> Member, BookID FK -> Book, DueDate)
This lets one member have many loans - a one-to-many relationship - without repeating the member's details in every loan.
Referential integrity
Referential integrity is the rule that every foreign-key value must match an existing primary-key value (or be null where allowed). It prevents:
- inserting a loan for a member who does not exist, and
- deleting a member who still has loans (which would leave orphaned references).
The database enforces this automatically, so the links between tables stay consistent.
Examples in context
Example 1. An online shop. Customers, orders and products are separate tables. An Order row carries the CustomerID of the buyer and links to ordered products through an OrderLine table. The customer's address lives once in Customer, so correcting a typo updates every order automatically.
Example 2. A hospital system. Patients and appointments are linked by a foreign key PatientID in the appointment table. Referential integrity stops an appointment being booked for a non-existent patient and stops a patient record being deleted while appointments remain, protecting the consistency of the records.
Try this
Q1. State two properties a primary key must have. [2 marks]
- Cue. It must be unique across all rows and must never be null.
Q2. In a one-to-many relationship between Department and Employee, where does the foreign key go and why? [2 marks]
- Cue. On Employee (the many side), holding
DepartmentID, so each employee points to one department without repeating department data.
Q3. Give one thing referential integrity prevents. [1 mark]
- Cue. It prevents a foreign key referencing a row that does not exist (an orphaned reference), or deleting a referenced row while references remain.
Exam-style practice questions
Practice questions written in the style of SEAB exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.
Original5 marksA library stores data about books and the members who borrow them. (a) Define primary key and foreign key. (b) Suggest a suitable primary key for a Member table and explain your choice. (c) Explain how a foreign key links a Loan table to the Member table and what referential integrity prevents.Show worked answer →
(a) A primary key is an attribute (or set of attributes) that uniquely identifies each row in a table; no two rows share the same value and it cannot be null. A foreign key is an attribute in one table that refers to the primary key of another, linking the two tables.
(b) A suitable primary key is a MemberID - a unique identifier assigned to each member. It is preferred over, say, a name because names can repeat and can change, whereas a dedicated ID is guaranteed unique and stable.
(c) The Loan table stores a MemberID as a foreign key referencing Member.MemberID, so each loan record points to exactly one member. Referential integrity prevents a loan from referencing a member who does not exist, and prevents deleting a member who still has loans, so the link can never dangle.
Markers reward correct definitions, a stable unique identifier with justification, and referential integrity stopping orphaned foreign-key references.
Original4 marksExplain two advantages of storing related data across multiple linked tables in a relational database rather than in a single large table.Show worked answer →
Less redundancy and inconsistency. In a single flat table, repeated facts (such as a supplier's address appearing on every order) are duplicated, wasting space and risking update anomalies where one copy is changed and others are not. Splitting into linked tables stores each fact once.
Easier maintenance and integrity. With separate tables joined by keys, you can update a customer's details in one place, enforce referential integrity between tables, and add new related data without restructuring everything. Querying remains flexible through joins.
A third acceptable point: independent tables can be indexed and secured separately, improving performance and access control.
Markers reward two distinct, correct advantages, ideally redundancy/anomaly reduction and single-point maintenance with integrity.
Related dot points
- Write SQL queries using SELECT with WHERE, ORDER BY, JOIN, GROUP BY and aggregate functions, and modify data with INSERT, UPDATE and DELETE
A focused answer to the H2 Computing outcome on SQL queries. SELECT with WHERE and ORDER BY, joining tables, grouping with aggregate functions, and modifying rows with INSERT, UPDATE and DELETE.
- Define database schemas with CREATE TABLE, choosing data types and enforcing PRIMARY KEY, FOREIGN KEY, NOT NULL and UNIQUE constraints
A focused answer to the H2 Computing outcome on defining schemas. CREATE TABLE, choosing appropriate data types, and enforcing primary key, foreign key, NOT NULL and UNIQUE constraints to protect data integrity.
- Normalise a relational design to first, second and third normal form, explaining the anomalies each form removes
A focused answer to the H2 Computing outcome on normalisation. First, second and third normal form, the insertion, update and deletion anomalies they remove, and how to decompose a table step by step.
- Model a problem domain with an entity-relationship diagram, identifying entities, attributes, relationships and cardinality, and map it to tables
A focused answer to the H2 Computing outcome on ER modelling. Entities, attributes and relationships, one-to-one, one-to-many and many-to-many cardinality, resolving many-to-many with a link table, and mapping an ER model to relational tables.
- Explain how characters are encoded using ASCII and Unicode, including code points and UTF-8, and the implications for storage and internationalisation
A focused answer to the H2 Computing outcome on character encoding. ASCII and its limits, Unicode code points, the UTF-8 variable-length scheme, and the implications for storage and supporting many languages.