Why is "Storing phone numbers, postcodes or IDs as numbers." a common mistake in Database management?

Why it happens: They look like numbers. How to avoid it: Use TEXT — it keeps leading zeros and formatting, and these values aren't used in calculations. Source: Edexcel 4IT1 examiner reports.

Why is "Putting everything in one flat table." a common mistake in Database management?

Why it happens: It seems simpler. How to avoid it: Use multiple related tables with keys to avoid redundancy and update/insert/delete anomalies. Source: Edexcel 4IT1 examiner reports.

Why is "Forgetting a primary key." a common mistake in Database management?

Why it happens: Overlooking unique identification. How to avoid it: Every table needs a primary key that is unique and never blank, so records can be identified and linked.

Why is "Confusing AND with OR in query criteria." a common mistake in Database management?

Why it happens: Everyday English uses 'and' loosely. How to avoid it: AND = both must be true (narrows). OR = either (broadens). Test records against each condition.

Software SkillsPearson Edexcel IGCSE ICT 4IT1

Database management

Q: Why is "Thinking validation guarantees correct data." a common mistake in Database management?

Why it happens: Validation passes plausible values. How to avoid it: Validation only checks PLAUSIBILITY; a valid value can still be wrong. Use verification (double-entry/proofread) too.

Work through the notes, try the practice questions, then take the quiz. The report tells you exactly what to revise next.

Previous Next

Learn this Topic

Start with the slides for the quick version, then go deeper with the full study notes.

Short Study Notes in the form of Slides

Read the notes first. If the method in a worked example clicks, you're ready for the questions.

Short Study Notes — Database management

Start with these resources to cover the key concepts, then work through the practice questions.

Page 1 / 0

Detailed Notes

Full prose, callouts and a recap — built for A* mastery, not just a quick scan.

Take these study notes with you

Download a branded PDF — full prose, callouts, recap and memorise list for Database management, ready to print or save offline.

Database Management Software — Edexcel IGCSE ICT (4IT1) Detailed Notes

Building and querying structured data: tables, fields, data types, primary/foreign keys, relationships, validation, queries, forms and reports.

What you’ll learn

Mapped to the Pearson Edexcel IGCSE 4IT1 syllabus (2026 onwards).

Describe database structure (tables, records, fields).
Choose appropriate data types and keys.
Explain relational design and why it beats a flat file.
Apply validation rules.
Build queries with criteria and sorting.
Create forms for input and reports for output.

Database structure

Tables, records, fields and keys.

Table — stores data in rows and columns. Record — one complete row (all data about one item). Field — one column (a single data item), with a defined data type.

Primary key — a field that UNIQUELY identifies each record (e.g., MemberID). It must be unique and never blank.

Foreign key — a field that links to the primary key of ANOTHER table, creating a relationship.

Example:

Members table: MemberID (PK), Name, Email.
Loans table: LoanID (PK), MemberID (FK → Members), BookID (FK → Books).

The foreign key MemberID in Loans links each loan to a member without repeating the member's details.

Data types and validation

Right type + sensible-data checks.

Data types:

Text / Short text — names, addresses, phone numbers, postcodes, IDs.
Number / Integer — counts, quantities.
Decimal / Currency — prices, measurements.
Date/Time — dates, times.
Boolean / Yes-No — true/false fields (Active, OnLoan).

Trap: phone numbers, postcodes and IDs are TEXT — they keep leading zeros and aren't used in calculations.

Validation checks (automated sense-checks):

Range — value within bounds (mark 0–100).
Presence — required field not blank.
Format (input mask) — matches a pattern (AA-999, email).
Length — correct number of characters.
Type — correct data type.
Lookup / list — value from an allowed list/another table.

Remember: validation only checks PLAUSIBILITY — a valid value can still be wrong. Pair it with verification (double-entry, proofreading).

Relational design

Multiple linked tables beat one flat table.

A flat file stores everything in one big table, repeating data on every row. This causes:

Redundancy — same data repeated, wasting space.
Update anomaly — a change must be made in many rows; miss one → inconsistency.
Insertion anomaly — can't easily store an item that has no related record yet.
Deletion anomaly — deleting one row may lose other needed data.

A relational database splits data into multiple tables linked by keys:

Each entity (Customer, Order, Product) gets its OWN table.
Store each fact ONCE.
Link with foreign keys.

Relationships:

One-to-many — one customer has many orders (most common).
Many-to-many — handled via a linking/junction table.

Benefits: no redundancy, single point of update, data integrity, easier querying, and scalability.

Queries

Retrieve exactly the records you need.

Query — retrieves records matching criteria.

Boolean logic:

AND — both conditions true (narrows results).
OR — either condition true (broadens).
NOT — excludes.

Comparison operators: =, <, >, <=, >=, <> (not equal). LIKE with wildcards for partial matches ('S*').

Query features:

Criteria — the filter conditions (incl. date ranges: BETWEEN, or Year()).
Sort — ascending (smallest first) or descending.
Shown fields — choose which columns appear.
Calculated fields — compute values (Total = Qty × Price).

Method: filter records by criteria FIRST, then sort. Test each record against EVERY condition.

Queries extract precise information from large datasets quickly — the core reason to use a database over a spreadsheet for records.

Forms and reports

Friendly input; professional output.

Forms — user-friendly screens for entering and editing data.

Easier and less error-prone than typing into a raw table.
Can include labels, drop-downs (lookups), validation messages and buttons.
Improve data quality and usability.

Reports — formatted, printable presentations of data, usually based on a query.

Add a title, headers/footers, page numbers and a record count.
Group and sort data (e.g., by city, then surname).
Include calculated totals/summaries.
Export to PDF or print.

Workflow to answer a question: build a QUERY to extract the right records, then base a REPORT on it to present them professionally — separating data retrieval from presentation.

How it’s examined

Paper 1: Define database terms (4 marks); choose data types (4–6 marks); identify validation checks (4–6 marks); predict query results (4–6 marks); explain relational vs flat file (6 marks).

Paper 2 (practical): Build tables with correct data types and keys; set validation; create queries with criteria and sorting; design forms and reports.

Frequent stems: 'State the data type for…', 'Name the validation check…', 'Which records does this query return…', 'Explain why a relational database is better than…'

Sources: Pearson Edexcel International GCSE in Information and Communication Technology (4IT1) Specification — Issue 3; Edexcel 4IT1 Examiner Reports (2022–2024). Last reviewed 2026-05-20.

Master this Topic

Worked examples, formulae, definitions and the mistakes examiners flag — everything you need to push from a pass to an A*.

Take this whole topic with you

Download a branded revision sheet — worked examples, formulae, definitions and common mistakes for Database management, ready to print or save as PDF.

Step-by-step worked examples — Database management

Step-by-step solutions to past-paper-style questions on database management, written exactly the way a tutor would explain them at the board.

1Identify the database object/feature
Getting started• Edexcel 4IT1 style — short-answer (6 marks)• objects, AO1
Question
Name the database object/feature: (a) Stores the records in rows and columns. (b) Retrieves records matching criteria. (c) A user-friendly screen for entering data. (d) A formatted printout of results. (e) A field that uniquely identifies each record. (f) A rule that checks data is sensible on entry.
Step-by-step solution
1. Step 1
  Match each description to its database object/feature.
Answer
(a) Table.

(b) Query.

(c) Form.

(d) Report.

(e) Primary key.

(f) Validation rule.
Examiner tip
Core database objects: tables (store), queries (retrieve), forms (input), reports (output). Know all four.
2Choose the data type for each field
Building confidence• Edexcel 4IT1 style — short-answer (6 marks)• data-types, AO1
Question
Choose the best data type: (a) StudentName. (b) DateOfBirth. (c) IsLibraryMember. (d) Price. (e) QuantityInStock. (f) PhoneNumber.
Step-by-step solution
1. Step 1
  Match field meaning to data type.
Answer
(a) Text / Short text.

(b) Date/Time.

(c) Boolean / Yes-No.

(d) Currency.

(e) Integer / Number.

(f) Text (NOT number — leading zeros and formatting must be preserved; no calculations).
Examiner tip
Phone numbers, postcodes and IDs are TEXT, not numbers — they keep leading zeros and aren't used in calculations. A classic exam trap.
3Identify the validation check for each rule
Building confidence• Edexcel 4IT1 style — short-answer (5 marks)• validation, AO1
Question
Name the validation check: (a) Mark must be 0–100. (b) Email cannot be left blank. (c) Product code must match pattern AA-999. (d) Country must come from a set list. (e) Phone number must be 11 digits.
Step-by-step solution
1. Step 1
  Range, presence, format, lookup, length.
Answer
(a) Range check.

(b) Presence check.

(c) Format check (input mask).

(d) Lookup / list check.

(e) Length check.
Examiner tip
Validation checks: range, presence, format, length, type, check digit, lookup. Match the rule to the check precisely.
4Predict the records a query returns
Building confidence• Edexcel 4IT1 style — calculation (5 marks)• query-logic, AO1, AO2
Question
A Members table has fields: Name, Age, City, Active(Y/N). Records: 1 Tom,17,London,Y · 2 Ann,25,Leeds,Y · 3 Sam,30,London,N · 4 Joy,19,London,Y · 5 Ben,40,Leeds,Y. Which records are returned by: City = 'London' AND Active = 'Y'?
Step-by-step solution
1. Step 1
  Test each record against BOTH conditions.
Answer
Check both conditions:

Tom: London ✓, Y ✓ → returned.

Ann: Leeds ✗ → no.

Sam: London ✓, N ✗ → no.

Joy: London ✓, Y ✓ → returned.

Ben: Leeds ✗ → no.

Records returned: Tom and Joy (records 1 and 4) — 2 records.
Examiner tip
AND requires BOTH conditions true. Test each record systematically against every condition.
5Determine sorted query output
Higher• Edexcel 4IT1 style — calculation (5 marks)• query-sort, AO1, AO2
Question
Using the same Members table, a query selects all records where Active='Y', sorted by Age ascending. List the Names in the order shown.
Step-by-step solution
1. Step 1
  Filter on Active='Y', then order by Age ascending.
Answer
Filter Active='Y': Tom(17), Ann(25), Joy(19), Ben(40) — Sam excluded (N).

Sort by Age ascending:

Tom (17)

Joy (19)

Ann (25)

Ben (40)

Output order: Tom, Joy, Ann, Ben.
Examiner tip
Apply the FILTER first, then the SORT. Ascending = smallest first; descending = largest first.
6Calculate database storage size
Higher• Edexcel 4IT1 style — calculation (5 marks)• storage-calc, AO2
Question
A table has fields: ID (4 bytes), Name (30 bytes), Email (40 bytes), DOB (8 bytes), Active (1 byte). Calculate: (a) Bytes per record. (b) Total bytes for 5,000 records. (c) The size in kilobytes (1 KB = 1,024 bytes), to 1 decimal place.
Step-by-step solution
1. Step 1
  Sum field sizes × number of records; convert to KB.
Answer
(a) Per record = 4 + 30 + 40 + 8 + 1 = 83 bytes.

(b) 83 × 5,000 = 415,000 bytes.

(c) 415,000 ÷ 1,024 = 405.3 KB (1 d.p.).

Estimating storage helps choose the right system and plan capacity.
Examiner tip
Add field sizes for one record, multiply by record count, then convert (÷1,024 for KB). Show units throughout.
7Design a database for a small library
Higher• Edexcel 4IT1 style — extended (8 marks)• database-design, AO1, AO2
Question
A small library wants a database to track books and which member has borrowed each. Describe the tables, key fields, data types, relationships and validation you would set up.
Step-by-step solution
1. Step 1
  Tables → keys → relationship → data types → validation.
Answer
1. Tables (avoid one big table — use related tables):

Members: MemberID (PK), Name, Email, JoinDate, Active.

Books: BookID (PK), Title, Author, ISBN, OnLoan(Y/N).

Loans: LoanID (PK), MemberID (FK), BookID (FK), DateOut, DateDue.

2. Keys.

Primary keys (PK) uniquely identify records (MemberID, BookID, LoanID).

Foreign keys (FK) link tables (Loans.MemberID → Members; Loans.BookID → Books).

3. Relationships.

One member can have many loans (one-to-many).

One book appears in many loans over time (one-to-many).

4. Data types.

IDs: Text/AutoNumber; Name/Title/Author: Text; Dates: Date/Time; Active/OnLoan: Boolean; ISBN: Text.

5. Validation.

Presence check on Name, Title.

Format check on Email and ISBN.

DateDue must be after DateOut.

Lookup for MemberID/BookID (must exist).

6. Objects.

Forms for easy data entry; queries (e.g., overdue loans); reports (e.g., books on loan).

Why related tables? They remove duplication, keep data consistent (a member's details stored ONCE), and maintain integrity.
Examiner tip
A* answers (1) use MULTIPLE related tables (not one flat table), (2) name primary AND foreign keys, (3) give correct data types, (4) add appropriate validation.
8Compare a flat file with a relational database
Higher• Edexcel 4IT1 style — explain (6 marks)• relational, AO1, AO2
Question
A shop stores all orders in a single flat table that repeats each customer's full details on every order row. Explain the problems this causes and how a relational database solves them.
Step-by-step solution
1. Step 1
  Identify redundancy/anomalies; show relational fix.
Answer
Problems with the single flat table:

Data redundancy — a customer's details are repeated on every order, wasting space.

Update anomaly — if a customer changes address, EVERY row must be edited; miss one and data becomes inconsistent.

Insertion anomaly — hard to store a customer who hasn't ordered yet.

Deletion anomaly — deleting their last order may delete the customer entirely.

Inconsistency — the same customer may be spelled differently across rows.

Relational solution:

Split into a Customers table and an Orders table.

Store each customer ONCE in Customers (with CustomerID as primary key).

Orders holds CustomerID as a foreign key linking to the customer.

Benefits: no redundancy; update a customer in ONE place; data integrity maintained; easier querying and scaling.

Conclusion: a relational design removes duplication and anomalies — essential for anything beyond trivial data.
Examiner tip
Name the specific problems (redundancy, update/insert/delete anomalies, inconsistency) and the relational fix (separate tables linked by keys).
9Build a query and report to answer a question
Stretch• Edexcel 4IT1 style — extended (8 marks)• query-report, AO1, AO2, AO3
Question
A manager asks: 'Give me a printed list of all members in London who joined in 2025, sorted by surname, showing name and email.' Describe how to produce this from the database.
Step-by-step solution
1. Step 1
  Design the query criteria, fields, sort; then the report.
Answer
1. Create a query.

Tables/fields: select from Members — Name (surname), Email, City, JoinDate.

Criteria:

City = 'London'.

JoinDate BETWEEN 01/01/2025 AND 31/12/2025 (or Year(JoinDate)=2025).

Sort: Surname ascending.

Show fields: Name and Email only (hide City/JoinDate from output if not needed).

2. Run and check the query.

Verify it returns only 2025 London members and is correctly sorted.

3. Create a report based on the query.

Use a report layout with a title ('London Members Joined 2025').

Group/sort by surname; show Name and Email columns.

Add header (title, date) and footer (page numbers, record count).

Format for clear printing.

4. Output.

Preview, then print or export to PDF for the manager.

Why a query + report? The query EXTRACTS exactly the right records; the report PRESENTS them professionally for printing — separating data retrieval from presentation.
Examiner tip
A* answers (1) set precise query CRITERIA (incl. date range), (2) choose SORT and shown FIELDS, (3) base a formatted REPORT on the query, (4) state the output format.

Key Formulae — Database management

The formulae you need to memorise for database management on the Pearson Edexcel IGCSE 4IT1 paper, with every variable defined in plain English and a note on when to use it.

Record size
$Bytes per record = sum of all field sizes$
When to use
Estimating storage for a table.
Example
4 + 30 + 40 + 8 + 1 = 83 bytes.
Table storage
$Total bytes = bytes per record × number of records$
When to use
Estimating total table size.
Example
83 × 5,000 = 415,000 bytes.
Bytes to kilobytes
$KB = bytes ÷ 1,024$
When to use
Converting storage units.
Example
415,000 ÷ 1,024 ≈ 405.3 KB.

Key Definitions and Keywords — Database management

Definitions to memorise and the exact keywords mark schemes credit for database management answers — sharpened from recent examiner reports for the 2026 Pearson Edexcel IGCSE 4IT1 sitting.

Database
Examiner keyword
An organised, structured collection of related data that can be stored, queried and updated.
Table
Examiner keyword
A structure storing records in rows, with fields as columns.
Record
Examiner keyword
A complete set of data about one item (one row in a table).
Field
Examiner keyword
A single item of data in a record (one column), with a defined data type.
Primary key
Examiner keyword
A field that uniquely identifies each record in a table (e.g., MemberID). No duplicates, never blank.
Foreign key
Examiner keyword
A field in one table that links to the primary key of another, creating a relationship.
Query
Examiner keyword
A request that retrieves records matching criteria, using Boolean logic and sorting.
Form
A user-friendly screen for entering and editing data in a table.
Report
A formatted, printable presentation of data, usually based on a query.
Relational database
Examiner keyword
A database of multiple tables linked by keys, removing redundancy and maintaining integrity.

Common Mistakes and Misconceptions — Database management

The traps other students keep falling into on database management questions — taken from recent Pearson Edexcel IGCSE 4IT1 examiner reports and mark schemes — and how to avoid them.

✕Storing phone numbers, postcodes or IDs as numbers.
Edexcel 4IT1 examiner reports.
Why it happens
They look like numbers.
How to avoid it
Use TEXT — it keeps leading zeros and formatting, and these values aren't used in calculations.
✕Putting everything in one flat table.
Edexcel 4IT1 examiner reports.
Why it happens
It seems simpler.
How to avoid it
Use multiple related tables with keys to avoid redundancy and update/insert/delete anomalies.
✕Forgetting a primary key.
Why it happens
Overlooking unique identification.
How to avoid it
Every table needs a primary key that is unique and never blank, so records can be identified and linked.
✕Confusing AND with OR in query criteria.
Why it happens
Everyday English uses 'and' loosely.
How to avoid it
AND = both must be true (narrows). OR = either (broadens). Test records against each condition.
✕Thinking validation guarantees correct data.
Why it happens
Validation passes plausible values.
How to avoid it
Validation only checks PLAUSIBILITY; a valid value can still be wrong. Use verification (double-entry/proofread) too.

Practice questions

Exam-style questions with step-by-step worked solutions. Try one before checking the method.

Past paper style quiz

Get a report showing which sub-topics you've nailed and which ones still need work.

Database management

Short Study Notes in the form of Slides

Short Study Notes — Database management

Detailed Notes

What you’ll learn

How it’s examined

1Identify the database object/feature

2Choose the data type for each field

3Identify the validation check for each rule

4Predict the records a query returns

5Determine sorted query output

6Calculate database storage size

7Design a database for a small library

8Compare a flat file with a relational database

9Build a query and report to answer a question

Record size

Table storage

Bytes to kilobytes

Database

Table

Record

Field

Primary key

Foreign key

Query

Form

Report

Relational database

✕Storing phone numbers, postcodes or IDs as numbers.

✕Putting everything in one flat table.

✕Forgetting a primary key.

✕Confusing AND with OR in query criteria.

✕Thinking validation guarantees correct data.

Practice questions

Past paper style quiz

Assess your understanding