Database Management Systems
How your data actually gets stored, organized, queried and kept safe, built up from absolute zero: data model history, the ER model, the relational model, relational algebra and calculus, SQL in depth (including window functions and CTEs), integrity constraints and normalization, file organization and indexing with B and B+ trees, transactions with concurrency control (isolation levels, MVCC, crash recovery), and query optimization and distributed databases. Every idea starts with a five-year-old's picture, then the real math behind it.
Written byRohan Sharma
Foundations of DBMS
6 lessons- 8 min read
What is data, and what is a database, really?
Starting from the absolute basics — what data means, why we collect it, and what turns a pile of facts into something a computer can use.
- 8 min read
Data models through history — hierarchical, network, relational, and NoSQL
How the way we organize data has evolved, from rigid tree-shaped hierarchical databases to today's relational and NoSQL systems, and why the relational model won for so long.
- 9 min read
The problems with file-based systems
Data redundancy, inconsistency, isolation, integrity, atomicity and concurrency problems — the exact failures a DBMS was invented to fix.
- 8 min read
What is a DBMS, and what does it actually give you?
Defining a DBMS precisely, and walking through every advantage it provides over raw file storage — with concrete examples for each one.
- 9 min read
The three-schema architecture and data independence
How a DBMS separates physical storage, logical structure, and each user's personal view of the data — and why that separation is the key to data independence.
- 7 min read
Database languages and the people who use them
DDL, DML, DCL and TCL explained simply, plus the different kinds of users who interact with a database system.
Foundations of DBMS Quiz
12 questions
The ER Model
5 lessons- 8 min read
Entities and attributes — the nouns of a database
What an entity, entity set, and attribute are, and the different types of attributes you'll use when modeling any real-world system.
- 9 min read
Relationships and cardinality
How entities connect to each other, relationship degree, and the four cardinality ratios that describe exactly how many entities can participate on each side.
- 8 min read
Keys in the ER model
Super keys, candidate keys, primary keys, and how weak entities borrow a key from a stronger entity they depend on.
- 9 min read
Weak entities, generalization, and specialization
A closer look at weak entities and identifying relationships, plus how the ER model expresses "is-a" hierarchies through generalization, specialization, and aggregation.
- 10 min read
Converting an ER diagram into relational tables
The mechanical rules for turning entities, relationships, weak entities, and specialization hierarchies into actual relational tables.
The ER Model Quiz
10 questions
Relational Model, Algebra & Calculus
6 lessons- 8 min read
The relational model — tables, tuples, and the math behind them
What a relation actually is in the mathematical sense, and how tuples, attributes, degree, and cardinality map onto the tables you already know.
- 7 min read
Keys and constraints, formally
Super keys, candidate keys, primary and foreign keys restated in relational terms, plus entity integrity and referential integrity.
- 10 min read
Relational algebra: the basic operators
Select, project, union, set difference, Cartesian product, and rename — the fundamental building blocks every relational query is made from.
- 9 min read
Relational algebra: joins and division
Theta join, equijoin, natural join, outer joins, and the division operator, all built from the basic operators of the previous lesson.
- 9 min read
Tuple relational calculus
A declarative way to describe what you want instead of how to get it, built from tuple variables, predicates, and the existential and universal quantifiers.
- 8 min read
Domain relational calculus
The value-based counterpart to tuple relational calculus, and a full comparison of every query language covered in this module.
Relational Model, Algebra & Calculus Quiz
10 questions
SQL Deep Dive
8 lessons- 9 min read
SQL DDL: creating tables and choosing data types
CREATE TABLE, ALTER TABLE, DROP TABLE, and a practical tour of the SQL data types you'll actually use, tying back to the domains from Module 2 and 3.
- 7 min read
SQL DML: INSERT, UPDATE, and DELETE
Adding, changing, and removing rows safely, and why the WHERE clause is the single most important word in this entire lesson.
- 8 min read
SELECT, filtering, and sorting in depth
The full anatomy of a SELECT statement, pattern matching, range and set conditions, NULL handling, and sorting query results.
- 8 min read
Aggregation and grouping
COUNT, SUM, AVG, MIN, MAX, GROUP BY, and the crucial difference between filtering rows with WHERE and filtering groups with HAVING.
- 8 min read
SQL joins in practice
INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, self joins, and CROSS JOIN written as real SQL, connecting directly back to the relational algebra join theory.
- 9 min read
Subqueries and set operations
Scalar, row, and correlated subqueries, EXISTS/NOT EXISTS for expressing "for all" logic, and UNION/INTERSECT/EXCEPT.
- 9 min read
Window functions and common table expressions
RANK/ROW_NUMBER/running totals with the OVER clause, and CTEs (including recursive CTEs) for readable, composable queries.
- 8 min read
Views, constraints, and triggers
CREATE VIEW as a saved query, the full set of SQL constraint keywords, and triggers for automatic actions on data changes.
SQL Deep Dive Quiz
13 questions
Integrity Constraints & Normal Forms
6 lessons- 7 min read
A complete tour of integrity constraints
Domain, entity, referential, and key constraints gathered into one formal picture, plus general assertions and how constraints connect to everything covered so far.
- 10 min read
Functional dependencies and Armstrong's axioms
The formal definition of a functional dependency, trivial vs non-trivial dependencies, and the three inference rules that let you derive every dependency implied by a given set.
- 11 min read
First, Second, and Third Normal Form
The three foundational normal forms explained through the update, insertion, and deletion anomalies they each eliminate, with worked decomposition examples.
- 9 min read
Boyce-Codd Normal Form (BCNF)
The subtle gap 3NF leaves open with overlapping candidate keys, the stricter BCNF definition that closes it, and why BCNF sometimes trades away dependency preservation.
- 9 min read
Multivalued dependencies, 4NF, and 5NF
What happens when a single-valued functional dependency isn't enough to describe a table's redundancy, and the two higher normal forms built to handle it.
- 9 min read
Decomposition properties — lossless join and dependency preservation
The two correctness guarantees every normalization decomposition must satisfy, with the exact test for each and worked examples of what happens when they fail.
Integrity Constraints & Normal Forms Quiz
10 questions
File Organization & Indexing
7 lessons- 9 min read
Storage devices and file basics
The memory hierarchy from registers to disk, how a database file is organized into blocks and records, and the math behind why disk access dominates database performance.
- 8 min read
RAID: combining disks for speed and safety
How RAID levels 0, 1, 5, and 10 trade off performance, storage efficiency, and fault tolerance by spreading and duplicating data across multiple physical disks.
- 10 min read
File organization: heap, sequential, and hashing
The three fundamental ways to physically arrange records in a file, and the worked disk-access math comparing insertion, search, and deletion cost for each.
- 9 min read
Indexing fundamentals: dense, sparse, and multilevel indexes
What an index actually is — a small lookup table pointing at the real data — and how dense, sparse, and multilevel indexes trade index size against search speed.
- 10 min read
B-tree indexing
The self-balancing multi-way search tree that keeps every lookup, insertion, and deletion within a guaranteed, small number of disk accesses, no matter how much the data grows.
- 9 min read
B+ tree indexing
How the B+ tree differs structurally from the B-tree, why every leaf-linked-list detail exists, and why it's the dominant index structure in production databases.
- 9 min read
Hashing techniques: static and dynamic
Static hashing's fixed-bucket weakness, and how extendible and linear hashing solve it by growing incrementally as data grows.
File Organization & Indexing Quiz
12 questions
Transactions & Concurrency Control
9 lessons- 10 min read
Transactions and the ACID properties
What a transaction actually is, transaction states, and a deep dive into Atomicity, Consistency, Isolation, and Durability — the four guarantees the rest of this module exists to deliver.
- 9 min read
Schedules and serializability
How to represent interleaved concurrent transactions as a schedule, and why serial and serializable schedules are the gold standard for correctness.
- 10 min read
Conflict serializability and view serializability
The precise definition of a conflicting operation pair, the precedence graph test for conflict serializability, and how view serializability captures a few extra safe schedules that conflict serializability misses.
- 10 min read
Lock-based concurrency control
Shared and exclusive locks, the two-phase locking protocol and why it guarantees conflict serializability, plus strict 2PL and the anomalies plain locking still allows.
- 9 min read
SQL isolation levels and multiple granularity locking
The four standard SQL isolation levels and the specific anomalies each one allows, plus intention locks for efficiently locking at the table, page, or row level.
- 9 min read
Timestamp ordering and optimistic concurrency control
How transactions can be serialized by assigned timestamps instead of locks, and how optimistic concurrency control bets on conflicts being rare and validates only at the end.
- 9 min read
Multiversion Concurrency Control (MVCC)
How keeping multiple versions of each row lets readers and writers avoid blocking each other entirely — the strategy behind PostgreSQL, MySQL/InnoDB, and most modern production databases.
- 8 min read
Deadlock handling in databases
Wait-for graphs for deadlock detection, prevention schemes using timestamps, and the difference between deadlock and starvation.
- 10 min read
Crash recovery and write-ahead logging
How the write-ahead log delivers atomicity and durability in practice, undo/redo logging, checkpoints, and the full recovery procedure after a crash.
Transactions & Concurrency Control Quiz
19 questions
- 10 min read
Query processing and join algorithms
How a SQL query actually gets executed step by step, and the three classic join algorithms — nested-loop, sort-merge, and hash join — with their worked disk-access cost comparisons.
- 9 min read
Query optimization and execution plans
Heuristic (rule-based) optimization, cost-based optimization using statistics and selectivity, and how to actually read an EXPLAIN plan.
- 9 min read
Distributed databases: fragmentation and replication
Why databases get split across multiple machines, horizontal/vertical fragmentation, replication strategies, and the trade-offs each introduces.
- 8 min read
Two-phase commit and distributed transactions
How atomicity is preserved for a transaction spanning multiple machines, the two-phase commit protocol in detail, and its blocking-failure weakness.
- 9 min read
The CAP theorem and distributed consistency trade-offs
Consistency, Availability, and Partition tolerance — why you can only fully guarantee two, and how this explains the real design choices behind relational and NoSQL systems alike.
Query Processing, Optimization & Distributed Databases Quiz
12 questions
Assessments
9 quizzesFoundations of DBMS Quiz
Foundations of DBMS · 12 questions
The ER Model Quiz
The ER Model · 10 questions
Relational Model, Algebra & Calculus Quiz
Relational Model, Algebra & Calculus · 10 questions
SQL Deep Dive Quiz
SQL Deep Dive · 13 questions
Integrity Constraints & Normal Forms Quiz
Integrity Constraints & Normal Forms · 10 questions
File Organization & Indexing Quiz
File Organization & Indexing · 12 questions
Transactions & Concurrency Control Quiz
Transactions & Concurrency Control · 19 questions
Query Processing, Optimization & Distributed Databases Quiz
Query Processing, Optimization & Distributed DBs · 12 questions
Database Management Systems Final Assessment
Final assessment · 28 questions