Year 11 · ICT · Summer 1
Final revision and the exams · Lesson 1 of 5
Red and amber points, pupil by pupil
Exam practice7.2 Design18.2 Manipulate data: calculations, sorting and queries20.1c Lookup functions
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
You will learn to
- Identify your red and amber points from your tracker, in theory and practical skills.
- Use the revision cycle on one point: key terms, quiz, then a past-paper question or task.
- Evaluate your progress: change a point's colour only after full marks under time.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Success criteria
You have got it when you can say:
- I can list my three reddest points before the lesson starts.
- I can show a marked past-paper question or task for each point I worked on.
- I can do one practical skill under time without notes.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Retrieval starter
Questions from before. Write your answers, then show them.
- Last unit
Extreme test data is…
A Typical dataB At the edges of the allowed rangeC Outside the range
B. At the edges of the allowed range
- Last unit
A 3D printer is…
A An output deviceB A storage deviceC An input device
A. An output device
- Earlier this year
Double data entry is…
A Proof-readingB VerificationC Validation
B. Verification
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Key words (1 of 7)
- System specification
- A list of the hardware and software the new system needs.
- Field
- One column of a table: one piece of data stored for every record.
- Coding of data
- Storing short codes such as M or F instead of whole words.
- Validation
- An automatic computer check that data is sensible and allowed.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Key words (2 of 7)
- Check digit
- An extra digit calculated from the other digits, used to detect input errors.
- Data capture form
- A paper or on-screen form used to collect data.
- Test plan
- A table listing each test, its data, the expected and actual outcomes and any remedial action.
- Normal data
- Sensible data that should be accepted.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Key words (3 of 7)
- Direct changeover
- The old system stops and the new one starts immediately.
- Database
- An organised store of data that can be searched, sorted and reported on.
- Table
- A set of records about one kind of thing, such as members or bookings.
- Record
- One row of a table: all the data about one item.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Key words (4 of 7)
- Field type
- The kind of data a field holds: text, numeric, date/time or Boolean.
- Boolean
- A field type with only two values, such as Yes/No or True/False.
- Query
- A search that shows only the records that meet given criteria.
- Criteria
- The condition COUNTIF uses to decide which cells to count.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Key words (5 of 7)
- Wildcard
- A symbol standing for unknown characters: * for any number, ? for exactly one.
- Calculated field
- A field in a query that is worked out from other fields, not typed in.
- Calculated control
- A box on a report or form that shows a calculation, such as a total.
- Cell
- One box in the grid, where a column and a row meet.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Key words (6 of 7)
- Cell reference
- The address of a cell: column letter then row number, such as C3.
- Range
- A block of cells written first:last, such as C3:C8.
- Formula
- A calculation in a cell that starts with =.
- Function
- A ready-made formula with a name, such as SUM or VLOOKUP.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Key words (7 of 7)
- Argument
- One input to a function, written inside its brackets and separated by commas.
- Absolute reference
- A reference like $I$7 that stays fixed when the formula is copied.
- Lookup
- A function that finds a value in a table and returns a matching value.
- Exact match
- A lookup that only accepts the identical value (FALSE in VLOOKUP).
The explanation · step 1 of 21
Start from your tracker
Your tracker lists every theory point and practical skill with a colour. Work on red first, then amber.
Green points only need a short quiz or task now and then.
Why not revise what I already know?
It feels good, but it gains few marks. Marks come from closing gaps.
The explanation · step 2 of 21 · part 1 of 3
The revision cycle
For a theory point:
- Write the key terms and their meanings from memory.
- Check them and correct them in another colour.
- Do the quiz for that point.
- Do one past-paper question, timed, and mark it with the scheme.
For a practical skill:
The explanation · step 2 of 21 · part 2 of 3
The revision cycle (continued)
- Do the skill once with your notes open.
- Close your notes. Do it again on a new past-paper task, timed.
- Mark it with the scheme. Check your evidence too.
A practical skill is green only when you can do it under time, without notes.
The explanation · step 2 of 21 · part 3 of 3
The revision cycle (continued)
Copying out notes is not revision. If you are not recalling or doing, you are not revising.
7.2 Design · The explanation · step 3 of 21 · part 1 of 3
Designing the file structure
A file here means a table of records. A record is all the data about one thing. A field is one item of data in a record.
For each field the designer chooses a field name, a data type and a field length (the maximum number of characters).
Coding of data means storing short codes instead of long words, for example M or F, Y or N.
7.2 Design · The explanation · step 3 of 21 · part 2 of 3
Designing the file structure (continued)
| Field name | Data type | Field length | Example |
|---|---|---|---|
| StudentID | Text | 6 | S10432 |
| Surname | Text | 30 | García |
| DateOfBirth | Date/Time | 10 | 14/03/2011 |
| Gender | Text (coded) | 1 | F |
| FinesOwed | Numeric (currency) | 6 | 1.50 |
| Overdue | Boolean | 1 | Y |
7.2 Design · The explanation · step 3 of 21 · part 3 of 3
Designing the file structure (continued)
Codes save storage space, are quicker to type and are easier to validate. There are only a few allowed values, so mistakes are easy to spot.
Phone numbers and IDs with leading zeros are stored as text, not numbers. A numeric field would drop the leading zero (0034 would become 34).
7.2 Design · The explanation · step 4 of 21 · part 1 of 3
Validation: what it is and why we need it
Validation is an automatic check, done by the computer, that data entered is sensible and allowed.
It stops impossible or unreasonable data getting into the system.
Validation cannot tell if data is correct. It can only tell if it is reasonable. An age of 15 passes a range check even if the student is really 14.
7.2 Design · The explanation · step 4 of 21 · part 2 of 3
Validation: what it is and why we need it (continued)
| Check | What it checks | Example |
|---|---|---|
| Range check | The value is between a lower and an upper limit | Exam mark from 0 to 100 |
| Length check | The number of characters is exact, or within limits | A Spanish mobile number has exactly 9 digits |
| Type check | The data is the right data type | Age must be a whole number, not 'fifteen' |
| Character check | Only allowed characters are used | A surname may contain letters, spaces, hyphens and apostrophes, but no digits |
| Format check | The data follows a set pattern | StudentID is a letter then 5 digits: S10432. A date is dd/mm/yyyy |
| Presence check | The field is not left empty | Surname must be filled in |
| Check digit | An extra digit worked out from the other digits matches | The last digit of an ISBN or a supermarket barcode |
7.2 Design · The explanation · step 4 of 21 · part 3 of 3
Validation: what it is and why we need it (continued)
Do not mix up validation and verification. Verification checks data was copied correctly (double entry, visual check). Validation checks data is sensible.
There is something to try for this step in the IGCSE library.
7.2 Design · The explanation · step 6 of 21 · part 1 of 2
Input formats: data capture forms
A data capture form collects data from a person. It can be on paper or on screen.
A good form has a clear title, instructions, logical field order and enough space for each answer.
On-screen forms can use drop-down menus, radio buttons (choose one) and tick boxes (choose any). These limit what can be entered, so there are fewer errors.
Paper forms may use boxes with one character per box, so writing is easier to read or scan by OCR.
7.2 Design · The explanation · step 6 of 21 · part 2 of 2
Input formats: data capture forms (continued)
Exam answers about forms need specific features: 'use a drop-down for year group' earns a mark, 'make it look nice' does not.
The explanation · 16 more steps
More on the lesson page
The lesson page has the rest of the explanation, step by step:
- The check digit, worked through
- Output formats: screen layouts and report layouts
- Calculated fields and calculated controls
- Sum, average, maximum, minimum, count
- Sort on one or more fields
- Read a query grid: a simple search
- Comparison operators
- AND: every condition must be true
- OR: any condition can be true
- NOT, LIKE and wildcards
- Queries across two tables, and evidence
- VLOOKUP: exact match
- VLOOKUP: approximate match
- HLOOKUP
- LOOKUP
- XLOOKUP
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Quick quiz · 1 of 3
RAM is…
- A The hard disk
- B Permanent storage
- C Volatile working memory
C. Volatile working memory
It holds what is running now and is wiped when the power goes.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Quick quiz · 2 of 3
A CLI is…
- A Clicking icons
- B A touch screen
- C A command line interface: typed commands
C. A command line interface: typed commands
Technicians use it for precise control. GUI is the icons and windows.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Quick quiz · 3 of 3
Which is application software?
- A A spreadsheet
- B The operating system
- C A device driver
A. A spreadsheet
Applications do jobs for the user. Drivers and the OS are system software.
Year 11 · ICT · Final revision and the exams · Lesson 1 of 5
Exit ticket
Answer on paper before you go. Then check.
A phablet is…
A Between a phone and a tablet in sizeB A printerC A server
A. Between a phone and a tablet in size A large phone, small tablet.
OMR reads…
A Printed text into editable textB Marks on a form, like an answer sheetC Barcodes
B. Marks on a form, like an answer sheet Optical mark recognition. OCR is the one that reads text.