Community resourceWorksheet
OCR H446 1.3.2 Databases consolidation
Part 12 of 13 · H446 1.3.2 · Databases
Every strand of H446 1.3.2 meets in one community-festival case here, from entity design and normalisation through SQL to indexing, integrity, transactions and data capture. No new specification content is introduced, so this works as a consolidation or revision session once the topic has been taught.
Students will:
- state the cardinalities in a supplied schema and explain how its keys support them
- normalise the given design to third normal form and name the dependencies removed
- write a multi-table query combining two conditions
- write insertion and deletion statements for the same scenario
- bring indexing, referential integrity, locking and capture methods to bear on a single case
Inside: 4 explanation cells, 1 multiple-choice question, 1 fill-in-the-blanks cell and 7 written answers. 61 marks, about 70 to 85 minutes.
Series: H446 1.3.2 · Databases, part 12 of 13.
Shared by Coding PathwayVerified teacher
- 13 cells
- About 75 minutes
- CC BY-SA 4.0
- Shared 31 Aug 2026
- Updated 3 Sept 2026
Preview
The whole resource, exactly as a class sees it. Answers and marking are held back.
Databases consolidation
This worksheet interleaves the whole 1.3.2 sequence in a new community-festival scenario. It introduces no new specification content.
Retrieval route
- Model entities, keys and relationships.
- Test 1NF, then partial dependency for 2NF, then transitive dependency for 3NF.
- Read or write SQL and predict database state.
- Apply integrity, index and transaction mechanisms.
- Justify capture, management and exchange choices.
Stopping point: complete Tasks 1 to 4 in lesson one; use Tasks 5 to 7 for a second lesson or independent study.
Scenario
A community festival stores visitors, sessions and reservations.
VISITOR(visitor_id, name, email)
SESSION(session_id, title, venue_id, venue_name, capacity, spaces)
RESERVATION(visitor_id, session_id, visitor_email, session_title, quantity)
One visitor may reserve many sessions and one session may have many visitors. The current Reservation primary key is (visitor_id, session_id). Staff often search Session by title. Paper feedback uses tick boxes; partner organisations need exported feedback and session metadata.
Which statement identifies the most immediate 2NF problem in RESERVATION?
- Avisitor_email depends only on visitor_id and session_title only on session_id
- Bquantity depends on the whole composite key
- Cvisitor_id and session_id are both foreign keys
- Dthe relation contains no numeric primary key
Task 1: State the Visitor–Reservation and Session–Reservation cardinalities. Explain how the keys support both relationships, then explain one reason this relational design is preferable to one flat file in this context.
Describe both directions; do not infer from table count. Link the comparison to repeated festival data.
Students type their answer here.
Task 2: Normalise the supplied design to 3NF. Show final tables and all primary/foreign keys and identify the partial and transitive dependencies removed.
venue_name depends on venue_id.
Students type their answer here.
Task 3: Write SQL returning Visitor.name and Session.title for reservations where quantity > 2 and the session title begins with M.
Join from Reservation through both foreign keys and use LIKE with a wildcard.
Students type their answer here.
Task 4: Write (a) an INSERT for reservation visitor V8, session S4, quantity 2; and (b) a DELETE removing only reservations for session S9.
Use the normalised Reservation field order you defined.
Students type their answer here.
Task 5: Evaluate an index on Session.title and explain one referential-integrity check before inserting the V8/S4 reservation.
Give both benefit and cost of the index.
Students type their answer here.
Task 6: Two visitors try to take the final space in S4. Explain a safe transaction using locking and apply two relevant ACID properties.
Use the state spaces=1 → spaces=0.
Students type their answer here.
Task 7: Recommend capture and exchange methods for paper tick-box feedback plus session metadata, with one limitation and mitigation.
Consider OMR and a structured exchange format.
Students type their answer here.
Closed-book checkpoint
Complete each sentence from memory. There is no answer bank and correctness is held for teacher review.
Review your understanding
Before submitting, check that you can explain the main distinction in your own words, apply it in an unfamiliar context and justify each consequence rather than only naming a feature.