Database Design & Development · Testing & Evaluation
DDD10 — DDD testing and evaluation
📅 Thu 5 Nov 2026
⏱ ~100–110 minutes · double period
Learning intentions
I can build a test plan for a query by predicting expected results before running it.
I can evaluate a query's fitness for purpose.
I can evaluate a query's accuracy of output.
Success criteria
I can draw an expected-results table by hand before running a query, and compare it against the actual output.
I can identify whether a flawed query is not fit for purpose, not accurate, or both — and explain why.
I can distinguish fitness for purpose from accuracy, using both terms correctly and separately.
Warm up — what do you already know?
Answer before the lesson begins — it's fine if you're unsure.
WU1
1. In DDD9, why is a missing WHERE clause on an UPDATE or DELETE statement so dangerous?
WU2
2. In DDD8, what SQL clause was required whenever an aggregate field was mixed with a non-aggregate field?
WU3
3. National 5 recap: what do we call the process of comparing a program or query's actual output against its expected output?
Key vocabulary
Test plan
A prediction of the expected results of a query, written before the query is actually run.
Expected result
The output a query should produce, worked out by hand from known data, before running it.
Actual result
The real output a query produces when it is run against the database.
Fitness for purpose
Whether the query is structurally capable of doing what was asked — right tables, displayed fields, equi-joins, and search criteria. A query that cannot run is not fit for purpose.
Accuracy of output
Whether a running query's results and presentation are right — no extra columns, correct grouping and sorting, and appropriate aliases.
Testing and evaluating a query
What does "testing" mean for a DDD query?
Testing a query means checking that it returns the correct rows and values against data you already know — not just checking that it runs without an error. A query with perfectly valid syntax can still return the wrong results, so "it ran" is never enough evidence on its own that a query is correct.
Building a test plan: predict before you run
The standard DDD testing method is to draw the expected results table by hand — headings and all — using known sample data, before running the query. Only then is the query actually run, and the real output compared row by row against the prediction. Predicting first matters because it stops the temptation to just glance at whatever the query happens to produce and assume it looks about right — a genuine test needs a fixed target to compare against.
Fitness for purpose
Fitness for purpose asks whether the query is structurally capable of doing everything the original requirement asked for: it uses the right tables, displays the right fields, joins tables correctly, and applies every search criterion correctly. A too-loose or too-strict criterion is therefore a fitness failure. A query that fails to run at all — for example, one that mixes aggregate and non-aggregate fields without a required GROUP BY — is also not fit for purpose.
Accuracy of output
Accuracy of output asks whether a running query's results and presentation are right: it shows no extra unrequested columns, groups on the required fields, uses the requested sort order, and presents appropriate aliases. A query can be fit for purpose yet inaccurate if, for example, it runs but groups the results wrongly or omits the requested sort.
Fitness for purpose and accuracy can fail independently
These two evaluation criteria are deliberately separate, and a query can fail either one without failing the other. A query missing a requested field is not fit for purpose, even if every value it does return is perfectly accurate. A query with all the right fields, tables, and criteria can still be inaccurate, if — for example — it's sorted in the wrong direction. Naming both criteria explicitly, and identifying which one (or both) a flawed query fails, is exactly what DDD10's evaluation questions test.
Fitness for purpose — checklist
Are the required table(s) used?
Are the required fields displayed?
Have the tables been joined correctly using equi-joins?
Are all search criteria applied correctly — neither too loose nor too strict?
Accuracy — checklist
Are there no extra, unrequested columns in the output?
Are records grouped using the required field(s)?
Are records sorted correctly (ascending/descending, as asked)?
Are required aliases present?
Are aliases appropriate, correctly spelled, and used consistently?
Worked examples
Example 1 — Building a test plan for DDD8's per-instructor query
1
Known data: Fiona Reid (INS01) teaches PIL01, PIL02, and PRT01 (3 classes); David Wallace (INS02) teaches YOG01 and ZUM01 (2 classes); Priya Shah (INS03) teaches SPN01 and SPN02 (2 classes). The equi-join therefore contains 7 rows.
2
Predict the three-column output — firstName, surname, NumberOfClasses — by hand, before running the query.
✓
Run the query, then compare the same three columns row by row: Fiona Reid 3, David Wallace 2, and Priya Shah 2. Every predicted row matches the actual output, so the test passes.
Predicted result — written before running
firstName
surname
NumberOfClasses
Fiona
Reid
3
David
Wallace
2
Priya
Shah
2
Actual result — after running
firstName
surname
NumberOfClasses
Fiona
Reid
3
David
Wallace
2
Priya
Shah
2
The predicted table is written first, by hand, from known data — then the query is run and its actual output is checked against the prediction, row by row.
Example 2 — Not fit for purpose
1
Requirement: "List the name and fee of every class with 'Spin' anywhere in its name."
2
The SQL submitted only selects className — feePerSession is missing entirely.
✓
Not fit for purpose — a required field (fee) is missing, even though the query runs without error and the class names it does return are correct.
-- Submitted (flawed)
SELECT className
FROM Class
WHERE className LIKE '%Spin%';
Example 3 — Not accurate
1
Requirement: "For each instructor, count their classes, sorted from most classes to fewest."
2
The SQL submitted has the right fields, tables, and grouping — but is sorted by firstName alphabetically instead of by the count.
✓
Fit for purpose, but not accurate — every value returned is correct, but the sort order doesn't match what was requested.
-- Submitted (flawed — right data, wrong sort)
SELECT Instructor.firstName, Instructor.surname, COUNT(Class.classCode) AS NumberOfClasses
FROM Instructor, Class
WHERE Instructor.instructorRef = Class.instructorRef
GROUP BY Instructor.instructorRef
ORDER BY Instructor.firstName ASC;
Now you try
Requirement: "List the surname and total number of bookings for each member who has made at least one booking, with the members who've booked the most appearing first." A pupil submits this SQL. Is it fit for purpose, accurate, both, or neither? Explain.
SELECT Member.surname, COUNT(Booking.bookingRef) AS TotalBookings
FROM Member, Booking
WHERE Member.memberID = Booking.memberID
GROUP BY Member.memberID
ORDER BY TotalBookings DESC;
Fit for purpose and accurate — this one is correct. It uses the right table join, displays the required fields (surname and a booking count with a sensible alias), groups by the member, and sorts by the count in descending order, exactly as requested. The inner join returns the 13 members who have made at least one booking; it does not claim to include M014 and M015, who have no bookings. This example is included to check that "fit for purpose and accurate" is also a valid answer — not every submitted query needs to have a flaw.
⚠️ Common mistakes — examiner feedback
Treating "it ran without an error" as proof a query is correct. Syntax validity says nothing about whether the results are the right ones — testing means comparing against a genuine prediction.
Confusing fitness for purpose with accuracy. A missing field is a fitness-for-purpose problem; a wrong sort order or miscalculated value is an accuracy problem — they're different failures with different fixes.
Skipping the expected-results prediction and eyeballing the output instead. Without writing down what the correct answer should be first, it's easy to convince yourself an incorrect result "looks about right".
Forgetting alias correctness as part of accuracy. A missing, misspelled, or inconsistent alias is a genuine accuracy fault, even if every underlying value is correct.
📝 Exam tip
When asked to evaluate a query, name both criteria explicitly — state clearly whether it is or isn't fit for purpose, and separately, whether it is or isn't accurate — and justify each judgement with a specific reason tied to the actual SQL shown, not a general statement like "it works fine".
Task Set B — Core questions
Work through all questions. Written answers use self-assessment — compare with the model answer.
B1
Name the two DDD evaluation criteria (comma-separated).
B2
A query is missing a field that was explicitly requested, but every value it does show is correct. What is this?
B3
What should be written before a query is actually run, as part of a proper test plan?
B4
Requirement: "For each class that has at least one booking, show its name and count how many bookings it has received." A pupil submits: SELECT Class.dayOfWeek, COUNT(Booking.bookingRef) AS NumberOfBookings FROM Class, Booking WHERE Class.classCode = Booking.classCode GROUP BY Class.dayOfWeek; Evaluate it — is it fit for purpose, accurate, both, or neither? Explain.
Model answer
B5
Why is predicting expected results before running a query better than just looking at whatever the query produces?
B6
If a query's alias is missing entirely from an aggregate function's column, which of the two evaluation criteria does this affect?
B7
Requirement: "List the name of every class whose name is exactly 'Spin1', 'Spin2', or similarly numbered Spin classes — not any class that merely mentions Spin." A pupil submits: SELECT className FROM Class WHERE className LIKE '%Spin%'; Evaluate it.
Model answer
B8
Which of these is part of the fitness-for-purpose checklist, not the accuracy checklist?
B9
Explain why fitness for purpose and accuracy are tested as two separate criteria rather than being combined into one overall "is it correct?" judgement.
Model answer
Task Set C — Extension · Beyond the specification
Optional challenge questions. Not required for your exam.
C1
DDD's two evaluation criteria are narrower than SDD's evaluation criteria from SDD18. Why might a single SQL query need a different, narrower set of evaluation criteria than a whole Python program?
One possible answer
C2
This lesson's test plans use small, hand-picked sample data. What's a genuine limitation of only testing against a small known sample, rather than the full real database?
One possible answer
C3
Testing and evaluation are covered together in this lesson. Explain the difference between what testing checks and what evaluation checks, and how the two relate.
One possible answer
📁 File this in OneNote under: Higher Computing Science → Database Design & Development → DDD10
📌 Teacher notes — Shift+T to hide
Suggested timing: 8 min warm up · 15 min notes (test plans, the two checklists) · 15 min worked examples · 5 min now you try · 20 min Task Set B · 5–10 min Task Set C / review, if time allows.
Sourcing note: the source-faithful four-item fitness checklist and five-item accuracy checklist, together with the worked-example structure, are adapted directly from Chris Meechan's Lesson 9 (Testing & Evaluation) materials, confirmed as genuinely reusable, operational content per ddd/DDD.md §9 item 5. His original wildcard example ('red bean bag' matching 'red bean bag chair') is re-scenarioed here as B7's too-loose Spin criterion: a fitness-for-purpose failure whose present output is accurate only by coincidence.
Key misconception: pupils very commonly treat "the query ran" as sufficient evidence of correctness, and separately, tend to lump fitness-for-purpose and accuracy failures together into a vague "it's wrong" judgement rather than naming which specific criterion fails and why. Insist on both being named explicitly in every evaluation answer, including Task Set B.
Deliberate scope limit: this lesson does not restate SDD16/17's syntax/execution/logic error types or trace-table debugging methods — DDD's testing scope is specifically "does this query return the predicted rows/values", not general program-debugging technique.
SQA command words covered: "describe and exemplify" (testing), "evaluate" (fitness for purpose, accuracy of output).