Database Management Systems
A database is a machine for answering questions about data without losing any of it. These topics take a single query and a single transaction and follow each one through the engine, step by step.
Start from the beginning →13 topics you can watch now, 11 still to come.
Foundations
What a database gives you that a folder of files does not, and the layering that makes it possible.
- What is a Database?Start here. Keep the same data in flat files and watch all five classic problems appear, one at a time.
- Three-Schema ArchitectureThree descriptions of one database — and the two kinds of change each boundary is there to absorb.
- Database users and the DBA
- Instances vs schemas
Data models
How a question about the world becomes a question about tables.
- ER Modelling to RelationsEntities, relationships and cardinality — and the mechanical rules that turn a diagram into tables.
- Relational AlgebraSix operators, one expression tree — and the rewrite that makes a query fast instead of slow.
- Relational model
- SQL as algebra
Schema design
Splitting tables until no fact is stored twice — and knowing when to stop.
- Functional Dependencies and ClosureOne algorithm — attribute closure — answers every question about keys, implication and lossless splits.
- Normalisation: 1NF to BCNFWatch one badly-designed table split apart until no fact is stored twice.
- Lossless decomposition
Storage and indexing
Where the rows actually live, and how the engine finds one fast.
- B+ Tree Insert and SplitEight keys, four splits, and a tree that grows at the root so every leaf stays the same depth.
- Hash IndexingO(1) for equality, useless for ranges — and what happens when a bucket fills up.
- Pages and heap files
- Clustered vs secondary indexes
Query processing
One query, several possible plans, and the cost of each.
- Sort-merge join
- Hash join
- Cost-based plan selection
Transactions
Many users at once, and the guarantee that none of them sees a half-finished change.
- ACID in PracticeFour properties, shown as the four different ways one bank transfer can go wrong.
- Conflict SerialisabilityBuild the precedence graph, look for a cycle. One theorem decides whether a schedule is safe.
- Two-Phase Locking and DeadlockThe protocol that guarantees serialisability — and the deadlock it does nothing to prevent.
- Write-Ahead Logging and RecoveryCrash mid-transaction, then rebuild: redo everything logged, undo everything uncommitted.
- MVCC snapshots