Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

Type a name or a two-letter code. Use the up and down arrow keys to move through the countries, Enter to choose one and Escape to close.

Computer science fundamentals

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 to read 16 modules

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

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.

  1. 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.

    1. What a DBMS does and why files are not enough Beginner Free lesson, Pro answers: 35 minutes
    2. Inside a database engine: the life of a query Beginner Free core, Pro depth: 7 of 35 minutes free
  2. 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.

    1. Relations, tuples, domains and Codd's rules Beginner Free: 25 minutes
    2. Keys: super, candidate, primary, alternate, foreign Beginner Free lesson, Pro answers: 25 minutes
    3. Integrity constraints and foreign keys in practice Beginner Free lesson, Pro answers: 30 minutes
  3. 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.

    1. ER model: entities, attributes and key attributes Beginner Free lesson, Pro answers: 25 minutes
    2. ER relationships: cardinality and participation Beginner Free lesson, Pro answers: 25 minutes
    3. Weak entities, specialisation and aggregation Intermediate Free lesson, Pro answers: 25 minutes
    4. Mapping ER diagrams to relational tables Intermediate Free lesson, Pro answers: 30 minutes
    5. ER case study: a train reservation system Intermediate Free lesson, Pro answers: 35 minutes
  4. 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.

    1. Relational algebra: the six core operators Beginner Free lesson, Pro answers: 25 minutes
    2. Joins in relational algebra and join sizes Intermediate Free lesson, Pro answers: 30 minutes
    3. Division, grouping and extended relational algebra Intermediate Free lesson, Pro answers: 30 minutes
    4. Tuple and domain relational calculus Intermediate Free lesson, Pro answers: 35 minutes
  5. 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.

    1. How SQL evaluates a query: logical order Beginner Free: 25 minutes
    2. Three-valued logic: how SQL reasons about NULL Intermediate Free lesson, Pro answers: 30 minutes
    3. GROUP BY, HAVING and aggregate semantics Intermediate Free: 30 minutes
    4. Subqueries: IN, EXISTS, ANY and ALL Intermediate Free lesson, Pro answers: 30 minutes
    5. Views, updatable views and materialized views Intermediate Free lesson, Pro answers: 30 minutes
    6. Recursive queries: WITH RECURSIVE and Datalog Intermediate Free lesson, Pro answers: 30 minutes
    7. Triggers, stored procedures and functions Intermediate Free lesson, Pro answers: 30 minutes
    8. Access control, row-level security, SQL injection Intermediate Free lesson, Pro answers: 30 minutes
  6. Module 6

    Functional dependencies

    Redundancy and update anomalies, functional dependencies and Armstrong's axioms, attribute closure and candidate keys, and canonical covers.

    1. Redundancy and update, insert, delete anomalies Beginner Free lesson, Pro answers: 20 minutes
    2. Functional dependencies and Armstrong's axioms Intermediate Free core, Pro depth: 8 of 20 minutes free
    3. Attribute closure and finding candidate keys Intermediate Free core, Pro depth: 6 of 15 minutes free
    4. Canonical cover and equivalence of FD sets Intermediate Free core, Pro depth: 6 of 15 minutes free
  7. 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.

    1. First, second and third normal forms Intermediate Free lesson, Pro answers: 20 minutes
    2. Boyce-Codd normal form and the 3NF trade-off Intermediate Free core, Pro depth: 8 of 20 minutes free
    3. Lossless joins and dependency preservation Intermediate Free core, Pro depth: 7 of 20 minutes free
    4. 3NF synthesis and BCNF decomposition algorithms Advanced Free core, Pro depth: 8 of 20 minutes free
    5. Multivalued and join dependencies: 4NF and 5NF Advanced Free core, Pro depth: 8 of 20 minutes free
    6. Practical schema design and denormalisation Intermediate Free core, Pro depth: 8 of 20 minutes free
  8. 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.

    1. Disks, SSDs, RAID and the I/O cost model Intermediate Free: 25 minutes
    2. Pages, records and the slotted-page layout Intermediate Free core, Pro depth: 10 of 30 minutes free
    3. File organisations: heap, sorted, hashed, clustered Intermediate Free core, Pro depth: 8 of 25 minutes free
    4. Buffer pool management and page replacement Intermediate Free core, Pro depth: 9 of 30 minutes free
    5. Row stores, column stores and compression Advanced Free core, Pro depth: 11 of 30 minutes free
  9. 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.

    1. Index basics: dense, sparse, primary, secondary Intermediate Free lesson, Pro answers: 30 minutes
    2. B-trees and B+ trees: structure and order Intermediate Free core, Pro depth: 8 of 20 minutes free
    3. B+ tree insertion and deletion: step by step Intermediate Free core, Pro depth: 7 of 20 minutes free
    4. B-tree indexes in practice: composite to covering Intermediate Free core, Pro depth: 8 of 20 minutes free
    5. Hashing: static, extendible and linear Intermediate Free core, Pro depth: 7 of 20 minutes free
  10. 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.

    1. From SQL to plan: reading EXPLAIN Intermediate Free lesson, Pro answers: 25 minutes
    2. External merge sort and aggregation Intermediate Free core, Pro depth: 8 of 30 minutes free
    3. Join algorithms: nested loops, sort-merge, hash Intermediate Free core, Pro depth: 11 of 35 minutes free
    4. Execution models: iterators, vectors, compilation Advanced Free core, Pro depth: 10 of 30 minutes free
    5. Logical optimisation: equivalence rules, rewrites Intermediate Free core, Pro depth: 11 of 30 minutes free
    6. Selectivity, statistics and cardinality estimation Advanced Free core, Pro depth: 11 of 30 minutes free
    7. Cost-based optimisation and join ordering Advanced Free core, Pro depth: 12 of 35 minutes free
    8. Query tuning for developers: EXPLAIN-driven fixes Intermediate Free core, Pro depth: 12 of 35 minutes free
  11. Module 11

    Transactions and schedules

    ACID stated precisely, conflict and view serializability, recoverable, cascadeless and strict schedules, and isolation levels with their anomalies.

    1. Transactions and ACID, precisely Beginner Free lesson, Pro answers: 25 minutes
    2. Schedules and conflict serializability Intermediate Free core, Pro depth: 14 of 35 minutes free
    3. View serializability and blind writes Advanced Free core, Pro depth: 9 of 25 minutes free
    4. Recoverable, cascadeless and strict schedules Intermediate Free core, Pro depth: 9 of 25 minutes free
    5. Isolation levels: dirty reads to write skew Intermediate Free core, Pro depth: 11 of 35 minutes free
  12. Module 12

    Concurrency control

    Two-phase locking, deadlocks, multiple-granularity locking and phantoms, timestamp ordering and optimistic control, MVCC, snapshot isolation and SSI.

    1. Locking and two-phase locking (2PL) Intermediate Free lesson, Pro answers: 30 minutes
    2. Deadlocks: detection, prevention and timeouts Intermediate Free core, Pro depth: 9 of 25 minutes free
    3. Multiple-granularity locking and phantoms Advanced Free core, Pro depth: 9 of 25 minutes free
    4. Timestamp ordering and optimistic control Intermediate Free core, Pro depth: 11 of 30 minutes free
    5. MVCC and snapshot isolation inside PostgreSQL Advanced Free core, Pro depth: 12 of 35 minutes free
    6. Serializable snapshot isolation and retries Advanced Free core, Pro depth: 10 of 30 minutes free
  13. 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.

    1. Failures, buffer policies and log-based recovery Intermediate Free lesson, Pro answers: 30 minutes
    2. Write-ahead logging and checkpoints Intermediate Free core, Pro depth: 11 of 35 minutes free
    3. ARIES recovery: analysis, redo and undo Advanced Free core, Pro depth: 12 of 40 minutes free
    4. Backups, point-in-time recovery and standbys Intermediate Free core, Pro depth: 10 of 30 minutes free
  14. 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.

    1. LSM trees and write-optimised storage Advanced Free lesson, Pro answers: 35 minutes
    2. Data warehousing, star schemas and OLAP Intermediate Free core, Pro depth: 12 of 35 minutes free
    3. Data preprocessing: scaling, binning, sampling Intermediate Free core, Pro depth: 11 of 30 minutes free
  15. 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.

    1. Replication: leaders, followers and quorums Intermediate Free lesson, Pro answers: 35 minutes
    2. Partitioning and sharding Intermediate Free core, Pro depth: 12 of 35 minutes free
    3. Distributed transactions: 2PC, consensus, sagas Advanced Free core, Pro depth: 13 of 40 minutes free
    4. CAP, PACELC and consistency models Advanced Free core, Pro depth: 12 of 35 minutes free
  16. Module 16

    GATE and interview readiness

    India-specific

    The GATE databases syllabus mapped to the lessons of this track, and the database interview questions worth preparing, explained properly.

    1. GATE databases: syllabus map and strategy Intermediate Free lesson, Pro answers: 25 minutes
    2. DBMS interview questions explained properly Intermediate Free intro, Pro: 5 of 35 minutes free

Related tools

Official documentation

More in Computer science fundamentals

Quick answers and tool search

Type to search tools or to get a quick answer, for example 18% of 2500. Use the up and down arrow keys to move through the results, Enter to choose, and Escape to close.