9.1 Databases

Relational Database Concepts

1. Flat File vs. Relational Databases

A database is a structured collection of data. There are two primary ways to organize this data:

Flat File Database

Stores all data in a single table or file.

  • Redundancy: Data is often repeated.
  • Errors: High risk of data entry mistakes.
  • Storage: Inefficient for large amounts of data.

Relational Database

Splits data into multiple tables linked by common fields.

  • Integrity: Reduces data redundancy.
  • Efficiency: Faster to search and update.
  • Security: Easier to control access to specific tables.

2. Key Database Terminology

Term Definition
Table The overall structure containing data about one specific "entity" (e.g., Customers).
Record (Row) A collection of data about one item (e.g., One specific customer).
Field (Column) A single category of data (e.g., "Surname" or "DateOfBirth").
Primary Key A unique field that uniquely identifies each record in a table.
Foreign Key A primary key from one table used in another table to create a relationship.

3. Database Data Types

Database systems use specific data types to ensure data is stored correctly:

  • Text / Alphanumeric: Stores words, symbols, and numbers not used for calculations (e.g., Phone Numbers).
  • Number (Integer/Decimal): Stores numeric values used in math.
  • Date / Time: Stores dates in a specific format (e.g., DD/MM/YYYY).
  • Boolean / Logical: Stores Yes/No or True/False values.
  • Currency: Automatically formats numbers with a currency symbol.

4. Single Table Design Example

Below is a design for a Books table. The BookID is the Primary Key.

BookID (PK) Title Author Price InStock
B001 The Great Gatsby F. Scott Fitzgerald 12.99 Yes
B002 1984 George Orwell 10.50 No
B003 The Hobbit J.R.R. Tolkien 15.00 Yes
⚠️ Exam Tip: When asked why a Primary Key is needed, always say: "To uniquely identify each record in a table and prevent duplicate data."

SQL Basics

1. The Foundation Table: Products

We will use this sample table for all SQL examples below:

ProdID Name Category Price StockLevel
P01LaptopHardware800.0015
P02MouseHardware25.0050
P03AntivirusSoftware40.00100
P04MonitorHardware150.005

2. Basic Data Retrieval

SELECT and FROM

Retrieves specific columns from a table.

SELECT Name, Price
FROM Products;
WHERE (Filtering)

Filters records based on a condition.

SELECT *
FROM Products
WHERE Price > 100.00;

3. Sorting and Multiple Conditions

ORDER BY

Sorts results in ascending (ASC) or descending (DESC) order.

SELECT Name, StockLevel
FROM Products
WHERE Category = 'Hardware'
ORDER BY StockLevel DESC;

4. SQL Aggregate Functions

These functions perform calculations on a set of values and return a single result.

SUM

Calculates the total of a numeric column.

SELECT SUM(StockLevel)
FROM Products;

COUNT

Counts the number of records that meet a criteria.

SELECT COUNT(*)
FROM Products
WHERE Price < 50.00;

5. Combining it All (The Exam Special)

A typical exam question might ask you to find the name and price of Hardware items, sorted by the most expensive first:

SELECT Name, Price
FROM Products
WHERE Category = 'Hardware'
ORDER BY Price DESC;
⚠️ Exam Note: When writing SQL for IGCSE:
  • Use * to select all columns.
  • Text values in the WHERE clause must be in single quotes (e.g., 'Software').
  • The order of clauses matters: SELECT ➔ FROM ➔ WHERE ➔ ORDER BY.