Study Guide

Relational database model

CIE A-Level Computer ScienceΒ· Unit 8: Databases, Topic 2Β· 15 min read

1. Core Components of the Relational Modelβ˜…β˜…β˜†β˜†β˜†β± 4 min

πŸ“˜ Definition

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. 1

    Recall the definition of each component type:

  2. 2
    1. checkout_date describes a single property of all checkout records, so it is an attribute
  3. 3
    1. Member is the full collection of all library member records, so it is a relation
  4. 4
    1. (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.

2. Types of Keysβ˜…β˜…β˜…β˜†β˜†β± 5 min

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:

πŸ“˜ Definition

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
    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
    1. 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.

3. Core Properties of Relationsβ˜…β˜…β˜†β˜†β˜†β± 3 min

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. 1

    Recall that the atomicity property requires every attribute to hold exactly one value from its domain.

  2. 2

    Storing two separate phone numbers in one attribute creates a multi-valued entry, which violates the atomicity property.

  3. 3

    To fix this, a separate StudentEmergencyContact relation would be created, with one tuple per contact number.

4. Relationships Between Relationsβ˜…β˜…β˜…β˜†β˜†β± 3 min

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
    1. First, identify the relationship type: this is a many-to-many relationship.
  2. 2
    1. 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
    1. Add a junction table (usually called Enrollment) that stores the primary key of each relation as a foreign key. The final schema becomes:
  4. 4
    • Student(student_id, student_name)
    • Module(module_code, module_name)
    • Enrollment(student_id, module_code, semester)
  5. 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.

5. Common Pitfalls

Wrong move:

Confusing candidate keys and primary keys

Why:

Many students incorrectly assume a relation can only have one candidate key, only one of which is selected as primary key.

Correct move:

A relation can have multiple candidate keys; the primary key is just the candidate chosen for unique identification.

Wrong move:

Storing multiple values in a single attribute

Why:

Students often try to save space by storing multiple values in one cell, forgetting the atomicity rule.

Correct move:

Split multi-valued attributes into a separate linked relation to maintain compliance with the relational model.

Wrong move:

Forgetting that many-to-many relationships need a junction table

Why:

Students try to store multiple foreign keys in a single attribute, which breaks atomicity.

Correct move:

Always add a junction table with one tuple per link, to correctly model many-to-many relationships.

Wrong move:

Claiming order of tuples or attributes is part of the relation definition

Why:

Visual tools like spreadsheets show ordered rows/columns, leading to the misconception that order matters.

Correct move:

Remember that changing the order of rows or columns does not create a new relation, per core relational properties.

Wrong move:

Making a foreign key the primary key of the many side of a one-to-many relationship

Why:

For example, multiple books can have the same author, so author_id cannot uniquely identify a book.

Correct move:

The many side gets its own unique primary key, with the foreign key stored as an additional attribute.

6. Quick Reference 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

7. Frequently Asked

Is a relation the same as a table?

Formally, a relation is the mathematical term for the 2D structure, which is implemented as a table in most database systems. They are functionally equivalent for A-Level exam purposes.

Are definitions of key terms frequently tested?

Yes, short answer questions regularly ask for definitions of core terms like primary key, foreign key, and properties of relations, so you should memorize these.

When this came up on past exams

AI-estimated based on syllabus patterns β€” cross-check with official past papers for accuracy. Use only as revision-focus signals.

  • 2022 Β· 12

    Describe relational model core features

  • 2023 Β· 11

    Identify keys in a relational schema

  • 2024 Β· 22

    Model a many-to-many relationship

Going deeper

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.