Chapter 9 — Data and databases
Lesson map
Lesson 09 published. Map, example, EX025.
flowchart TD
N0["Learning outcomes"]
N1["9.1 A sharing database"]
N2["9.2 The query"]
N3["9.3 Reading the result"]
N4["9.4 Recommendations"]
N5["Lab — Classroom apps receiving pupil data without an"]
N6["Synthesis"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6
N6 --> EX["Worked example"]
EX --> Q["Exercise EX025"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["9.1 A sharing database"]
N2["9.2 The query"]
N3["9.3 Reading the result"]
N4["9.4 Recommendations"]
N5["Lab — Classroom apps receiving pupil data without an"]
N6["Synthesis"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> Q["EX025"]
[Learning outcomes]
|
v
[9.1 A sharing database]
|
v
[9.2 The query]
|
v
[9.3 Reading the result]
|
v
[9.4 Recommendations]
|
v
[Lab — Classroom apps receiving pupil data without an]
|
v
[Synthesis]
|
v
[Worked example] --> [Exercise EX025]
Learning outcomes
You will (1) query a database to find sharing risks; (2) relate contracts to data flows; (3) recommend actions from query results.
9.1 A sharing database
Elena builds a small SQLite database of classroom apps and the pupil data each receives, based on a synthetic version of the trust's records. Each app has a vendor, a region and a flag showing whether a data processing agreement is signed. Article 28 of the GDPR requires a contract with processors. The database lets her ask questions that a spreadsheet makes hard, such as which apps receive data without an agreement and how many pupils they affect. It uses only stdlib sqlite3.
9.2 The query
The lab joins apps with shared fields and filters apps without agreements. Three apps appear. ClassChat, based in the EEA, receives names and email addresses for 3,100 pupils. ReadAlong, based in the US, receives names, reading levels and a special educational needs flag for up to 1,800 pupils. QuizBlitz, in the US, receives names for 900 pupils. In total there are 5,800 pupil-app shares without an agreement, counting a pupil once per app. The query joins the apps table with the agreements table and counts pupils per app.
9.3 Reading the result
Elena notes that ReadAlong is the most serious case. It receives a special educational needs flag, which may be health data, sends data outside the EEA and has no agreement. ClassChat affects the most pupils but holds less sensitive data. She ranks the apps by sensitivity, transfer and scale rather than scale alone. She explains that a database query gives facts, but judgement is needed to decide which facts matter most for children. QuizBlitz needs attention too despite smaller numbers, because its data leave Europe.
9.4 Recommendations
Elena recommends suspending ReadAlong until an agreement and a transfer mechanism are in place and the special needs flag is removed; obtaining an agreement for ClassChat; and reviewing whether QuizBlitz is needed at all. She also recommends that the trust keep the database updated as apps change, turning it into a living register. Dr. Asante notes that a register queried regularly is more useful than a one-off audit spreadsheet. Each new app is added before teachers start using it. Teachers can query it themselves.
Lab — Classroom apps receiving pupil data without an agreement
# SQLite: which classroom apps receive pupil data without a signed data processing agreement?
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE app(name TEXT, vendor TEXT, dpa INTEGER, region TEXT);
CREATE TABLE share(app TEXT, field TEXT, pupils INTEGER);
INSERT INTO app VALUES ('MathsQuest','Qbit Ltd',1,'EEA'),('ReadAlong','Fable Inc',0,'US'),('ClassChat','Talko',0,'EEA'),
('Timetabler','Sched AB',1,'EEA'),('QuizBlitz','Blitz LLC',0,'US'),('LunchPay','Payfair',1,'EEA');
INSERT INTO share VALUES ('MathsQuest','name',2400),('MathsQuest','year_group',2400),('ReadAlong','name',1800),('ReadAlong','reading_level',1800),
('ReadAlong','sen_flag',140),('ClassChat','name',3100),('ClassChat','email',3100),('Timetabler','name',6000),('QuizBlitz','name',900),
('LunchPay','name',6000),('LunchPay','free_meals',1150);
""")
q = """SELECT a.name, a.region, GROUP_CONCAT(s.field), MAX(s.pupils) FROM app a JOIN share s ON s.app=a.name
WHERE a.dpa=0 GROUP BY a.name ORDER BY MAX(s.pupils) DESC"""
for n, reg, f, p in db.execute(q): print(f"{n:10s} {reg:4s} fields={f:28s} pupils={p}")
print("pupil-app shares without a DPA (a pupil may count in several apps):", db.execute("SELECT SUM(m) FROM (SELECT MAX(s.pupils) m FROM app a JOIN share s ON s.app=a.name WHERE a.dpa=0 GROUP BY a.name)").fetchone()[0])
Run with python3 lab_sqlite.py. Verified output:
ClassChat EEA fields=email,name pupils=3100
ReadAlong US fields=name,reading_level,sen_flag pupils=1800
QuizBlitz US fields=name pupils=900
pupil-app shares without a DPA (a pupil may count in several apps): 5800
Worked example
Elena's query finds three apps without agreements (5,800 pupil-app shares), ranks ReadAlong first because of the special needs flag and US transfer, and recommends suspension, agreements and a living register.
Synthetic case. Apps and counts are synthetic; GDPR Article 28 is real.
Errors and limits
Counting pupils per app double-counts children using several apps.
Exercises
- EX025 — Apps without agreements. Which apps lack a data processing agreement? Rubric: 3 = ClassChat, ReadAlong, QuizBlitz;; 2 = two;; 1 = one;; 0 = none.
- EX026 — Most serious. Why is ReadAlong the most serious case? Rubric: 3 = special needs flag, US transfer and no agreement;; 2 = two reasons;; 1 = scale;; 0 = none.
- EX027 — Living register. Why keep the database as a living register? Rubric: 3 = apps change, so regular queries catch new gaps;; 2 = mentions updates;; 1 = one-off audit;; 0 = none.
Synthesis
Chapter 9 queries data. Chapter 10 discusses ethics and authorised practice.
Continue studying — chapter 09 of 40
← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →