DDD10 — DDD testing and evaluation
- 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.
- 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.
Answer before the lesson begins — it's fine if you're unsure.
Key vocabulary
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.
- 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?
- 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
firstName, surname, NumberOfClasses — by hand, before running the query.| firstName | surname | NumberOfClasses |
|---|---|---|
| Fiona | Reid | 3 |
| David | Wallace | 2 |
| Priya | Shah | 2 |
| firstName | surname | NumberOfClasses |
|---|---|---|
| Fiona | Reid | 3 |
| David | Wallace | 2 |
| Priya | Shah | 2 |
className — feePerSession is missing entirely.-- Submitted (flawed)
SELECT className
FROM Class
WHERE className LIKE '%Spin%';firstName alphabetically instead of by the count.-- 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;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.
- 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.
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".
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.SELECT className FROM Class WHERE className LIKE '%Spin%'; Evaluate it.📁 File this in OneNote under:
Higher Computing Science → Database Design & Development → DDD10
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).