← ACARA Digital Technologies Years 9–10

One-to-one and many-to-many relationships

A table that splits a many-to-many relationship into two one-to-many links by holding two foreign keys, such as Enrolment with studentId and subjectId.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 13, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 65, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 93, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 10, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 104, WA Computer Science ATAR Year 11 — Data Management — Module 31

Understanding One-to-one and many-to-many relationships in ACARA Digital Technologies Years 9–10

What it does: Crow's feet on both ends of the line show many on both sides, which is a many-to-many relationship. Enrolment sits in the middle. Each of Student and Subject has a one-to-many link to it, replacing the many-to-many line. Each person has at most one passport and each passport belongs to exactly one person, so it is one-to-one. A junction table splits the many-to-many relationship into two one-to-many links.

Common mistakes: A many-to-many link cannot be stored as a list in one field. Each enrolment row holds one subjectId (an INT foreign key), so the line should be ' subjectId INT'. The comma between the two selected fields is missing, so the query has a syntax error. It should be 'SELECT Student.firstName,'.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add One-to-one and many-to-many relationships to a class →

This module's key terms are also in a printable crossword (Modules 433–451) and word search (Modules 413–451).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A person and their passport (one person holds one passport, and each passport belongs to one person). Which relationship type is this?
Many-to-manyOne-to-one
Matches
Student(studentId PK, subjectId FK)
↓
A single subjectId foreign key in Student lets each student have only one subject, so it cannot show a many-to-many relationship
Applying
Meanings
Term
junction table
Meaning
A table that splits a many-to-many relationship into two one-to-many links by holding two foreign keys, such as Enrolment with studentId and subjectId.
Practice
SELECT students.firstName, subjects.name
Testing
Gaps
INT
Check Answer
Spots
3 studentId INT,
4 subjectIds VARCHAR(50)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Octopus' badge — Multi-Tasker, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A person and their passport (one person holds one passport, and each passport belongs to one person). Which relationship type is this?
A. Many-to-many
B. One-to-one ✓
Each person has at most one passport and each passport belongs to exactly one person, so it is one-to-one.
2. Students and subjects: one student studies many subjects, and one subject has many students. Which relationship type is this?
A. Many-to-many ✓
B. One-to-one
Both sides can have many matches, so the relationship is many-to-many.
3. How do you build a many-to-many relationship between Student and Subject in a relational database?
A. Add a junction table such as Enrolment ✓
B. Add one foreign key column to the Student table
A junction table splits the many-to-many relationship into two one-to-many links.
4. Which fields does an Enrolment junction table need to link Student and Subject?
A. Only a firstName and a lastName
B. studentId and subjectId, both foreign keys ✓
The junction table holds one foreign key pointing to each table it links.
5. Why can a single foreign key column NOT store a many-to-many relationship?
A. A field holds one value per record, so it can't list many subjects for one student ✓
B. Foreign keys only work with number data types
One foreign key value links to only one record. Many links need many rows, which a junction table provides.

Match the Pairs

Passport(passportId PK, personId FK UNIQUE) → A one-to-one relationship: each person has at most one passport and each passport belongs to one person
Enrolment(studentId FK, subjectId FK) → A junction table that links students and subjects by holding two foreign keys
A student can take many subjects and a subject has many students, so this is a many-to-many relationship → Student >o---o< Subject
A many-to-many relationship is split into two one-to-many links by adding a junction table between the entities → Student ||--o< Enrolment >o--|| Subject
Student(studentId PK, subjectId FK) → A single subjectId foreign key in Student lets each student have only one subject, so it cannot show a many-to-many relationship
Enrolment(enrolId PK, studentId PK) → studentId should be a foreign key, and the table is missing a subjectId foreign key to the Subject table

Key Term

junction table
A table that splits a many-to-many relationship into two one-to-many links by holding two foreign keys, such as Enrolment with studentId and subjectId.

Fill in the Blanks

1. CREATE TABLE Enrolment ( studentId INT, subjectId INT
Hint: Type the name of the second foreign key column, which links each enrolment row to a subject.
2. FOREIGN KEY (subjectId) REFERENCES Subject(subjectId)
Hint: Type the keyword that points a foreign key at the primary key in another table.
3. FROM Student JOIN Enrolment ON Student.studentId = Enrolment.studentId
Hint: Type the keyword that introduces the join condition matching the foreign key to the primary key.
4. CREATE TABLE Passport ( personId INT UNIQUE, passportNo INT
Hint: Type the constraint keyword that allows each person to appear only once, making the link one-to-one.
5. INSERT INTO Enrolment VALUES (1, 2), (1, 3);
Hint: Type the command that adds rows. Here one student is linked to two subjects through the junction table.

Spot the Error

Question 1
A CREATE TABLE Enrolment (
B enrolId INT PRIMARY KEY,
C studentId INT,
D subjectIds VARCHAR(50)
E );
Line D — A many-to-many link cannot be stored as a list in one field. Each enrolment row holds one subjectId (an INT foreign key), so the line should be ' subjectId INT'.
Question 2
A SELECT Student.firstName
B Enrolment.subjectId
C FROM Student
D JOIN Enrolment
E ON Student.studentId =
F Enrolment.studentId;
Line A — The comma between the two selected fields is missing, so the query has a syntax error. It should be 'SELECT Student.firstName,'.
Question 3
A CREATE TABLE Passport (
B passNo INT PRIMARY KEY,
C personId INT UNIQUE,
D FOREIGN KEY (passNo)
E REFERENCES Person(personId)
F );
Line D — In a one-to-one relationship the foreign key is the column that holds the other table's key. passNo is the Passport primary key, so the line should be ' FOREIGN KEY (personId)'.
Question 4
A INSERT INTO Enrolment
B VALUES (1, 'Maths');
C INSERT INTO Enrolment
D VALUES (1, 3);
Line B — Enrolment stores the subject's key, which is a number (subjectId), not the subject's name. The line should be 'VALUES (1, 2);'.
Question 5
A CREATE TABLE Subject (
B studentId INT,
C name VARCHAR(30)
D );
Line B — The Subject table needs its own primary key, subjectId. A single studentId here could link only one student, so students and subjects are linked in the Enrolment junction table instead.

Student with a class code? Join your class →