Database Management Systems (DBMS)
How database systems work: the relational model, SQL semantics, normal forms, indexes, query plans, transactions and recovery, with examples that run.
74 lessons: 4 free, 28 free with Pro answers, 41 with a free core and 1 with a free intro.
Examples run with sql.js 1.14.2.
Who it is for
Computer science students preparing for university exams, entrance exams and interviews, and back-end developers who want to know what their database does.
What you will be able to do
- Model data with ER diagrams and map it to relations with keys and integrity constraints
- Write the same question in relational algebra and in SQL, and predict the result with NULLs and duplicates
- Find candidate keys and normalise a schema to 3NF or BCNF, and say what each decomposition costs
- Explain how pages, buffer pools, B+ trees and hash indexes store and find rows
- Read a query plan and explain the join algorithm and the estimates behind it
- Test schedules for serializability and recoverability, and explain locking and MVCC
- Explain how write-ahead logging and ARIES bring a database back after a crash
- Compare replication, partitioning and NoSQL data models by their trade-offs
Revise and practise
- Practice: every exercise and quiz (73 exercises, 74 quizzes)
Modules and lessons
What the labels mean
- Free
- the whole lesson and every answer in it
- Free lesson, Pro answers
- the whole lesson is free; the model answers to its interview questions open with a pass
- Free core, Pro depth
- the core of the lesson is free; its in-depth parts open with a pass
- Free intro, Pro
- the first part is free; the rest opens with a pass
- Pro
- opens with a Pro, Premium or Ultimate pass
Minutes are reading time: “7 of 35 min free” means 7 minutes of the lesson are free.
What a pass opens in this track
95 in-depth parts and 183 interview answers. The same pass opens every other track and every Pro tool.
- Module 1
Foundations: what a DBMS does and how it works
Why applications use a database system instead of plain files, and what happens inside an engine between receiving a query and returning its rows.
- What a DBMS does and why files are not enough Free lesson, Pro answers: 35 minutes
- Inside a database engine: the life of a query Free core, Pro depth: 7 of 35 minutes free
- Module 2
The relational model, keys and integrity
Relations, tuples and domains, superkeys, candidate, primary and foreign keys, and the integrity constraints that keep data valid.
- Relations, tuples, domains and Codd's rules Free: 25 minutes
- Keys: super, candidate, primary, alternate, foreign Free lesson, Pro answers: 25 minutes
- Integrity constraints and foreign keys in practice Free lesson, Pro answers: 30 minutes
- Module 3
Conceptual design with the ER model
Entities, attributes and relationships, cardinality and participation, weak entities and specialisation, mapping ER diagrams to tables, and a full case study.
- ER model: entities, attributes and key attributes Free lesson, Pro answers: 25 minutes
- ER relationships: cardinality and participation Free lesson, Pro answers: 25 minutes
- Weak entities, specialisation and aggregation Free lesson, Pro answers: 25 minutes
- Mapping ER diagrams to relational tables Free lesson, Pro answers: 30 minutes
- ER case study: a train reservation system Free lesson, Pro answers: 35 minutes
- Module 4
Relational algebra and relational calculus
The core operators of relational algebra, joins and their sizes, division and extended operators, and tuple and domain relational calculus.
- Relational algebra: the six core operators Free lesson, Pro answers: 25 minutes
- Joins in relational algebra and join sizes Free lesson, Pro answers: 30 minutes
- Division, grouping and extended relational algebra Free lesson, Pro answers: 30 minutes
- Tuple and domain relational calculus Free lesson, Pro answers: 35 minutes
- Module 5
SQL semantics: how queries really evaluate
The logical order of a query, NULL and three-valued logic, grouping and aggregates, subqueries, views, recursive queries, triggers, and access control with SQL injection.
- How SQL evaluates a query: logical order Free: 25 minutes
- Three-valued logic: how SQL reasons about NULL Free lesson, Pro answers: 30 minutes
- GROUP BY, HAVING and aggregate semantics Free: 30 minutes
- Subqueries: IN, EXISTS, ANY and ALL Free lesson, Pro answers: 30 minutes
- Views, updatable views and materialized views Free lesson, Pro answers: 30 minutes
- Recursive queries: WITH RECURSIVE and Datalog Free lesson, Pro answers: 30 minutes
- Triggers, stored procedures and functions Free lesson, Pro answers: 30 minutes
- Access control, row-level security, SQL injection Free lesson, Pro answers: 30 minutes
- Module 6
Functional dependencies
Redundancy and update anomalies, functional dependencies and Armstrong's axioms, attribute closure and candidate keys, and canonical covers.
- Redundancy and update, insert, delete anomalies Free lesson, Pro answers: 20 minutes
- Functional dependencies and Armstrong's axioms Free core, Pro depth: 8 of 20 minutes free
- Attribute closure and finding candidate keys Free core, Pro depth: 6 of 15 minutes free
- Canonical cover and equivalence of FD sets Free core, Pro depth: 6 of 15 minutes free
- Module 7
Normal forms and decomposition
1NF to BCNF, lossless joins and dependency preservation, the 3NF synthesis and BCNF decomposition algorithms, 4NF and 5NF, and when to denormalise.
- First, second and third normal forms Free lesson, Pro answers: 20 minutes
- Boyce-Codd normal form and the 3NF trade-off Free core, Pro depth: 8 of 20 minutes free
- Lossless joins and dependency preservation Free core, Pro depth: 7 of 20 minutes free
- 3NF synthesis and BCNF decomposition algorithms Free core, Pro depth: 8 of 20 minutes free
- Multivalued and join dependencies: 4NF and 5NF Free core, Pro depth: 8 of 20 minutes free
- Practical schema design and denormalisation Free core, Pro depth: 8 of 20 minutes free
- Module 8
Storage and file organisation
Disks, SSDs and the I/O cost model, pages and slotted records, heap, sorted and hashed files, buffer pool management, and row and column stores.
- Disks, SSDs, RAID and the I/O cost model Free: 25 minutes
- Pages, records and the slotted-page layout Free core, Pro depth: 10 of 30 minutes free
- File organisations: heap, sorted, hashed, clustered Free core, Pro depth: 8 of 25 minutes free
- Buffer pool management and page replacement Free core, Pro depth: 9 of 30 minutes free
- Row stores, column stores and compression Free core, Pro depth: 11 of 30 minutes free
- Module 9
Indexing
Dense and sparse indexes, B-trees and B+ trees with insertion and deletion, B-tree indexes in practice, extendible and linear hashing, and specialised indexes.
- Index basics: dense, sparse, primary, secondary Free lesson, Pro answers: 30 minutes
- B-trees and B+ trees: structure and order Free core, Pro depth: 8 of 20 minutes free
- B+ tree insertion and deletion: step by step Free core, Pro depth: 7 of 20 minutes free
- B-tree indexes in practice: composite to covering Free core, Pro depth: 8 of 20 minutes free
- Hashing: static, extendible and linear Free core, Pro depth: 7 of 20 minutes free
- Module 10
Query processing and optimisation
From SQL to a plan with EXPLAIN, external sorting, join algorithms, execution models, rewrites, cardinality estimation, join ordering and query tuning.
- From SQL to plan: reading EXPLAIN Free lesson, Pro answers: 25 minutes
- External merge sort and aggregation Free core, Pro depth: 8 of 30 minutes free
- Join algorithms: nested loops, sort-merge, hash Free core, Pro depth: 11 of 35 minutes free
- Execution models: iterators, vectors, compilation Free core, Pro depth: 10 of 30 minutes free
- Logical optimisation: equivalence rules, rewrites Free core, Pro depth: 11 of 30 minutes free
- Selectivity, statistics and cardinality estimation Free core, Pro depth: 11 of 30 minutes free
- Cost-based optimisation and join ordering Free core, Pro depth: 12 of 35 minutes free
- Query tuning for developers: EXPLAIN-driven fixes Free core, Pro depth: 12 of 35 minutes free
- Module 11
Transactions and schedules
ACID stated precisely, conflict and view serializability, recoverable, cascadeless and strict schedules, and isolation levels with their anomalies.
- Transactions and ACID, precisely Free lesson, Pro answers: 25 minutes
- Schedules and conflict serializability Free core, Pro depth: 14 of 35 minutes free
- View serializability and blind writes Free core, Pro depth: 9 of 25 minutes free
- Recoverable, cascadeless and strict schedules Free core, Pro depth: 9 of 25 minutes free
- Isolation levels: dirty reads to write skew Free core, Pro depth: 11 of 35 minutes free
- Module 12
Concurrency control
Two-phase locking, deadlocks, multiple-granularity locking and phantoms, timestamp ordering and optimistic control, MVCC, snapshot isolation and SSI.
- Locking and two-phase locking (2PL) Free lesson, Pro answers: 30 minutes
- Deadlocks: detection, prevention and timeouts Free core, Pro depth: 9 of 25 minutes free
- Multiple-granularity locking and phantoms Free core, Pro depth: 9 of 25 minutes free
- Timestamp ordering and optimistic control Free core, Pro depth: 11 of 30 minutes free
- MVCC and snapshot isolation inside PostgreSQL Free core, Pro depth: 12 of 35 minutes free
- Serializable snapshot isolation and retries Free core, Pro depth: 10 of 30 minutes free
- Module 13
Logging and recovery
Failure types and buffer policies, log-based recovery, write-ahead logging and checkpoints, ARIES, and backups with point-in-time recovery.
- Failures, buffer policies and log-based recovery Free lesson, Pro answers: 30 minutes
- Write-ahead logging and checkpoints Free core, Pro depth: 11 of 35 minutes free
- ARIES recovery: analysis, redo and undo Free core, Pro depth: 12 of 40 minutes free
- Backups, point-in-time recovery and standbys Free core, Pro depth: 10 of 30 minutes free
- Module 14
Storage engines, NoSQL and analytics
LSM trees and write-optimised storage, document, key-value, wide-column and graph models, data warehouses and OLAP, and preparing data for analysis.
- LSM trees and write-optimised storage Free lesson, Pro answers: 35 minutes
- Data warehousing, star schemas and OLAP Free core, Pro depth: 12 of 35 minutes free
- Data preprocessing: scaling, binning, sampling Free core, Pro depth: 11 of 30 minutes free
- Module 15
Distributed and cloud databases
Replication and quorums, partitioning and sharding, distributed transactions and consensus, CAP and PACELC, and cloud-native and vector databases.
- Replication: leaders, followers and quorums Free lesson, Pro answers: 35 minutes
- Partitioning and sharding Free core, Pro depth: 12 of 35 minutes free
- Distributed transactions: 2PC, consensus, sagas Free core, Pro depth: 13 of 40 minutes free
- CAP, PACELC and consistency models Free core, Pro depth: 12 of 35 minutes free
- Module 16
GATE and interview readiness
India-specificThe GATE databases syllabus mapped to the lessons of this track, and the database interview questions worth preparing, explained properly.
- GATE databases: syllabus map and strategy Free lesson, Pro answers: 25 minutes
- DBMS interview questions explained properly Free intro, Pro: 5 of 35 minutes free
Related tools
Official documentation
- PostgreSQL 18 Documentation (The PostgreSQL Global Development Group)
- SQLite Documentation (SQLite)
- MySQL 8.4 Reference Manual (Oracle Corporation)
- GATE 2027 syllabus for Computer Science and Information Technology (CS) (Indian Institute of Technology Madras (GATE 2027))