Foreign key and table relationships

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which statement describes a foreign key?

A A field that holds a value copied from any column in the same table

B A field that holds the primary key value of a record in another table

2. A Students table has a field ClassID that matches the ClassID primary key in the Classes table. What is ClassID in Students?

A A foreign key

B A primary key

3. One class has many students, and each student belongs to one class. What type of relationship is this?

A One-to-one

B One-to-many

4. In a one-to-many relationship, where does the foreign key go?

A In the table on the 'many' side (Students)

B In the table on the 'one' side (Classes)

5. Why do we store ClassID in the Students table instead of typing the full class name and teacher for every student?

A It avoids repeating data, so each class is stored only once

B It makes the database store more copies of each class, to be safe

Match the Pairs

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

Left column		Right column
1FOREIGN KEY (class_id) REFERENCES Classes(class_id)		AA field in the Students table that stores the class_id of the class the student belongs to
2class_id INTEGER		BThe foreign key value 99 does not match any primary key in Classes, so it links to a class that does not exist.
3One-to-many relationship: one class can have many students, so the student table holds the class link		CStudents(student_id, name, class_id)
4Avoiding repeated data: store the class details once in Classes and link to them with the class ID		DMakes class_id in this table a link to the primary key class_id in the Classes table
5Students(student_id, name, class_name, teacher, room)		EClass details are typed into every student record instead of using a foreign key, so data is repeated and can become inconsistent. Store them in Classes and add class_id to Students.
6Students: class_id = 99, Classes: 1, 2, 3		FClasses(class_id, class_name, teacher)

Definitions

FOREIGN KEY

Fill in the Blanks

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

___________ (ClassID) REFERENCES Classes(ID)

ClassID INT, FOREIGN KEY (ClassID) __________ Classes(ID)

ClassID INT, FOREIGN KEY (ClassID) REFERENCES _______(ID)

CREATE TABLE Classes (ID INT ___________);

ClassID INT, FOREIGN KEY (_______) REFERENCES Classes(ID)

Spot the Error

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

Question 1

A CREATE Classes TABLE (

B ClassID INT PRIMARY KEY,

C ClassName TEXT

D );

Question 2

A CREATE TABLE Classes (

B ClassName TEXT,

C ClassID INT

D );

Question 3

A SELECT Name, ClassName

B FROM Students JOIN Classes

C ON Students.StudentID =

D Classes.ClassID;

Question 4

A INSERT INTO Classes

B VALUES (1, 'Maths');

C INSERT INTO Students

D VALUES (101, 'Mia', 7);

Question 5

A SELECT StudentName

B FROM Classes

C WHERE ClassID = 1;

Answer Key

Foreign key and table relationships

Section A: Quick Questions

B — A field that holds the primary key value of a record in another table

A — A foreign key

B — One-to-many

A — In the table on the 'many' side (Students)

A — It avoids repeating data, so each class is stored only once

Section B: Match the Pairs

FOREIGN KEY (class_id) REFERENCES Classes(class_id) → Makes class_id in this table a link to the primary key class_id in the Classes table

class_id INTEGER → A field in the Students table that stores the class_id of the class the student belongs to

One-to-many relationship: one class can have many students, so the student table holds the class link → Students(student_id, name, class_id)

Avoiding repeated data: store the class details once in Classes and link to them with the class ID → Classes(class_id, class_name, teacher)

Students(student_id, name, class_name, teacher, room) → Class details are typed into every student record instead of using a foreign key, so data is repeated and can become inconsistent. Store them in Classes and add class_id to Students.

Students: class_id = 99, Classes: 1, 2, 3 → The foreign key value 99 does not match any primary key in Classes, so it links to a class that does not exist.

Section C: Definitions

FOREIGN KEY — A field in one table that holds the primary key value of a record in another table, linking the two tables together so related data is stored once and not repeated.

Section D: Fill in the Blanks

FOREIGN KEY (ClassID) REFERENCES Classes(ID)

ClassID INT,
FOREIGN KEY (ClassID) REFERENCES Classes(ID)

ClassID INT,
FOREIGN KEY (ClassID) REFERENCES Classes(ID)

CREATE TABLE Classes (ID INT PRIMARY KEY);

ClassID INT,
FOREIGN KEY (ClassID) REFERENCES Classes(ID)

Section E: Spot the Error

A — The keywords are in the wrong order. The statement must start with CREATE TABLE and then the table name: CREATE TABLE Classes (

C — ClassID has lost its PRIMARY KEY. A foreign key in Students can only link to a primary key in Classes. It should be: ClassID INT PRIMARY KEY

C — The tables are linked by matching the foreign key to the primary key it points to. Students.StudentID is not that foreign key. It should be ON Students.ClassID =

D — The foreign key value 7 does not match any ClassID in the Classes table, so the link is broken. Mia should use ClassID 1, which is the Maths class.

B — StudentName is stored in the Students table, not in Classes. The query should read FROM Students. Then ClassID, the foreign key, picks the class.