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

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

junction table

Fill in the Blanks

Complete each line of code by filling in the missing parts.

CREATE TABLE Enrolment ( studentId INT, _________ INT

FOREIGN KEY (subjectId) __________ Subject(subjectId)

FROM Student JOIN Enrolment __ Student.studentId = Enrolment.studentId

CREATE TABLE Passport ( personId INT ______, passportNo INT

___________ Enrolment VALUES (1, 2), (1, 3);

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A CREATE TABLE Enrolment (

B enrolId INT PRIMARY KEY,

C studentId INT,

D subjectIds VARCHAR(50)

E );

Question 2

A SELECT Student.firstName

B Enrolment.subjectId

C FROM Student

D JOIN Enrolment

E ON Student.studentId =

F Enrolment.studentId;

Question 3

A CREATE TABLE Passport (

B passNo INT PRIMARY KEY,

C personId INT UNIQUE,

D FOREIGN KEY (passNo)

E REFERENCES Person(personId)

F );

Question 4

A INSERT INTO Enrolment

B VALUES (1, 'Maths');

C INSERT INTO Enrolment

D VALUES (1, 3);

Question 5

A CREATE TABLE Subject (

B studentId INT,

C name VARCHAR(30)

D );

Answer Key

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

Section A: Quick Questions

B — One-to-one

A — Many-to-many

A — Add a junction table such as Enrolment

B — studentId and subjectId, both foreign keys

A — A field holds one value per record, so it can't list many subjects for one student

Section B: 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

Section C: Definitions

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.

Section D: Fill in the Blanks

CREATE TABLE Enrolment (
  studentId INT,
  subjectId INT

FOREIGN KEY (subjectId)
REFERENCES Subject(subjectId)

FROM Student JOIN Enrolment
ON Student.studentId = Enrolment.studentId

CREATE TABLE Passport (
  personId INT UNIQUE,
  passportNo INT

INSERT INTO Enrolment
VALUES (1, 2),
       (1, 3);

Section E: Spot the Error

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'.

A — The comma between the two selected fields is missing, so the query has a syntax error. It should be 'SELECT Student.firstName,'.

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)'.

B — Enrolment stores the subject's key, which is a number (subjectId), not the subject's name. The line should be 'VALUES (1, 2);'.

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.