DIAGRAMS, MODELING, THERAT

📐 Modelling systems - visual examples

Seven data and system modelling methods with diagrams. Examples are fictional, designed for training.

Diagram of enthusiasm and relationship

ERD - an example of the library (reader, performance, book)

ERD - an example of a library The reader shall remove zero or multiple releases. Each issue contains one or more books stored in the intertable LOAN ITEM with a composite primary key. The book may be zero or in several editions. MEMBER PK member_id full_name : varchar email : varchar joined_on : date LOAN PK loan_id FK member_id loaned_on : date returned_on : date? BOOK PK book_id isbn : varchar(13) title : varchar author : varchar LOAN ITEM PK, FK loan_id PK, FK book_id copies : int intermediate "many to many" removed contains is issued exactly one zero or more one or more PK primary key FK external key ? may be empty

When to use

At the very beginning, when you have to understand what things (enthities) are in the system and how they are connected to each other - even before creating tables.

Related Standard

Chen ERD (1976), Crow's Foot notation, IDEF1X. Data dictionary is often entered.

Restrictions

Do not show any final tables or business laws. Many to many links should always be divided by an intermediate table.

Objective class diagram

Figure 1

Table of UML classes - Vehicles Abstract class Vehicle with subclasses Car and Truck. Both inherit Vehicle fields and methods, and each has its own additional fields and methods. in this respect, it should be pointed out that, in the light of the above considerations, the commission considers that the measure at issue constitutes state aid within the meaning of article 107 (1) tfeu Vehicle - vin : String - year : int # mileage : double + start() : bool Car - seatCount : int - bodyStyle : BodyStyle + openSunroof() + engageCruise() Truck - payloadKg : int - axleCount : int + loadCargo(kg : int) + tilt(up : bool) succession: a subclass is a class type italic and: the object cannot be directly created -private #protected (including subclasses) +public

When to use

Designing an object-oriented code - to see classes, their fields, methods and succession even before writing.

Related Standard

OMG UML 2.5.1. Also includes sequence, state, activity and employment charts.

Safety Note

private/protected is not a security wall - the code can bypass them (e.g. Java reflection). Real security limits are individual processes or services.

Transformation of a three-layer data model

Conceptual → Logical → Physical model (warehouse)

Three layers of the data model One example of the warehouse in three layers: in the conceptual warehouse there are shelves and shelves, in the logic they are tables with keys and intertables, in the physical - PostgreSQL commands. Conceptual Business View - What We Keep Storage is Shelves keep Commodity 1 N N M Just things and links. Without data types and keys. You know, business, too. Logical Tables and keys - for any RDBMS Shelf PK shelf_id : int FK warehouse_id : int code : string(10) Item PK item_id : int sku : string(40) weight_g : int ShelfItem PK shelf_id, item_id FK shelf_id FK item_id quantity : int "Many to many" - an intermediate table. Physical PostgreSQL - specific engine CREATE TABLE item ( item_id serial PRIMARY KEY, sku varchar(40) NOT NULL UNIQUE, weight_g int CHECK (weight_g > 0) ); CREATE INDEX idx_item_sku ON item (sku); Types, restrictions and indices the engine in question. Shelf and ShelfItem likewise, with FOREIGN KEY.

When to use

To gradually get from the business view to the ready database scheme. Each layer is designed for another reader - business, architect, DBA.

Related Standard

ANSI/SPARC three-schema architecture (1975). Nowadays: dbt stagging→intermediate→mart layers follow a similar principle.

Restrictions

In small projects, the conceptual layer is often omitted - but this does not allow business logic to get stuck in the database scheme.

Data flow diagram, Level 1

DFD - restaurant order with reliability limits

DFD - restaurant order The customer chooses food, the order process calculates the amount and transfers it to the payment, which asks the card network permission. An approved order comes to the repository, and from it a check goes to the kitchen area for cooking. Two limits of reliability: order system and kitchen. Boundary of trust: order system Boundary of credibility: kitchen Client outside Card Network outside 1.0 Order 2.0 Payments 3.0 Preparation D1 orders choice amount request for payment approval paid order cocks for kitchen purposes outside process data repository data flow confidence limits

When to use

To see how data flows between people and systems and where they are stored. This is the basis for the modelling of threats.

Related Standard

Yourdon/DeMarco notation. Nowadays - Microsoft Thread Modeling Tool, OWASP pytm, draw.io DFD template.

Safety Note

Each confidence limit is the place where you have to check who you are (authentication) that you are (authorization) and whether the data is valid (validation). STRIDE starts from it.

Relation normalisation - from unnormalized to 3NF

Normalization - film rental

UNF Unnormalised table

One rental table, as could be recorded in the box.

rental_idrenterphonemovies
1Alice2000 1111Matrix, Inception
2Bob2000 2222Heat
3Alice2000 1111Tenet

Problem: there are several films in one cell, so one film cannot be found or counted.

1NF The amount to be reported in column 060 of this row: Original deduction Article 66 (d) of CRR

Each film in its own line. The key now has two columns together: rental id + Movie id.

rental_idmovie_idrenterphonetitle
1M1Alice2000 1111Matrix
1M2Alice2000 1111Inception
2M3Bob2000 2222Heat
3M4Alice2000 1111Tenet

Problem: renter and phone depend only on the rental id, while the title only depends on the movie id, i.e. the part key. So Alice's data is repeated in every row of her movie (partial dependence).

2NF Nothing depends on the part key

Every fact is transferred to a table whose key it really belongs to.

rentals
rental_idrenterphone
1Alice2000 1111
2Bob2000 2222
3Alice2000 1111
rental_movies
rental_idmovie_id
1M1
1M2
2M3
3M4
movies
movie_idtitle
M1Matrix
M2Inception
M3Heat
M4Tenet

Problem: in the rental table phone depends on the rendelet, not on the rental id: rental id → rent → phone. If Alice replaces her phone, it must be corrected in each of her rentals (transit dependence).

3NF Nothing depends on another key column

The tenant becomes his table, and the rental refers to it with an external key.

renters
renter_idnamephone
R1Alice2000 1111
R2Bob2000 2222
rentals
rental_idrenter_id
1R1
2R2
3R1
rental_movies + movies
…
kā 2NF solī

Result: each fact is kept in one place. Change your phone is one line and there are no insertions, fixes and deletions anomalies.

A short rule: each column depends on the key (1NF), on all the key (2NF) and not on anything other than the key (3NF).

When to use

In systems where data are frequently changed and duplication leads to errors (OLTP). Data warehouses (OLAP) often do the opposite - deliberately duplicated due to speed.

Related Standard

1NF, 2NF and 3NF - Cod (1970-1971), BNF - Boyce and Cod (1974), 4NF and 5NF - Fagin. In practice, usually stops at 3NF or BCNF.

Restrictions

Too much normalisation = much JOIN = slower system. In practice start with 3NF and denormalize only if measurements require it.

C4 architecture model, level 2 (Container)

C4 container level - weather forecast platform

C4 container level - weather platform The user opens a web panel that triggers the forecast API. APIs read and write a time series database, receive measurements from an external sensor network and themselves require images from an external satellite image provider. External systems are located outside the system boundary. User [person] System: Weather Forecasts Web panel [browser app] forecasts, station map Forecast API [REST service] collect data, calculation of projections Time series DB [TSDB] measurements, historical projections Sensor Network [external system] track-side stations Satellite images [external system] supplier API used [HTTPS] JSON [HTTPS] articles of apparel and clothing accessories sends measurements [MQTT] requires images [HTTPS] system boundary external system C4 levels: context → containers → components → code

When to use

To describe the system architecture at a high level. The level of the Container indicates the separate parts to be launched - services, databases, external systems.

Related Standard

Simon Brown, c4model.com. arc42 frame (sec. 5 = Building Block View) is often used instead of C4 levels.

Safety Note

The system boundary is the first place to record how authentication takes place, where TLS ends and where audit logs are stored in the system.

Danger Modelling - Microsoft (1999)

STRIDE - threats along DFD elements

STRIDE - threat modelling DFD elements with their applicable STRIDE threat categories. User [external entity] S, R South service [process] S T R I D E application data T, I, D User Tablet (argon2 hash) [data repository] T, R, I, D query T, I, D STRIDE categories: S Spooofing - tying on another (account takeover, certificate forgery) T Tampere - data change (DB entry, MITM injection) R Repudiation - denial of activity (incomplete audit log) I Information malfunction (TLS deficiency, error stack tracks) D Denial of service - loss of availability E Elevation of privatege - RCE, IDOR, role bypass
DFD Element S T R I D E
External Facility (user, system outside the border) ✓-✓---
Process (services, lambdas, microservice) ✓✓✓✓✓✓
Data Store (DB, files, S3 basket) -✓✓✓✓-
Data Flow (HTTP/TCP, queue, IPC) -✓-✓✓-

Four issues - a framework for threat modelling

QuestionResultA tool in this guide
What are we building?System model with confidence limitsDFD, C4
What can go wrong?List of challenges for each elementSTRIDE
What will we do with that?Action, responsible and time frame for each threatreduce, adopt, prevent, carry
Did we do good?Verification that the measures have been implemented and the model is in line with the systemtests, review after change

This Regulation shall be binding in its entirety and directly applicable in all Member States. The model is not a unique document - it is renewed when architecture changes.

When to use

After DFD - pass through each element and check which six types of threat (STRIDE) fits it. Every threat found shall be recorded as to how to mitigate it.

Related Standard

Microsoft Three Modeling Tool, OWASP pytm. MITRE ATT&CK adds real tactics. PASTA, LINDDUN - other approaches.

Safety Note

STRIDE does not cover business logic errors, crypto weaknesses or supply chain. It is therefore often combined with OWASP Top 10 and MITRE ATT&CK.

Abbreviations

Abbreviations used in the Roadmap: the original name and what it means.

ERD
Entity-Relationship Diagram - a diagram of the entities and their links.
UML
Unified Modeling Language - modelling language for software diagrams.
DFD
Data Flow Diagram - data flow diagram.
PK / FK
Primary Key / Foreign Key - the primary key/external key indicating another table.
1NF / 2NF / 3NF / BCNF
Normal Forms - normal forms - the degrees by which the tables exempt duplicate data.
OLTP / OLAP
Online Transaction / Analytical Processing - daily transaction systems/analytical systems.
DBA
Database Administrator - database administrator.
RDBMS
Relational Database Management System - Relationship database system (PostgreSQL, MySQL).
C4
Context, Containers, Components, Code - architectural chart model on four levels.
STRIDE
Spoofing, Tampering, Repudiation, Information disclosure, Denial of service, Elevation of privilege - six threat classes.
PASTA / LINDDUN
Other threat modelling methods; LINDDUN - privacy threats.
IDOR
Insecure Direct Object Reference - access to a foreign record by replacing the identifier.
MITM
Man-in-the-Middle - an attacker between the two parties reading or changing data.
RCE
Remote Code Execution - the attacker can run his code on the server.
ANSI/SPARC
Three schematic (external, conceptual, internal) database architecture (1975).
TSDB
Time-Series Database - time series database (sensor measurements).
MQTT
Easy message protocol for IoT devices.