9.Databases

By the end of this lesson, you will be able to:

• Explain what a database is and describe why databases are useful for storing, retrieving, sorting and searching data.
• Identify fields and records and distinguish a field (column) from a record (row).
• Describe a flat-file/single-table database and recognise how it differs from a relational database.
• Suggest suitable basic data types for database fields, including text/alphanumeric, character, Boolean, integer, real, date/time and currency.
• Identify a suitable primary key and explain how a foreign key can link tables.
• Explain and apply validation and verification checks, including length, format, range, type, presence, check digit, visual and double-entry checks.
• Read, understand and complete SQL statements using SELECT, FROM, WHERE, AND, OR, LIKE, ORDER BY, SUM and COUNT.
• Work through database questions, predict SQL output and choose appropriate fields, data types and keys.

• Database- an organised, structured collection of data that can be stored and managed efficiently.
• Table- a collection of records with a similar structure.
• Field- one piece of information about a person, item or object; shown as a column.
• Record- a collection of related fields about one person, item or object; shown as a row.
• Flat-file / single-table database- a database where data is stored in a table or separate tables, with each table made from records and fields; the IGCSE notes focus on single-table databases.
• Relational database- a database made from multiple related tables; the notes include it for awareness beyond the basic IGCSE single-table focus.
• Data type- the kind of data that a field can hold.
• Primary key- a unique field used to identify each record in a table.
• Foreign key- a field that refers to the primary key in another table so tables can be linked.
• Validation- a set of rules controlling what data can be entered into a field.
• Verification- a check that confirms entered data is correct and matches the intended information.
• SQL- Structured Query Language, used to interact with a DBMS and query/manipulate database data.
• DBMS- a database management system used to work with databases.
• Wildcards- special matching symbols; the notes show * for all columns and % with LIKE for pattern matching.
• Aggregation- using functions such as SUM and COUNT to calculate totals or counts.
• Secondary storage- storage on which database and text-file information can be stored so it remains available when an application is closed.

A database is an organised collection of data. The notes describe databases as structured, persistent collections that allow data to be arranged and stored in tables for querying and manipulation.

• Databases are useful when working with large amounts of data.
• Database information is stored on secondary storage.
• A database may be stored on a remote server so multiple users can access it at the same time, which is useful for online systems.
• Data can be sorted and searched efficiently using database structures.
• The notes state that databases are more secure than text files.

Fields, records and text files

• A field is one piece of information relating to one person, item or object and is represented by a column.
• A record is a collection of fields relating to one person, item or object and is represented by a row.
• A text file is useful when working with small amounts of data. Text files are stored on secondary storage and read into a program when needed.
• Text files keep information when the application is closed.
• Each entry can be stored on a new line or separated by a special identifier such as a comma.
• It can be difficult in a text file to know exactly where a record begins and ends.

Single-table databases

• A single-table database is a simple form of database where all the data is stored in a single table.
• A flat-file database consists of one or more tables.
• Each table has a number of records (rows).
• Each record has a number of separate fields (columns).
• Records in the same table have the same structure.
• Each field has a defined data type.
• Each table should have a primary key so each record can be uniquely identified.
• Database table fields can have validation rules such as length, format, range, presence and custom rules.

Data types used in database fields

• Text/alphanumeric — text data.
• Character — one character.
• Boolean — true/false values (or Yes/No in the database software example).
• Integer — a whole number.
• Real — a decimal number.
• Date/time — a date or time value.
• The notes also show Currency as a type in database software.

Primary keys

• A primary key is a unique field that identifies a record in a table.
• Each record must be uniquely identifiable, so no two records should have the same primary-key value.
• A primary key supports data integrity and efficient data retrieval.
• A primary key can also be used to establish relationships between tables.
• Sometimes one field in the existing data can be used; in other situations an additional field must be created.

Validation

• A validation rule controls what data can be entered into a field.
• Validation helps maintain data integrity and prevents invalid input from causing incorrect results or system problems.
• The notes list length, format, range, presence and custom rules such as Apprentice, Expert or Master.
• Validation can be assigned to any field of a table.

Verification

• Verification checks whether entered data is correct, valid and consistent with the intended information.
• Visual check — a person manually reviews the entered data. The notes give a bank-teller checking a customer signature as an example.
• Double-entry check — the same data is entered twice independently and the entries are compared. The notes give entering an email address twice when creating an account as an example.

When setting up a database, each field must be given an appropriate data type. The notes show groups such as Alphanumeric (Text, Memo), Numeric (Number, Currency, etc.), Date/Time and Boolean (Yes/No).

The source image uses Microsoft Access as an example of database software where each field is assigned a data type.

The primary key in a relational database uniquely identifies each record (row) in a table. The notes emphasise that no two records should have the same key value.

• A primary key is a unique identifier for each record.
• It helps maintain data integrity.
• It supports efficient retrieval of records.
• It can provide a way to establish relationships between tables.
• A suitable key can be generated manually or an additional field can be created when no existing field is unique.

Foreign keys

• A foreign key is a column or group of columns in a relational database that provides a link between data in two tables.
• It refers to the primary key of another table.
• It acts as a cross-reference so related records can be connected.
• The notes illustrate an ArtistID primary key in an Artists table and an ArtistID foreign key in an Albums table, and also a DirectorID relationship between Directors and Movies.

SQL (Structured Query Language)is a programming language used to interact with a DBMS. The notes explain that database information is stored in records, each record can contain fields, and SQL is used to manipulate and query that data.

• The notes state that SQL can select data, order data, sum data and count data.
• SQL statements can be used to create, delete, modify and manipulate records, but for IGCSE the SELECT statement is the key focus in the notes.
• The notes show a strict pattern for SELECT statements: SELECT, FROM, WHERE and optional ORDER BY.

SELECT <field(s)>
FROM <table>
WHERE <condition>
ORDER BY <field> ASC

SQL commands and operators in the notes

SQL walkthrough: world database examples

SELECT population
FROM world
WHERE name = "Germany"

SELECT population
FROM world
WHERE name = "Albania"

SELECT name, continent, area, population, gdp, capital
FROM world
WHERE name = "Algeria"

SELECT name, continent, area, population, gdp, capital
FROM world
WHERE name LIKE "A%" AND population > 10000000
ORDER BY name ASC

SELECT COUNT(*)
FROM world
WHERE continent = "Asia"

SELECT SUM(population)
FROM world
WHERE continent = "Europe"

Worked Example — tbl_animals

SELECT Animal
FROM tbl_animals
WHERE Breeding = "Yes" AND Number of Young = 1

SQL examples using a Customers table

SELECT * FROM Customers;

SELECT ID, name, age
FROM Customers
WHERE Age > 25;

SELECT Name, Country
FROM Customers
WHERE Country LIKE 'U%';

SELECT *
FROM Customers
WHERE City = 'London' OR City = 'Paris';

SELECT *
FROM Customers
ORDER BY age DESC;

Creating and inserting tables shown in the notes

CREATE TABLE Students (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Gender VARCHAR(10),
    Grade INT
);

INSERT INTO Students (StudentID, Name, Age, Gender, Grade) VALUES
(1, 'Nuriya', 15, 'Female', 95),
(2, 'Artur', 15, 'Male', 68),
(3, 'Danial', 15, 'Male', 70),
(4, 'Inaaya', 15, 'Female', 95),
(5, 'Harsh', 15, 'Male', 90),
(6, 'Ivan', 15, 'Male', 80),
(7, 'Eker', 15, 'Male', 65),
(8, 'Beiza', 15, 'Female', 81),
(9, 'Nicholas', 15, 'Male', 66),
(10, 'Darius', 15, 'Male', 62),
(11, 'Samina', 15, 'Female', 90),
(12, 'Emir', 15, 'Male', 95);

SELECT Name, Age
FROM Students
WHERE Gender = 'Female' AND Grade > 90
ORDER BY Age;

SELECT COUNT(*)
FROM Students
WHERE Gender = 'Male';

SELECT SUM(Grade)
FROM Students;

CREATE TABLE Sales (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    Category VARCHAR(50),
    QuantitySold INT,
    UnitPrice DECIMAL(10, 2)
);

INSERT INTO Sales (ProductID, ProductName, Category, QuantitySold, UnitPrice) VALUES
(1, 'Laptop', 'Electronics', 10, 800.00),
(2, 'Smartphone', 'Electronics', 20, 500.00),
(3, 'Desk Chair', 'Furniture', 5, 120.00),
(4, 'LED TV', 'Electronics', 15, 1000.00),
(5, 'Coffee Table', 'Furniture', 8, 150.00);

SELECT SUM(QuantitySold) AS TotalQuantity, COUNT(*) AS QuantitySold
FROM Sales;

SELECT country, SUM(QuantitySold) AS TotalQuantity
FROM Sales
JOIN Customers ON Sales.ProductID = Customers.customer_id
WHERE country = 'UK'
GROUP BY country;

SELECT * FROM Customers;

SELECT Name, Country
FROM Customers
WHERE Country LIKE 'U%';

SELECT Name, Age
FROM Students
WHERE Gender = 'Female' AND Grade > 90
ORDER BY Age;

The source notes finish with ten database past-paper style question sets and answers. They practise field/record counting, primary keys, data types, SQL queries and predicting outputs.

SELECT TVCode, ScreenSize, Price$
FROM TVRange
WHERE SmartTV = YES

SUM (Minutes) FROM Songs WHERE Genre = "rock";
COUNT (Title) FROM Songs WHERE Genre = "rock";

SELECT Name, NumberGuest, Rate$
FROM Site1
WHERE NumberGuest >= 10;

SELECT CatNo, Title, Author
FROM BookList
WHERE StockLevel = 0;

SELECT Title
FROM BookList
WHERE Author = "B Penn";

SELECT CatNo, Title
FROM 2018MOV
WHERE Genre = "Comedy";

SELECT StudentName
FROM MARKS
WHERE Maths < 30;

SELECT Town, TourDate, NumberOfSeats
FROM THEATRETOURS
WHERE NumberOfSeats = 125;

SELECT PCID, ScreenSize, RAM, Type, HDD(GB), Price
FROM PCSTOCK
WHERE HDD(GB) = 500;

• Database = organised collection of data; tables contain fields (columns) and records (rows).
• Single-table/flat-file databases are the main IGCSE focus; relational databases use multiple related tables.
• Choose field data types to match the stored data: text, character, Boolean, integer, real, date/time and currency where appropriate.
• A primary key must uniquely identify every record; if no existing field is unique, add a suitable identifier.
• A foreign key refers to another table’s primary key to create a relationship.
• Validation controls what can be entered: length, format, range, type, presence, check digit and custom rules.
• Verification checks entered data: visual check and double-entry check.
• SQL SELECT follows SELECT → FROM → optional WHERE → optional ORDER BY.
• Use AND when all conditions must be true; use OR when at least one condition can be true.
• Use LIKE with % for pattern matching; * selects all columns.
• SUM totals numeric values; COUNT counts matching rows.
• In exam questions, show the exact requested fields and output in the correct order.