# Dbms
All Dbms notes →1 — Why Databases Exist
Traces the shift from file-based storage to DBMS, the problems that shift solved, and introduces OLTP vs OLAP and the CAP perspective.
2 — Database Architecture
Covers the three-schema architecture (external, conceptual, internal), data independence, and the core components inside a DBMS.
3 — Database Models
Surveys the hierarchical, network, relational, object-oriented, object-relational, and NoSQL data models and how each represents data.
1 — Relational Model Fundamentals
Defines the core vocabulary of the relational model — relations, tuples, attributes, domains, keys, degree, cardinality, and NULL semantics.
2 — Constraints
Covers the constraint types that enforce relational integrity — primary/candidate/alternate/composite/foreign keys, unique, check, default, and referential integrity.
3 — Relational Algebra
Introduces the relational algebra operators — selection, projection, rename, union, difference, Cartesian product, join, and division — that underpin SQL query semantics.
4 — Relational Calculus
Explains tuple and domain relational calculus, the safety restriction on formulas, and their expressive equivalence with relational algebra.
1 — SQL Basics
Covers the SQL language families — DDL, DML, DCL, TCL — along with core data types and constraint syntax.
2 — Querying Data
Covers the fundamental query clauses — SELECT, WHERE, ORDER BY, LIMIT, DISTINCT, LIKE, IN, BETWEEN, and CASE expressions.
3 — Joins
Compares inner, left, right, full, self, cross, anti, and semi joins and how each changes a query's result set.
4 — Aggregation
Covers GROUP BY, HAVING, aggregate functions, and multi-dimensional aggregation via ROLLUP, CUBE, and GROUPING SETS.
5 — Subqueries
Covers scalar and correlated subqueries and the EXISTS, NOT EXISTS, ANY, and ALL predicates.
6 — Common Table Expressions
Covers recursive and non-recursive common table expressions and their use in querying hierarchical data.
7 — Window Functions
Covers the OVER() clause and ranking/offset window functions — ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, and LAST_VALUE.
8 — Advanced SQL
Covers views, materialized views, stored procedures, functions, triggers, sequences, and identity columns.
1 — ER Modeling
Covers entity-relationship modeling — entities, attributes, relationships, weak entities, cardinality, participation constraints, and ISA hierarchies.
2 — Mapping ER to Relational Model
Covers the rules for translating an ER diagram's entities, relationships, weak entities, and ISA hierarchies into relational tables.
3 — Functional Dependencies
Covers trivial and non-trivial functional dependencies, closure, attribute closure, and computing a minimal cover.
4 — Normalization
Walks through the normal forms — 1NF through 5NF and Domain-Key Normal Form — and the anomalies each eliminates.
5 — Denormalization
Covers why and when to denormalize, the read/write tradeoffs involved, and practical scenarios where it pays off.
1 — Physical Storage
Covers how data is physically laid out on disk — pages, blocks, records, slotted pages, heap files, and clustered storage.
2 — Indexing
Covers why indexes exist and the major index types — clustered, non-clustered, composite, covering, partial, and bitmap.
3 — B-Trees
Covers B-tree and B+-tree structure, insert/delete operations, and why B+-trees are favored for range queries.
4 — Hash Indexes
Covers static and dynamic hashing schemes, including extendible and linear hashing, for equality-lookup indexes.
1 — Query Execution
Traces a query's path from parsing through optimization to execution, and how to read an execution plan.
2 — Query Optimization
Covers cost-based and rule-based optimization, join ordering, and predicate/projection pushdown.
3 — Join Algorithms
Compares nested loop, block nested loop, index nested loop, merge join, and hash join algorithms and when the optimizer picks each.
1 — Transaction Fundamentals
Covers the transaction lifecycle, its states, and the atomicity guarantee that ties them together.
2 — ACID Properties
Covers the four ACID properties — atomicity, consistency, isolation, durability — and what each guarantees.
3 — Concurrency Problems
Covers the concurrency anomalies isolation levels exist to prevent — dirty reads, non-repeatable reads, phantom reads, lost updates, and write skew.
4 — Concurrency Control
Covers lock-based concurrency control — shared and exclusive locks, lock granularity, and intention locks.
5 — Two-Phase Locking
Covers basic, strict, and rigorous two-phase locking and the serializability guarantees each provides.
6 — Timestamp Protocols
Covers timestamp-ordering concurrency control and the Thomas write rule optimization.
7 — Optimistic Concurrency Control
Covers optimistic concurrency control's read/validate/write phases and its rollback behavior on conflict.
8 — Isolation Levels
Covers the standard isolation levels — read uncommitted, read committed, repeatable read, snapshot isolation, and serializable — and which anomalies each permits.
1 — Logging
Covers write-ahead logging and the redo/undo logging schemes that make crash recovery possible.
2 — Recovery Algorithms
Covers checkpointing, the ARIES recovery algorithm, and crash vs. media recovery.
1 — Distributed Databases
Covers data fragmentation, replication, distributed query processing, and distributed transactions.
2 — Two-Phase Commit
Covers the two-phase commit protocol's coordinator/participant roles and its failure modes.
3 — Consensus Basics
Introduces Paxos and Raft at a high level as the consensus protocols distributed databases build on.
1 — NoSQL Overview
Covers why NoSQL databases emerged, the major categories, and their tradeoffs against relational systems.
2 — Key-Value Stores
Covers key-value store concepts through Redis and DynamoDB.
3 — Document Databases
Covers document database concepts through MongoDB and Couchbase.
4 — Column Family Databases
Covers wide-column store concepts through Cassandra and Bigtable.
5 — Graph Databases
Covers graph database concepts through Neo4j, the property graph model, and RDF.
1 — Replication
Covers master-replica, multi-master, and leaderless replication topologies and their tradeoffs.
2 — Partitioning
Covers horizontal and vertical partitioning and consistent hashing for distributing data across nodes.
3 — Distributed Transactions
Covers the saga and outbox patterns and eventual consistency as alternatives to distributed ACID transactions.
4 — CAP Theorem
Covers the CAP theorem's consistency, availability, and partition tolerance tradeoff.
5 — PACELC
Extends CAP with PACELC's latency-vs-consistency tradeoff and its practical implications for system design.
1 — Query Performance
Covers reading EXPLAIN plans, diagnosing slow queries, and choosing the right index.
2 — Database Tuning
Covers connection pooling, buffer pool sizing, caching, statistics, and vacuum/analyze maintenance.
3 — Common Bottlenecks
Covers the most common production bottlenecks — lock contention, hot partitions, index bloat, and deadlocks.
1 — Authentication & Authorization
Covers authentication and authorization in a DBMS via roles, privileges, and role-based access control.
2 — Encryption
Covers encryption at rest, encryption in transit, and transparent data encryption (TDE).
3 — SQL Injection
Covers SQL injection prevention through prepared statements and safe ORM usage.
1 — Choosing the Right Database
Covers how to choose a database for a system design — SQL vs NoSQL, read-heavy vs write-heavy workloads, time-series, and graph use cases.
2 — Designing Data Models
Covers data modeling for common system-design domains — user service, e-commerce, banking, messaging, and social networks.
3 — Scaling Databases
Covers the standard database scaling toolkit — sharding, replication, read replicas, caching, and CQRS.
4 — Interview Case Studies
Walks through database design for classic interview case studies — Instagram, WhatsApp, Uber trips, YouTube metadata, and Amazon's catalog.
1 — Frequently Asked Interview Questions
Covers the recurring DBMS comparison questions asked in interviews — ACID vs BASE, clustered vs non-clustered indexes, B-tree vs B+-tree, 2PL vs MVCC, OLTP vs OLAP, and normalization vs denormalization.
2 — SQL Coding Interview
Covers a SQL coding interview practice set spanning easy through hard window-function and recursive SQL problems.
3 — Internal Architecture Deep Dive
Covers internals deep dives across PostgreSQL, MySQL InnoDB, Oracle, and SQL Server.
4 — Mock Interview Problems
Covers full mock-interview problem sets spanning theory, SQL, database design, performance, and troubleshooting.
1 — SQL Cheat Sheet
A quick-reference cheat sheet of SQL syntax across DDL, DML, joins, aggregation, and window functions.
10 — MySQL EXPLAIN Cheat Sheet
A quick-reference cheat sheet for reading MySQL EXPLAIN output.
11 — Top 200 MAANG DBMS Interview Questions
A running list of the top 200 DBMS interview questions asked at MAANG-tier companies.
12 — DBMS Glossary
A glossary of core DBMS terminology used throughout this book.
13 — Further Reading
A curated list of papers, books, and blogs for going deeper on database internals.
2 — Relational Algebra Cheat Sheet
A quick-reference cheat sheet of relational algebra operators and their SQL equivalents.
3 — Normalization Cheat Sheet
A quick-reference cheat sheet of the normal forms and the anomaly each one eliminates.
4 — Isolation Levels Matrix
A matrix cross-referencing isolation levels against the concurrency anomalies each one permits or prevents.
5 — Lock Compatibility Matrix
A compatibility matrix for lock modes used in concurrency control.
6 — Join Algorithms Comparison
A side-by-side comparison of join algorithms and their cost characteristics.
7 — Index Selection Guide
A decision guide for choosing an index type and columns for a given query pattern.
8 — Database Selection Decision Matrix
A decision matrix for choosing between SQL and NoSQL databases based on workload characteristics.
9 — PostgreSQL EXPLAIN Cheat Sheet
A quick-reference cheat sheet for reading PostgreSQL EXPLAIN and EXPLAIN ANALYZE output.
Database Management Systems
A book-shaped table of contents for DBMS: relational foundations through SQL mastery, storage internals, transactions, distributed databases, NoSQL, and MAANG interview prep — cross-linking existing system-design/patterns notes instead of duplicating them.