Relational Database and Query Design
Create entity and occurrence models, keys, data dictionaries and complete designs for multi-table queries.
How to study Higher Computing Science
Move from requirements to structured designs, trace code and data precisely, and support every test or evaluation judgement with observable evidence.
Core concepts
Concept 1
Entity–relationship diagrams for three or more entities show names, attributes where required, relationship names and one-to-one, one-to-many or many-to-many cardinality.
Exam cue: Derive cardinality by asking how many occurrences can relate in each direction.
Concept 2
Entity-occurrence diagrams exemplify relationships with instances; compound keys combine attributes when one field alone is insufficient.
Exam cue: Check that every foreign key points to a suitable primary key.
Concept 3
A data dictionary records entity, attribute, primary or foreign key, type, size and validation such as presence, restricted choice, length or range.
Exam cue: Separate row-selection criteria from grouping and aggregate calculations.
Concept 4
Query designs specify tables or queries, fields, criteria, sort order, calculations and grouping.
Risk pitfalls and guardrails
Drawing a many-to-many relationship without considering how occurrences are represented.
Guardrail: Check route, scope, data type, boundary, loop condition, identifier and expected output before committing to the response.
Choosing a compound key that still permits duplicate real-world events.
Guardrail: Check route, scope, data type, boundary, loop condition, identifier and expected output before committing to the response.
Applying validation that rejects legitimate values or fails to enforce the requirement.
Guardrail: Check route, scope, data type, boundary, loop condition, identifier and expected output before committing to the response.
Memory anchors
Entity
A category of object or event about which the database stores data.
Cardinality
The one-to-one, one-to-many or many-to-many participation between entities.
Foreign key
An attribute that references the primary key of a related table.
Compound key
A key made from two or more attributes whose combined value identifies an occurrence.
Data dictionary
The design record for fields, keys, types, sizes and validation rules.
Query design
Tables + fields + criteria + sort + calculations + grouping.
Checkpoint rule
Do the check-up only after you can summarize each concept in one sentence and identify one dangerous pitfall from memory.
Knowledge Check (after reading)
Short check-up to confirm understanding of this module.
Check-up Questions
What is the purpose of a primary key?
A Book table contains publisherID matching Publisher.publisherID. What is Book.publisherID?
Answer all questions to submit.
Next step personalized recommendations
Continue learning
Move forward only after this module is stable.
What is Pass Harbor?
Completely free exam prep for 247 UK exams.
- Practice questions
- Flashcards
- Study guides
- Mock exams
- No registration
- No paywall
- Start instantly
“No more expensive exam prep. Quality study tools should be accessible to everyone.”