Community resourceWorksheet

OCR H446 1.3.2 Indexing and referential integrity

Part 7 of 13 · H446 1.3.2 · Databases

Indexing and referential integrity are easily blurred together, so this H446 1.3.2 worksheet keeps them apart: one is about finding data quickly, the other about keeping the links between tables meaningful. Students trace lookups and parent-child operations, then weigh what an index costs to maintain.

Students will:

  • explain how an index supports retrieval in a large table
  • decide whether an operation leaves every foreign key pointing at a record that exists
  • explain why a particular insertion, deletion or update would be rejected
  • weigh search speed against storage and update cost for a proposed index
  • design a safe procedure for deleting a record that other records still refer to

Inside: 7 explanation cells, 2 multiple-choice questions, 2 fill-in-the-blanks cells and 3 written answers. 29 marks, about 25 to 40 minutes.

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

Shared by Coding PathwayVerified teacher

  • 14 cells
  • About 30 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.

Indexing and referential integrity

Indexes help a database find data efficiently. Referential integrity protects the meaning of links between tables. They solve different problems.

By the end, you will be able to

  • explain how an index supports retrieval;
  • evaluate index storage and update trade-offs;
  • identify valid and orphaned foreign-key references;
  • explain how referential integrity constrains inserts, changes and deletions.

Reactivate: a foreign key stores a value that refers to a primary key in another table.

Two routes to reliable retrieval

Index lookup and referential integrityTitle indexCode Lab → row 18Jazz Night → row 42Robotics → row 7Web Studio → row 31EVENT rowsrow 7: E22 Roboticsrow 18: E31 Code Labrow 31: E44 Web Studiorow 42: E51 Jazz NightBOOKINGbooking_id B9event_id E22 FKThe index points to rows; the foreign key must match an existing Event primary key.

An index stores selected field values with pointers to records, usually in an order or structure that supports faster lookup than checking every row. It needs additional storage and must be updated when indexed values change.

Referential integrity means each foreign-key value must refer to an existing primary-key value, unless a permitted null/no-reference state is part of the design. It prevents orphan records.

Worked operations

EVENT contains event_id E22. Inserting BOOKING(B9, E22) preserves referential integrity.

Inserting BOOKING(B10, E99) fails if no Event E99 exists. Deleting Event E22 while Booking B9 still refers to it would also break the relationship. A database may reject the deletion, require related bookings to be handled first, or apply a defined cascading rule.

The index does not enforce this relationship. The key constraint does.

Multiple choice1 mark

What is the primary purpose of a database index?

  • ATo guarantee every foreign key has a parent record
  • BTo remove every duplicate value from a table
  • CTo provide an additional lookup route to records
  • DTo turn a flat file automatically into 3NF
Multiple choice1 mark

Which statement correctly separates an index from referential integrity?

  • AAn index can speed lookup; referential integrity requires a child foreign key to match an allowed parent key
  • BAn index guarantees every foreign key exists; referential integrity sorts records
  • CBoth terms mean that every field value is unique
  • DReferential integrity always removes the need for indexes
Fill in the blanks3 marks
A foreign key with no matching parent key creates an gap 1 record. gap 2 integrity prevents this. An index may speed up gap 3 but adds storage and update work.
  • orphan
  • duplicate
  • referential
  • transaction
  • retrieval

Guided checkpoint: predict the consequence

CUSTOMER contains C1 and C2. BOOKING contains B1→C1 and B2→C2.

For each operation, ask: does the referenced parent exist after the operation?

  1. Insert B3→C1.
  2. Insert B4→C9.
  3. Delete C2 while B2 remains.
  4. Change B1 from C1 to C2.
Written answer6 marks

State which four operations preserve referential integrity and explain each rejection.

Refer to the matching primary key.

Students type their answer here.

Written answer6 marks

Evaluate adding an index on event_title in a large event table that is searched often but updated every minute.

Discuss retrieval, storage and maintenance before concluding.

Students type their answer here.

Independent transfer: health appointments

PATIENT(patient_id PK, name) and APPOINTMENT(appointment_id PK, patient_id FK, time) serve a busy clinic. Staff search by appointment time and sometimes delete duplicate patient records.

Written answer8 marks

Recommend an index and design an integrity-safe procedure for deleting a duplicate Patient record that still has appointments.

State what must happen to every referencing appointment.

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.

Fill in the blanks4 marks
An index stores search values with completion 1 to table rows. It can reduce search work but needs extra completion 2 and maintenance after updates. A child foreign key must match an existing completion 3 key unless null is permitted. This preserves valid table completion 4.

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.