ISDD3 - Testing, Fixing and Accuracy | National 3/4 Computing Science

Learning intentions

Test a database using simple SQL queries
Identify and fix simple errors in records
Maintain information so it stays accurate

Success criteria

I can find records that need corrected
I can use UPDATE to fix inaccurate information
I can keep before-and-after evidence of my changes

Testing and Maintaining a Database

Testing a database means checking that the structure works and that the stored information is correct. A database can be technically valid but still inaccurate: for example, a bike could have the wrong price, an impossible frame size, or be linked to the wrong type.

Useful test queries

Find, fix, prove

BeforeWrong value appears in a record

→

UPDATE ... WHEREFix only the intended record

→

AfterSELECT proves it is accurate

Evidence should show what was wrong, the command used to fix it, and the corrected result.

SELECT * FROM bikes;
SELECT model, price FROM bikes WHERE price > 500;
SELECT model, frame_size FROM bikes WHERE frame_size = 'Small';
SELECT bikes.model, bike_types.type_name
FROM bikes
INNER JOIN bike_types ON bikes.type_id = bike_types.type_id;

The final query tests the link between tables. If the wrong type appears beside a bike, the foreign key value may need fixed.

Maintaining accuracy

National 3 specifically requires evidence of keeping information accurate. That means you should show what was wrong, how you corrected it, and the final accurate result.

Practical Evidence

Practical Task 1 - Find and fix errors

Run these commands to add three deliberate errors, then correct them. Keep screenshots or copied output for your evidence.

UPDATE bikes SET price = 78.00 WHERE bike_id = 103;
UPDATE bikes SET frame_size = 'Huge' WHERE bike_id = 101;
UPDATE bikes SET type_id = 2 WHERE bike_id = 103;

-- Test and inspect the records
SELECT * FROM bikes;

-- Correct the errors
UPDATE bikes SET price = 780.00 WHERE bike_id = 103;
UPDATE bikes SET frame_size = 'Medium' WHERE bike_id = 101;
UPDATE bikes SET type_id = 3 WHERE bike_id = 103;

-- Show the corrected data
SELECT * FROM bikes;

N3 evidence checklist

Identifies simple errors in the stored information (N3 O1.3)
Shows the corrected records after using UPDATE (N3 O1.3)
Explains how the final database has been kept accurate (N3 O1.4)

N4 consolidation checklist

Uses a linked query to check that the relationship still works
Explains why accurate foreign keys matter in a linked structure

Common mistakes

Running UPDATE bikes SET price = 780.00; without a WHERE clause. That would update every bike.
Only showing the corrected data, with no evidence of what was wrong before.
Fixing spelling in one place but not checking linked tables.

Task Set

1. Which keyword changes existing records? TYPE 1

UPDATE
CREATE
DROP

2. Why is the WHERE clause important in an update query? TYPE 2

The WHERE clause tells MySQL which record or records to update. Without it, every record in the table could be changed by mistake.

3. Paste your before-and-after evidence for one corrected record. TYPE 3 N3

4. Explain one way you kept the information accurate. TYPE 2 N3

I checked the bike records, found that one price was missing a zero, then used UPDATE with the correct bike_id to fix only that record. I ran SELECT again to prove the corrected value was stored.

Teacher notes - Shift+T to hide