◇ OpenCyber Academy
Bachelor's · Chapter 9 · English

Chapter 9 — Data and databases

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 9 — Data and databases

Lesson map

Lesson 09 published. Map, example, EX025.

flowchart TD
  N0["Learning outcomes"]
  N1["9.1 Why inventories matter"]
  N2["9.2 Tables and keys"]
  N3["9.3 Queries"]
  N4["9.4 Accuracy and 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 Why inventories matter"]
  N2["9.2 Tables and keys"]
  N3["9.3 Queries"]
  N4["9.4 Accuracy and minimisation"]
  N5["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Why inventories matter]
    |
    v
[9.2 Tables and keys]
    |
    v
[9.3 Queries]
    |
    v
[9.4 Accuracy and minimisation]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) model network inventory data; (2) query it; (3) keep data accurate and minimal.

9.1 Why inventories matter

Priya cannot secure devices she does not know exist. An inventory lists each device with its type, owner, location, network group and address. The CIS Controls place inventory of enterprise assets first for this reason: every later control depends on knowing what is there. At the clinics, the inventory is a spreadsheet last updated two years ago. Comparing it with devices actually seen on the network reveals missing entries, retired devices still listed and devices nobody can identify, each of which needs follow-up.

9.2 Tables and keys

A relational database stores data in tables of rows and columns. Priya designs two tables: devices, with a unique identifier, type, owner and group, and addresses, linking device identifiers to IP addresses and dates. A key uniquely identifies each row, and linking tables by keys avoids repeating information. If a device changes address, only the addresses table changes. This structure also records history, which helps investigations ask which device held an address at a given time, a question flat spreadsheets struggle to answer reliably.

9.3 Queries

A query asks the database a question, such as "which devices in the guest group have fixed addresses" or "which clinical devices have no named owner". Python's standard library includes sqlite3, so Priya can create a small database file and run SQL queries without a server. She writes queries that answer operational questions rather than dumping all data. Each query result is a list of follow-up actions: devices to readdress, owners to find or records to correct. Queries are saved with the project so the same checks can be rerun each month.

9.4 Accuracy and minimisation

Inventory data decays as devices are added and removed, so it needs regular checks against what the network actually sees. It should also contain only what is needed. Recording which staff member uses which laptop is useful for security, but storing personal details beyond a name and role is not. Priya applies data minimisation: each column must have a purpose. Smaller, accurate data is easier to protect and more useful than large stores of stale information, which also carry privacy obligations.

Worked example

A query finds three clinical devices with no owner and one visitor tablet recorded in the staff group.

Synthetic case. Inventory data are synthetic.

Errors and limits

An inventory that is never compared with the live network quickly becomes misleading.

Exercises

Synthesis

Accurate, minimal inventories are the base for every later network control.

Continue studying — chapter 09 of 40

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