◇ OpenCyber Academy
Technologist · Chapter 4 · English

Chapter 4 — Data models

Open, free learning for anyone who wants to protect people and technology. No account is required to read this text or its worked answers.

Chapter 4 — Data models

Lesson map

Lesson 04 published. Map, example, EX010.

flowchart TD
  N0["Learning outcomes and connection"]
  N1["4.1 Entities, attributes and keys"]
  N2["4.2 Constraints: making impossible records impossibl"]
  N3["4.3 Missing values are findings"]
  N4["4.4 Joins answer the real questions"]
  N5["4.6 Practical activity: inventory queries"]
  N6["Chapter synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6
  N6 --> EX["Worked example"]
  EX --> Q["Exercise EX010"]

Logical drawing

flowchart LR
  N0["Learning outcomes and connection"]
  N1["4.1 Entities, attributes and keys"]
  N2["4.2 Constraints: making impossible records impossibl"]
  N3["4.3 Missing values are findings"]
  N4["4.4 Joins answer the real questions"]
  N5["4.6 Practical activity: inventory queries"]
  N6["Chapter synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> Q["EX010"]

[Learning outcomes and connection]
    |
    v
[4.1 Entities, attributes and keys]
    |
    v
[4.2 Constraints: making impossible records impossibl]
    |
    v
[4.3 Missing values are findings]
    |
    v
[4.4 Joins answer the real questions]
    |
    v
[4.6 Practical activity: inventory queries]
    |
    v
[Chapter synthesis]
    |
    v
[Worked example] --> [Exercise EX010]

Learning outcomes and connection

After this chapter you should be able to:

Every later chapter depends on records: inventories, data-flow registers, risk registers, access lists, evidence logs. When these live in spreadsheets edited by many people, they decay — duplicated rows, free-text owners, "TBC" in date fields. This chapter shows the minimum data modelling an implementer needs so that operational records stay trustworthy.

4.1 Entities, attributes and keys

A data model says what kinds of things the records describe (entities: asset, owner, support contract), what is recorded about each (attributes: model, firmware, site, criticality) and how they relate. Each entity has a primary key — a value that identifies exactly one record, such as an asset tag. A foreign key in one table points to the primary key of another: an asset's owner_id refers to a row in the owner table. That one design choice replaces free-text owner names, which drift ("Water Ops", "water operations", "A. Okafor (left)") with a reference that either points to a real owner or does not.

4.2 Constraints: making impossible records impossible

A constraint is a rule the database enforces on every write. NOT NULL says a value must be present; CHECK (criticality BETWEEN 1 AND 3) says only those values are allowed; a foreign key says the referenced row must exist (in SQLite, foreign-key enforcement must be switched on per connection, a detail worth knowing because it surprises people). Constraints move validation from "someone should check" to "the system refuses". They are Chapter 3's input validation applied to records.

In Lab 4 the last query is exactly this: inserting an asset with criticality 7 fails at the database, whoever wrote the script.

4.3 Missing values are findings

In an inventory, a missing value is not a blank; it is a statement that something is unknown. An asset with no owner is an asset nobody will patch, review or decommission. An asset with unknown firmware is one whose vulnerabilities cannot be assessed. Queries should therefore look for missing values, not ignore them. SQL's NULL behaves specially: owner_id = NULL is never true; you must write owner_id IS NULL. Many silent reporting errors come from forgetting this.

Lab 4's ownerless criticality-1 asset, PS-09's RTU, appears only because the query asks for it.

4.4 Joins answer the real questions

Operational questions usually span tables. "Which assets run on a model whose vendor support has ended?" needs the asset table joined to the support table on model. "How many criticality-1 assets does each owner have?" needs assets joined to owners, with a left join so that assets with no owner appear as "(none)" instead of vanishing from the count. The difference between an inner and a left join is often the difference between a report that hides a problem and one that shows it.

4.5 Worked example: FSTS inventory extract (synthetic)

Lab 4 builds a small inventory in an in-memory SQLite database (Python's sqlite3 module, documentation) and asks five questions. The answers are each actionable. PS-09's RTU has no owner, is criticality 1, and its model's vendor support ended on 2025-12-31: three reasons to act, on the same asset, which a spreadsheet sorted by site would never have put together. The station switch at PS-04 has unknown firmware. Two servers run operating-system versions on FSTS's own retire-by-policy list. The criticality-by-owner query shows one criticality-1 asset owned by nobody. Finally, an attempt to insert an asset with criticality 7 is refused by the CHECK constraint.

4.6 Practical activity: inventory queries

Lab 4 (save as lab04.py, run with python3 lab04.py):

# Lab 4 - an asset inventory as a relational model (offline, standard library only: sqlite3)
# Synthetic: Fallowmere Shared Technology Services inventory extract.
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE owner(id INTEGER PRIMARY KEY, name TEXT NOT NULL, service TEXT NOT NULL);
CREATE TABLE asset(id TEXT PRIMARY KEY, kind TEXT NOT NULL, model TEXT, firmware TEXT,
  site TEXT NOT NULL, owner_id INTEGER REFERENCES owner(id), criticality INTEGER CHECK (criticality BETWEEN 1 AND 3));
CREATE TABLE support(model TEXT PRIMARY KEY, vendor_end TEXT);
INSERT INTO owner VALUES (1,'Water Ops','drinking water'),(2,'Clinical Eng','hospital'),(3,'Depot IT','transit');
INSERT INTO asset VALUES
 ('PS04-PLC','plc','Arlen 300','4.2','PS-04',1,1),('PS07-PLC','plc','Arlen 300','3.9','PS-07',1,1),
 ('PS09-RTU','rtu','Kell R2','1.1','PS-09',NULL,1),('RLY-01','server','generic x86','deb12','Hub',1,1),
 ('HIST-01','server','generic x86','win2016','Hub',1,2),('INF-PUMP-12','infusion','Moro IP5','2.0','Ward 3',2,1),
 ('PACS-GW','server','generic x86','win2012r2','Hospital',2,2),('DEPOT-KIOSK','kiosk','Tella K1','8.1','Depot',3,3),
 ('SW-STN-04','switch','Brack 24',NULL,'PS-04',1,2);
INSERT INTO support VALUES ('Arlen 300','2027-06-30'),('Kell R2','2025-12-31'),('Moro IP5','2029-01-01'),('Tella K1','2026-03-31');
""")
q = {
 "no owner": "SELECT id, site FROM asset WHERE owner_id IS NULL",
 "unknown firmware": "SELECT id, site FROM asset WHERE firmware IS NULL",
 "vendor support ended (as of 2026-10-12)":
   "SELECT a.id, s.vendor_end FROM asset a JOIN support s ON a.model=s.model WHERE s.vendor_end < '2026-10-12'",
 "OS on the service's retire-by-policy list (synthetic)": "SELECT id, firmware FROM asset WHERE firmware IN ('win2012r2','win2016')",
 "criticality-1 assets by owner": """SELECT COALESCE(o.name,'(none)'), COUNT(*) FROM asset a LEFT JOIN owner o
   ON a.owner_id=o.id WHERE a.criticality=1 GROUP BY o.name ORDER BY 2 DESC""",
}
for title, sql in q.items():
    print(f"-- {title}")
    for row in db.execute(sql):
        print("  ", row)
try:
    db.execute("INSERT INTO asset VALUES ('X','plc','m','1','PS-01',1,7)")
except sqlite3.IntegrityError as e:
    print("constraint rejected bad row:", e)

Expected output (Python 3.13.5):

-- no owner
   ('PS09-RTU', 'PS-09')
-- unknown firmware
   ('SW-STN-04', 'PS-04')
-- vendor support ended (as of 2026-10-12)
   ('PS09-RTU', '2025-12-31')
   ('DEPOT-KIOSK', '2026-03-31')
-- OS on the service's retire-by-policy list (synthetic)
   ('HIST-01', 'win2016')
   ('PACS-GW', 'win2012r2')
-- criticality-1 assets by owner
   ('Water Ops', 3)
   ('Clinical Eng', 1)
   ('(none)', 1)
constraint rejected bad row: CHECK constraint failed: criticality BETWEEN 1 AND 3

Read the output as a work list rather than a report. PS-09's RTU is the first line of work, because three findings meet on one asset: no owner, criticality 1 and vendor support ended on 2025-12-31. The PS-04 station switch's unknown firmware becomes a question for Water's engineer, not a guess. The two servers on FSTS's retire-by-policy list go to the platform owner with their replacement dates. Finally, check the refused insert: if the criticality-7 row had been accepted, foreign-key and CHECK enforcement were not active on that connection, and every earlier answer would deserve less trust.

4.7 Common errors and limits

A data model cannot tell you that an asset exists if nobody recorded it. It can make sure that what is recorded is consistent, and that gaps are visible.

Exercises — chapter 4

EX010 — Find the hidden asset. Objective: see an inner join hide a problem. Inputs: Lab 4. Constraints: change only the last query. Steps: (1) change LEFT JOIN to JOIN in the criticality-by-owner query; (2) run and compare; (3) explain in two sentences which asset disappeared and why that matters for a coverage report. Expected evidence: both outputs and explanation. Rubric: 3 = PS09-RTU row disappears, total criticality-1 count falls from 5 to 4, explanation links to unowned assets vanishing from management reports; 2 = observed, explanation weak; 1 = not run; 0 = none.

EX011 — Add a constraint and a table. Objective: extend the model safely. Inputs: Lab 4. Constraints: standard library only. Steps: (1) add a review table (asset id, review date, reviewer, result) with a foreign key to asset and a CHECK that result is one of ok, issue, not-reachable; (2) enable foreign keys with PRAGMA foreign_keys = ON; (3) show that inserting a review for a non-existent asset fails; (4) write a query listing criticality-1 assets with no review in the last 90 days. Expected evidence: code and output. Rubric: 3 = constraint and foreign key enforced, query uses a left join or NOT EXISTS so never-reviewed assets appear; 2 = works but misses never-reviewed assets; 1 = schema only; 0 = none.

EX012 — Spreadsheet to model. Objective: recognize data-quality faults. Inputs: a synthetic spreadsheet row set: PS-03 PLC | owner: Water Ops | fw: 4.2 | support: Jun 2027; PS-03 plc | owner: water operations | fw: ? | support: 30/06/27; PS-05 PLC | owner: A. Okafor (left) | fw: 4.1 | support: TBC. Constraints: no code needed. Steps: (1) list every data-quality fault; (2) say which constraint or design choice in Chapter 4's guidance on keys, constraints and missing values prevents each. Expected evidence: table. Rubric: 3 = duplicate asset with inconsistent case, free-text owner variants, person instead of role as owner and a leaver, unknown firmware as "?", mixed date formats, "TBC" — each mapped to primary key, foreign key, NOT NULL/explicit unknown, ISO dates; 2 = most found; 1 = few; 0 = none.

Chapter synthesis

Operational records are only useful if they stay consistent. Model them as entities with keys, link them with foreign keys instead of free text, and let constraints refuse impossible records. Treat missing values as findings, query for them explicitly, and use left joins in any report about coverage. A good model makes gaps visible; it cannot find assets nobody recorded.

Continue studying — chapter 04 of 30

← Chapter 03 · Course chapter index · Chapter 05 →

Continue to the next authored lesson.

Worked answers

EX010 — Find the hidden asset. With JOIN, the criticality-1 query prints only ('Water Ops', 3) and ('Clinical Eng', 1); the ('(none)', 1) row disappears, so the total falls from 5 to 4. PS09-RTU vanishes because it has no owner and an inner join keeps only rows with a match. In a management report this means the one critical asset nobody owns — the one most likely to be neglected — is absent from the count that is supposed to show coverage.

EX011 — Add a constraint and a table. Schema: CREATE TABLE review(asset_id TEXT NOT NULL REFERENCES asset(id), reviewed TEXT NOT NULL, reviewer TEXT NOT NULL, result TEXT NOT NULL CHECK (result IN ('ok','issue','not-reachable'))), with PRAGMA foreign_keys = ON executed on the connection first. Inserting ('NOPE','2026-10-01','b.lind','ok') raises sqlite3.IntegrityError: FOREIGN KEY constraint failed. Query: SELECT a.id FROM asset a WHERE a.criticality = 1 AND NOT EXISTS (SELECT 1 FROM review r WHERE r.asset_id = a.id AND r.reviewed >= '2026-07-14') — with no reviews inserted, all five criticality-1 assets appear. Full credit notices that an inner join to review would have returned none of the never-reviewed assets.

EX012 — Spreadsheet to model. Faults: duplicate record for PS-03 PLC with different case ("PLC"/"plc") — primary key on asset tag; owner variants "Water Ops"/"water operations" — foreign key to an owner table; owner recorded as a person who has left — owner as a role, with joiner–mover–leaver updates (Chapter 7); firmware "?" — explicit NULL meaning unknown, queried as a finding; mixed date formats "Jun 2027"/"30/06/27" — ISO 8601 dates; "TBC" in a date field — NULL plus a ticket, never text in a date column. Full credit maps every fault to a design choice.