Design and query a single-table database
Apply fields, records, data types, validation, keys and SQL to one coherent storage requirement.
Exam scope and assessment notes
This sequence follows Cambridge IGCSE Computer Science 0478, 2026–2028 syllabus Version 6, Topic 9. It covers single-table databases only and the specified SELECT, FROM, WHERE, ORDER BY, SUM, COUNT, AND and OR content. The official specimen also demonstrates NOT in a filter. Multi-table joins, GROUP BY, database implementation and the separate 2029–2031 syllabus are not mapped here. Practice is original, self-checked and does not award an official Cambridge grade. Full timed papers and human subject-teacher review remain outstanding.
Before you begin
- Be able to read a small table and compare values. Each lesson introduces the database and SQL terms it uses.
Work on paper. Try each question before revealing help, and explain why your method works.
Quick readiness check
Which step should come before choosing the SQL fields?
What you will learn
- Design fields from a scenario
- Assign types and validation rules
- Choose a primary key and write a focused query
Plan one record type first
Read the scenario and decide what one row represents. List each required fact as a separate field, then choose a basic data type that matches its meaning.
Mark any field that needs validation, such as a required value, a permitted range or a fixed code format. State the rule clearly.
Check identity and query needs
Choose a primary key whose values distinguish every record. Then translate a question into selected fields, a source table and any WHERE or ORDER BY clauses.
This topic is limited to a single table and the listed SQL operations. A schema and query that satisfy these requirements do not by themselves demonstrate implementation or broader database design.
Pause and explain
A query uses WHERE Capacity >= 20, but the proposed table has no Capacity field. What must be corrected?
Worked example
A centre stores each workshop session's code, topic, seat capacity, date and whether it is cancelled. Design fields and write a query for the topics with capacity at least 20.
Show the worked solution
- Use one row per workshop session and separate fields for its facts.
- Choose a unique SessionCode key, text for Topic, integer for Capacity, date/time for SessionDate and Boolean for IsCancelled.
- Select Topic and filter Capacity >= 20.
Answer Fields: SessionCode (primary key), Topic (text), Capacity (integer), SessionDate (date/time), IsCancelled (Boolean). Query: SELECT Topic FROM WORKSHOPS WHERE Capacity >= 20;
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.
A garden centre stores each delivery's reference, supplier, arrival date and number of trays. Suggest fields and suitable types.
Give me a hint
Represent one delivery per record.
Compare my reasoning
- Use Reference and Supplier as text fields.
- Use ArrivalDate as date/time.
- Use TrayCount as integer.
DeliveryReference (text/key), Supplier (text), ArrivalDate (date/time), TrayCount (integer).
Look for these in your work
- Record type
- Fields
- Types
For a table of library loans, choose a candidate primary key and one validation rule for DueDate.
Give me a hint
A loan identifier should distinguish loans; the date must be acceptable.
Compare my reasoning
- Choose LoanID if distinct for every loan.
- Use a date/time type for DueDate.
- Validate that a due date is present and follows the accepted date rule.
LoanID as the primary key; validate DueDate as a required date/time value.
Look for these in your work
- Unique identifier
- Date/time
- Validation
Design a minimal table for event registrations and write a query for registrations that are confirmed OR on a waitlist. State your type and key choices.
Give me a hint
One record per registration; use a status field with two defined alternatives.
Compare my reasoning
- Include RegistrationID, AttendeeName and Status.
- Use RegistrationID as a unique key; names are text and Status is text/alphanumeric.
- Select the attendee names with the two accepted status values.
RegistrationID (unique key), AttendeeName (text), Status (text); SELECT AttendeeName FROM REGISTRATIONS WHERE Status = "Confirmed" OR Status = "Waitlist";
Look for these in your work
- Schema meets requirement
- Key and types justified
- OR query matches alternatives
Common mistakes
- Leaving a required fact out of the schema
- Choosing a repeated descriptive field as the key
- Writing a query against a field not included in the table
What can you explain now?
What should one row represent in a single-table design?
Compare with the explanation
One item of the type described by the requirement
A consistent record type keeps each row's meaning clear.
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
What sequence takes a storage requirement from a scenario to a checked 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.