Databases

What you'll learn

Databases form a critical component of the CIE IGCSE Information and Communication Technology syllabus, covering how organisations store, retrieve and manipulate large quantities of structured data. You must understand database terminology, structure, validation methods, and how to design effective databases for real-world applications. This topic consistently appears across Paper 1 and Paper 2, requiring both theoretical knowledge and practical application skills.

Key terms and definitions

Database — an organised collection of related data stored in a structured format, typically in tables, that can be easily accessed, managed and updated.

Table — a collection of related data organised into rows (records) and columns (fields) within a database.

Record — a complete set of data about one item in a database table, represented as a single row containing values for each field.

Field — a single category of data within a table, represented as a column; each field has a defined data type and properties.

Primary key — a field (or combination of fields) that uniquely identifies each record in a table; no two records can share the same primary key value.

Query — a request to retrieve specific data from one or more database tables based on defined criteria, often using filters and sorts.

Validation — automatic checks applied to data entry to ensure that data meets specific rules before being accepted into the database.

Data type — the classification of data that determines what values a field can hold (e.g. text, number, date/time, Boolean, currency).

Core concepts

Database structure and components

A relational database organises data into tables that can be linked together through common fields. Each table represents a distinct entity (customers, products, orders) and contains:

• Fields (columns): Define what type of information is stored
• Records (rows): Contain the actual data entries
• Field names: Descriptive labels at the top of each column
• Data types: Determine the format and operations allowed for each field

Common data types tested in CIE IGCSE Information and Communication Technology include:

• Text/Alphanumeric: Letters, numbers and symbols (e.g. names, addresses, postcodes)
• Number/Integer: Numeric values for calculations (e.g. quantity, age, price)
• Date/Time: Dates and times in standardised formats
• Currency: Monetary values with appropriate decimal places
• Boolean: True/False or Yes/No values
• Autonumber: Automatically generated sequential numbers, often used for primary keys

Primary keys and relationships

The primary key serves as the unique identifier for each record. Essential characteristics:

• Must contain a unique value for every record
• Cannot be left blank (null)
• Should not change over time
• Can be a single field or combination of fields (composite key)

Examples of suitable primary keys:

• StudentID in a student database (better than using Name, which may duplicate)
• ISBN in a book database (universally unique)
• OrderNumber in an orders database (assigned sequentially)

Foreign keys create relationships between tables by referencing the primary key from another table, enabling relational databases to link related information without duplicating data.

Validation and verification

Validation involves automatic checks performed by the database software to ensure data meets specific criteria:

• Range check: Values must fall within specified limits (e.g. age between 11-18)
• Type check: Data must match the designated data type (e.g. number field rejects text)
• Length check: Limits the number of characters (e.g. telephone number exactly 11 digits)
• Presence check: Ensures required fields are not left empty
• Format check: Data must follow a specific pattern (e.g. postcode format AB12 3CD)
• Lookup check: Values must match entries in a predefined list (e.g. Title must be Mr, Mrs, Ms, Dr)

Verification ensures data is entered accurately through:

• Double entry: Entering the same data twice and comparing for discrepancies
• Visual check: User reviews data on screen against original source document
• Screen display: Showing entered data for user confirmation before saving

Validation prevents incorrect data types and formats; verification prevents transcription errors when copying from source documents.

Queries and searching

Queries extract specific information from databases using search criteria. CIE IGCSE Information and Communication Technology examinations test understanding of:

Simple queries use single criteria:

• Find all customers in London (Field: City, Criteria: London)
• Find products priced over £50 (Field: Price, Criteria: >50)

Complex queries combine multiple criteria using logical operators:

• AND: Both conditions must be true (e.g. Price > 20 AND Price < 50 finds items between £20-£50)
• OR: Either condition can be true (e.g. City = "London" OR City = "Manchester")
• NOT: Excludes records meeting the condition (e.g. NOT Country = "UK")

Wildcard searches use special characters:

• Asterisk () replaces multiple characters (e.g. "Sm" finds Smith, Smythe, Small)
• Question mark (?) replaces a single character (e.g. "B?ll" finds Ball, Bell, Bill)

Sorting arranges records in ascending (A-Z, 0-9, oldest-newest) or descending (Z-A, 9-0, newest-oldest) order based on selected field(s).

Forms, reports and data entry

Forms provide a user-friendly interface for entering and editing data:

Advantages:

• Display one record at a time, reducing confusion
• Can include dropdown lists and validation controls
• Require no database knowledge from users
• Reduce data entry errors through structured input
• Can hide fields that users should not access

Reports present database information in formatted, printable layouts:

Features:

• Group related records together (e.g. sales by region)
• Calculate totals, averages and other statistics
• Include headers, footers and page numbers
• Display selected fields in customised layouts
• Professional appearance suitable for stakeholders

Both forms and reports improve usability for non-technical users and ensure consistent data handling.

Advantages and disadvantages of databases

Advantages:

• Data is stored centrally and can be accessed by multiple users simultaneously
• Reduces data redundancy (duplicate information) through normalisation
• Maintains data consistency across the organisation
• Powerful searching and sorting capabilities for large datasets
• Automated backups and security controls protect information
• Easy to update information once, which changes everywhere it appears
• Can generate complex reports and perform calculations automatically

Disadvantages:

• Expensive to purchase database software and hardware
• Requires specialist staff for database administration and maintenance
• Users need training to use database systems effectively
• Risk of total data loss if backup procedures fail
• Security breaches could expose large quantities of sensitive data
• More complex than simple flat-file systems for small-scale applications

Flat-file vs relational databases

Flat-file databases store all data in a single table. Suitable for:

• Simple applications with one entity type
• Small amounts of data
• No complex relationships between data items

Limitations: Data redundancy, update anomalies, limited flexibility.

Relational databases use multiple linked tables. Required for:

• Complex data with multiple entity types
• Large organisations with extensive data requirements
• Situations requiring data integrity and minimal redundancy
• Applications where multiple users access shared data

CIE IGCSE Information and Communication Technology exams frequently test understanding of when each approach is appropriate.

Worked examples

Example 1: Choosing appropriate data types and validation

Question: A school database includes a Students table. For each field listed, identify the most appropriate data type and suggest one validation rule.

a) DateOfBirth b) YearGroup c) AuthorisedForTrips

Answer:

a) DateOfBirth

• Data type: Date/Time
• Validation rule: Range check to ensure date is between 11 and 18 years ago from current date (students must be school age)

b) YearGroup

• Data type: Number/Integer (or Text if using format "Year 7")
• Validation rule: Lookup check limiting values to 7, 8, 9, 10, 11 (the valid year groups in the school)

c) AuthorisedForTrips

• Data type: Boolean/Yes-No
• Validation rule: Presence check to ensure field is not left blank (every student must have this specified)

Example 2: Designing a query

Question: A library database contains a Books table with fields: BookID, Title, Author, Genre, YearPublished, CopiesAvailable.

Write the query criteria to find all Crime fiction books published after 2015 that currently have copies available.

Answer:

Field	Criteria
Genre	Crime
YearPublished	>2015
CopiesAvailable	>0

This query uses AND logic because all three conditions must be true simultaneously. The database will return only records matching all criteria. (3 marks: 1 mark for each correct criterion)

Example 3: Explaining database advantages

Question: A medical practice currently stores patient records in paper files. The practice manager suggests creating a database system. Give three advantages of using a database compared to paper-based records. [3 marks]

Answer:

1. Faster searching and retrieval — staff can find patient records instantly by entering name or ID number, rather than manually searching through filing cabinets. (1 mark)

2. Multiple simultaneous access — several doctors and nurses can view different patient records at the same time, whereas paper files can only be in one location. (1 mark)

3. Automated backup copies — the database can be automatically backed up to prevent data loss, whereas paper records could be destroyed by fire or flood with no recovery option. (1 mark)

(Other acceptable answers: easier to update information, reduced storage space, ability to generate appointment reports, mail merge for appointment reminders, better security through password protection)

Common mistakes and how to avoid them

• Confusing validation with verification — Remember: validation is automatic checking by the software (e.g. range check, type check), while verification is human confirmation that data was entered correctly (e.g. double entry, visual check). Validation prevents unreasonable data; verification prevents typing errors.

• Suggesting Name as a primary key — Names duplicate frequently and can change (marriage, deed poll), making them unsuitable as primary keys. Always suggest a unique identifier like StudentID, CustomerNumber or ProductCode that will never change and cannot duplicate.

• Using AND when OR is needed, and vice versa — If a question asks for records meeting "either" condition, use OR. If records must meet "both" conditions simultaneously, use AND. Practice identifying the difference in past paper questions.

• Describing field properties instead of data types — When asked for a data type, answer with Text, Number, Date/Time, Boolean or Currency. Field properties (like field size or validation rules) are different concepts and will not earn marks.

• Providing vague advantages without context — Avoid generic statements like "databases are more efficient." Instead, explain specifically how: "A database allows instant searching by postcode, whereas manual filing would require checking each paper record individually, saving significant staff time."

• Confusing reports with forms — Forms are for data entry and editing (input), while reports are for presenting formatted information (output). Forms show one record at a time; reports typically show multiple records with grouping and calculations.

Exam technique for Databases

• Command word "State" typically requires brief factual answers (1-2 words or short phrases). For "State a suitable data type for Field X," simply write "Number" or "Date/Time" — no explanation needed.

• Command word "Describe" or "Explain" requires detailed responses showing understanding. For validation rules, state the rule type AND what it checks: "Range check ensures age values are between 11 and 18" earns marks, whereas "Range check" alone may not.

• Advantage/disadvantage questions award marks for making a point and developing it with context. Structure as: [Advantage] + [because/this means/allowing] + [specific benefit]. Example: "Multiple users can access the database simultaneously, allowing the sales team and accounts department to view customer records at the same time without conflicts."

• Query questions may present tables of data or ask you to complete a query design grid. Show all criteria clearly, use correct operators (>, <, =, AND, OR), and ensure wildcards are positioned correctly. Partial credit is available for mostly-correct queries, so attempt all parts even if uncertain.

Quick revision summary

Databases store structured data in tables containing records (rows) and fields (columns). Each field has a data type (text, number, date/time, Boolean, currency) and may have validation rules (range, type, length, presence, format, lookup) to ensure data quality. Primary keys uniquely identify records and cannot be null. Queries extract specific information using criteria and logical operators (AND, OR, NOT). Forms facilitate data entry; reports present formatted output. Relational databases link multiple tables through foreign keys, reducing redundancy. Databases offer advantages (fast searching, multi-user access, reduced redundancy, automated backups) but require significant investment and training. Understanding when validation versus verification is appropriate, and distinguishing between flat-file and relational structures, are essential for CIE IGCSE Information and Communication Technology examinations.