One-to-many relationships between tables

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. In a one-to-many relationship, how many records in the related table can match ONE record in the primary table?

A Only one record

B Many records

2. A school database has a Classes table and a Students table. Which statement describes the relationship?

A One class has many students

B One student has many classes in this design

3. Where does the foreign key go in a one-to-many relationship?

A In the table on the 'many' side

B In the table on the 'one' side

4. Which Students table design correctly links each student to one class?

A Students(StudentID, Name, ClassID)

B Classes(ClassID, Room, StudentID)

5. Why use a one-to-many relationship instead of typing the class room and teacher into every student row?

A It makes every record longer

B It avoids repeating the same data

Match the Pairs

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

Left column		Right column
1Students.ClassID (FK) → Classes.ClassID (PK)		AStudent names are stored as repeating columns in the one side's table. Instead, create a Students table with a ClassID foreign key, with one row per student.
2Classes (1) ───< Students (many)		BEach student row stores the ClassID of one class, so many students can point to the same class record.
3The foreign key goes on the 'many' side of the relationship, so each student stores the class they belong to.		CSELECT Students.Name, Classes.Teacher FROM Students JOIN Classes ON Students.ClassID = Classes.ClassID;
4Storing the class details once in a Classes table avoids repeating them in every student record.		DOne class record is linked to many student records, a one-to-many relationship.
5Classes table: ClassID (PK), Teacher, Student1, Student2, Student3		EThe foreign key is on the 'one' side. It should be on the 'many' side, so Students has ClassID (FK) and Classes has no StudentID.
6Classes table: ClassID (PK), StudentID (FK)		FCREATE TABLE Students (StudentID INT PRIMARY KEY, ClassID INT, FOREIGN KEY (ClassID) REFERENCES Classes(ClassID));

Definitions

one-to-many relationship

Fill in the Blanks

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

SELECT * FROM ________ WHERE classId = 3;

FOREIGN KEY (classId) REFERENCES _______(classId)

SELECT className, firstName FROM classes ____ students ON classes.classId = students.classId;

INSERT INTO students (firstName, _______) VALUES ('Ava', 3);

CREATE TABLE classes ( classId INT ___________, className VARCHAR(20));

Spot the Error

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

Question 1

A SELECT ClassID, SUM(*)

B FROM Student

C GROUP BY ClassID;

Question 2

A CREATE TABLE Student (

B StudentID INT PRIMARY KEY,

C ClassID INT PRIMARY KEY,

D FOREIGN KEY (ClassID)

E REFERENCES Class(ClassID)

F );

Question 3

A SELECT c.ClassName, s.Name

B FROM Class c JOIN Student s

C ON c.ClassID = s.StudentID;

Question 4

A SELECT Teacher, StudentName

B FROM Class

C WHERE ClassID = 1;

Question 5

A CREATE TABLE Class (

B ClassID INT PRIMARY KEY,

C StudentID INT,

D Teacher VARCHAR(30)

E );

Answer Key

One-to-many relationships between tables

Section A: Quick Questions

B — Many records

A — One class has many students

A — In the table on the 'many' side

A — Students(StudentID, Name, ClassID)

B — It avoids repeating the same data

Section B: Match the Pairs

Students.ClassID (FK) → Classes.ClassID (PK) → Each student row stores the ClassID of one class, so many students can point to the same class record.

Classes (1) ───< Students (many) → One class record is linked to many student records, a one-to-many relationship.

The foreign key goes on the 'many' side of the relationship, so each student stores the class they belong to. → CREATE TABLE Students (StudentID INT PRIMARY KEY, ClassID INT, FOREIGN KEY (ClassID) REFERENCES Classes(ClassID));

Storing the class details once in a Classes table avoids repeating them in every student record. → SELECT Students.Name, Classes.Teacher FROM Students JOIN Classes ON Students.ClassID = Classes.ClassID;

Classes table: ClassID (PK), Teacher, Student1, Student2, Student3 → Student names are stored as repeating columns in the one side's table. Instead, create a Students table with a ClassID foreign key, with one row per student.

Classes table: ClassID (PK), StudentID (FK) → The foreign key is on the 'one' side. It should be on the 'many' side, so Students has ClassID (FK) and Classes has no StudentID.

Section C: Definitions

one-to-many relationship — One record in a primary table matches many records in a related table. The foreign key sits on the many side, e.g. one class has many students, so class details are not repeated.

Section D: Fill in the Blanks

SELECT * FROM students WHERE classId = 3;

FOREIGN KEY (classId)
REFERENCES classes(classId)

SELECT className, firstName
FROM classes JOIN students
ON classes.classId = students.classId;

INSERT INTO students (firstName, classId)
VALUES ('Ava', 3);

CREATE TABLE classes (
classId INT PRIMARY KEY,
className VARCHAR(20));

Section E: Spot the Error

A — To see how many students are in each class, count the matching rows on the 'many' side with COUNT(*). SUM(*) is not valid. It should be SELECT ClassID, COUNT(*).

C — ClassID in Student is a foreign key, not a primary key. If it were a primary key, each class could only appear once, so a class could only have one student. It should be ClassID INT, so many students can share a ClassID.

C — The join must match the primary key in Class with the foreign key in Student. s.StudentID is the student's own key, so the match is wrong. It should be ON c.ClassID = s.ClassID.

A — The query reads from the Class table, but StudentName is stored in the Student table on the 'many' side. Class details such as Teacher and ClassName are stored once in Class. It should be SELECT Teacher, ClassName.

C — The Class table is the 'one' side, so it must not hold a StudentID. A single StudentID column would let each class link to only one student and put the foreign key on the wrong side. The foreign key ClassID belongs in Student. Line 3 should store class details, e.g. ClassName VARCHAR(20),.