Databases / DBMS
Relational theory, SQL, storage engines, transactions, and how queries get fast.
Topics in this field: 37
- Database Fundamentals — A database is a managed collection of data with a query language, integrity rules, and a storage engine.
- Relational Databases — Data as relations (tables), queried with algebra/calculus, constrained by keys.
- SQL — The declarative language of relational databases: SELECT describes the result, not the plan.
- Tables — Named relations: a heading of columns and a body of rows. Duplicates depend on whether you mean bag or set.
- Primary Keys — A column (or set) that uniquely identifies a row. The handle the rest of the schema points at.
- Foreign Keys — A column that must match a key in another table. Referential integrity as a constraint, not a hope.
- Constraints — Declarations the engine enforces: NOT NULL, UNIQUE, CHECK, FK — integrity that survives a bad insert.
- Joins — Combining rows from two relations by a predicate: inner, left, right, full, cross, and the nested-loop vs hash implementations.
- Aggregation — GROUP BY, HAVING, and the reduction of many rows to a statistic.
- Subqueries — A query used as a value, a table, or a predicate. Correlated vs uncorrelated is the performance fork.
- Indexes — Auxiliary structures (usually B+ trees) that make predicates cheap at the cost of writes and space.
- Views — Named queries. Virtual by default; materialized when you cache the result.
- Transactions — A grouping of reads and writes that should succeed or fail as a unit.
- ACID — Atomicity, Consistency, Isolation, Durability — the classical transaction contract.
- Isolation Levels — Read uncommitted through serializable: which anomalies you tolerate for concurrency.
- Locks (DB) — Shared/exclusive, row/table, intention locks. Two-phase locking as a correctness argument.
- MVCC — Readers see a snapshot; writers create versions. Postgres and InnoDB's answer to blocking reads.
- Database Concurrency — Interleaving transactions without losing integrity. Conflict serializability is the theory.
- Normalization — Decomposing schemas to remove redundancy. Normal forms are tests, not decorations.
- 1NF — Atomic values in cells; no repeating groups. The floor of the relational model.
- 2NF — 1NF plus no partial dependence of a non-key attribute on part of a composite key.
- 3NF — 2NF plus no transitive dependence of a non-key attribute on the key.
- BCNF — Every determinant is a candidate key. Stricter than 3NF; sometimes you lose a dependency.
- Query Optimization — Turning SQL into a cheap plan: statistics, join order, and access paths.
- Query Planners — Cost-based vs rule-based planners, EXPLAIN, and why the optimizer is the real engine.
- Storage Engines — The physical implementation: heap files, clustered indexes, InnoDB vs MyISAM vs LSM.
- Buffer Pool — The database's cache of disk pages. Replacement policy, dirty pages, and checkpoints.
- Write-Ahead Log — Log the change before the page. Redo/undo, crash recovery, and replication's raw feed.
- B-Trees — Balanced multiway trees. Internal nodes hold keys and children; used widely in filesystems and DBs.
- B+ Trees — All values in leaves, leaves linked. The index structure behind almost every relational PK.
- Replication (DB) — Copying data to other nodes: statement vs row, sync vs async, failover.
- Sharding — Partitioning rows across machines by a key. Hotspots and resharding are the cost.
- Partitioning — Splitting a table by range, list, or hash — within one instance or across them.
- Key-Value Stores — The simplest NoSQL: get/put by key. Redis, Dynamo-style systems.
- Document Stores — JSON/BSON documents with secondary indexes. Schema-on-read, query-on-fields.
- Column Stores — Values stored by column, not row. Analytics, compression, and vectorized execution.
- Graph Databases — First-class nodes and edges. Index-free adjacency and graph query languages.