The sign-up sheet in the group chat
A games shop runs a tournament. Players sign up on a sheet with their name, phone number and age.
Somebody shares the sheet in a group chat by mistake. Now two hundred strangers have a list of teenagers' phone numbers. Nobody meant any harm, and it still happened.
By the end of this lesson
Note
I can handle personal data lawfully.
Key words
| Word | What it means | Example |
|---|---|---|
| Personal data | any fact about a person who can be identified | an email |
| Identify | to work out exactly who somebody is | |
| Minimise | to keep only the data a job needs | |
| Mask | to hide part of a value | l***@example.com |
| Pseudonym | a code that stands in for a name | customer 4 |
| Anonymise | to change data so nobody can be identified from it |
The cells students fill in are left blank in this preview.
What counts as personal data
Personal data is any fact about a living person who can be identified. A name, an email, a phone number and a photo are all personal data.
So is a fact that points at one person when you put it beside other facts. "The only customer in Glasgow" identifies somebody without a name.
Sign in to the shop
Note
Start the lab. If your terminal is new, press the next two Runs. The shop's customers are made up for the lab, so nobody real is in it.
Trust the lab's server
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for signing in
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Everything the shop knows about one customer
sql "SELECT first_name, last_name, email, phone, city FROM customers WHERE id = 4"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: the rules in our words
| Rule | What it means | In the gift shop |
|---|---|---|
| Have a fair reason | Only use data for a reason people would expect | An address is for sending the parcel |
| Take only what you need | Do not collect "just in case" | No birthday unless it is used |
| Use it for that reason only | Do not reuse it for something new | Do not sell the email list |
| Keep it safe | Only the right people can see it | Passwords, and no sheets in chats |
| Keep it only as long as needed | Delete what is no longer used | Remove accounts left for years |
| Let people see and fix it | A person may ask what you hold | A way to ask, and a reply |
The cells students fill in are left blank in this preview.
Give each job only the columns it needs
A new analyst wants to count customers by country and by the year they joined. That job needs no names, emails or phones.
So give the analyst only those columns. Where a person must still be told apart, use the id as a pseudonym, or show only initials. Where part of a value helps, mask the rest.
Only the columns the analyst needs
sql "SELECT id, country, joined_on FROM customers ORDER BY id LIMIT 4"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Initials instead of names
sql "SELECT id, CONCAT(LEFT(first_name, 1), '.', LEFT(last_name, 1), '.') AS initials, country FROM customers ORDER BY id LIMIT 4"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Mask the emails
sql "SELECT CONCAT(LEFT(email, 1), '***@', SUBSTRING_INDEX(email, '@', -1)) AS masked FROM customers ORDER BY id LIMIT 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A small group can point at one person
Taking the names out is not enough. If only one customer lives in a city, then "a customer in that city" is that person.
Count how many people share a value before you share it. Where a group is very small, use a bigger group instead, such as the country.
Cities with fewer than three customers
sql "SELECT city, COUNT(*) AS customers FROM customers GROUP BY city HAVING COUNT(*) < 3 ORDER BY city"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The only customer in Glasgow
sql "SELECT first_name, last_name FROM customers WHERE city = 'Glasgow'"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think data is anonymous once the names are gone. A city, an age or a date can still point at one person. Data is only anonymous when nobody can work out who it is about, even by putting facts together.
Going further: keeping data too long
One rule is to keep data only as long as it is needed. Some customers made an account and have never ordered.
In the lab, write a query that lists the customers with no orders. Use a LEFT JOIN from customers to orders and keep the rows where orders.id IS NULL. What should the shop do about them?