STUDY GUIDE · 2 INTERACTIVE LABS · ABOUT 12 MIN

Find one row among a billion in about five steps.

Every bank balance, flight booking and social feed lives in a database. A good one answers questions in milliseconds, never loses a committed change, and lets thousands of people use it at once. This guide shows how.

ACID: WHAT A TRANSACTION GUARANTEES
AAtomicityAll of a transaction happens, or none of it.
CConsistencyRules and constraints always hold afterwards.
IIsolationSimultaneous transactions don't see each other's half-finished work.
DDurabilityOnce committed, it survives a crash or power cut.

What is a database system?

A database management system (DBMS) stores data so it can be queried, updated and shared safely. Most use the relational model: data sits in tables of rows and columns, linked by keys, and is queried with SQL.

PostgreSQL, MySQL, Oracle, SQL Server and SQLite are relational. NoSQL systems such as MongoDB, Redis and Cassandra trade some guarantees for flexible schemas or massive scale.

id (PK)namecity_id (FK)
1Amara3
2Ben7
3Chen3
A primary key uniquely identifies each row. A foreign key points to a row in another table, here a cities table.
LAB 1 · SQL QUERY BUILDER

Ask the table a question

SQL is declarative: you describe the result you want, and the database works out how to get it. Click the clauses and watch the query and its answer change.

SELECT
WHERE
GROUP BY
ORDER BY
LIMIT
SELECT name, city, scoreFROM studentsWHERE city = 'Leeds'ORDER BY score DESC;
namecityscore
AmaraLeeds91ChenLeeds88GusLeeds59
3 row(s) returned from 8The database applies FROM, then WHERE, then SELECT and ORDER BY, then LIMIT: not the order you write them in.
LAB 2 · INDEXES

Scan everything, or use an index?

Without an index, finding one customer means checking rows one by one. A B-tree index is like a library catalogue: a short, wide tree where each node points to around a hundred children. Grow the table and compare.

Full table scan500,000 comparisonsChecks rows one by one; on average, half the table.
Binary search on sorted data20 comparisonsHalves the search space each step.
B-tree index (fan-out ≈ 100)3 page readsEach page read narrows the search a hundredfold.
500 msscan time
300 μsindex lookup
At 1 million rows a scan checks around 500,000 rows, while a B-tree needs just 3 page reads. The catch: every index must be updated on each insert, and it takes extra storage, so index only the columns you search by.
Bars are on a logarithmic scale. Times assume about 1 μs per row checked and 0.1 ms per index page read; real figures vary with hardware and caching.

Normalisation: say each fact once

1NFOne value per cellNo lists like 'Maths, Physics' in a single field: split them into separate rows.
2NFDepends on the whole keyIn a table keyed by (student, course), a student's address belongs in a separate students table.
3NFDepends on nothing but the keyIf city decides postcode area, store that in a cities table, not on every student.

Core ideas at a glance

Table / relationA set of rows sharing the same columns.
Primary keyA column (or set) that uniquely identifies each row.
Foreign keyA column referring to another table's primary key.
JOINCombines rows from two tables where keys match.
IndexAn extra structure, often a B-tree, that speeds up lookups at the cost of slower writes.
TransactionA group of operations treated as one ACID unit: COMMIT or ROLLBACK.
NormalisationOrganising tables to remove duplication and update anomalies.
CAP theoremA distributed system can't guarantee consistency, availability and partition tolerance all at once.

From punch cards to the cloud

1960sNavigational databases.IBM's IMS (used for Apollo) stored data as trees you walked through by hand.
1970The relational model.Edgar F. Codd at IBM proposed tables and a mathematical query language.
1974SQL.Chamberlin and Boyce designed SEQUEL at IBM, later renamed SQL.
1979Oracle.The first commercially available SQL database.
1996PostgreSQL.Grew out of Berkeley's Postgres project; now a leading open-source database.
2009NoSQL.MongoDB, Redis and others popularised flexible, horizontally scalable stores.

Further reading

Database System ConceptsSilberschatz, Korth & Sudarshan · the standard textbook
Designing Data-Intensive ApplicationsMartin Kleppmann · modern systems at scale
A Relational Model of Data for Large Shared Data BanksE. F. Codd (1970) · the founding paper
Test yourself: 20 database questionsSQL programming test

Queries are pure logic. How's yours?

Our full IQ test covers logical, numerical, verbal and spatial reasoning: 60 questions, about 30 minutes.

Take the full IQ test