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)

2. Formal Structure of a Relation

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

\[

R = \{ (a1, a2, \dots , an) \mid ai \in D_i \}

\]

• R – the relation (i.e. the table name).
• ai – the value of attribute Ai in a particular row.
• Di – the domain of attribute Ai.

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

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.

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)

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)

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.