The repeated-data problem in one big table

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A shop stores every order in one big table, and each row includes the customer's full address. What is the problem?

A The same address is repeated on many rows, wasting space

B Each customer can only place one order

2. Ava's address is typed again for each of her 20 orders. What risk does this create?

A The database will automatically fix every typo

B Some copies may have typing errors, so the data is inconsistent

3. Ava moves house. In a one big table with her address on 20 rows, what must happen?

A Changing one row updates the other 19 automatically

B All 20 rows must be changed, or some will keep the old address

4. Which table design causes data redundancy?

A Customer name and address copied onto every order row

B Customer address stored once, with orders pointing to the customer

5. True or false: A spreadsheet with one customer address typed on 50 rows has the same repeated-data problem as one big database table.

A True

B False

Match the Pairs

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

Left column		Right column
1Ava Nguyen, 12 Pine St \| Order 1 \| Pencil Ava Nguyen, 12 Pine St \| Order 2 \| Notebook Ava Nguyen, 12 Pine St \| Order 3 \| Ruler		AThe same customer details are repeated on every order row, which is data redundancy and wastes space.
2Ava Nguyen, 12 Pine St Ava Nguyen, 12 Pine Street Ava Nguyen, 12 Pyne St		BChanging just one row leaves the old address on the other rows, so the data is inconsistent. With one big table every row would need updating, or the address should be stored once.
3Changing a customer's address in one big table means editing every order row, and missing one leaves inconsistent data.		CSELECT orders.orderId, customers.address FROM orders JOIN customers ON orders.customerId = customers.customerId;
4A relational database stores the address once in a customers table and links each order to it, so only one row needs to change.		DCopying the address onto every order row repeats data. It wastes space and makes updates and typos more likely. Store it once in a customers table instead.
5Fix: put the customer's address on every order row so it is easy to find.		EUPDATE orders SET address = '5 Oak Rd' WHERE customer = 'Ava Nguyen';
6Customer moved house, so change only the first order row in the table.		FTyping errors and different spellings creep into repeated data, so the same address no longer matches.

Definitions

data redundancy

Fill in the Blanks

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

______ orders SET address = '12 Oak St' WHERE customer = 'Ava';

SELECT _____(*) FROM orders WHERE customer = 'Ava';

SELECT ________ address FROM orders WHERE customer = 'Ava';

CREATE TABLE customers ( customerId INT ___________, address VARCHAR(50));

SELECT orders.orderId, customers.address FROM orders ____ customers ON orders.customerId = customers.customerId;

Spot the Error

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

Question 1

A UPDATE bigOrders

B SET address = '9 Oak St'

C WHERE orderId = 1;

Question 2

A SELECT COUNT(*)

B FORM bigOrders

C WHERE customerName = 'Ava';

Question 3

A UPDATE bigOrders

B SET address == '12 Elm Rd'

C WHERE customerId = 4;

Question 4

A SELECT customerName, address

B FROM bigOrders

C WHERE address = '5 Prak St';

Question 5

A CREATE TABLE customers (

B customerId INT PRIMARY KEY

C address VARCHAR(50)

D );

Answer Key

The repeated-data problem in one big table

Section A: Quick Questions

A — The same address is repeated on many rows, wasting space

B — Some copies may have typing errors, so the data is inconsistent

B — All 20 rows must be changed, or some will keep the old address

A — Customer name and address copied onto every order row

A — True

Section B: Match the Pairs

Ava Nguyen, 12 Pine St Ava Nguyen, 12 Pine Street Ava Nguyen, 12 Pyne St → Typing errors and different spellings creep into repeated data, so the same address no longer matches.

Changing a customer's address in one big table means editing every order row, and missing one leaves inconsistent data. → UPDATE orders SET address = '5 Oak Rd' WHERE customer = 'Ava Nguyen';

A relational database stores the address once in a customers table and links each order to it, so only one row needs to change. → SELECT orders.orderId, customers.address FROM orders JOIN customers ON orders.customerId = customers.customerId;

Fix: put the customer's address on every order row so it is easy to find. → Copying the address onto every order row repeats data. It wastes space and makes updates and typos more likely. Store it once in a customers table instead.

Customer moved house, so change only the first order row in the table. → Changing just one row leaves the old address on the other rows, so the data is inconsistent. With one big table every row would need updating, or the address should be stored once.

Section C: Definitions

data redundancy — The same details stored again and again in one big table. It wastes space and risks typos or missed updates, like a customer's address copied onto every order.

Section D: Fill in the Blanks

UPDATE orders
SET address = '12 Oak St'
WHERE customer = 'Ava';

SELECT COUNT(*) FROM orders
WHERE customer = 'Ava';

SELECT DISTINCT address
FROM orders
WHERE customer = 'Ava';

CREATE TABLE customers (
  customerId INT PRIMARY KEY,
  address VARCHAR(50));

SELECT orders.orderId, customers.address
FROM orders JOIN customers
ON orders.customerId = customers.customerId;

Section E: Spot the Error

C — Ava's address is repeated on many rows. Filtering on orderId = 1 changes only one row and leaves the old address on her other orders. It should be WHERE customerName = 'Ava'; so every copy is updated.

B — FORM is a typing error and SQL does not recognise it. The keyword is FROM. This query counts how many rows repeat Ava's details in the one big table.

B — SQL uses a single = to set a value, not ==. It should be SET address = '12 Elm Rd'. Without it, the update to the repeated address fails.

C — The address is typed as 'Prak' instead of 'Park', so rows stored with the correct spelling are missed. Typing errors in repeated data cause the same problem: copies of the address no longer match. It should be '5 Park St'.

B — A comma is missing at the end of the line, so the next column definition is not separated from this one. It should end with a comma. Storing each address once in a customers table avoids the repeated data.