Chapter 9 — Data and databases
Lesson map
Lesson 09 published. Map, example, EX025.
flowchart TD
N0["Learning outcomes"]
N1["9.1 Tables"]
N2["9.2 Keys"]
N3["9.3 Queries"]
N4["9.4 Retention"]
N5["9.5 Privacy"]
N6["Building the tables"]
N7["Updating data"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7
N7 --> EX["Worked example"]
EX --> Q["Exercise EX025"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["9.1 Tables"]
N2["9.2 Keys"]
N3["9.3 Queries"]
N4["9.4 Retention"]
N5["9.5 Privacy"]
N6["Building the tables"]
N7["Updating data"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX025"]
[Learning outcomes]
|
v
[9.1 Tables]
|
v
[9.2 Keys]
|
v
[9.3 Queries]
|
v
[9.4 Retention]
|
v
[9.5 Privacy]
|
v
[Building the tables]
|
v
[Updating data]
|
v
[Worked example] --> [Exercise EX025]
Learning outcomes
You will (1) design tables for devices, updates and events; (2) use keys; (3) protect personal data.
9.1 Tables
Devices (asset tag, model, owner role, location), updates (device, update, released, installed) and events (device, time, type).
Each of the three tables answers a different question about Bramley's fleet. The devices table says what Bramley owns and where it is, the updates table says which fixes reached which device and when, and the events table records sign-ins, installs and errors. Keeping them separate means one laptop can have many update and event rows without its model and location being repeated in each one.
9.2 Keys
Asset tags are unique keys; owner roles rather than names reduce personal data.
The asset tag, such as BHA-L19, links each row in the updates and events tables back to one device. Because tags are never reused, a laptop retired and replaced keeps its own history instead of merging with the new machine. Using "finance manager" rather than a person's name means the table stays accurate when staff change roles, and it holds less personal data. Aisha adds a uniqueness check when loading each export, so a duplicated asset tag stops the load with a message instead of creating two rows for one laptop.
9.3 Queries
Which devices miss critical updates older than 14 days? Which devices have not reported in 30 days?
Each question becomes a short query that joins two tables on the asset tag. The first compares release and install dates in the updates table for critical items; the second finds devices whose latest event is older than 30 days. Aisha writes the expected answer for her small test set beside each query before running it, so she can tell a wrong query from a surprising fleet.
9.4 Retention
Records are kept for a year.
A year covers twelve monthly update cycles, enough for Aisha to compare the baseline with the final measures and to show seasonal patterns. Rows older than a year are deleted from the database during the weekly refresh, and the deletion is logged with the number of rows removed. Records needed for a live investigation are held back with a written reason. The one-year period matches the length of Aisha's project plus time for the dissertation, after which her own copies are deleted under the agreement.
9.5 Privacy
No tenant data enters these tables.
The tables hold device facts only: asset tags, owner roles, update dates and event types. Tenant names, addresses and support notes stay on the file server and never appear in an export Aisha loads. Before each load she runs a short check for columns she does not expect, so an export that suddenly includes extra fields is rejected rather than stored. Aisha records this rule at the top of the data dictionary, so anyone extending the database later knows which fields must never be added.
Building the tables
Aisha builds the tables in a small database file using Python's built-in sqlite3 module, loading synthetic exports. She writes the two queries listed above and a third for laptops not seen for 30 days.
She checks that each query returns what she expects on a small test set, then runs them on the full synthetic data. The 30-day query returns six devices, which matches her manual count.
She documents each table and field in a short data dictionary, including what each value means and where it comes from, so later readers can interpret results correctly.
Updating data
The tables are refreshed weekly from new exports; old rows are kept for trends until the retention period ends.
Access
Only Aisha and Gareth can open the database file, which is stored on encrypted storage.
A worked Bramley example
Aisha walks through one laptop from purchase to retirement: it is ordered and added to the register, enrolled in management, encrypted and baselined, updated monthly, checked weekly, and finally wiped and recorded as disposed. Each step names who does it and where the evidence lives. Seeing a single device's life on one page helps Gareth notice two missing steps: recording the disposal certificate and removing the device from management.
Worked example
Aisha's query for overdue critical updates returns four devices; she checks each by hand and confirms the query is right.
Synthetic case. Synthetic.
Errors and limits
Queries are only as good as the data's timeliness.
Exercises
- EX025 — Tables. Name the three tables. Rubric: 3 = devices, updates, events; 2 = two; 1 = one; 0 = none.
- EX026 — Owner role. Why store owner role instead of name? Rubric: 3 = reduce personal data; 2 = partial; 1 = vague; 0 = none.
- EX027 — Validation. How did Aisha validate her query? Rubric: 3 = checked each result by hand; 2 = partial; 1 = vague; 0 = none.
Synthesis
Design data for the questions you must answer.
Continue studying — chapter 09 of 40
← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →