DBMS Workbook

A shared, structured, queryable, persistent collection of data — and the DBMS that gives those four properties teeth.

The limits of file-based systems that drove the move to databases — redundancy, integrity, concurrency, recovery.

Conceptual, logical, and physical schemas; data independence; how a schema change at one level shouldn't break the others.

Relational vs document vs key-value vs graph vs columnar vs time-series. What each is good at and where the boundaries blur.

Model = vocabulary. Schema = structure. Instance = data. The difference matters for migration, validation, and discussion.

Entities, attributes, primary keys; entity sets vs entity types; the smallest vocabulary to design a schema.

Relationship types, degree (binary / ternary), cardinality constraints, participation constraints, attributes on relationships.

Entities whose identity depends on another entity, identifying keys, and where this pattern shows up in real schemas.

The standard rules — each entity becomes a table, each relationship becomes a foreign key or junction table, weak entities collapse.

Relations, tuples, attributes, domains. Why the relational model won — composability and a closed algebra.

What each key is for, how to choose a primary key, why surrogate keys are usually the right default.

Entity integrity, referential integrity, domain constraints, CHECK constraints, and triggers as the catch-all.

Selection, projection, join, union, difference. The algebra SQL is compiled into.

Atomicity, Consistency, Isolation, Durability. What each one promises and where each one bends in practice.

Read-uncommitted to serializable; the anomalies each prevents (dirty read, non-repeatable, phantom, write skew).

A → B as the unit of schema reasoning; closures; minimal covers; how FDs drive the normal forms.

Atomicity, full functional dependency, transitive dependency — the three levels every relational schema starts with.

BCNF as the strict-3NF; the pragmatic stopping point and where denormalization is genuinely worth it.

Defining the schema. Tables, columns, constraints, indexes — and the ALTER traps on a live system.

Mutating data. RETURNING, UPSERT / ON CONFLICT, the things to know about row-locking on writes.

The shape of every read. Predicate logic, NULL handling, the difference between AND/OR and short-circuit eval.

Connecting tables. When LEFT JOIN is the right tool, what FULL OUTER is for, and how CROSS JOIN should be feared.

COUNT / SUM / AVG / MIN / MAX, the GROUP BY contract, why HAVING is not WHERE.

Correlated vs non-correlated subqueries, the readability case for CTEs, recursive CTEs for trees.

OVER, PARTITION BY, ORDER BY, frame clauses. Running totals, ranks, lag/lead — the part of SQL most engineers underuse.

How the planner reads a query, the cost-based optimizer, EXPLAIN/EXPLAIN ANALYZE, the four operators you'll see most.

Why OLTP is row-oriented and OLAP is column-oriented; the storage layout that explains the performance gap.

The classic disk-friendly tree, fanout, leaf-page linking, why every relational DBMS defaults to B-tree.

Hash indexes for equality lookups, bitmap indexes for low-cardinality columns, the trade-offs vs B-tree.

Memtables, SSTables, compaction. The structure behind LevelDB, RocksDB, Cassandra, ScyllaDB, and DynamoDB's storage.

Why every durable database writes a WAL, ARIES, redo/undo, checkpointing, crash recovery.

Snapshot isolation, vacuum, the trade-off vs lock-based concurrency. Why Postgres, MySQL InnoDB, and Spanner all use MVCC.

Process-per-connection model, MVCC, the planner, extensibility (FDW, custom types), and what makes Postgres the default.

The B+ tree clustered-index storage engine, group replication, the historical mistakes and how 8.x undid them.

Partition + sort key model, single-table design, on-demand vs provisioned capacity, GSIs, transactions, the cost model.

TrueTime, externally consistent transactions, Paxos groups, the breakthrough that made global ACID actually work.

A mistaken `rm -rf` on the primary; five backup mechanisms that all failed; the public postmortem everyone should read.

An old code path enabled by a deploy; loose-state assumptions in a trading system; what 'shared mutable state' costs at scale.

Count occurrences of values in a column. The COUNT(*) GROUP BY template every SQL interview opens with.

Bucket continuous values into ranges (age bands, price tiers) using CASE WHEN inside GROUP BY.

Cumulative aggregations over a window. SUM() OVER (ORDER BY ... ROWS BETWEEN ...). The time-series running-total template.

Fill missing rows in a sequence with default values via LEFT JOIN against a generated calendar or series.

EXISTS vs NOT EXISTS vs IN vs LEFT JOIN IS NULL — four ways to say where this related row does or does not exist.

ROW_NUMBER() OVER (PARTITION BY ...) = 1 and the DISTINCT ON (Postgres) alternatives for keeping one row per group.

LIKE, ILIKE, regex, trigram, full-text — the spectrum of find rows that match a string pattern.

INTERSECT / EXCEPT / UNION and the equivalent JOIN / NOT EXISTS forms. When set algebra beats joins.

Self-join, anti-join, semi-join, lateral join, theta-join. Beyond the standard four kinds of join.

Correlated vs uncorrelated subqueries; subquery in SELECT vs WHERE vs FROM. When CTEs beat nested subqueries.

ROW_NUMBER vs RANK vs DENSE_RANK. Per-partition top-N selection. The second-highest-salary canon.

LEAD() and LAG() for previous/next-row comparison. Streak detection, change-from-previous metrics, run-length tracking.

Recursive CTEs (WITH RECURSIVE) for tree traversal, transitive closures, organisational hierarchies.

The streak-detection template: subtract row number from a value, group by the difference. Identifies runs of consecutive rows.

Long-to-wide and wide-to-long reshapes. PIVOT (T-SQL), crosstab (Postgres), conditional aggregation as the portable form.