Show understanding of and use the terminology associated with a relational database model

8.1 Database Concepts – Relational Model

Learning Objective

Show understanding of, and correctly use, the terminology associated with a relational database model (AO1). Analyse a given schema, design tables that satisfy the required relationships, and write basic SQL statements to create, query and modify those tables (AO2 & AO3).

1. Key Terminology (Cambridge wording)

Term	Definition
Database	An organised collection of data stored electronically and accessed by a computer system.
Relational Database Management System (RDBMS)	Software that creates, maintains and provides controlled access to a relational database (e.g. MySQL, PostgreSQL, Microsoft SQL Server).
Table (Relation)	A set of rows (records) and columns (attributes) that stores data about a single entity.
Row (Tuple, Record)	A single, complete set of related data items in a table.
Column (Attribute, Field)	A named data item that holds a particular type of information for all rows.
Domain	The set of all possible values that an attribute may take (e.g. INTEGER, VARCHAR(50)).
Primary Key (PK)	A column, or minimal set of columns, that uniquely identifies each row in a table.
Foreign Key (FK)	A column (or set of columns) that references the primary key of another table, thereby creating a link between the two tables.
Relationship	The logical connection between two tables. Cambridge requires knowledge of the three cardinalities: One‑to‑One (1:1) One‑to‑Many (1:N) Many‑to‑Many (M:N) – implemented with a junction table.
Referential Integrity	The rule that a foreign‑key value must either be null or match an existing primary‑key value in the referenced table.
Normalization	The systematic process of organising tables to reduce redundancy and improve data integrity. Cambridge expects understanding of 1NF, 2NF and 3NF.
SQL (Structured Query Language)	The standard language used to define, query and manipulate relational data.

2. Formal Structure of a Relation

A relational table can be expressed mathematically as a set of ordered n‑tuples:

\[ R = \{ (a_1, a_2, \dots , a_n) \mid a_i \in D_i \} \]

• R – the relation (i.e. the table name).
• a_i – the value of attribute A_i in a particular row.
• D_i – the domain of attribute A_i.

Each cell therefore contains a single, atomic value – the “atomicity” requirement of First Normal Form (1NF).

3. Illustrative Example – Student‑Course Database

3.1 Entity‑Relationship Overview

Figure 1 (placeholder) shows an ER diagram with two entities, Student and Enrollment, linked by a one‑to‑many relationship (a student can enrol in many courses).

3.2 Physical Tables

Students

StudentID (PK)	Name	Email
1001	Alice Brown	alice@example.com
1002	Bob Smith	bob@example.com
1003	Charlie Davis	charlie@example.com

Enrollments

EnrollID (PK)	StudentID (FK)	CourseCode	Semester
2001	1001	CS101	Fall 2025
2002	1002	CS101	Fall 2025
2003	1001	MA102	Fall 2025

3.3 Relationship Cardinalities

• 1:N (Student → Enrollments) – the foreign key StudentID resides on the “many” side (Enrollments).
• M:N (Students ↔ Courses) – would be realised with a junction table, e.g. StudentCourse(StudentID FK, CourseCode FK).

4. Normalisation – From an Un‑normalised Table to 3NF

Start with an un‑normalised table that mixes student data, course details and lecturer names.

Un‑normalised Student Enrolment

StudentID	Name	CourseCode	CourseTitle	Lecturer
1001	Alice Brown	CS101	Intro to CS	Dr Lee
1001	Alice Brown	MA102	Calculus II	Prof Khan
1002	Bob Smith	CS101	Intro to CS	Dr Lee

1. 1NF – atomic values & one fact per row Split into two tables:
   • Students(StudentID, Name)
   • StudentCourses(StudentID, CourseCode, CourseTitle, Lecturer)
2. 2NF – eliminate partial dependencies In StudentCourses the attributes CourseTitle and Lecturer depend only on CourseCode. Create a separate Courses table:
   • Courses(CourseCode, CourseTitle, Lecturer)
   • StudentCourses(StudentID, CourseCode)
3. 3NF – remove transitive dependencies All non‑key attributes now depend directly on the primary key of their own table, so the schema is in Third Normal Form.

5. SQL – Core Commands Required for the Syllabus

5.1 Data Definition Language (DDL)

-- Create the three tables
CREATE TABLE Students (
    StudentID   INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL,
    Email       VARCHAR(100) UNIQUE
);

CREATE TABLE Courses (
    CourseCode  CHAR(6) PRIMARY KEY,
    CourseTitle VARCHAR(100) NOT NULL,
    Lecturer    VARCHAR(100)
);

CREATE TABLE Enrollments (
    EnrollID    INT PRIMARY KEY,
    StudentID   INT NOT NULL,
    CourseCode  CHAR(6) NOT NULL,
    Semester    VARCHAR(20),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseCode) REFERENCES Courses(CourseCode)
);

5.2 Data Manipulation Language (DML)

-- INSERT rows
INSERT INTO Students VALUES (1004,'Diana Prince','diana@example.com');
INSERT INTO Courses  VALUES ('PH201','Physics II','Dr Miller');
INSERT INTO Enrollments VALUES (2004,1004,'PH201','Spring 2026');

-- UPDATE a value (AO3)
UPDATE Students
SET Email = 'alice.brown@university.edu'
WHERE StudentID = 1001;

-- DELETE a row (AO3)
DELETE FROM Enrollments
WHERE EnrollID = 2002;

-- SELECT examples (AO2)

-- a) Inner join – list all enrolments
SELECT s.Name, c.CourseTitle, e.Semester
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseCode = c.CourseCode;

-- b) Left outer join – students with no enrolments
SELECT s.Name, e.EnrollID
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.EnrollID IS NULL;

6. Assessment Objective Mapping (quick reference)

AO	What the notes help you do
AO1	Recall definitions, recognise primary/foreign keys, and describe 1NF‑3NF.
AO2	Analyse a scenario, identify relationships, and decide which normal form a schema satisfies.
AO3	Design tables, write DDL/DML, and modify data while preserving referential integrity.

7. Practical Checklist for Paper 4 (Programming & SQL)

• Choose an RDBMS that supports standard SQL (MySQL, SQLite, PostgreSQL).
• Set up a test database and create the tables using the DDL above.
• Verify primary‑key uniqueness and foreign‑key constraints with SHOW CREATE TABLE (or equivalent).
• Practice each DML command (INSERT, UPDATE, DELETE, SELECT) at least three times with different data sets.
• Test edge cases:
  • Inserting duplicate primary keys (should raise an error).
  • Inserting a NULL foreign key where allowed.
  • Deleting a referenced row – observe the referential‑integrity violation.
• Comment your SQL scripts – clear comments earn marks for presentation.

8. Key Points to Remember (Exam Flashcards)

• Data is stored in **tables**; each table represents one entity.
• A **primary key** guarantees that every row is uniquely identifiable.
• A **foreign key** creates a link between tables and enforces **referential integrity**.
• Relationships are classified as **1:1**, **1:N**, or **M:N** (the latter needs a junction table).
• **Normalization** (1NF → 2NF → 3NF) removes redundancy and prevents update anomalies.
• **SQL** is the language for defining structures (DDL), manipulating data (DML) and querying information (SELECT with various JOINs).
• For the Cambridge exam you must be able to **explain** terminology (AO1), **analyse** a given schema (AO2) and **design/write** appropriate SQL (AO3).

9. Action‑Oriented Review Checklist – Relational Model (AS & A‑Level 9618, 2026)

Checklist Item	Evidence in Notes	Action if Missing
All key terms (PK, FK, domain, normal forms, etc.) defined in Cambridge wording.	Section 1 – complete table.	Add any omitted term.
Formal mathematical definition of a relation.	Section 2 – present.	None.
Clear example showing ER diagram, tables, and cardinalities (1:N, M:N).	Section 3 – includes ER placeholder and tables.	Insert a simple ER diagram sketch if possible.
Step‑by‑step normalisation from UNF → 1NF → 2NF → 3NF.	Section 4 – detailed.	None.
DDL for creating tables, including primary‑key and foreign‑key constraints.	Section 5.1 – present.	None.
DML examples covering INSERT, UPDATE, DELETE and SELECT with INNER & LEFT JOIN.	Section 5.2 – present.	None.
Mapping of notes to AO1, AO2, AO3.	Section 6 – table provided.	None.
Practical exam‑style checklist (test edge cases, comment scripts).	Section 7 – present.	None.
Concise “flashcard” summary of the most exam‑relevant points.	Section 8 – present.	None.

10. Quick Reference Sheet (downloadable PDF)

For last‑minute revision, a one‑page PDF containing the terminology table, normalisation steps, and the core SQL commands can be generated from the notes above. Keep it handy during the exam.