Data consistency

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A student table stores a house as 'Red' in one row and 'red' in another. Is this data consistent?

A No, the same fact is written in two different ways

B Yes, a computer treats 'Red' and 'red' as the same

2. Which choice best describes data consistency?

A Every value in a table is a number

B The same fact has the same value and format everywhere it is stored

3. A school stores Ava's phone number in the students table as 0412 345 678 and in the library table as 0412 999 111. What is the problem?

A Conflicting copies of the same fact

B A spelling variation in a column name

4. Which approach helps keep a fact consistent?

A Store the fact in one place and refer to it from elsewhere

B Copy the fact into every table that needs it

5. Which set of values for the Subject column is consistent?

A Maths, Math, maths

B Maths, Maths, Maths

Match the Pairs

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

Left column		Right column
1house = 'Red' in one row, 'red ' in another		AA spelling variation: the same fact is stored in two different formats, so the data is inconsistent
2students.house, with houses.houseName as the only place a house name is stored		BSELECT firstName FROM students WHERE studentId = 1;
3Conflicting copies: the same fact is stored twice with different values		COne place per fact: every other table refers to the single stored value, so it cannot conflict
4Fixing inconsistency by keeping every fact in one place and linking with a key		DOnly one copy of the fact was updated, so the copies now conflict. Store the fact in one place only.
5UPDATE students SET points = 130 WHERE studentId = 1; (the same points total is also kept in another table and not changed)		ERow 1: Ava, points 120 \| Row 7: Ava, points 95
6WHERE house = 'Red' (but some rows hold 'RED' and 'red')		FSpelling variations mean the query misses matching rows. Data should be entered in one agreed format, such as 'Red'.

Definitions

data consistency

Fill in the Blanks

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

SELECT ________ house FROM students;

UPDATE students SET house = 'Red' _____ house = 'red';

SELECT firstName, quantity FROM orders ____ students ON orders.studentId = students.studentId;

UPDATE ________ SET price = 4.5 WHERE name = 'Notebook';

house TEXT _____ (house IN ('Red','Blue'))

Spot the Error

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

Question 1

A UPDATE FROM students

B SET house = 'Green'

C WHERE house = 'Grean';

Question 2

A UPDATE products

B SET category = 'Tech'

C WHERE category = 'Tech';

Question 3

A SELECT s.firstName, r.mark

B FROM students s

C JOIN results

D ON s.studentId = r.studentId;

Question 4

A input house

B house = upper(house)

C if house = "RED" then

D print "Red house"

E end if

Question 5

A input suburb

B suburb <- trim(suburb)

C suburb <- upper(suburb)

D save suburb to both tables

Answer Key

Data consistency

Section A: Quick Questions

A — No, the same fact is written in two different ways

B — The same fact has the same value and format everywhere it is stored

A — Conflicting copies of the same fact

A — Store the fact in one place and refer to it from elsewhere

B — Maths, Maths, Maths

Section B: Match the Pairs

house = 'Red' in one row, 'red ' in another → A spelling variation: the same fact is stored in two different formats, so the data is inconsistent

students.house, with houses.houseName as the only place a house name is stored → One place per fact: every other table refers to the single stored value, so it cannot conflict

Conflicting copies: the same fact is stored twice with different values → Row 1: Ava, points 120 | Row 7: Ava, points 95

Fixing inconsistency by keeping every fact in one place and linking with a key → SELECT firstName FROM students WHERE studentId = 1;

UPDATE students SET points = 130 WHERE studentId = 1; (the same points total is also kept in another table and not changed) → Only one copy of the fact was updated, so the copies now conflict. Store the fact in one place only.

WHERE house = 'Red' (but some rows hold 'RED' and 'red') → Spelling variations mean the query misses matching rows. Data should be entered in one agreed format, such as 'Red'.

Section C: Definitions

data consistency — The same fact has the same value and format everywhere it is stored, with no spelling variations or conflicting copies. Storing each fact in one place helps keep it consistent.

Section D: Fill in the Blanks

SELECT DISTINCT house FROM students;

UPDATE students SET house = 'Red'
WHERE house = 'red';

SELECT firstName, quantity FROM orders
JOIN students
ON orders.studentId = students.studentId;

UPDATE products
SET price = 4.5
WHERE name = 'Notebook';

house TEXT CHECK (house IN ('Red','Blue'))

Section E: Spot the Error

A — UPDATE does not use FROM. It should be UPDATE students. This statement fixes the spelling variation 'Grean' so every student's house is spelt the same way.

C — The WHERE clause must find the inconsistent value 'tech'. Matching 'Tech' selects rows that are already correct, so the lowercase variation stays. It should be WHERE category = 'tech';

C — The alias r is missing, so r.mark and r.studentId are not recognised. It should be JOIN results r. The name is stored once in students and results only holds the studentId.

B — Assignment in pseudocode uses <-, not =. It should be house <- upper(house). This puts every entry in one format, such as RED, so spelling variations like red or Red cannot appear.

D — Saving the suburb into two tables creates copies that can end up with different values. It should be saved in one place only: save suburb to customers.