Community resourceWorksheet

OCR H446 1.3.2 Entity relationships and linking tables

Part 2 of 13 · H446 1.3.2 · Databases

Cardinality is where students most often guess, so relationship modelling for H446 1.3.2 is taught here as a derivation from business rules and foreign-key placement. The worksheet also shows why a many-to-many relationship needs a linking table of its own.

Students will:

  • read a relationship in both directions before naming its cardinality
  • distinguish one-to-one, one-to-many and many-to-many relationships
  • explain how a foreign key in a child table supports a stated relationship
  • correct a model that confuses the number of tables with the cardinality between them
  • specify an entity relationship model in words, including the linking table a many-to-many case requires

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

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

Entity relationships and linking tables

An entity relationship model records what is stored and how instances can be related. Cardinality comes from business rules and keys, not from how many boxes happen to be drawn.

By the end, you will be able to

  • distinguish 1:1, 1:M and M:M relationships;
  • infer cardinality from a scenario and key placement;
  • resolve an M:M relationship with a linking table;
  • specify entities, keys and relationships in a fresh model.

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

Read cardinality in both directions

Cardinality and a linking tableCUSTOMERcustomer_id PKnameBOOKINGbooking_id PKcustomer_id FKevent_id FKquantityEVENTevent_id PKtitle1M1MCustomer and Event are M:M overall; Booking turns this into two 1:M relationships.

One Customer can appear in many Booking records; each Booking belongs to one Customer. One Event can appear in many Booking records; each Booking refers to one Event.

Customer and Event are therefore many-to-many overall: one customer can book many events and one event can be booked by many customers. Booking is the linking table that stores the two foreign keys and facts about their association.

Worked decision routine

Scenario: each venue has one current manager; each manager manages one venue.

  1. Ask how many managers one venue may have: one.
  2. Ask how many venues one manager may manage: one.
  3. Relationship: Venue 1:1 Manager.

Change the rule to one manager may manage several venues, while each venue still has one manager. The relationship becomes Manager 1:M Venue. The foreign key manager_id belongs in Venue because many venue rows may refer to one manager row.

Multiple choice1 mark

A performer can appear at many events, and each event can include many performers. Which design is most suitable?

  • AAn M:M relationship resolved by EventPerformer(event_id, performer_id)
  • BA 1:1 relationship with performer_id stored once in Event
  • CA 1:M relationship with event_id stored once in Performer
  • DTwo unrelated tables with no shared keys
Multiple choice1 mark

One customer can make many bookings, and each booking belongs to one customer. What relationship follows from Customer to Booking?

  • A1:1
  • BM:M with no link
  • CNo relationship can be inferred from this rule
  • D1:M
Fill in the blanks3 marks
If one A can relate to many B records and each B relates to one A, the relationship from A to B is gap 1. If many A records relate to many B records, introduce a gap 2 containing their gap 3.
  • 1:1
  • 1:M
  • M:M
  • linking table
  • foreign keys

Guided checkpoint: derive, do not guess

A library stores AUTHOR(author_id PK) and BOOK(book_id PK, author_id FK). The policy says one author may write several books; each book in this simplified model has one author.

Use evidence in this order: business rule → repeated foreign-key possibility → relationship in both directions.

Written answer4 marks

State the Author-Book relationship and explain how author_id in Book supports it.

Describe both directions.

Students type their answer here.

Written answer5 marks

A designer draws Customer 1:1 Event because there is one Customer table and one Event table. Explain the error and describe the corrected model.

Use the ticket-booking business rules and a linking table.

Students type their answer here.

Independent transfer: clubs and students

A college has students and clubs. A student may join several clubs. A club may have many students. For each membership, the system stores join_date and role.

Written answer9 marks

Specify an ER model in words. Name the three entities/tables, primary and foreign keys, and the cardinality of both relationships created by the linking table.

Write table names with fields, then relationships.

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 real-world thing represented in a database is an completion 1. A property stored about it is an completion 2. Relationship completion 3 states how many instances may participate. An M:M relationship is resolved into two one-to-many relationships by an completion 4 table.

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.