Reducing redundancy by splitting a table

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A Orders table repeats a customer's name and address on every order they place. What is the best fix?

A Keep the details in Orders and copy them again each time to be safe

B Move the customer details into a Customers table and link each order with a foreign key

2. Which statement describes the goal of splitting a table?

A Each fact is stored in one place only

B Each fact is stored in as many places as possible

3. A Students table repeats the house captain's name on every student row. Where should that detail go?

A In a separate Houses table, linked by a houseId foreign key

B In the Students table, typed again on every row

4. After splitting a table, how does a row in the new table get connected back to the original one?

A A foreign key in the original table holds the primary key value of the new table

B The original table copies all the new table's fields into itself

5. A shop's Sales table repeats the product name and price on every sale. Which design reduces redundancy?

A Sales(saleId, productName, productPrice, quantity) with the details repeated

B Sales(saleId, productId, quantity) and Products(productId, name, price)

Match the Pairs

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

Left column		Right column
1CREATE TABLE houses (house TEXT PRIMARY KEY, headOfHouse TEXT);		AStudent and subject details are repeated on every result row, so the same fact is stored many times. Move them into students and subjects tables and keep only the keys in results.
2students(studentId, firstName, house) -> houses(house)		BCopying the teacher into results instead of linking by subjectId stores the same fact in two places. Keep teacher only in subjects and link with the foreign key subjectId.
3One fact in one place: the teacher name is stored once in subjects, not repeated on every results row.		CThe house field in students is a foreign key that links each student back to the one house row that holds the details.
4After splitting, a join uses the foreign key to bring the linked details back together when needed.		DSELECT name, teacher FROM subjects;
5One big table: results(studentId, firstName, lastName, subjectName, teacher, mark)		ESELECT students.firstName, results.mark FROM students INNER JOIN results ON students.studentId = results.studentId;
6results(studentId, subjectId, mark, teacher) -- teacher copied into results as well as subjects		FCreates a separate table so each house and its head are stored once, instead of being repeated on every student row.

Definitions

normalisation

Fill in the Blanks

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

CREATE TABLE houses ( houseId INTEGER ___________, houseName TEXT);

CREATE TABLE students (studentId INTEGER, houseId INT __________ houses(houseId));

INSERT INTO houses VALUES (1, 'Red'); INSERT INTO students VALUES (1, 'Ava', _);

UPDATE houses SET houseName = 'Crimson' WHERE _______ = 1;

SELECT students.firstName, houses.houseName FROM students JOIN houses ON students.houseId = ______________;

Spot the Error

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

Question 1

A CREATE TABLE teachers (

B id INTEGER,

C name TEXT

D );

Question 2

A CREATE TABLE subjects (

B id INTEGER PRIMARY KEY,

C teacherId INTEGER,

D PRIMARY KEY (teacherId)

E REFERENCES teachers(id)

F );

Question 3

A SELECT s.name, t.name

B FROM subjects s

C JOIN teachers

D ON s.teacherId = t.id;

Question 4

A INSERT INTO teachers VALUES

B (1, 'Ms Lee');

C INSERT INTO subjects VALUES

D (1, 'Maths', 'Ms Lee');

Question 5

A -- Ms Lee changes her name

B UPDATE subjects

C SET name = 'Ms Lee-Ray'

D WHERE id = 1;

Answer Key

Reducing redundancy by splitting a table

Section A: Quick Questions

B — Move the customer details into a Customers table and link each order with a foreign key

A — Each fact is stored in one place only

A — In a separate Houses table, linked by a houseId foreign key

A — A foreign key in the original table holds the primary key value of the new table

B — Sales(saleId, productId, quantity) and Products(productId, name, price)

Section B: Match the Pairs

CREATE TABLE houses (house TEXT PRIMARY KEY, headOfHouse TEXT); → Creates a separate table so each house and its head are stored once, instead of being repeated on every student row.

students(studentId, firstName, house) -> houses(house) → The house field in students is a foreign key that links each student back to the one house row that holds the details.

One fact in one place: the teacher name is stored once in subjects, not repeated on every results row. → SELECT name, teacher FROM subjects;

After splitting, a join uses the foreign key to bring the linked details back together when needed. → SELECT students.firstName, results.mark FROM students INNER JOIN results ON students.studentId = results.studentId;

One big table: results(studentId, firstName, lastName, subjectName, teacher, mark) → Student and subject details are repeated on every result row, so the same fact is stored many times. Move them into students and subjects tables and keep only the keys in results.

results(studentId, subjectId, mark, teacher) -- teacher copied into results as well as subjects → Copying the teacher into results instead of linking by subjectId stores the same fact in two places. Keep teacher only in subjects and link with the foreign key subjectId.

Section C: Definitions

normalisation — Splitting a table so repeated details move into their own table, linked back with a foreign key, so each fact is stored only once and redundancy is reduced.

Section D: Fill in the Blanks

CREATE TABLE houses (
  houseId INTEGER PRIMARY KEY,
  houseName TEXT);

CREATE TABLE students (studentId INTEGER,
  houseId INT REFERENCES houses(houseId));

INSERT INTO houses VALUES (1, 'Red');
INSERT INTO students VALUES (1, 'Ava', 1);

UPDATE houses
SET houseName = 'Crimson'
WHERE houseId = 1;

SELECT students.firstName, houses.houseName
FROM students JOIN houses
ON students.houseId = houses.houseId;

Section E: Spot the Error

B — The new teachers table needs a primary key so other tables can link back to each teacher. Line 2 should be ' id INTEGER PRIMARY KEY,'.

D — teacherId links each subject to a row in teachers, so it must be a FOREIGN KEY. The subjects table already has its own primary key (id). Line 4 should be ' FOREIGN KEY (teacherId)'.

C — The query uses the alias t (t.name and t.id), but line 3 never defines it. Line 3 should be 'JOIN teachers t'.

D — After the split, subjects stores only the teacher's key, not the name. Copying 'Ms Lee' brings the repeated detail back. Line 4 should be ' (1, 'Maths', 1);'.

B — The teacher's name is stored once, in the teachers table, so that is where it is updated. subjects has no name column for teachers. Line 2 should be 'UPDATE teachers'.