Community resourceWorksheet

OCR H446 1.3.2 Third normal form and complete normalisation

Part 5 of 13 · H446 1.3.2 · Databases

Third normal form removes dependencies between one non-key attribute and another, and H446 1.3.2 expects students to carry a relation the whole way from unnormalised data to 3NF. This worksheet keeps partial and transitive dependencies clearly apart and asks for the organisational benefit as well as the mechanics.

Students will:

  • distinguish a partial dependency from a transitive one
  • follow a dependency chain to find the attribute that determines another
  • decompose a relation already in 2NF into third normal form with correct keys
  • distinguish update, insertion and deletion anomalies using the same data
  • normalise a multi-table clinic schema from first to third normal form

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

Series: H446 1.3.2 · Databases, part 5 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.

Third normal form and complete normalisation

Third normal form (3NF) removes dependencies where a non-key attribute determines another non-key attribute. This is a transitive dependency.

By the end, you will be able to

  • distinguish partial and transitive dependencies;
  • decompose a 2NF relation into 3NF;
  • normalise a small dataset from unnormalised form to 3NF;
  • explain how normalisation reduces update, insertion and deletion anomalies.

Reactivate: 2NF requires 1NF and no partial dependency on part of a composite key.

Follow the dependency chain

Transitive dependency and third normal formevent_idprimary keyvenue_idnon-key attribute herevenue detailsvenue_namevenue_cityevent_id → venue_id → venue details is a transitive dependency.Create Venue(venue_id, venue_name, venue_city); retain venue_id as Event foreign key.

EVENT(event_id, event_title, venue_id, venue_name, venue_city) can be in 2NF because its key is not composite. It is not in 3NF: venue_name and venue_city depend on venue_id, a non-key attribute, rather than directly on event_id.

Move venue details to VENUE. Keep venue_id in EVENT as a foreign key.

Worked normalisation chain

Unnormalised BOOKING stores several event groups in one record.

  1. 1NF: create one row per booking-event pair; use (booking_id, event_id).
  2. 2NF: move booking_date to BOOKING and event_title/venue_id to EVENT; keep quantity in BOOKING_LINE.
  3. 3NF: move venue_name and venue_city to VENUE because venue_id determines them.

Final relations:

  • BOOKING(booking_id PK, customer_id FK, booking_date)
  • BOOKING_LINE(booking_id PK/FK, event_id PK/FK, quantity)
  • EVENT(event_id PK, event_title, venue_id FK)
  • VENUE(venue_id PK, venue_name, venue_city)

Each step answers a different question. Repeating the 1NF rule does not establish 2NF or 3NF.

Multiple choice1 mark

Which dependency violates 3NF in EVENT(event_id, venue_id, venue_name)?

  • Aevent_id determines venue_id
  • Bevent_id is the primary key
  • Cvenue_id is stored as a foreign key
  • Dvenue_id determines venue_name
Multiple choice1 mark

Which pair gives the complete test for Third Normal Form at OCR level?

  • AThe relation is in 1NF, and every value is atomic
  • BThe relation has a composite key, and all fields depend on part of it
  • CThe relation is in 2NF, and no non-key attribute depends on another non-key attribute
  • DThe database uses transactions, and every transaction is atomic
Fill in the blanks3 marks
1NF removes repeating groups and requires gap 1 values. 2NF removes gap 2 dependency on part of a composite key. 3NF removes gap 3 dependency between non-key attributes.
  • atomic
  • partial
  • transitive
  • foreign
  • encrypted

Guided checkpoint: diagnose the next move

MEMBER(member_id PK, member_name, postcode, town) is already in 2NF. The organisation uses a supplied postcode-to-town mapping, so postcode determines town.

Dependency chain: member_id → postcode → town.

Plan: name the violation → create a table keyed by its determinant → retain a reference in Member → explain the anomaly reduced.

Written answer6 marks

Transform MEMBER to 3NF and explain one benefit.

State both resulting tables and keys.

Students type their answer here.

Written answer6 marks

Distinguish an update anomaly, insertion anomaly and deletion anomaly using the unnormalised booking or event data.

For each, state an operation and the information at risk.

Students type their answer here.

Independent transfer: clinic appointments

APPOINTMENT(patient_id, doctor_id, appointment_time, patient_name, doctor_name, department_id, department_name)

Primary key: (patient_id, doctor_id, appointment_time). Patient details depend on patient_id; doctor_name and department_id depend on doctor_id; department_name depends on department_id.

Written answer12 marks

Normalise APPOINTMENT to 3NF. Show the final relations with primary and foreign keys and identify one partial and one transitive dependency removed.

Use Patient, Doctor, Department and Appointment or defensible equivalents.

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
In 3NF, a non-key attribute must depend on the key, the whole key and completion 1 but the key. If one non-key attribute determines another, place the dependent facts in a completion 2 table. Retain the determinant as a completion 3 key so the relationship can be 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.