Community resourceWorksheet

OCR H446 1.3.2 Databases exam transition

Part 13 of 13 · H446 1.3.2 · Databases

This is the assessment piece for H446 1.3.2: an original public-library scenario with much less support and no teaching between questions. It runs from key definitions and relationship justification through normalisation, SQL and integrity to transaction reasoning, and closes by asking students what to revisit.

Students will:

  • explain and exemplify primary, foreign and secondary keys in an unfamiliar system
  • justify the relationships implied by a supplied set of tables
  • normalise a multi-table schema to third normal form without step-by-step support
  • write selection, nested and modification SQL for the library scenario
  • decide from evidence in their own attempt which database skill needs more work

Inside: 2 explanation cells, 1 multiple-choice question and 11 written answers. 82 marks, about 80 minutes.

Series: H446 1.3.2 · Databases, part 13 of 13.

Shared by Coding PathwayVerified teacher

  • 14 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 exam transition

This original public-library scenario assesses OCR H446 1.3.2 with reduced scaffolding. There is no teaching between questions. Auto-checkable feedback is held for teacher review.

Readiness

You should be able to derive relationships from keys, normalise to 3NF, interpret and write all assumed SQL structures, explain indexes and integrity, and apply ACID, locking and redundancy.

Exam routine

Use exact terminology. When a context is supplied, make each point about that context. For a comparison, cover both sides using shared criteria.

Scenario

A regional library service stores:

MEMBER(member_id PK, name, email)

BRANCH(branch_id PK, branch_name, town_id FK, town_name)

BOOK(book_id PK, title, category)

LOAN(member_id PK/FK, book_id PK/FK, loan_date, member_email, book_title, branch_id FK)

A member may borrow many books over time and a book may be loaned to different members at different times. For this assessment, assume the composite Loan key shown uniquely identifies a current loan. The service imports barcode scans, searches by title, exchanges nested branch and stock data, and processes concurrent loans for limited copies.

Written answer10 marks

Question 1: Explain the purpose of a primary key, foreign key and secondary key. Give one suitable example of each from or for this system. Explain two benefits of using these related tables instead of one flat file for the library service.

Use role plus example. Link each comparison point to the library data.

Students type their answer here.

Written answer7 marks

Question 2: State and justify the Member–Loan and Book–Loan relationships. Explain the overall Member–Book relationship.

Use keys and both-direction business rules.

Students type their answer here.

Written answer12 marks

Question 3: Normalise the supplied tables to 3NF. Show final relations and identify one partial and one transitive dependency removed.

Retain all facts and label keys.

Students type their answer here.

Written answer11 marks

Question 4: Write SQL returning member names and book titles for current loans where category is 'Computing' and the book title begins with P.

Use joins through Loan, AND, LIKE and a wildcard.

Students type their answer here.

Written answer6 marks

Question 5: Write a nested SELECT returning all fields for books in the same category as the book whose book_id is 'B17'.

Use * for all fields and evaluate the inner query as a category value.

Students type their answer here.

Written answer9 marks

Question 6: Write (a) an INSERT adding member M44, 'Safiya Khan', 'safiya@example.org'; (b) a DELETE removing only member M44; and (c) a statement removing the entire Member table.

Use INSERT, DELETE and DROP.

Students type their answer here.

Multiple choice1 mark

Question 7: Which is the most accurate effect of indexing Book.title?

  • AIt guarantees every Loan refers to a valid Book
  • BIt can speed title retrieval but needs extra storage and maintenance
  • CIt removes transitive dependencies from Book
  • DIt prevents all duplicate titles
Written answer3 marks

Question 8: A Loan insertion contains book_id B99, which is absent from Book. Explain what referential integrity requires and why.

Use foreign key, parent record and orphan.

Students type their answer here.

Written answer6 marks

Question 9: Recommend a capture method for printed barcodes and an exchange format for nested branch and stock data. Justify both and identify one limitation.

Use method feature → contextual consequence.

Students type their answer here.

Written answer10 marks

Question 10: Two branches attempt to loan the final available copy. Explain how transaction processing, record locking and ACID can prevent an incorrect state.

Develop a sequence and apply named properties.

Students type their answer here.

Written answer7 marks

Question 11: Evaluate keeping a synchronised recovery copy of the loan database while also repeating member_email in every Loan record.

Distinguish deliberate resilience from uncontrolled duplication.

Students type their answer here.

Written answer

Which database skill should you revisit, and what evidence from this attempt supports that decision?

This reflection is not assessed.

Students type their answer here.