Chapter 9 — Data and databases

Lesson map

Lesson 09 published. Map, example, EX025.

flowchart TD
  N0["Learning outcomes"]
  N1["9.1 Designing a table"]
  N2["9.2 Querying with SQL"]
  N3["Lab — Counting reports with SQLite"]
  N4["9.3 Data minimisation"]
  N5["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5
  N5 --> EX["Worked example"]
  EX --> Q["Exercise EX025"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["9.1 Designing a table"]
  N2["9.2 Querying with SQL"]
  N3["Lab — Counting reports with SQLite"]
  N4["9.3 Data minimisation"]
  N5["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Designing a table]
    |
    v
[9.2 Querying with SQL]
    |
    v
[Lab — Counting reports with SQLite]
    |
    v
[9.3 Data minimisation]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) design a simple table for security reports; (2) query data with SQL; (3) apply data minimisation.

9.1 Designing a table

Nadia designs a table for reports with columns for report identifier, date, branch, category and verdict. She deliberately leaves out the reporter's name, because analysis needs only branch-level counts and personal data would create privacy risk. Each column has a type and rules, such as the verdict being one of malicious, harmless or unknown. A well-designed table makes errors harder: a misspelled verdict is rejected rather than silently counted as a new category that distorts results. The IT lead reviews the design and agrees the rules before any data are loaded.

9.2 Querying with SQL

Using Python's built-in sqlite3 module, Nadia loads synthetic reports and writes queries. One counts reports per branch, another counts malicious reports per month, and a third finds the most common categories. SQL expresses these questions compactly and lets the database do the work. The lab shows that invoice-themed emails were the most common malicious category. She learns to read query results critically, checking whether a high count reflects more attacks or simply more reporting at a particular branch. She saves each query with a comment.

Lab — Counting reports with SQLite

# Counting synthetic reports with SQLite.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("create table reports(id integer primary key, month text, branch text, category text, verdict text check (verdict in ('malicious','harmless','unknown')))")
rows = [("2026-01", "Central", "invoice", "malicious")] * 5 + [("2026-02", "Riverside", "parcel", "malicious")] * 3 + \
       [("2026-02", "Hill", "account", "malicious")] * 2 + [("2026-03", "Central", "invoice", "malicious")] * 2 + [("2026-03", "Market", "newsletter", "harmless")] * 36
db.executemany("insert into reports(month,branch,category,verdict) values (?,?,?,?)", rows)
for r in db.execute("select category, count(*) from reports where verdict='malicious' group by category order by 2 desc"): print(r)

Run with python3 lab_sql.py. Verified output:

('invoice', 7)
('parcel', 3)
('account', 2)

9.3 Data minimisation

Data minimisation means collecting only the data needed for a purpose. Nadia explains to Ashcombe's IT lead why reporter names should not be stored in the analysis table: names could be used to judge individuals, which would discourage reporting, and they add little to programme decisions. The original reports remain in the email system under its normal retention rules. The principle connects privacy law with the human factors goal of making people feel safe to report anything that seems odd. The IT lead agrees.

Worked example

Nadia designs a report table without names, queries it with SQLite and argues for data minimisation.

Synthetic case. Data are synthetic.

Errors and limits

Removing names prevents some follow-up. Category labels can be inconsistent.

Exercises

• EX025 — Name excluded. Why does the table exclude reporter names?
  Show marking criteria

  Rubric: 3 = analysis needs only branch counts and names create risk;; 2 = minimisation;; 1 = vague;; 0 = none.
• EX026 — Top category. Which malicious category was most common in the lab?
  Show marking criteria

  Rubric: 3 = invoice;; 2 = invoices;; 1 = parcel;; 0 = none.
• EX027 — Reporting effect. What should Nadia check when a branch count is high?
  Show marking criteria

  Rubric: 3 = whether it reflects more attacks or more reporting;; 2 = reporting;; 1 = vague;; 0 = none.

Synthesis

Well-designed tables, simple queries and minimised data support safe analysis.

Continue studying — chapter 09 of 40

← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →