Chapter 13 — Data systems
Lesson map
Lesson 13 published. Map, example, EX037.
flowchart TD
N0["Learning outcomes"]
N1["13.1 Records needed"]
N2["13.2 Keys"]
N3["13.3 Privacy"]
N4["13.4 Retention"]
N5["13.5 Queries"]
N6["Testing queries"]
N7["Access to data"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7
N7 --> EX["Worked example"]
EX --> Q["Exercise EX037"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["13.1 Records needed"]
N2["13.2 Keys"]
N3["13.3 Privacy"]
N4["13.4 Retention"]
N5["13.5 Queries"]
N6["Testing queries"]
N7["Access to data"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX037"]
[Learning outcomes]
|
v
[13.1 Records needed]
|
v
[13.2 Keys]
|
v
[13.3 Privacy]
|
v
[13.4 Retention]
|
v
[13.5 Queries]
|
v
[Testing queries]
|
v
[Access to data]
|
v
[Worked example] --> [Exercise EX037]
Learning outcomes
You will (1) design records for inventory, restore results and events; (2) choose keys and fields; (3) protect privacy in data.
13.1 Records needed
The project needs an inventory of PCs (asset tag, branch, model, firmware version), nightly restore results (PC, date, success, duration) and security events (PC, time, event type).
Each record type answers a different need. The inventory tells Marta what she owns and whether it is up to date. Restore results show whether the core promise of the design is kept each night. Security events show what the controls blocked. Mateo kept the fields to what the agreed questions require, so the records are small and easy to check for mistakes.
13.2 Keys
Asset tags uniquely identify PCs even when hostnames change, so they are used as keys.
Mateo found that hostnames at Millbrook had been changed twice in a year when PCs moved between rooms, which would have split each PC's history into pieces. The asset tag is printed on a label on the case and stored in the firmware, so it stays the same whatever the PC is called. Reports that need a readable name join the inventory to show the current hostname alongside the tag.
13.3 Privacy
Records never store patron names or browsing. Session records count logons, not identities.
Mateo checks each field against the purpose of the project before adding it. A count of sessions per PC helps plan capacity; who used a PC does not help with anything in scope. The data protection officer reviewed the table definitions and agreed that none of the fields identifies a patron. If a later request asks for identities, it goes back to her. Her agreement is filed with the design.
13.4 Retention
Restore and event records are kept for one year, enough to analyse trends; older records are deleted.
A year covers every season, including exam periods and holidays, so trends across the calendar can be compared. Mateo's deletion job runs monthly and removes records older than the limit from the store and from backups on the backup schedule. The job writes a count of deleted records to a log, so Marta can show that retention is enforced rather than just written in a policy.
13.5 Queries
Typical questions: which PCs failed restore last night, which branch has the slowest restores, which firmware versions are in use.
Mateo collected these questions by asking Marta what she checked each week and what she wished she could see. Each became a test for the data design. The slowest-branch question showed that restore results needed a branch field or a join, and the firmware question showed that the inventory needed versions recorded in a consistent format. Both were fixed before the pilot began.
Testing queries
Mateo writes the three typical queries before building the tables, then checks that each can be answered. One cannot: which branch has the slowest restores needs branch in the restore results or a join with inventory. He adds the join and confirms it works.
He also checks that records can be deleted cleanly after one year, so retention is enforced automatically rather than relying on someone remembering.
Access to data
Only IT staff can read event records; librarians see summaries on the dashboard.
Data for the dashboard
The same tables later feed the librarians' dashboard in chapter 35, showing PCs ready, PCs out of service and recent restore results per branch.
Data quality
Records are checked weekly for missing or duplicated asset tags, which would distort reports.
Naming
Field names are short and documented.
Worked example
Mateo designs a simple table structure and tests it with a month of synthetic records. A query for PCs with old firmware finds 12 at Millbrook, which becomes an update task.
Synthetic case. Synthetic.
Errors and limits
Data designed without questions in mind often cannot answer them later.
Exercises
- EX037 — Inventory fields. What fields does the inventory hold? Rubric: 3 = asset tag, branch, model, firmware; 2 = three; 1 = two; 0 = none.
- EX038 — Key choice. Why use asset tags as keys? Rubric: 3 = unique even when hostnames change; 2 = partial; 1 = vague; 0 = none.
- EX039 — Retention. How long are records kept? Rubric: 3 = one year; 2 = partial; 1 = vague; 0 = none.
Synthesis
Design data around the questions it must answer, without personal data.
Continue studying — chapter 13 of 50
← Chapter 12 (draft) · Course chapter index · Chapter 14 (draft) →