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.
The three types
- One-to-one (1:1): each record in A relates to at most one in B. Example: a member and their confidential medical record, split so that fewer staff can see the medical details. Implement by giving both tables the same primary key, with B's key also a foreign key to A.
- One-to-many (1:M): one record in A relates to many in B, but each B relates to one A. One customer makes many hires. Implement with a foreign key in the many table.
- Many-to-many (M:M): many A relate to many B. A hire includes many bikes and a bike is on many hires. Relational databases cannot implement this directly: you resolve it with a link table (also called a junction table) holding both keys, giving two one-to-many relationships.
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 tblBikeRead 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.HireDateResult (3 rows):
FirstName | HireID | HireDate
----------+--------+-----------
Amira | 101 | 2026-03-02
Amira | 104 | 2026-03-20
Amira | 108 | 2026-05-01This 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.
- Member to Team: many-to-many, so add a link table tblTeamMember (MemberID, TeamID) with a composite key.
- Coach to Team: one-to-one; store CoachID in tblTeam as a unique foreign key (or merge the tables).
- 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.
Check yourself
- 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.
- 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.
- 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'.
- 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.
- 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.
- 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.
- 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.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: IAL Information Technology scheme of work for Unit 4
- Pearson: WIT14/01 mark scheme, June 2024
Last updated 8 November 2026. Spotted a mistake? Tell us.