Chapter 4 — Data models | Technologist Security governance, risk and compliance

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"]
  N1["4.1 Tables"]
  N2["4.2 Relationships"]
  N3["4.3 Consistency"]
  N4["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4
  N4 --> EX["Worked example"]
  EX --> Q["Exercise EX010"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["4.1 Tables"]
  N2["4.2 Relationships"]
  N3["4.3 Consistency"]
  N4["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX010"]

[Learning outcomes]
    |
    v
[4.1 Tables]
    |
    v
[4.2 Relationships]
    |
    v
[4.3 Consistency]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX010]

Learning outcomes

You will (1) model governance data as related tables; (2) define keys and relationships; (3) avoid duplication and inconsistency.

4.1 Tables

Priya designs tables for Riverton's governance data: clinics, assets, owners, risks and controls. Each table has a key: a clinic code, an asset tag, an owner's staff number. Separate tables avoid repeating the same information many times, such as a clinic's address on every asset row. She learns that well-structured data make it easier to answer questions and harder to introduce errors when information changes. If a clinic moves, for example, its address changes in one row of the clinics table rather than on every asset row.

4.2 Relationships

Assets belong to clinics, owners are responsible for assets, risks affect assets and controls treat risks. Priya draws these relationships. She finds that one control, encrypted laptops, treats several risks, and one risk, ransomware, affects many assets. She learns that many-to-many relationships need linking tables. Getting relationships right lets Riverton answer questions like which risks depend on a control that failed, which is important for audits. A linking table between controls and risks, for instance, lets one encryption test update the status of every risk it treats.

4.3 Consistency

In the old spreadsheet, the same clinic appears as North, North Clinic and N. Priya standardises names using codes and adds validation rules so only valid codes can be entered. She learns that inconsistency is a common source of reporting errors: counts per clinic were wrong because the same clinic was spelled differently. Simple data rules prevent problems that would otherwise appear in board reports and audit findings. Mr. Lind points out that the three spellings of North had made that clinic appear smaller than it was in every per-clinic count.

Worked example

Priya models governance data in related tables with keys, uses linking tables for many-to-many relationships and standardises clinic codes.

Synthetic case. The data model is synthetic.

Errors and limits

Models need maintenance. Codes can be mistyped too.

Exercises

EX010 — Key example. Give one key used in Priya's tables. Rubric: 3 = clinic code, asset tag or staff number;; 2 = key;; 1 = vague;; 0 = none.
EX011 — Linking tables. When are linking tables needed? Rubric: 3 = for many-to-many relationships;; 2 = many-to-many;; 1 = vague;; 0 = none.
EX012 — Clinic names. What problem did inconsistent clinic names cause? Rubric: 3 = wrong counts per clinic;; 2 = miscounts;; 1 = vague;; 0 = none.

Synthesis

Related tables with keys and validation keep governance data consistent.

Continue studying — chapter 04 of 30

← Chapter 03 (draft) · Course chapter index · Chapter 05 (draft) →