Chapter 9 — Data and databases

Lesson map

Lesson 09 published. Map, example, EX025.

flowchart TD
  N0["Learning outcomes"]
  N1["9.1 Register in a database"]
  N2["9.2 Queries"]
  N3["Lab — Querying a synthetic risk register"]
  N4["9.3 Why structure helps"]
  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 Register in a database"]
  N2["9.2 Queries"]
  N3["Lab — Querying a synthetic risk register"]
  N4["9.3 Why structure helps"]
  N5["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Register in a database]
    |
    v
[9.2 Queries]
    |
    v
[Lab — Querying a synthetic risk register]
    |
    v
[9.3 Why structure helps]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) store a risk register in a database; (2) query it for priorities and gaps; (3) understand why structure improves governance data.

9.1 Register in a database

Elmfield's risk register is a spreadsheet with merged cells and free text. Sofia moves a sample of six risks into a small SQLite database with defined columns: identifier, title, owner, likelihood from one to five and impact from one to five. Defined columns let her run queries that would be slow and error-prone by hand. She learns that structure is what turns a register from a document into data that can answer questions. Merged cells and free text, for instance, make it impossible to sort risks by score or to list those without an owner.

9.2 Queries

The lab runs two queries. The first ranks risks by score, likelihood times impact. Tenant data emailed to the wrong person and a lost laptop with case notes tie at twelve, followed by ransomware on the file server at ten. The second lists risks without an owner: the repairs contractor account misuse risk and the weak board portal passwords risk. Sofia notes that ties show the limits of a five-point scale, which cannot separate risks that may differ greatly in real consequences. The two unowned risks are a clearer governance gap than the tie, since without an owner nobody will act on either.

Lab — Querying a synthetic risk register

"""A synthetic Elmfield risk register in SQLite: top risks and unowned entries."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("create table risk(id text, title text, owner text, likelihood int, impact int)")
db.executemany("insert into risk values (?,?,?,?,?)", [
    ("R1", "tenant data emailed to wrong person", "Housing", 4, 3), ("R2", "ransomware on file server", "IT", 2, 5),
    ("R3", "repairs contractor account misuse", None, 3, 3), ("R4", "rent payment page outage", "Finance", 3, 2),
    ("R5", "lost laptop with case notes", "Housing", 3, 4), ("R6", "weak passwords on board portal", None, 2, 2)])
for row in db.execute("select id, title, likelihood*impact s from risk order by s desc limit 3"):
    print("top", *row)
print("unowned:", [r[0] for r in db.execute("select id from risk where owner is null")])

Run with python3 lab_sqlite.py. Verified output:

top R1 tenant data emailed to wrong person 12
top R5 lost laptop with case notes 12
top R2 ransomware on file server 10
unowned: ['R3', 'R6']

9.3 Why structure helps

With structured data, Elmfield can track how scores change over time, check that every risk has an owner and produce board reports automatically. Sofia also adds a constraint so that likelihood and impact must be between one and five, preventing typing errors. Dr. Haddad notes that databases do not make risk judgements better by themselves, but they make the judgements visible, consistent and easy to review, which improves governance by exposing gaps. The one-to-five constraint, for instance, stops a likelihood of 33 being typed in by mistake and silently topping the ranking.

Worked example

Sofia loads six risks into SQLite, finds two tied at twelve and two unowned risks and adds range constraints.

Synthetic case. The register is synthetic.

Errors and limits

Structured data can still hold poor judgements. Scales cause ties.

Exercises

• EX025 — Score formula. How is the risk score computed?
  Show marking criteria

  Rubric: 3 = likelihood times impact;; 2 = product;; 1 = sum;; 0 = none.
• EX026 — Tied risks. Which two risks tied at the top?
  Show marking criteria

  Rubric: 3 = misdirected tenant data and lost laptop, both 12;; 2 = tie;; 1 = ransomware;; 0 = none.
• EX027 — Unowned list. Which risks had no owner?
  Show marking criteria

  Rubric: 3 = R3 contractor misuse and R6 board portal passwords;; 2 = two;; 1 = vague;; 0 = none.

Synthesis

Storing a register as structured data makes priorities and gaps easy to see.

Continue studying — chapter 09 of 40

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