This module covers how a database table is set up and protected: choosing field types, defining a primary key, writing validation rules, importing a file and checking the result. It matters because most database marks depend on decisions made before any query is run. A wrong type or a hidden duplicate spoils every later result.
You will be working with small invented tables. Nothing here uses real people or records. For current wording and assessment details, use the Cambridge ICT 0417 page linked below.
What should you know first?
You should be comfortable with a table of rows (records) and columns (fields), and with the difference between a number you calculate with and a code you only read. Basic spreadsheet skills help, but no earlier ICT module is required. If you want a wider view of the subject, see the IGCSE ICT tuition page.
An orienting example
An invented cycling club keeps a member table. You are told: “Set up the table, load the supplied CSV and make sure fees are valid.”
- Types: MemberID text, Name text, DateJoined date, Fee currency.
- Key: MemberID, because it is unique and never changes.
- Validation: Fee must satisfy >=0 AND <=200.
- Import: the file has 1 header line and 5 data lines, so 5 records are expected.
- Check: the fees add up to 45.50 + 45.50 + 30.00 + 45.50 + 30.00 = 196.50, and the table total must match.
Each of these five steps is a lesson in this module.
In what order should you study the lessons?
- Choose field types from sample values. Everything else depends on the right types, so start here.
- Define a key and a validation condition. Add protection once the types are set.
- Import a small fictional dataset. Load data and prove the load is correct.
- Detect an incorrect numeric-text interpretation. Learn the symptoms that appear when types or imports go wrong.
- Explain why a duplicate record matters. Finish with the effect of bad data on counts and totals, and how to write it.
- Then work through the database structure and validation practice set.
What are the common traps?
- Storing phone numbers, postcodes or membership codes as numbers, which removes a zero at the start.
- Believing that passing validation means the data is correct. Validation filters unreasonable values, and verification checks accuracy.
- Writing “>0” when the rule says the lower limit itself is allowed.
- Forgetting the header option on import and getting an extra record.
- Assuming a primary key stops the same person being entered under two IDs.
How should you use the practice set?
Attempt the twelve questions on paper before opening any answer. Write explanations in full sentences. Then use the table at the end of the practice set to send each error back to the right lesson, and log it in the mistake log and retest queue.
The read-only SQL practice lab and the ICT practical task and evidence checker offer extra invented data to examine. Queries come next in the following module of the course.
Some students complete every step correctly yet cannot say why in a written answer. A teacher in online one-to-one ICT tuition can help you practise both the clicks and the wording.