What you'll learn
This revision guide covers all database concepts required for the Pearson Edexcel International IGCSE Computer Science specification. You'll learn how databases store and retrieve information, understand their structure and components, and know when to use databases instead of flat files. This topic appears regularly in both Paper 1 and Paper 2, particularly in questions about data management and information systems.
Key terms and definitions
Database — a structured collection of data organised for efficient storage, retrieval and manipulation
Table — a collection of related data organised in rows and columns within a database
Record — a single row in a table containing all the data about one entity (e.g., one customer, one product)
Field — a single column in a table that holds one type of data (e.g., surname, date of birth, price)
Primary key — a field (or combination of fields) that uniquely identifies each record in a table
Query — a request to retrieve specific data from a database based on defined criteria
Data validation — automatic checks applied to data entry to ensure it meets specified rules
Foreign key — a field in one table that links to the primary key in another table, creating a relationship
Core concepts
Database structure and components
A database consists of one or more tables, each containing related information. Tables use rows and columns to organise data systematically.
Each table represents an entity type. For example:
- A school database might have separate tables for Students, Teachers, and Classes
- An online store database might include tables for Customers, Products, and Orders
Fields define what type of information each column stores:
- Field name (e.g., CustomerID, Surname, DateOfBirth)
- Data type (e.g., text, number, date, boolean)
- Field properties (e.g., maximum length, required/optional)
Records contain the actual data values. Each record in a Students table represents one student, with fields for their ID, name, year group, and tutor group.
Example structure:
| StudentID | Surname | FirstName | YearGroup | TutorGroup |
|---|---|---|---|---|
| 10234 | Ahmed | Zara | 11 | 11B |
| 10235 | Chen | Li | 10 | 10A |
| 10236 | Patel | Ravi | 11 | 11B |
Primary keys and data integrity
Every table requires a primary key to ensure each record can be uniquely identified. The primary key must be:
- Unique — no two records can have the same primary key value
- Not null — every record must have a value in this field
- Unchanging — the value should remain constant throughout the record's lifetime
Common primary key approaches:
- Natural keys using existing data (e.g., National Insurance number, ISBN for books)
- Artificial keys created specifically as identifiers (e.g., CustomerID, ProductCode)
Artificial keys are often preferred because:
- They guarantee uniqueness without relying on external data
- They avoid issues if natural identifiers change
- They're typically shorter and more efficient for the database system
Databases vs flat file systems
A flat file stores all data in a single table or text file. This works adequately for simple applications but creates problems as data complexity increases.
Advantages of databases over flat files:
Reduced redundancy
- Data appears once instead of being duplicated
- Updates only need to occur in one location
- Storage space is used more efficiently
Data integrity
- Validation rules ensure data meets requirements before entry
- Relationships between tables maintain consistency
- Primary and foreign keys prevent orphaned records
Complex queries
- Retrieve data from multiple tables simultaneously
- Sort, filter and combine data flexibly
- Generate reports based on specific criteria
Security and access control
- Different users can have different permission levels
- Sensitive data can be restricted to authorised personnel
- Activity can be logged and audited
Concurrent access
- Multiple users can access data simultaneously
- Record locking prevents conflicting changes
- Backup and recovery systems protect against data loss
Example comparison:
A flat file storing library loans might repeat the borrower's name, address, and phone number for every book they borrow. A database would store borrower details once in a Members table, then link to a Loans table using the MemberID as a foreign key.
Relationships between tables
Relational databases link tables together through relationships, eliminating data duplication.
One-to-many relationships are most common:
- One customer can place many orders
- One teacher can teach many classes
- One author can write many books
The relationship is created using a foreign key:
- The "many" table includes a field storing the primary key from the "one" table
- In an Orders table, CustomerID acts as a foreign key linking to the Customers table
- This allows orders to be associated with customer details without repeating the name and address for each order
Many-to-many relationships require a linking table:
- Many students attend many classes
- A StudentClasses table would contain StudentID and ClassID pairs
- This resolves the many-to-many into two one-to-many relationships
Queries and data retrieval
A query extracts specific information from a database. Queries can:
- Select specific fields to display
- Filter records meeting certain criteria
- Sort results in ascending or descending order
- Perform calculations on numeric data
- Combine data from multiple tables
Query criteria examples:
- YearGroup = 11 (finds all Year 11 students)
- Price > 50 (finds products costing more than £50)
- DateOfBirth > 01/09/2006 (finds students born after this date)
- Surname LIKE "A*" (finds surnames beginning with A)
Queries can use logical operators:
- AND — both conditions must be true (YearGroup = 11 AND TutorGroup = "11B")
- OR — either condition can be true (Subject = "Computer Science" OR Subject = "ICT")
- NOT — excludes matching records (NOT YearGroup = 7)
Data validation in databases
Data validation checks that entered data is reasonable, allowable and sensible. Validation catches errors before they corrupt the database.
Common validation types:
Range check
- Ensures numeric values fall within specified limits
- Example: Age must be between 11 and 18 for a secondary school database
Type check
- Confirms data matches the expected data type
- Example: Quantity must be a whole number, not text or decimal
Length check
- Restricts the number of characters
- Example: UK postcodes must be 6-8 characters
Presence check
- Ensures required fields are not left empty
- Example: Surname must be entered for every student record
Format check
- Verifies data follows a specific pattern
- Example: Email addresses must contain an @ symbol
Lookup check
- Compares input against a list of acceptable values
- Example: Title must be Mr, Mrs, Ms, Miss, Dr, or Prof
Validation improves data quality but cannot guarantee data is correct. A student entering a date of birth of 01/01/2005 instead of 01/01/2006 would pass validation despite being inaccurate.
Worked examples
Example 1: Database design
Question: A veterinary surgery wants to computerise its appointment system. The database will store information about pet owners, their pets, and appointments.
(a) State what is meant by a primary key. [2 marks]
(b) Suggest a suitable primary key for a Pets table. [1 mark]
(c) The Appointments table needs to link to both the Pets table and a Vets table. Explain how this would be achieved. [3 marks]
Mark scheme answers:
(a)
- A field (or combination of fields) that uniquely identifies each record [1]
- No two records can have the same primary key value [1]
Alternative acceptable answers:
- Must be unique and not null [1]
- Allows each record to be distinguished from all others [1]
(b)
- PetID / Pet registration number [1] (Any sensible unique identifier acceptable; do not accept pet name as this may not be unique)
(c)
- The Appointments table would include a PetID field as a foreign key [1]
- This PetID would reference/link to the primary key in the Pets table [1]
- Similarly, a VetID foreign key would link to the primary key in the Vets table [1]
Example 2: Queries and validation
Question: A database stores information about students in a STUDENTS table.
| StudentID | Surname | FirstName | YearGroup | DateOfBirth |
|---|---|---|---|---|
| 2301 | Clarke | Maya | 10 | 12/03/2009 |
| 2302 | Williams | Aaron | 11 | 08/07/2008 |
| 2303 | Baptiste | Sophie | 10 | 15/11/2008 |
(a) Write a query using logical operators to find all Year 10 students born before 01/01/2009. [2 marks]
(b) Describe two validation checks that could be applied to the YearGroup field. [4 marks]
Mark scheme answers:
(a)
- YearGroup = 10 AND DateOfBirth < 01/01/2009 [2]
Award [1] for correct fields but incorrect operator (e.g., using OR instead of AND)
(b) Any two from:
- Range check to ensure YearGroup is between 7 and 13 [1] to ensure only valid year groups for a secondary school are entered [1]
- Type check to ensure YearGroup is numeric/integer [1] preventing text being entered [1]
- Presence check to ensure YearGroup is not left blank [1] as this is required information [1]
- Lookup/list check against allowed values (7, 8, 9, 10, 11, 12, 13) [1] to prevent invalid year groups [1]
Award [1] for naming the check, [1] for explaining its purpose in this context
Example 3: Flat files vs databases
Question: A small business currently stores customer orders in a spreadsheet (flat file). The manager is considering moving to a database system.
Explain two advantages of using a database instead of a flat file for storing this information. [4 marks]
Mark scheme answers:
Any two from:
- Reduced data redundancy/duplication [1] — customer details stored once in a Customers table instead of repeated for every order, saving storage space and ensuring consistency [1]
- Better data integrity through validation [1] — validation rules can ensure data meets requirements (e.g., email format, valid product codes) preventing errors [1]
- Multiple users can access simultaneously [1] — staff can process different orders at the same time without conflicts, improving efficiency [1]
- Complex queries possible [1] — can easily generate reports combining customer and order data, or find specific groups of orders meeting criteria [1]
- Improved security [1] — different staff can have different permission levels, restricting access to sensitive information [1]
Award [1] for identifying advantage, [1] for explanation in context
Common mistakes and how to avoid them
Confusing records and fields — Remember: fields are columns (types of data), records are rows (complete entries). A field defines what data is stored; a record contains actual values for one entity.
Thinking validation ensures data is correct — Validation only checks data is reasonable and in the correct format. It cannot verify accuracy. A date of birth of 01/01/2000 might pass validation but be wrong for that particular person.
Using non-unique fields as primary keys — Surnames, phone numbers, or email addresses should not be primary keys as they may not be unique or may change. Use unique identifiers like StudentID or ProductCode.
Mixing up primary and foreign keys — The primary key uniquely identifies records in its own table. A foreign key is a copy of another table's primary key, creating the relationship. The same field cannot be both in a single table.
Incorrectly using AND/OR in queries — AND makes queries more restrictive (both conditions must be true), while OR makes them broader (either condition can be true). "Year 11 AND studying Computer Science" finds fewer students than "Year 11 OR studying Computer Science".
Stating databases "prevent errors" — Databases reduce errors through validation and reduce inconsistencies through reduced redundancy, but they cannot eliminate all errors. Human mistakes in data entry can still occur even with validation.
Exam technique for "Computer Systems: Databases"
"State" questions (1-2 marks) require brief, factual answers. For "State what is meant by a primary key," write one or two clear sentences defining it. Don't write paragraphs explaining advantages or examples unless asked.
"Explain" questions need context and reasoning — When asked to explain why databases are better than flat files, identify the advantage AND explain why it matters in the specific scenario. "Reduced redundancy [1] means customer details are stored once, so updates only happen in one place, maintaining consistency [1]."
Validation questions must be specific — Don't just write "range check." State the actual range (e.g., "Range check ensuring age is between 11 and 18") and explain why this makes sense for the scenario.
Show understanding of relationships — When describing how tables link, mention both primary and foreign keys explicitly. "The Orders table contains CustomerID as a foreign key, which references the primary key in the Customers table" scores full marks; "tables are linked" does not.
Quick revision summary
Databases store data in tables containing records (rows) and fields (columns). Each table needs a unique primary key to identify records. Databases link tables using foreign keys, creating relationships that reduce data duplication. Queries retrieve specific data using criteria and logical operators (AND, OR, NOT). Validation checks improve data quality by ensuring entries meet specified rules. Databases offer advantages over flat files including reduced redundancy, better integrity, complex queries, security, and concurrent access. Understanding the structure and purpose of database components is essential for exam success.