← ACARA Digital Technologies Years 9–10

The repeated-data problem in one big table

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.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 5, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 55, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 41, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 19, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 99, QCE Digital Solutions Unit 3 — Real-World Problems and Solution Requirements — Module 30, WA Computer Science ATAR Year 11 — Data Management — Module 42

Understanding The repeated-data problem in one big table in ACARA Digital Technologies Years 9–10

What it does: The UPDATE must change every row for that customer. If any row was missed, the old address would still be there, so one customer would have two addresses. The address lives in one place and orders point to it with a key. A join brings the details together without copying them, unlike a single spreadsheet-style table. Repeating the same details on every row is called data redundancy. It wastes storage space. The more times data is typed, the more chances there are for typos like 'Smyth St' and 'Smith St'. In one big table, every copy is separate. Missing even one row leaves inconsistent data.

Common mistakes: 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. 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.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add The repeated-data problem in one big table to a class →

This module's key terms are also in a printable crossword (Modules 433–451) and word search (Modules 413–451).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

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

The same address is repeated on many rows, wasting spaceEach customer can only place one order

Matches

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.

Applying

Meanings

Term

data redundancy

Meaning

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.

Practice

=COUNTIF(B2:B9,"Tech")

Testing

Gaps

orders

Check Answer

Spots

2SET address = '9 Oak St'

3WHERE orderId = 1;

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

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

Repeating the same details on every row is called data redundancy. It wastes storage space.

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 ✓

The more times data is typed, the more chances there are for typos like 'Smyth St' and 'Smith St'.

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 ✓

In one big table, every copy is separate. Missing even one row leaves inconsistent data.

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

Storing the address once and linking to it avoids repeating it. This is what a relational database does.

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

A spreadsheet that copies details on every row has the same problem. A relational database splits the data into linked tables to avoid it.

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.

Key Term

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.

Fill in the Blanks

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

Hint: Type the keyword that changes existing rows. In one big table, it must change every row with a copy of the address.

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

Hint: Type the aggregate function that counts rows. It shows how many copies of one customer's details are stored.

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

Hint: Type the keyword that removes duplicate values from the results. It reveals typing errors in repeated data.

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

Hint: Type the two keywords that make each customer row unique, so the address is stored only once.

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

Hint: Type the keyword that combines two tables, so the address can be stored once and looked up.

Spot the Error

Question 1

A UPDATE bigOrders

B SET address = '9 Oak St'

C WHERE orderId = 1;

Line 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.

Question 2

A SELECT COUNT(*)

B FORM bigOrders

C WHERE customerName = 'Ava';

Line 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.

Question 3

A UPDATE bigOrders

B SET address == '12 Elm Rd'

C WHERE customerId = 4;

Line 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.

Question 4

A SELECT customerName, address

B FROM bigOrders

C WHERE address = '5 Prak St';

Line 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'.

Question 5

A CREATE TABLE customers (

B customerId INT PRIMARY KEY

C address VARCHAR(50)

D );

Line 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.

Student with a class code? Join your class →