# Relational database model

> CIE A-Level Computer Science · 9618
> Source: https://www.owlsprep.com/study/cie-9618-u8-relational-database-model/

This module covers E.F. Codd's relational database model, the global standard for modern structured data storage. You will learn core terminology, key properties, types of keys, and how relationships between data are structured.

**Prerequisites:** [Introduction to databases](https://www.owlsprep.com/study/cie-9618-u8-introduction-to-databases/)

## Learning objectives

- Understand core principles of the relational database model
- Distinguish between different types of relational keys
- Name and explain core properties of relations
- Identify and model relationships between relations

## Core Components of the Relational Model

**Relational Database Model** — A structured data model that organizes data into one or more independent relations, with formal rules for how data links across relations, designed to eliminate the redundancy and inconsistency of file-based systems.

*Example:* A university database splits student, course, and enrollment data into three separate linked relations.

The relational model uses consistent terminology for core components that you must memorize for CIE exams:

- **Relation**: The full 2D structure of data (commonly called a table in implementation)
- **Tuple**: A single, complete record in a relation (commonly called a row)
- **Attribute**: A single property shared by all tuples (commonly called a column)
- **Domain**: The set of allowed valid values for an attribute

**Worked example:** For a library book checkout system, categorize the following as relation, tuple, or attribute: (1) `checkout_date`, (2) `Member`, (3) (1001, "Priya Mehta", "2024-01-15")

1. Recall the definition of each component type:
2. 1. `checkout_date` describes a single property of all checkout records, so it is an **attribute**
3. 2. `Member` is the full collection of all library member records, so it is a **relation**
4. 3. (1001, "Priya Mehta", "2024-01-15") is a single complete member record, so it is a **tuple**

> **Exam tip:** CIE examiners award full marks only when you use formal terminology: use 'tuple' not 'row' and 'attribute' not 'column' in answers that ask for formal definitions.

## Types of Keys

Keys are the core mechanism for uniquely identifying tuples and creating links between different relations. There are several distinct key types you need to distinguish:

**Candidate Key** — A minimal set of one or more attributes that can uniquely identify every tuple in a relation. A relation can have multiple candidate keys.

*Example:* In a `Patient` relation, both `patient_id` and `nhs_number` are separate candidate keys.

- **Primary Key**: The single candidate key selected by the designer to uniquely identify tuples. A primary key must always be non-null and unique.
- **Foreign Key**: An attribute in one relation that matches the primary key of another relation, creating a link between the two relations.

**Worked example:** Given the schema `Product(product_id, product_name, supplier_id, unit_price)` where `supplier_id` references `Supplier(supplier_id)`, identify the primary key and foreign key in the `Product` relation.

1. 1. The primary key uniquely identifies each product. `product_id` is a unique identifier for every product, so this is the **primary key**.
2. 2. The foreign key links to the primary key of another relation. `supplier_id` references the `supplier_id` primary key of the `Supplier` relation, so this is the **foreign key**.

## Core Properties of Relations

All valid relations in the relational model follow four core properties that you may be asked to list or explain in the exam:

- Tuples are unordered: the order of rows does not change the relation
- Attributes are unordered: the order of columns does not change the relation
- Attribute values are atomic: each attribute holds exactly one value from its domain
- No duplicate tuples: all tuples are unique (enforced by the candidate key)

**Worked example:** A student stores 2 emergency contact numbers in a single `emergency_contact` attribute of a `Student` relation. Which core property is violated? Explain your answer.

1. Recall that the atomicity property requires every attribute to hold exactly one value from its domain.
2. Storing two separate phone numbers in one attribute creates a multi-valued entry, which violates the **atomicity property**.
3. To fix this, a separate `StudentEmergencyContact` relation would be created, with one tuple per contact number.

## Relationships Between Relations

The relational model supports three common types of relationships between relations, defined by how primary and foreign keys are structured:

- **One-to-one**: One tuple in relation A links to exactly one tuple in relation B (rare for most use cases)
- **One-to-many**: One tuple in relation A links to many tuples in relation B (the most common relationship type)
- **Many-to-many**: Many tuples in relation A link to many tuples in relation B, which requires an extra junction table

**Worked example:** How do you model the relationship between `Student` and `Module` in a university enrollment system, where each student takes multiple modules and each module has multiple students?

1. 1. First, identify the relationship type: this is a many-to-many relationship.
2. 2. Many-to-many relationships cannot be directly modeled with just two relations, as this would violate atomicity if multiple foreign keys are stored in one attribute.
3. 3. Add a junction table (usually called `Enrollment`) that stores the primary key of each relation as a foreign key. The final schema becomes:
4. - `Student(student_id, student_name)`
- `Module(module_code, module_name)`
- `Enrollment(student_id, module_code, semester)`
5. Each tuple in `Enrollment` represents one student enrollment in one module, correctly modeling the relationship.

> **Exam tip:** Questions asking to model many-to-many relationships are extremely common. Always remember to add a junction table to get full marks.

## Common pitfalls

- **Wrong:** Confusing candidate keys and primary keys
  - Why it fails: Many students incorrectly assume a relation can only have one candidate key, only one of which is selected as primary key.
  - Correct: A relation can have multiple candidate keys; the primary key is just the candidate chosen for unique identification.
- **Wrong:** Storing multiple values in a single attribute
  - Why it fails: Students often try to save space by storing multiple values in one cell, forgetting the atomicity rule.
  - Correct: Split multi-valued attributes into a separate linked relation to maintain compliance with the relational model.
- **Wrong:** Forgetting that many-to-many relationships need a junction table
  - Why it fails: Students try to store multiple foreign keys in a single attribute, which breaks atomicity.
  - Correct: Always add a junction table with one tuple per link, to correctly model many-to-many relationships.
- **Wrong:** Claiming order of tuples or attributes is part of the relation definition
  - Why it fails: Visual tools like spreadsheets show ordered rows/columns, leading to the misconception that order matters.
  - Correct: Remember that changing the order of rows or columns does not create a new relation, per core relational properties.
- **Wrong:** Making a foreign key the primary key of the many side of a one-to-many relationship
  - Why it fails: For example, multiple books can have the same author, so `author_id` cannot uniquely identify a book.
  - Correct: The many side gets its own unique primary key, with the foreign key stored as an additional attribute.

## Cheatsheet

| Term | Core Definition |
| --- | --- |
| Relation | Full 2D table of related data |
| Tuple | Single record/row in a relation |
| Attribute | Single property/column in a relation |
| Candidate Key | Minimal unique identifier for tuples |
| Primary Key | Chosen candidate key for a relation |
| Foreign Key | Links to primary key of another relation |
| Atomicity | One value per attribute, no multi-values |
| Many-to-many | Requires a junction table in the model |

## What's next

The relational model is the foundation for all remaining topics in the databases unit. Next, you will learn normalization, the process of refining relational schemas to remove data redundancy and prevent update, insertion, and deletion anomalies. After mastering normalization, you will move on to Structured Query Language (SQL), the standard language for creating, querying, and modifying relational databases. Every concept you learn in these topics builds directly on the terminology, rules, and structure we have covered here, so mastering this sub-topic will make all subsequent database work much easier to understand.

- [Structured Query Language (SQL)](https://www.owlsprep.com/study/cie-9618-u8-structured-query-language/)
- [Normalisation](https://www.owlsprep.com/study/cie-9618-u8-normalisation/)
- [Client-server and distributed databases](https://www.owlsprep.com/study/cie-9618-u8-client-server-and-distributed-databases/)

---

From [OwlsPrep](https://www.owlsprep.com) — free study guides for A-Level, IB, AP and IGCSE, written against the official syllabus. Canonical page: https://www.owlsprep.com/study/cie-9618-u8-relational-database-model/