The delivery list with everything on it
The shop needs a list for the person who drops off orders. Somebody sends the whole customer table, with names, towns and every purchase.
The delivery person needed a first name and a town. Everything else was a risk for nothing.
Key words
| Word | What it means |
|---|---|
| Subset | a part of the data, picked because it is needed |
| SQL | a language for asking questions of data kept in tables |
| Query | a question you ask of data, written in SQL |
| View | a saved query that shows part of a table as if it were a table |
| Data minimisation | keeping and sharing only the data you need |
| Personal data | facts about a living person who can be named |
| Redundancy | the same fact stored in more than one place |
| Least access | giving each person only the access their job needs |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can extract a safe subset of data, avoid needless copies and say what protects data in a solution.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Worked example: what each person needs
| Person | Needs | Should not see |
|---|---|---|
| Delivery person | First name and town | Surname, purchases, points |
| Shop owner | Everything about her own customers | Nothing hidden |
| Report reader | Totals by town | Any name at all |
The cells students fill in are left blank in this preview.
Run it: a safe view
CREATE VIEW delivery_list AS
SELECT SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name, town
FROM customer;
SELECT * FROM delivery_list ORDER BY first_name;Why a view helps
A view shows only the columns you choose. Give the delivery person the view and not the table, and the surname can not leak from there.
This is least access: each person gets what the job needs. It is also data minimisation, a principle of UK data protection law.
One copy of each fact
A fact stored twice can disagree with itself. If a total is kept in a column and also worked out in a query, one will go stale.
Store each fact once. Work out totals when they are asked for, as the quarter sheet did.