Community resourceWorksheet

OCR H446 1.3.2 Second normal form

Part 4 of 13 · H446 1.3.2 · Databases

Second normal form only bites when a relation has a composite key, so partial dependency for H446 1.3.2 is taught here by testing each non-key attribute against both parts of that key. Students then decompose the relation and reconnect the resulting tables through their keys.

Students will:

  • state the prerequisite and the dependency test for second normal form
  • trace which part of a composite key determines each non-key attribute
  • identify every partial dependency in a supplied relation
  • decompose a relation into tables that keep each fact reachable through keys
  • explain an update or insertion problem caused by leaving a partial dependency in place

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

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

Second normal form

Second normal form (2NF) asks whether every non-key attribute depends on the whole primary key. The test matters when a relation has a composite key.

By the end, you will be able to

  • state the prerequisites and dependency test for 2NF;
  • identify partial dependencies;
  • decompose a 1NF relation into 2NF tables;
  • explain how the change reduces anomalies.

Reactivate: a composite key contains more than one field; 1NF removes repeating groups and non-atomic values.

Ask what determines each attribute

Partial and full dependencies for second normal formcomposite primary keybooking_idevent_idboth fields identify one BookingLinebooking_datedetermined by booking_idevent_titledetermined by event_idquantitydetermined by both fieldsPARTIAL: booking_id onlyPARTIAL: event_id onlyFULL: whole composite keyMove booking_date to BOOKING and event_title to EVENT; keep quantity in BOOKING_LINE.

In BOOKING_LINE(booking_id, event_id, booking_date, event_title, quantity), the key is (booking_id, event_id).

  • booking_date depends only on booking_id;
  • event_title depends only on event_id;
  • quantity depends on the whole pair because it describes this event within this booking.

The first two are partial dependencies and violate 2NF.

Worked decomposition

Create:

  • BOOKING(booking_id PK, booking_date)
  • EVENT(event_id PK, event_title)
  • BOOKING_LINE(booking_id PK/FK, event_id PK/FK, quantity)

Every non-key attribute now depends on the whole key of its table. The original relation was already in 1NF; the decomposition removes the additional 2NF problem.

Benefit chain: event_title stored once → one edit when a title changes → fewer inconsistent copies.

Multiple choice1 mark

Which statement correctly completes the definition of 2NF?

  • AEvery field contains letters and numbers
  • BEvery table has exactly one non-key field
  • CIt is in 1NF and every non-key attribute depends on the whole primary key
  • DNo foreign key value may repeat
Multiple choice1 mark

Which pair gives the complete test for Second Normal Form?

  • AEvery value is stored twice, and every table has two keys
  • BThe relation is in 1NF, and every non-key attribute depends on the whole key with no partial dependency
  • CThe relation is in 3NF, and all fields are numeric
  • DNo non-key attribute depends on another non-key attribute only
Fill in the blanks3 marks
A dependency on only part of a composite key is a gap 1 dependency. To reach 2NF, move that attribute to a table where its determinant is the gap 2. An attribute that describes the association may depend on the gap 3 key.
  • partial
  • transitive
  • primary key
  • foreign key
  • whole composite

Guided checkpoint: workshop attendance

ATTENDANCE(student_id, workshop_id, student_name, workshop_title, attendance_status)

Primary key: (student_id, workshop_id).

Use three tests: Does student_name need workshop_id? Does workshop_title need student_id? Does attendance_status need both?

Written answer7 marks

Identify all partial dependencies and give the three-table 2NF decomposition.

State primary and foreign keys.

Students type their answer here.

Written answer3 marks

Explain one update or insertion problem caused by keeping workshop_title in every Attendance row.

Use repeated value → operation → possible consequence.

Students type their answer here.

Independent transfer: team assignments

ASSIGNMENT(student_id, project_id, student_email, project_name, hours) is in 1NF. Its primary key is (student_id, project_id).

Written answer9 marks

Normalise ASSIGNMENT to 2NF. Show dependencies, resulting tables and keys, then explain why hours remains in the linking table.

Test each non-key attribute against both parts of the key.

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
A relation must already satisfy completion 1 before testing 2NF. In 2NF, every non-key attribute depends on the completion 2 key, not only one component. Decomposition moves attributes beside their completion 3 and reconnects tables using keys without losing 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.