Classifying Database Systems

---

In NoSQL_Overview we met five data models. To compare them fairly, we always ask the same questions. This scriptum introduces that list of questions — the classification grid — and uses it on the system you know best: the relational database.

During the next lessons you fill in one column of the grid per database family. At the end you have one table that compares them all.

---

1. The classification grid

#	Criterion	Question to ask
1	Data model	What is the basic unit of data? (row, document, pair, node, …)
2	Schema	Is the structure fixed before inserting (schema on write) or flexible (schema on read)?
3	Query language	How do I ask questions? Standardised or product-specific?
4	Relationships	How are connections between data stored and used?
5	Transactions & consistency	ACID or BASE? What is the unit of atomicity?
6	CAP (default)	In a cluster: CP or AP?
7	Scaling	Vertical or horizontal? How is data split?
8	Strengths	What is it really good at?
9	Weaknesses	Where does it hurt?
10	Typical use cases	Where would you use it?
11	Examples	Which products implement it?

---

Criteria 5–7 come straight from Transactions_CAP_Scaling. If you are unsure about ACID, BASE, CP/AP or the Scale Cube, re-read it now.

---

2. Relational databases — the reference

The relational model was described by Edgar F. Codd (IBM) in 1970 and has been the standard for business data for more than 40 years. Everything new is measured against it.

---

2.1 Data model

• Data lives in relations = tables.
• A table has columns (attributes with a fixed data type) and rows (tuples).
• Every row is identified by a primary key.
• Data is normalised: every fact is stored exactly once (1NF–3NF).

---

2.2 Schema: schema on write

CREATE TABLE customer (
  id    INT PRIMARY KEY,
  name  VARCHAR(50) NOT NULL,
  city  VARCHAR(40)
);

• The structure is defined before data is inserted.
• The database rejects everything that does not fit: wrong data type, missing NOT NULL value, unknown column.
• Changing the structure needs ALTER TABLE — on a table with 500 million rows that can take hours.

---

2.3 Query language: SQL

• Declarative: you say what you want, the database decides how (query optimizer).
• Standardised (ISO SQL since 1987): what you learn for MySQL mostly works on PostgreSQL, SQL Server and Oracle.
• Very powerful: JOINs, subqueries, aggregation, window functions, recursive CTEs.

---

2.4 Relationships: foreign keys and JOINs

• A relationship is stored as a value (foreign key) — not as a link.
• At query time, the database computes the connection with a JOIN (using an index).
• Referential integrity: the database guarantees that a foreign key always points to an existing row.

SELECT c.name, o.id
FROM customer c
JOIN orders o ON o.customer_id = c.id;   -- relationship computed here

---

2.5 Transactions and consistency

• Full ACID transactions across any number of rows and tables.
• Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) keep the data valid.
• Isolation levels control concurrent access (you tested this in the last exercise).

---

2.6 CAP

Setup	CAP	Why
single server	CA	no network between nodes → no partition possible
primary + async read replicas	reads on replicas may be stale	replicas lag behind
primary + sync replication / distributed SQL (CockroachDB, Spanner)	CP	during a partition, the minority side refuses writes

---

2.7 Scaling

• Classic way: scale up (bigger server).
• Reads: X-axis read replicas.
• Writes: sharding is possible (e.g. Vitess for MySQL) but hard — JOINs and transactions across shards are expensive.

---

2.8 Strengths and weaknesses

✓ Strengths	✗ Weaknesses
any ad-hoc question possible (SQL)	rigid schema, migrations needed
no duplicates thanks to normalisation	many JOINs for nested data (one order = 3 tables)
strong consistency, ACID	horizontal scaling of writes is hard
huge ecosystem, decades of experience	impedance mismatch: objects in C# ≠ rows in tables (that is why ORMs like Entity Framework exist)
standardised language	deep relationship chains (friends of friends of friends) → many self-JOINs

---

2.9 The relational column — filled in

Criterion	Relational
Data model	tables (rows & columns), normalised
Schema	fixed, schema on write
Query language	SQL (ISO standard, declarative)
Relationships	foreign keys, computed with JOINs at query time
Transactions	ACID across any rows and tables
CAP (default)	single node: CA; distributed SQL: CP
Scaling	vertical + read replicas; sharding is hard
Strengths	flexible queries, integrity, consistency
Weaknesses	rigid schema, JOIN cost, write scaling
Typical use	banking, ERP, orders, stock, anything where correctness matters most
Examples	MySQL, PostgreSQL, SQL Server, Oracle, SQLite

---

3. Choosing a family

A first rule of thumb — we will refine it with every family:

---

4. Summary

• We compare database systems with one fixed classification grid (data model, schema, query language, relationships, transactions, CAP, scaling, strengths, weaknesses, use cases).
• Relational = tables, fixed schema, SQL, relationships computed by JOINs, ACID.
• Its weak spots are exactly where NoSQL systems are strong: flexible structure, horizontal write scaling, deep relationships.
• When in doubt, relational is still the safe default.

---

5. Self-check questions

1. Who invented the relational model and when?
2. What does schema on write mean? Give one advantage and one disadvantage.
3. Why is SQL called declarative?
4. How is a relationship stored in a relational database, and when is it “evaluated”?
5. What is referential integrity?
6. Why is a single MySQL server classified as CA?
7. What is the impedance mismatch, and which tool tries to hide it?
8. Name two situations where a relational database is the clear best choice.
9. Name two situations where a relational database gets into trouble.

---

Short answers

1. Edgar F. Codd, IBM, 1970.
2. The structure is defined and enforced before inserting. + invalid data is rejected. – changing the structure needs migrations.
3. You describe what result you want; the optimizer decides how to get it.
4. As a foreign key value; the connection is computed at query time by a JOIN.
5. The database guarantees that every foreign key refers to an existing row.
6. There is no network between nodes, so no partition can happen — it is consistent and available, but not partition tolerant.
7. Objects (with nested lists) do not map directly to flat tables. ORMs like Entity Framework hide the mapping.
8. e.g. banking, order processing, stock management, anything needing ACID and ad-hoc reporting.
9. e.g. very high write volume across many servers, rapidly changing structure, deep relationship chains (social networks).