Chapter 7: Databases

Organised Intelligence: Structuring, Searching, and SQL

Step 1: Anatomy of a Database

Before we can use data, we have to organize it. In the IGCSE, you must be able to navigate a table like a map.

The Table: The entire container for a specific subject (e.g., Staff Table).
The Record: A horizontal row. it represents one single entity (e.g., one specific teacher).
The Field: A vertical column. It represents one category of data (e.g., Phone Numbers).

Lab 1: The Table Inspector Can you spot the difference? Click on the correct part of the database to answer the questions.

Step 2: Choosing the Right "Bucket" (Data Types)

Computers are picky. If you try to do math on a "Text" field, the program will crash. As a database architect, you must choose the correct Data Type for every field.

Step 3: The Primary Key (The Identity Card)

Every record needs a "DNA Fingerprint"—something that is unique to that row and nothing else. This is the Primary Key.

Bad Primary Key: FirstName (Two people can be named "John").
Good Primary Key: StudentID or PassportNumber (No two people share these).

Lab 2: The Primary Key Auditor Below is a list of fields for a "Hospital Database." Drag the "Primary Key" badge to the only field that is guaranteed to be unique.

Step 4: Relational Databases

A Relational Database is a collection of tables that are linked together. This is much more efficient than one big table.

🗝️ The Link: Foreign Keys

To connect two tables, we take the Primary Key from one table and drop it into the second table. In the second table, it is known as a Foreign Key.

Primary Key: The unique ID in the "Home" table.
Foreign Key: The link used in the "Related" table.

🏫 Example: The School System

Table 1: Students (StudentID, Name, Age).
Table 2: Grades (GradeID, Subject, Score, StudentID).

By putting the StudentID in the Grades table, we can see exactly which student got which grade without having to type the student's name and age a hundred times!

Relational Databases & Foreign Keys

Most professional databases aren't just one single table. Storing everything in one place causes Data Redundancy (where the same data is repeated unnecessarily), which wastes space and leads to errors.

To solve this, we use a Relational Database—a collection of tables linked together by common fields.

🗝️ The Bridge: Primary vs. Foreign Keys

To link two tables, we need a "bridge." This bridge is built using two types of keys:

Primary Key (PK): The unique identifier in the "Home" table (e.g., CustomerID).
Foreign Key (FK): When that same CustomerID appears in another table (like the Orders table) to create a link, it is called a Foreign Key.

Why bother with relationships?

Data Integrity: If a customer changes their address, you only have to update it in one place (the Customer table), and every linked order is automatically correct.
Reduced Redundancy: You don't have to re-type a customer’s name and phone number every time they buy something.
Security: You can restrict access to sensitive tables while still allowing users to see linked data.

🎓 Exam Tip: Defining the Relationship

In the IGCSE exam, you are often asked to define the relationship between two tables. Most relationships are One-to-Many.

Example: One Author can write Many books.
Example: One Customer can place Many orders.

Step 5: Structured Query Language (SQL)

Now that our tables are linked and our data types are set, we need a way to talk to the database. SQL (Structured Query Language) is the universal language used to search, filter, and sort database records.

Think of an SQL query like a digital filter. Instead of scrolling through thousands of rows, you ask the database to extract and display exactly what you need.

The "Big Four" Syntax Rule

To score full marks on your Paper 2 exam, you must memorize the statements in this exact sequence:

`SELECT`: Which fields (columns) do you want to see? (Use `*` to see all fields).
`FROM`: Which database table are we searching inside?.
`WHERE`: What are the search criteria? (e.g., `Price > 10`).
`ORDER BY`: How should the results be sorted? (Use `ASCENDING` or `DESCENDING`).

💡 Exam Tip: The syllabus also requires you to know `AND` / `OR` to combine criteria, along with the functions `SUM` (adds values up) and `COUNT` (counts the total rows).

🔎 Code Example: How It Works

Imagine we have a single table named `Movies` containing columns for Title, Genre, Rating, and ReleaseYear.

If an exam question asks you to:

"Show the Title and Rating of all Action movies, sorted from the highest rating to the lowest."

Your exact SQL query would look like this:
SELECT Title, Rating
FROM Movies
WHERE Genre = "Action"
ORDER BY Rating DESCENDING;

💡 Pro Searcher Tips

To master the IGCSE exam, remember these three search rules:

1. The "Not" Rule: If you want to find everything except Horror movies, use the "not equal to" sign: WHERE Genre <> 'Horror'.

2. The Double Filter: You can combine rules using AND. For example, to find cheap movies that are also highly rated: WHERE Price < 5 AND Rating > 8.

3. Wildcards: Sometimes you don't know the full name. In many SQL versions (like MS Access used in exams), we use LIKE with a * or % to find partial matches.

Additional AQA SQL Operations

While Cambridge covers retrieving data (SELECT, FROM, WHERE, ORDER BY), AQA also requires you to know how to modify data within a database using three extra SQL commands:

INSERT INTO: Used to add a new record (row) into a table.
- Example: INSERT INTO students (StudentID, student_name, floors) VALUES (105, 'Ali', 2);

UPDATE: Used to modify existing records in a table. It is almost always used alongside a WHERE clause to avoid changing every row in the table.
- Example: UPDATE students SET floors = 3 WHERE StudentID = 101;

DELETE: Used to remove records from a table. Like UPDATE, you typically use it with a WHERE clause to target a specific row.
- Example: DELETE FROM students WHERE StudentID = 101;

SQL Exam Practice

Now that you've used the sandbox, let's test your SQL skills with some typical exam-style questions. Remember the four keywords: SELECT, FROM, WHERE, ORDER BY!

📝 Your Task: The Music Database

Use the Songs table (which has fields like Title, Artist, Genre, Year, LengthSeconds) to answer the following questions.

© 2026 The CS Lab | [Legal & Privacy Information] The CS Lab is an independent resource. iGCSE is a registered trademark of Cambridge University Press & Assessment. This site is not affiliated with, endorsed by, or connected to any official exam board.