Chai Visual

Database Systems

Know exactly what your query is doing.

A full course, from an ER diagram on paper down to the B+ tree the database walks, the plan it picks, and the log it writes before it commits. The whole university syllabus, taught the way an engineer actually needs it.

Practise the SQL in a real databaseChaiCode SQL Labs, run queries against live tables, right in the browser.

Start here

InIntroduction5 sections
A

Foundations

Why databases exist, how a DBMS is built, data models, and ER design turned into tables.

  1. 01From Files to a Database8 sections
  2. 02Inside a DBMS8 sections
  3. 03Data Models & Keys8 sections
  4. 04ER Modelling9 sections
  5. 05From ER Diagram to Tables9 sections
B

The Relational Model & SQL

Relational algebra, DDL and DML, every kind of join, subqueries, aggregation and views.

  1. 06Relational Algebra8 sections
  2. 07Creating Tables8 sections
  3. 08Querying & Changing Rows8 sections
  4. 09Joins8 sections
  5. 10Aggregation & Grouping8 sections
  6. 11Subqueries8 sections
  7. 12Views, CTEs & Window Functions8 sections
C

Design & Normalization

Functional dependencies, 1NF through BCNF and beyond, and when to stop normalizing.

  1. 13Functional Dependencies8 sections
  2. 14Normal Forms8 sections
  3. 15When to Stop Normalizing6 sections
  4. 16Schema Design in Practice7 sections
D

Storage & Indexing

Pages and heap files, B+ trees, hash indexes, composite and covering indexes, and why an index gets ignored.

  1. 17How Rows Are Stored7 sections
  2. 18The Buffer Pool6 sections
  3. 19B+ Trees7 sections
  4. 20Other Index Types6 sections
  5. 21Using Indexes Well6 sections
  6. 22Why the Index Is Ignored7 sections
E

Query Processing & Optimization

Physical operators, join algorithms, cost estimation, and reading a real EXPLAIN plan.

  1. 23From SQL to a Plan6 sections
  2. 24Physical Operators6 sections
  3. 25Reading a Query Plan7 sections
  4. 26Application-Level Performance5 sections
F

Transactions & Recovery

ACID, schedules and serializability, locking, MVCC, isolation anomalies, and write-ahead logging.

  1. 27Transactions & ACID6 sections
  2. 28Schedules & Serializability5 sections
  3. 29Locking & Deadlocks5 sections
  4. 30Isolation Levels & MVCC5 sections
  5. 31Logging & Recovery5 sections
G

Modern Databases & Capstone

Replication and sharding, SQL versus NoSQL, and designing a real schema end to end.

  1. 32Replication & Sharding6 sections
  2. 33SQL & NoSQL5 sections
  3. 34Capstone: Designing a Real Schema7 sections

34 chapters published · every concept illustrated.