Phase 4 Interactive Simulators(18)
Browse all 18 labs →Why a DBMS Exists: Flat File vs Engine
Same data, same workload — compare lookups, races, and crashes with and without a database engine.
Engine Event Log
- B+ Tree Index Scan
- SQL vs NoSQL Wizard
- DBMS vs Flat FilesFREE
- Relational Integrity ConstraintsFREE
- SQL Join AlgorithmsFREE
- ACID & Crash RecoveryFREE
- Normalization vs Read CostFREE
- Primary Key ClusteringFREE
- Index Lookup Costs
- EXPLAIN Plan Cost Model
- Isolation Anomaly Lab
- Optimistic vs Pessimistic Locking
- CAP & PACELC Replicas
- NoSQL Workload Fit
- Embed vs Reference Modeling
- Redis Structure Costs
- LSM-Tree Write Path
- Graph Traversal vs Joins
Databases: Foundations & Modeling
Every system design interview hinges on the data layer. Phase 4 deconstructs database engines from the ground up:
All Topics in Phase 4
0 of 18 completedUnderstand the fundamental purpose of Database Management Systems (DBMS): Structured storage, concurrency control, crash recovery, and why flat files fail at scale.
Deconstruct Edgar F. Codd's relational algebra: Relations (tables), Tuples (rows), Attributes (columns), Foreign Key constraints, and Referential Integrity.
Master the relational engine: Venn diagrams of JOIN semantics, physical join algorithms (Nested Loop, Hash Join, Merge Join), and the N+1 query problem.
Deconstruct the 4 guarantees of transactional databases: WAL-based rollback, schema invariance, MVCC isolation, and fsync durability.
Balance write integrity against read latency: The normal forms (1NF → 3NF/BCNF), update anomalies, and when to deliberately denormalize for scale (OLTP vs OLAP).
Design robust database keys: Natural vs Surrogate keys, UUIDv4 vs UUIDv7 vs Auto-Increment BIGINT, and composite indexing tradeoffs.
Understand the universal database index: B-Tree vs B+ Tree internals, fan-out factors, search complexity, page splits, and why B+ Trees power PostgreSQL and MySQL.
Master database index structures: Clustered Indexes vs Secondary Indexes, Index Double-Lookups, Covering Indexes (Index-Only Scans), and GIN/GiST for full-text.
Interpret database execution plans: Cost-Based Optimizer (CBO) statistics, Seq Scan vs Index Scan vs Index-Only Scan, WorkMem spills, and query tuning.
Deconstruct SQL concurrency anomalies: Dirty Reads, Non-Repeatable Reads, Phantom Reads, Serialization Anomaly, and Multi-Version Concurrency Control (MVCC).
Compare concurrency control patterns: Row-level exclusive locks (SELECT FOR UPDATE) vs version numbers (OCC / CAS), and SELECT FOR UPDATE SKIP LOCKED for job queues.
Examine the birth of NoSQL: Horizontal scaling limits of relational databases, schema flexibility, the CAP Theorem, and the PACELC trade-off matrix.
Deconstruct the 4 major NoSQL families: Key-Value (Redis), Document (MongoDB), Wide-Column (Cassandra), and Graph (Neo4j) architectures and use cases.
Master NoSQL document modeling in MongoDB: Embedded Documents (Denormalized 1:1, 1:Few) vs Normalized References (1:Many, 1:Squillions, M:N), and the 16MB BSON limit.
Explore ultra-fast key-value engines: Redis data structures (Strings, Hashes, Lists, Sets, Sorted Sets, HyperLogLog, Bitmaps), single-threaded event loop, and persistence (RDB vs AOF).
Deconstruct massive write engines: Partition Keys vs Clustering Columns, Log-Structured Merge (LSM) Trees, Memtables, CommitLogs, SSTables, Bloom Filters, and Compaction.
Navigate complex connected networks: Labeled Property Graphs, Index-Free Adjacency, Declarative Cypher queries, and graph traversal algorithms (Breadth-First, Dijkstra).