Database structures, queries and reports
Build a fit-for-purpose database structure, enter and manipulate records, and present checked outputs.
Section Paper 1 · Theory
Exam scope and assessment notes
Cambridge IGCSE Information and Communication Technology 0417, for exams in 2026, 2027 and 2028. Examples, datasets and practice are original StudyForA material; they are not Cambridge questions, source files, mark schemes or grades. Practical lessons teach transferable operations, but learners must perform them in locally available office and web-authoring software and inspect their own files. This site does not reproduce a Cambridge practical examination or award an official grade. The separate 2029 syllabus is not mapped here.
Before you begin
- No previous ICT course is assumed. Read each scenario carefully and justify a tool or method by connecting it to the task, user and constraints.
Work on paper. Try each question before revealing help, and explain why your method works.
Quick readiness check
Which field type is most suitable for a learner's date of birth?
What you will learn
- Import data; select field names and types; use primary and foreign keys and create relationships.
- Design forms and perform calculations, sorting, queries, searches and selection with logical operators and wildcards.
- Create reports with required fields, headings, formats, alignment, headers and footers.
Structure records before building outputs
A flat-file database stores records in one table; a relational database links related tables to reduce repeated data. Choose a data type for each field: text, integer, decimal, currency, date/time or Boolean. Set appropriate subtypes and display formats. A primary key uniquely identifies a record; a foreign key refers to a related record in another table.
Forms help users enter data. Use clear labels, suitable spacing and controls such as radio buttons, check boxes or drop-down menus where the choices are controlled. A good form reduces ambiguity without hiding important information.
Manipulate and present data accurately
Use calculated fields and controls with arithmetic or functions such as SUM, AVERAGE, MAX, MIN and COUNT. Sort by one or more criteria. Queries select records using AND, OR, NOT, comparison operators and wildcards; state exactly which records should match.
Reports should display all required data and full labels, with suitable report/page headers and footers, a title, tabular or columnar layout and correct alignment. Format numbers as currency, percentages or the specified decimal places. Verify a sample against the source and check that the report includes the intended records.
Sources for this lesson
Pause and explain
A query should return books that are overdue and either belong to member A or member B. Which logic is needed?
Worked example
A library has a Loans table with MemberID, BookID and DueDate. Explain how relationships and a query could find overdue loans for one member.
Show the worked solution
- Use a unique LoanID primary key and foreign keys MemberID and BookID.
- Relate MemberID to Members and BookID to Books, avoiding repeated member names in every loan row.
- Query for the selected MemberID and DueDate earlier than today; inspect matching records.
Answer Use LoanID as the Loans primary key and MemberID and BookID as foreign keys linked to the Members and Books tables. Query where MemberID equals the chosen member AND DueDate is before today's date. Check the date rule and returned records before using them for reminders.
Try a topic test
Choose Easy, Normal or Difficult. Save your answers and open worked review after finishing.
Test this topic →From guided practice to a new situation
Write your answer and reasoning first. Use a hint only if you are stuck. The model solution is for self-checking; your written work is not automatically marked.
Design fields and types for a simple event booking table with BookingID, email, event date, number of seats and paid status.
Give me a hint
Choose unique identifiers and data types that support sorting and validation.
Compare my reasoning
- Use a unique identifier for each booking.
- Store email as text and event date as a date/time value.
- Use an integer for seats and a Boolean for paid status; define suitable validation.
BookingID: unique primary key (integer or text code). Email: text. EventDate: date/time. Seats: integer with a positive range check. Paid: Boolean with a clear yes/no display. Verify email and event choice against the source and avoid making email the primary key because it can change or repeat.
Look for these in your work
- Selects appropriate data types
- Defines a key and validation
A report must list products with stock below 5 and price above $20, sorted by price descending. State the query and sort.
Give me a hint
Both conditions must be true, so connect them with AND.
Compare my reasoning
- Filter Stock < 5.
- Filter Price > 20 and combine with AND.
- Sort matching records by Price in descending order and check labels.
Query: Stock < 5 AND Price > 20. Sort the result by Price descending. Include the required product fields and labels and verify a sample against the source records.
Look for these in your work
- Uses AND for both requirements
- Applies the requested sort direction
A flat-file table repeats each customer's address on every order. Explain the risk and a relational redesign.
Give me a hint
Separate entities that have a one-to-many relationship.
Compare my reasoning
- Create a Customers table with one row per customer and a primary key.
- Keep each order in an Orders table with its own primary key and customer foreign key.
- Relate the keys and update the address in one place.
Repeated addresses can become inconsistent when one order is updated but others are not. Store one customer record in Customers with CustomerID as primary key, and put CustomerID as a foreign key in Orders. This reduces duplication, though the relationship and referential integrity must be configured correctly.
Look for these in your work
- Explains data duplication risk
- Uses primary and foreign keys
Common mistakes
- Using a non-unique field such as a person's name as a primary key
- Confusing AND with OR in a query
- Formatting a report while omitting required fields or records
What can you explain now?
What is the role of a foreign key?
Compare with the explanation
It links a record to a related record in another table.
A foreign key refers to a primary key in another table and supports a relationship between the tables.
After trying it yourself, choose your next review. This is your self-assessment.
Your review choice appears on Today. Sign in to sync it across devices.
Make it stick
Explain how a database key differs from a field label and how AND and OR change a multi-condition query.
If you needed the solution, close it and solve the problem again from a blank page. A correct answer is stronger when you can explain the reason for each step.