Unnormalised data and why normalise

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which best describes the purpose of normalisation?

A Making a table as wide as possible so everything is in one place

B Organising tables to reduce repeated data and prevent anomalies

2. A student's row has the columns Subject1, Subject2 and Subject3 holding different subjects. What is this?

A A repeating group, which should be moved into its own table

B A properly normalised design, because every subject has a column

3. A single orders table stores the customer's name and address again on every order. What problem is this?

A Redundancy: the same data is stored many times

B Good practice: it keeps all customer details close to each order

4. After normalising, how are the new tables connected?

A By a foreign key that matches a primary key in another table

B By copying all the fields from one table into the other

5. Which list shows the normal forms in the order you apply them?

A 3NF, 2NF, 1NF

B 1NF, 2NF, 3NF

Match the Pairs

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

Left column		Right column
1Orders(orderId, student, item1, item2, item3)		AHouses and house colours are stored with every student, so the colour is repeated and an update anomaly can happen. Put house and houseColour in a separate Houses table.
2Students(studentId, name, house) + Orders(orderId, studentId, item)		BFOREIGN KEY (studentId) REFERENCES Students(studentId)
3One big table stores the same student details again on every order row, which wastes space and risks anomalies		CRepeating group: subject1, subject2 and subject3 repeat one kind of data in a record, so this table is not in 1NF. Move subjects into their own table with one subject per row.
4Normalising is organising tables to reduce redundancy by splitting data and linking the new tables with keys		D1 \| Ava Nguyen \| Red \| Pencil 2 \| Ava Nguyen \| Red \| Notebook 3 \| Ava Nguyen \| Red \| Ruler
5Students(studentId, name, subject1, subject2, subject3)		EA normalised design: data split into two linked tables so each fact is stored once and joined by a key
6Students(studentId, name, house, houseColour)		FAn unnormalised table with a repeating group: item1, item2 and item3 repeat the same kind of data in one record

Definitions

normalisation

Fill in the Blanks

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

-- Remove repeating groups: ___

-- Remove partial dependencies: ___

CREATE TABLE Customer ( CustomerID INT ___________, Name VARCHAR(50));

CustomerID INT, ___________ (CustomerID) REFERENCES Customer(CustomerID)

SELECT Orders.OrderID, Customer.Name FROM Orders JOIN Customer __ Orders.CustomerID = Customer.CustomerID;

Spot the Error

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

Question 1

A CREATE TABLE students (

B studentId INT PRIMARY KEY

C firstName VARCHAR(50)

D );

Question 2

A CREATE TABLE results (

B studentId INT,

C subject1 INT, subject2 INT,

D mark INT

E );

Question 3

A UPDATE subjects

B SET teacher == 'Ms Lee'

C WHERE subjectId = 1;

Question 4

A CREATE TABLE orders (

B orderId INT FOREIGN KEY,

C studentId INT,

D FOREIGN KEY (studentId)

E REFERENCES students(id)

F );

Question 5

A SELECT firstName, mark

B FROM students s

C JOIN results r

D ON s.studentId = r.subjectId;

Answer Key

Unnormalised data and why normalise

Section A: Quick Questions

B — Organising tables to reduce repeated data and prevent anomalies

A — A repeating group, which should be moved into its own table

A — Redundancy: the same data is stored many times

A — By a foreign key that matches a primary key in another table

B — 1NF, 2NF, 3NF

Section B: Match the Pairs

Orders(orderId, student, item1, item2, item3) → An unnormalised table with a repeating group: item1, item2 and item3 repeat the same kind of data in one record

Students(studentId, name, house) + Orders(orderId, studentId, item) → A normalised design: data split into two linked tables so each fact is stored once and joined by a key

Normalising is organising tables to reduce redundancy by splitting data and linking the new tables with keys → FOREIGN KEY (studentId) REFERENCES Students(studentId)

Students(studentId, name, subject1, subject2, subject3) → Repeating group: subject1, subject2 and subject3 repeat one kind of data in a record, so this table is not in 1NF. Move subjects into their own table with one subject per row.

Students(studentId, name, house, houseColour) → Houses and house colours are stored with every student, so the colour is repeated and an update anomaly can happen. Put house and houseColour in a separate Houses table.

Section C: Definitions

normalisation — Organising tables to reduce repeated data and prevent anomalies by splitting data into linked tables, using the normal forms 1NF to 3NF.

Section D: Fill in the Blanks

-- Remove repeating groups: 1NF

-- Remove partial dependencies: 2NF

CREATE TABLE Customer (
  CustomerID INT PRIMARY KEY,
  Name VARCHAR(50));

CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID)

SELECT Orders.OrderID, Customer.Name
FROM Orders JOIN Customer
ON Orders.CustomerID = Customer.CustomerID;

Section E: Spot the Error

B — There is no comma after the studentId column definition. Columns in CREATE TABLE are separated by commas, so it should be ' studentId INT PRIMARY KEY,'.

C — Columns like subject1 and subject2 are a repeating group, which breaks 1NF. Normalised data has one row per subject, so the column should be ' subjectId INT,'.

B — SQL uses a single = in a SET clause. Because the teacher is stored once in subjects, this one update fixes it everywhere. The line should be "SET teacher = 'Ms Lee'".

B — orderId uniquely identifies each order, so it is the primary key. The foreign key is studentId, which links to the students table. The line should be ' orderId INT PRIMARY KEY,'.

D — Tables split by normalisation are linked by matching the primary key to its foreign key. students.studentId must equal results.studentId, not results.subjectId.