Update anomaly
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. A student's address appears in 3 rows of an Orders table. You change it in only 1 row. What is this called?
2. Which statement describes the result of an update anomaly?
3. Which table design is MOST likely to cause an update anomaly?
4. What is the root cause of an update anomaly?
5. Ava moves house. Her address is stored in rows 2, 5 and 9. After the update only row 2 shows the new address. What is the problem?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1UPDATE students SET address = '5 Park St' WHERE studentId = 3; | AThe address is repeated in several rows, and only one copy was changed. The old rows still show the old address, which is an update anomaly. | |
| 2UPDATE students SET address = '5 Park St' WHERE address = '12 Hill Rd'; | BRow 1: Ava, 5 Park St | Row 2: Ava, 12 Hill Rd | |
| 3Redundancy causes update anomalies because the same address is stored in many rows, so each copy must be changed separately. | CChanges every row that holds the old address, so all copies of the repeated fact stay in agreement and no update anomaly is left behind. | |
| 4An update anomaly shows up when one customer has two different addresses after a change was made in only one row. | DOrders: (1, Ava, 12 Hill Rd), (2, Ava, 12 Hill Rd), (3, Ava, 12 Hill Rd) | |
| 5A student moves house. The office edits the address in their latest order row only, and leaves the older order rows alone. | EChanges the address in only one row. If the same address is repeated in other rows, those copies stay old and the data disagrees (an update anomaly). | |
| 6Fix: keep the address in every order row and just try to remember to update all of them each time. | FRelying on memory to update every copy does not remove the redundancy that causes the anomaly. Mistakes will still happen. |
C
Definitions
update anomaly
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
UPDATE Orders SET address = '5 Elm St'
WHERE _______ = 1;
2.
UPDATE Orders
SET _______ = '5 Elm St'
WHERE orderId = 1;
3.
SELECT ________ address FROM Orders
WHERE custId = 3;
4.
UPDATE _________ SET address = '5 Elm St'
WHERE custId = 3;
5.
SELECT _____(*) FROM Orders
WHERE custId = 3;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
-- Address repeated per order
B
UPDATE orders
C
SET address = '9 Elm St'
D
WHERE orderId = 3;
Question 2
A
UPDATE FROM bookings
B
SET address = '7 Bay Rd'
C
WHERE guestId = 4;
Question 3
A
newAddr <- '5 Pine St'
B
for each row in orders
C
if row.studentId = 3 then
D
newAddr <- row.address
Question 4
A
SELECT DISTINCT address
B
FROM address
C
WHERE studentId = 3;
Question 5
A
count <- 0
B
for each row in orders
C
if row.addr = newAddr then
D
count <- count + 1
E
print count
Answer Key
Update anomaly
Section A: Quick Questions
Q1
B — An update anomaly
Q2
B — The same fact has different values in different rows
Q3
A — One big table that repeats a customer's address on every order row
Q4
A — Redundancy: the same fact is stored in more than one row
Q5
A — Rows 5 and 9 still hold the old address, so the data is inconsistent
Section B: Match the Pairs
Q1
UPDATE students SET address = '5 Park St' WHERE studentId = 3; → Changes the address in only one row. If the same address is repeated in other rows, those copies stay old and the data disagrees (an update anomaly).
Q2
UPDATE students SET address = '5 Park St' WHERE address = '12 Hill Rd'; → Changes every row that holds the old address, so all copies of the repeated fact stay in agreement and no update anomaly is left behind.
Q3
Redundancy causes update anomalies because the same address is stored in many rows, so each copy must be changed separately. → Orders: (1, Ava, 12 Hill Rd), (2, Ava, 12 Hill Rd), (3, Ava, 12 Hill Rd)
Q4
An update anomaly shows up when one customer has two different addresses after a change was made in only one row. → Row 1: Ava, 5 Park St | Row 2: Ava, 12 Hill Rd
Q5
A student moves house. The office edits the address in their latest order row only, and leaves the older order rows alone. → The address is repeated in several rows, and only one copy was changed. The old rows still show the old address, which is an update anomaly.
Q6
Fix: keep the address in every order row and just try to remember to update all of them each time. → Relying on memory to update every copy does not remove the redundancy that causes the anomaly. Mistakes will still happen.
Section C: Definitions
Q1
update anomaly — A problem where a repeated fact is changed in one row but not its other copies, so the data disagrees. Caused by redundancy, and fixed by normalisation.Section D: Fill in the Blanks
Q1
UPDATE Orders SET address = '5 Elm St'
WHERE orderId = 1;Q2
UPDATE Orders
SET address = '5 Elm St'
WHERE orderId = 1;Q3
SELECT DISTINCT address FROM Orders
WHERE custId = 3;Q4
UPDATE Customers SET address = '5 Elm St'
WHERE custId = 3;Q5
SELECT COUNT(*) FROM Orders
WHERE custId = 3;Section E: Spot the Error
Q1
D — Filtering by orderId changes the address in only one row. Student 3's other orders keep the old address, which causes an update anomaly. It should be WHERE studentId = 3; so every copy changes.
Q2
A — In SQL, UPDATE is followed directly by the table name. FROM is not allowed here. It should be UPDATE bookings so the address of every row for guest 4 can be changed.
Q3
D — The assignment goes the wrong way, so the old address overwrites the new one and no stored copy changes. It should be row.address <- newAddr, applied to every copy of the repeated address.
Q4
B — FROM must name a table, and address is a column. It should be FROM orders. If this query returns more than one address for one student, the copies disagree, which is an update anomaly.
Q5
C — This counts the rows that already have the new address. To find the stale copies left behind after a partial update, compare with != so it counts rows that still differ.