What are the most common mistakes in Databases?

Mixing up records and fields: Records are rows; fields are columns. Storing phone numbers as integers: The leading 0 is lost; use text. Using OR when AND is needed: Check whether both conditions must apply.

Where can I practise Databases questions for free?

Kramizo has free CIE IGCSE Computer Science practice questions on Databases, each marked instantly with a full explanation. No card is required.

Databases — CIE IGCSE Computer Science Revision Notes

Q: What is Database?

Database — A structured, persistent collection of data stored so that it can be easily searched and updated.

What you'll learn

A database is a structured way of storing data so that it can be searched, sorted and updated efficiently. This overview covers the databases unit of CIE IGCSE Computer Science (0478): single-table databases, tables, records and fields, choosing suitable data types, primary keys, validation in databases, and writing SQL scripts using SELECT, FROM, WHERE, ORDER BY, SUM and COUNT, together with the operators AND and OR. It works through the kinds of tasks set on Paper 2, where you design a table, choose data types and write or complete SQL queries.

Database questions are precise: a field needs exactly the right data type, a query needs the correct field names and operators, and results must be in the right order. Practise by writing queries for sample tables and predicting their output.

Key terms and definitions

Database — A structured, persistent collection of data stored so that it can be easily searched and updated.

Table — A collection of related records about one type of thing.

Record — All the data about one item; a row in a table.

Field — One item of data about each record; a column in a table.

Data type — The kind of data a field holds: text/alphanumeric, character, Boolean, integer, real, date/time.

Primary key — A field that uniquely identifies each record in a table.

Validation — Checks made on data as it is entered to ensure it is reasonable.

SQL (Structured Query Language) — A language used to manage and query databases.

Query — A request to retrieve data that matches given criteria.

Core concepts

Why use a database?

Databases store large amounts of data in an organised way. They allow data to be searched quickly, sorted, updated in one place, validated as it is entered, shared by many users and protected by access controls. Examples include school records, library catalogues, stock systems and customer records.

Tables, records and fields

A single-table database stores all its data in one table. Consider a table Books:

BookID	Title	Author	Year	Price	InStock
B001	The River	Okafor	2019	8.99	TRUE
B002	Night Sky	Patel	2021	12.50	FALSE
B003	Deep Water	Lee	2017	6.75	TRUE

Each row is a record (one book). Each column is a field (an attribute such as Title). The table has 3 records and 6 fields.

Data types

Data type	Use	Example
Text/alphanumeric	Letters, numbers and symbols not used in calculations	Title, BookID, telephone number
Character	A single character	Grade "A"
Boolean	One of two values	InStock TRUE/FALSE
Integer	Whole numbers	Year, Quantity
Real	Numbers with decimals	Price
Date/time	Dates and times	DateBorrowed

Choosing correctly matters. Telephone numbers are stored as text because they may start with 0 and are not used in calculations. A price needs real (or currency) so that decimals are stored. A date field allows dates to be sorted and compared correctly.

Primary keys

A primary key uniquely identifies each record. In the Books table, BookID is the primary key: no two books share the same ID. Names and titles are unsuitable because they may be duplicated. A good primary key is unique, always present and does not change.

Validation in databases

Validation rules can be set on fields so that only reasonable data is accepted:

range check — Year between 1900 and the current year;
length check — BookID exactly four characters;
type check — Price must be a number;
presence check — Title cannot be blank;
format check — BookID must be a letter followed by three digits.

Validation reduces errors but cannot guarantee data is correct.

SQL: retrieving data

SELECT lists the fields to show; FROM names the table; WHERE sets the criteria; ORDER BY sorts the results.

SELECT Title, Author
FROM Books
WHERE Price < 10
ORDER BY Title;

Output: Deep Water, Lee; The River, Okafor (sorted alphabetically by title).

SELECT * returns all fields. ORDER BY sorts in ascending order by default; add DESC for descending:

SELECT Title, Year
FROM Books
ORDER BY Year DESC;

Using AND and OR

SELECT Title
FROM Books
WHERE InStock = TRUE AND Year > 2018;

returns only books meeting both conditions (The River).

SELECT Title
FROM Books
WHERE Author = "Patel" OR Author = "Lee";

returns books meeting either condition (Night Sky, Deep Water).

Text values are written in quotation marks; numbers are not.

SUM and COUNT

SUM adds the values in a numeric field:

SELECT SUM(Price)
FROM Books
WHERE InStock = TRUE;

This returns 8.99 + 6.75 = 15.74.

COUNT counts the records that match:

SELECT COUNT(*)
FROM Books
WHERE Year < 2020;

This returns 2 (The River and Deep Water).

Comparison operators

= (equal), <> (not equal), <, <=, >, >=. For example, WHERE Year <> 2021 excludes books from 2021.

Designing a table step by step

Exam questions sometimes describe a situation and ask you to design the table. Work through these steps.

Identify the entity. What is each record about? For a sports club, each record is a member.
List the fields. What needs to be stored about each member? MemberID, FirstName, LastName, DateOfBirth, Phone, MembershipType, FeesPaid.
Choose a data type for each field.

Field	Data type	Reason
MemberID	Text	Code such as M0042; not used in calculations
FirstName, LastName	Text	Letters
DateOfBirth	Date/time	Allows sorting and age calculation
Phone	Text	May start with 0; not calculated
MembershipType	Character	A single code such as J (junior) or S (senior)
FeesPaid	Boolean	Either paid or not paid

Choose the primary key. MemberID, because it is unique for every member.
Add validation. A format check on MemberID (letter M followed by four digits), a presence check on LastName, a lookup or range check on MembershipType, and a range check on DateOfBirth so members are a plausible age.

Presenting a design as a table like this makes your reasoning clear and is quick to mark.

Reading queries carefully

Many marks are lost by misreading conditions. Use the Books table to practise predicting results:

Query condition	Records returned
`WHERE Year >= 2019`	The River, Night Sky
`WHERE Year > 2019`	Night Sky
`WHERE InStock = FALSE`	Night Sky
`WHERE Price <= 8.99`	The River, Deep Water
`WHERE Author <> "Lee"`	The River, Night Sky

Notice the difference between > and >=: one record changes the result. Check each record against the condition one at a time.

Databases in everyday systems

A school's database lets staff find every student in a form group instantly, count how many have paid for a trip, and sort a class list alphabetically for a register. A shop's stock database can list every product below its reorder level and total the value of stock held. A library can list overdue books ordered by due date. Each of these is a SELECT query with a WHERE condition, sometimes combined with ORDER BY, COUNT or SUM — exactly the skills this unit tests.

Worked examples

Example 1: Identifying parts of a table

In the Books table, state the number of fields, the number of records and the primary key.

Answer: 6 fields; 3 records; BookID.

Example 2: Choosing data types

Suggest data types for: Title, Year, Price, InStock.

Answer: Title — text; Year — integer; Price — real; InStock — Boolean.

Example 3: Writing SQL

Write an SQL query to show the Title and Price of all books by Okafor, sorted by Price from highest to lowest.

SELECT Title, Price
FROM Books
WHERE Author = "Okafor"
ORDER BY Price DESC;

Example 4: Predicting output

What does this query return?

SELECT COUNT(*)
FROM Books
WHERE Price > 7 AND InStock = TRUE;

Answer: Only The River (8.99, in stock) meets both conditions, so the result is 1.

Common mistakes and how to avoid them

Mixing up records and fields. Records are rows; fields are columns.
Storing phone numbers as integers. The leading 0 is lost; use text.
Forgetting quotation marks around text in WHERE conditions.
Using OR when AND is needed. Check whether both conditions must apply.
Forgetting DESC when the question asks for descending order.
Using SUM to count records. SUM adds values; COUNT counts records.
Choosing a non-unique field as the primary key.

Exam technique for "Databases"

Read the table carefully and use field names exactly as written.
Write SQL keywords in capitals and on separate lines for clarity: SELECT, FROM, WHERE, ORDER BY.
When predicting output, check each record against every condition.
When justifying data types, give a reason linked to the data, such as "real, because prices have decimal places".

Quick revision summary

A single-table database: table → records (rows) → fields (columns).
Data types: text/alphanumeric, character, Boolean, integer, real, date/time.
Primary key: unique identifier for each record.
Validation: range, length, type, presence, format.
SQL: SELECT fields FROM table WHERE condition ORDER BY field [DESC];
Combine conditions with AND (both) and OR (either); text in quotes.
SUM(field) adds values; COUNT(*) counts matching records.

Databases