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 is structurally capable of doing what was asked: right tables, displayed fields, equi-joins, and search criteria.
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 a complete ER diagram, here is DDD2's Craigmillar diagram again. It includes all four entity rectangles, every attribute in its own connected oval, primary- and foreign-key markings, and the three labelled crow's-foot relationships: teaches, generates, and makes.
↔ Scroll horizontally if needed. The complete Craigmillar Community Sports Club ER diagram uses a separate connected oval for every attribute: primary-key text is underlined and foreign-key text has an asterisk. The crow's foot (fork) sits at the "many" end of each entity-to-entity relationship — here, at Class and at Booking on both of its relationships.
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 TimesRented
FROM Bike, Rental
WHERE Bike.bikeID = Rental.bikeID
GROUP BY Bike.bikeID
ORDER BY TimesRented 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: Fit for purpose, but not accurate. The query runs and structurally uses the right tables, displayed fields, equi-join, and grouping. Its output presentation is inaccurate because the required descending ORDER BY is missing and SUM(Rental.cost) has no appropriate alias. A runs-but-wrongly-grouped query would likewise be an accuracy failure.
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 two appropriate, specific validation checks for bikeID, and one for each of model, hireRatePerHour, and available.
1 mark — bikeID: Presence check (it is the primary key).
1 mark — bikeID: Field length check, maximum 6 characters.
1 mark — model: Field length check, maximum 25 characters.
1 mark — hireRatePerHour: Range check, e.g. £1–£20 (any sensible bounded range is acceptable).
1 mark — 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.
SQA past papers sometimes write aliases in square brackets — AS [Times Rented]. Recognise that style when reading exam SQL, but always write the portable form (AS TimesRented) — square brackets are a syntax error in MySQL/DataGrip.
Task Set B — Mixed practice paper
Timed attempt: work for 25–30 minutes, in exam conditions with no notes. Then self-mark: use the model answers and marking bullets only after the timed attempt is finished.
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
Marking bullet
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
💻 Practical check — in DataGrip
Task Set B's SQL questions above are answered on paper/on screen, under exam conditions, using GoGo Cycle Hire — deliberately unfamiliar data, since that's what the real exam gives you. Once Task Set B is marked, go back and actually run the equivalent techniques for real: using your existing craigmillar-sports-club-setup.sql database from DDD7-9, write and execute a GROUP BY query with an alias, an UPDATE with a WHERE clause, and a query using a wildcard — the same skills, on data you already know, to confirm you can execute what you've just practised writing.
Task Set C — Optional extra practice: full multi-part mock question
Optional extra practice of required Higher DDD content, presented as a realistic full-length 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.