Cambridge Syllabus Notes

8.1 Database Concepts

What is a Relational Database?

Think of a relational database as a giant spreadsheet that can store lots of information in a structured way. Each sheet is called a table, and each row in the sheet is a record (like a single book in a library). Each column is an attribute (like the book’s title, author, or ISBN).

📚 Analogy: Imagine a school library. The Books table lists every book, the Students table lists every student, and the Borrowing table links them together.

Key Terminology

Table – a collection of related data organized in rows and columns.
Row (Tuple) – a single record in a table.
Column (Attribute) – a field that holds a specific type of data.
Primary Key – a column (or set of columns) that uniquely identifies each row.
Foreign Key – a column that creates a link between two tables.
Entity – a real‑world object represented by a table.
Relationship – how two entities are connected (one‑to‑many, many‑to‑many).

Example: Student Database

Table	Primary Key	Foreign Keys	Description
Students	student_id	–	Information about each student.
Courses	course_id	–	Details of each course.
Enrollments	enroll_id	student_id, course_id	Links students to courses.

Relationships & Keys

🔗 One‑to‑Many: One student can enroll in many courses, but each enrollment belongs to one student.

🔗 Many‑to‑Many: Many students can take many courses. We solve this with a linking table (Enrollments).

🗝️ Primary Key: Guarantees uniqueness. Think of it as a student’s unique ID card.

🔗 Foreign Key: Creates the link. It must match a primary key in another table.

Normalization (Keeping Data tidy)

1NF – No repeating groups. Each cell holds a single value.
2NF – All non‑key attributes fully depend on the whole primary key.
3NF – No transitive dependencies. Non‑key attributes depend only on the key.

📐 Why it matters: Reduces duplicate data and prevents update anomalies.

$$ \text{If } A \rightarrow B \text{ and } B \rightarrow C \text{, then } A \rightarrow C \text{ is a transitive dependency.} $$

Entity‑Relationship (ER) Diagrams

🗺️ ER diagrams are visual maps of your database. Entities are boxes, relationships are diamonds, and keys are underlined.

Example: Students –enrolls in–Courses (many‑to‑many via Enrollments).

SQL Basics (The Database Language)

SELECT – retrieve data.
INSERT – add new rows.
UPDATE – modify existing rows.
DELETE – remove rows.

🔍 Example Query: SELECT name, major FROM Students WHERE student_id = 12345;

Exam Tips 📚

Know the definitions of primary key and foreign key.
Practice drawing ER diagrams from simple scenarios.
Write SQL queries that use WHERE, JOIN, and aggregate functions.
Remember the stages of normalisation and why each is important.
Use the analogy of a library to explain tables, rows, and relationships.

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