Database Design & Development · Testing & Evaluation
DDD10 — DDD testing and evaluation
📅 To be scheduled
⏱ ~50–60 minutes · single 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 a query does everything the original requirement asked for — the right fields, tables, and criteria.
Accuracy of output
Whether a query's results are correctly calculated, grouped, sorted, and labelled — not whether it addresses the right requirement.
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 a query does everything the original requirement actually asked for. A query can run perfectly and still fail this test, if it's missing a requested field, uses the wrong table, or applies too loose or too strict a search condition.
Accuracy of output
Accuracy of output asks whether the results themselves are calculated, grouped, sorted, and labelled correctly — assuming the query is already addressing the right requirement. A query can be perfectly fit for purpose (right fields, right tables, right criteria) and still be inaccurate, if a calculation is wrong, records are grouped by the wrong field, or the sort order doesn't match what was asked.
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
Have the tables been joined correctly (equi-joins)?
Are all the required search criteria applied?
Are the required table(s) used?
Are the required fields displayed?
Accuracy — checklist
Is there extra, unrequested information in the output?
Are records grouped using the required field(s)?
Are records sorted correctly (ascending/descending, as asked)?
Are aggregate functions and calculated fields given aliases?
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: INS01 teaches PIL01 and PIL02 (2 classes); INS02 teaches YOG01 (1 class); INS03 teaches SPN01 and SPN02 (2 classes).
2
Predict the expected results table by hand, before running the query.
✓
Run the query, then compare row by row — every predicted row matches the actual output, so the test passes.
firstName
surname
Number Of Classes (expected)
—
—
2 (INS01)
—
—
1 (INS02)
—
—
2 (INS03)
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 [Number Of Classes]
FROM Instructor, Class
WHERE Instructor.instructorRef = Class.instructorRef
GROUP BY Instructor.instructorRef
ORDER BY Instructor.firstName ASC;
Now you try
Requirement: "List every member's surname and the total number of bookings they've made, 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 [Total Bookings]
FROM Member, Booking
WHERE Member.memberID = Booking.memberID
GROUP BY Member.memberID
ORDER BY [Total Bookings] 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. 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, count how many bookings it has received." A pupil submits: SELECT Class.className, COUNT(Booking.bookingRef) AS [Number Of Bookings] 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: both checklists and the "not fit for purpose / not accurate" 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 the Spin-class wildcard example in B7, keeping the same underlying teaching point.
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).