All subjects · Information Technology · WIT14 3 hour practical exam on database solutions

Relationships and entity relationship diagrams

Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions

Specification 18.2.2 (a) asks you to implement one-to-one, one-to-many and many-to-many relationships. Entity relationship diagrams (ERDs, met in Unit 1) are the way to plan them before you build; this lesson focuses on building and using them.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

The three types

Drawing an ERD

An ERD shows entities as boxes and relationships as lines. In crow's foot notation the 'many' end of a line splits into three prongs. Written in text, the Harbour Bike Hire ERD is:

tblCustomer 1 ----< tblHire 1 ----< tblHireItem >---- 1 tblBike

Read it as: one customer has many hires; one hire has many hire items; one bike appears in many hire items. The M:M between hires and bikes has become two 1:M relationships through tblHireItem.

To work out a relationship, ask two questions in both directions: 'Can one A have many B?' and 'Can one B have many A?'

Implementing and using relationships

In Access you drag a primary key onto the matching foreign key in the Relationships window, then tick Enforce Referential Integrity, which shows 1 and the infinity symbol at the ends. In SQL the link is a REFERENCES clause on the foreign key. Once linked, a query can follow the relationship:

SELECT c.FirstName, h.HireID, h.HireDate
FROM tblCustomer c
JOIN tblHire h ON h.CustomerID = c.CustomerID
WHERE c.CustomerID = 1
ORDER BY h.HireDate

Result (3 rows):

FirstName | HireID | HireDate
----------+--------+-----------
Amira     | 101    | 2026-03-02
Amira     | 104    | 2026-03-20
Amira     | 108    | 2026-05-01

This follows the 1:M from customer 1 to their hires.

Worked example

A sports club has members and teams. A member can play in several teams and a team has many members. Each team has one coach, and a coach coaches only one team. Draw the ERD in text and list the tables.

  1. Member to Team: many-to-many, so add a link table tblTeamMember (MemberID, TeamID) with a composite key.
  2. Coach to Team: one-to-one; store CoachID in tblTeam as a unique foreign key (or merge the tables).
  3. ERD: tblMember 1 ----< tblTeamMember >---- 1 tblTeam, and tblCoach 1 ---- 1 tblTeam.

Answer: The M:M becomes two 1:M through a link table; the 1:1 links coach and team.

Key idea: 1:1 (shared key), 1:M (foreign key on the many side), M:M (resolve with a link table holding both keys, giving two 1:M). In crow's foot ERDs the many end has three prongs. Ask 'one A, many B?' in both directions.

Check yourself

  1. A country has one capital city and a capital belongs to one country. What relationship is this?
    • One-to-many
    • One-to-one
    • Many-to-many
    • Many-to-one only from city to country
    Show answer

    One-to-one. Each record relates to exactly one in the other table.

  2. Students borrow many library books over time and each book is borrowed by many students over time. How is this implemented?
    • By putting BookID in tblStudent
    • By putting StudentID in tblBook
    • By merging the two tables
    • With a link table of loans holding StudentID and BookID
    Show answer

    With a link table of loans holding StudentID and BookID. M:M relationships are resolved with a link table, here tblLoan, which can also store the loan date.

  3. In crow's foot notation, what does the three-pronged end of a line show?
    • The one side of the relationship
    • The many side of the relationship
    • The primary key
    • A validation rule
    Show answer

    The many side of the relationship. The crow's foot marks 'many'.

  4. How many rows does this query return on the Harbour Bike Hire database? ```sql SELECT c.FirstName, h.HireID, h.HireDate FROM tblCustomer c JOIN tblHire h ON h.CustomerID = c.CustomerID WHERE c.CustomerID = 1 ORDER BY h.HireDate ```
    Show answer

    3. Customer 1 (Amira) has 3 hires: Amira, 101, 2026-03-02; Amira, 104, 2026-03-20; Amira, 108, 2026-05-01.

  5. How many different bikes has customer 1 hired? This query follows two relationships: ```sql SELECT COUNT(DISTINCT BikeID) FROM tblHireItem WHERE HireID IN (SELECT HireID FROM tblHire WHERE CustomerID = 1) ```
    Show answer

    4. Following customer 1 to tblHire, then tblHireItem, gives 4 different bikes.

  6. Why might a designer split employees' basic details and their salary details into two tables with a one-to-one relationship?
    • To create a many-to-many relationship
    • Because salaries cannot be stored as Currency
    • So that access to the sensitive salary table can be restricted
    • To remove the need for a primary key
    Show answer

    So that access to the sensitive salary table can be restricted. Splitting sensitive fields into a separate table lets permissions protect them.

  7. Explain how a many-to-many relationship between Orders and Products is implemented in a relational database.
    Show answer

    Create a link table, such as tblOrderLine, containing OrderID and ProductID as foreign keys, which together form a composite primary key, giving two one-to-many relationships.. M:M is resolved by a link table holding the keys of both tables (often as a composite key), creating two 1:M relationships.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.