I can answer genuine past-paper-style questions spanning every DDD strand.
I can identify which DDD lesson a given question is drawing on, and recall the relevant technique.
I can combine skills from more than one lesson to answer a single multi-part question.
Success criteria
I can complete a mixed practice paper covering analysis, ER diagrams, cardinality, data dictionaries, validation, query design, SQL, and evaluation.
I can self-assess my own written answers honestly against a model answer.
I can identify my own weakest DDD strand from this session's results, ready to revisit before the exam.
Warm up — quick-fire unit recap
One question per strand — answer before the practice paper begins.
WU1
1. (DDD1) What is the difference between an end-user requirement and a functional requirement, in one word each — who they're framed around?
WU2
2. (DDD3) Which cardinality type resolves via a linking entity like Booking?
WU3
3. (DDD8) What does the "GO" mnemonic stand for?
Key vocabulary — unit-wide recap
Functional requirement
What the database itself must process or store, distinct from what the end-user must be able to do.
DDD1
Cardinality
How many records in one entity can relate to a record in another — one-to-one, one-to-many, or many-to-many.
DDD3
Compound key
A primary key made from two or more attributes together, used when no single attribute is unique alone.
DDD4
Restricted choice
A validation check confirming a value matches one item from a fixed, pre-defined list.
DDD5
Grouping
Collecting records into sets sharing a common value, so a calculation applies separately per set.
DDD6
Aggregate function
A function calculating a single summary value across records: MIN, MAX, AVG, SUM, COUNT.
DDD8
Fitness for purpose
Whether a query does everything the original requirement asked for.
DDD10
Consolidating the whole unit
How DDD is assessed
DDD is one of two optional 25-mark question paper sections (alongside WDD) — pupils answer either DDD or WDD, not both, in Section 2 of the 80-mark, 2-hour question paper. DDD also contributes to Task 2 of the 40-mark assignment (15 marks, again as a choice against WDD's Task 3). Every DDD1–10 lesson has been building toward exactly this: a real exam paper mixing analysis, design, implementation, testing, and evaluation questions on a single unfamiliar database scenario, not the Craigmillar Community Sports Club scenario used throughout this course.
The unit, strand by strand
Analysis (DDD1) asks you to read a scenario and separate end-user requirements from functional requirements. Design spans four lessons: ER diagrams and cardinality (DDD2–3), compound keys and data dictionaries (DDD4), validation (DDD5), and query design (DDD6). Implementation is SQL itself — retrieval (DDD7), aggregation (DDD8), and mutation (DDD9). Testing and evaluation (DDD10) closes the loop, checking whether a finished query actually does what was asked, correctly.
Why past-paper practice matters more than re-reading notes
Re-reading DDD1–10's notes tells you whether you recognise a technique when you see it explained. Answering genuine mixed questions under the same conditions as the real exam — unfamiliar scenario, no notes open, a real mark allocation — tells you whether you can actually produce the technique yourself, unprompted. This is a genuinely different skill, and it's the one the exam actually tests.
A quick ER diagram recap, for context
The practice questions below use a new, unfamiliar scenario rather than reusing Craigmillar's — but as a reminder of what a complete ER diagram output should look like, here's Craigmillar's from DDD2 one more time.
Member
memberID
firstName
surname
address
town
postcode
dateJoined
Class
classCode
className
instructorRef
dayOfWeek
startTime
capacity
feePerSession
Instructor
instructorRef
firstName
surname
qualified
Booking
bookingRef
memberID
classCode
bookingDate
attended
Worked examples — mixed, multi-strand questions
Example 1 — Design question, spanning cardinality and compound keys (4 marks)
1
New scenario, GoGo Cycle Hire: a bike-hire company has a Customer entity, a Bike entity, and a Rental entity linking them. A customer can rent many different bikes over time; a bike can be rented by many different customers over time, but never by two customers at once.
2
Q: State the cardinality between Customer and Bike (ignoring Rental), and explain whether Rental could use a compound key of customerID + bikeID.
✓
A: Many-to-many between Customer and Bike directly — both sides can have multiple connections. A compound key of customerID + bikeID would only work if a customer could rent a specific bike at most once ever, which isn't stated and is unlikely for a hire company (the same customer will likely rent the same bike again on a different day) — so Rental needs its own single-attribute primary key instead, exactly like Booking in the sports club scenario.
Example 2 — Implementation question: SQL from a written requirement (4 marks)
1
Requirement: "For each bike, show its model and how many times it's been rented, most-rented first."
2
Non-aggregate (model) mixed with aggregate (COUNT) → GROUP BY required; sort by the count, descending.
✓
See the full statement below.
SELECT Bike.model, COUNT(Rental.rentalRef) AS [Times Rented]
FROM Bike, Rental
WHERE Bike.bikeID = Rental.bikeID
GROUP BY Bike.bikeID
ORDER BY [Times Rented] DESC;
Example 3 — Evaluation question: fitness for purpose vs accuracy (3 marks)
1
Requirement: "List every customer's name and total amount spent on rentals, customers with the highest spend first."
2
Submitted SQL: SELECT Customer.firstName, Customer.surname, SUM(Rental.cost) FROM Customer, Rental WHERE Customer.customerID = Rental.customerID GROUP BY Customer.customerID; — no alias, no ORDER BY.
✓
A: Not fully fit for purpose (the required sort order is missing entirely) and not fully accurate (SUM(Rental.cost) has no alias, so the column heading is unclear) — two separate faults, one per criterion, from the same statement.
Now you try — full mini past-paper question (5 marks)
GoGo Cycle Hire wants to add validation to its Bike entity: bikeID (PK, text, 6), model (text, 25), hireRatePerHour (number), available (Boolean).
State an appropriate, specific validation rule for each of the four attributes.
bikeID — Presence check (it's the primary key) and field length check, max 6 characters.
model — Field length check, max 25 characters.
hireRatePerHour — Range check, e.g. £1–£20 (any sensible bounded range is acceptable).
available — Restricted choice: True, False.
⚠️ Common mistakes — unit-wide top four
Running UPDATE or DELETE with no WHERE clause (DDD9) — always write and check WHERE before running, never after.
Writing ORDER BY before GROUP BY (DDD8) — the fixed clause order is SELECT, FROM, WHERE, GROUP BY, ORDER BY.
Naming a validation type without exemplifying the actual rule (DDD5) — "range check" alone is incomplete; state the real minimum and maximum.
Conflating fitness for purpose with accuracy (DDD10) — a missing required field is a fitness-for-purpose fault; a wrong calculation or sort order is an accuracy fault. Name both criteria explicitly in every evaluation answer.
📝 Exam tip
When a DDD question uses an unfamiliar scenario (as every real exam question will), spend the first minute identifying the entities, their likely primary keys, and which relationships are one-to-many versus many-to-many, exactly the way DDD1–3's method works — before attempting to answer. Rushing straight into SQL or a data dictionary without first understanding the scenario's structure is the single most common source of lost marks.
Task Set B — Mixed practice paper
Genuine past-paper-style questions spanning every DDD strand. Mark values shown, as in a real exam.
B1
GoGo Cycle Hire's Customer entity needs a query to list customers who've never made a rental. Is this best framed as an end-user requirement or a functional requirement? 1 mark
B2
On an entity-occurrence diagram, if every dot on one side has multiple lines but every dot on the other side has exactly one, what is the cardinality? 1 mark
B3
Rental has attributes rentalRef, customerID, bikeID, startDate, returnDate, cost. Build its full data dictionary, stating PK/FK, data type, and field size for each. 4 marks
Model answer
B4
State a specific, exemplified validation rule for Rental's cost, assuming a sensible rental never costs more than £100. 1 mark
B5
Requirement: "List the model and hourly rate of every currently available bike, cheapest first." Complete the five-row query design. 4 marks
Model answer
B6
Write the SQL for the query designed in B5. 3 marks
Model answer
B7
Write the SQL to mark bike 'BK012' as no longer available (available = False). 2 marks
Model answer
B8
A pupil writes DELETE FROM Rental; intending to remove one specific overdue rental. What is the consequence? 1 mark
B9
Requirement: "For each bike, show its model and total number of rentals." A pupil submits: SELECT Bike.model, COUNT(Rental.rentalRef) FROM Bike, Rental WHERE Bike.bikeID = Rental.bikeID; (no GROUP BY). Evaluate it. 3 marks
Model answer
Task Set C — Extension: full multi-part mock question
Optional. Not required for your exam, but a realistic full-length practice question.
C1
GoGo Cycle Hire wants to add a StaffMember entity to record which staff member processed each rental. (a) List all entities needed, including the new one. (b) State the cardinality between every pair of directly connected entities. (c) Build the data dictionary row for the new StaffMember entity, and state what new foreign key Rental would need. 7 marks
Model answer
📁 File this in OneNote under: Higher Computing Science → Database Design & Development → DDD11
📌 Teacher notes — Shift+T to hide
Suggested timing: 5 min warm up · 10 min notes/unit recap · 15 min worked examples · 5 min now you try · 25–30 min Task Set B, run under exam-style conditions (no notes, timed) · remaining time for Task Set C or whole-class review of common wrong answers.
Structural note: per ddd/DDD.md §3, this lesson deliberately combines SDD's two-lesson pattern (SDD21 practice + SDD22 test) into one, since DDD is 11 lessons against SDD's 22. All practice questions here use a new, unfamiliar scenario (GoGo Cycle Hire) rather than reusing Craigmillar Community Sports Club, so pupils practise transferring the technique to unseen data — exactly the skill the real exam requires.
Marking suggestion: Task Set B's mark values (1–4 marks per question, 20 marks total) are illustrative of real SQA weighting patterns, not an official past paper — treat pupil self-assessment against the model answers as formative, and use whole-class review of B9 and C1 in particular to check for the fitness-for-purpose/accuracy conflation flagged as the unit's top mistake.
This is the final DDD lesson. After this session, pupils have covered every strand in the Higher DDD content statement (ddd/DDD.md §1) — direct them to the course-wide revision resources (course-overview.html, course-spec.html) for cross-unit consolidation ahead of the prelim.