Database system concepts

Nearly every app you use sits on a database, and this is the book that trained the people who built them. It matters if you're a CS student facing exams, a developer who writes SQL but never learned the theory, or an engineer preparing for system design interviews. You get vocabulary and mental models—keys, ACID, serializability—that make production behavior predictable instead of mysterious.

1. 1Chapter 1

   Files rot, databases don't: the case for a DBMS

   A database is a collection of interrelated data, and the DBMS is the set of programs that accesses it. The book contrasts this with file-processing systems, which suffer from redundancy, inconsistency, difficulty in accessing data, data isolation, and integrity problems. A DBMS instead provides data independence, efficient data access, data integrity and security, concurrent access, and crash recovery. Data abstraction at physical, logical, and view levels keeps application code stable even when storage changes. Database users are categorized as naive users, application programmers, and sophisticated users, plus database administrators (DBAs) who run the whole show.

   The idea

   A database is a collection of interrelated data, and the DBMS is the set of programs that accesses it. The book's opening argument is that file-processing systems fail in predictable ways—redundancy, inconsistency, hard-to-write queries, isolation, integrity problems—while a DBMS gives you data independence, efficient access, integrity, security, concurrency, and crash recovery. Data abstraction at physical, logical, and view levels keeps application code stable even when storage changes. Users split into naive users, application programmers, sophisticated users, plus the DBA who runs the whole show.

   Try this: Find one spreadsheet in your life where two copies of the same data disagree, and move it into a single table with one primary key.

2. 2Chapter 2

   Tables all the way down: the relational model

   The whole book pivots on one small table: the instructor relation, with attributes ID, name, dept_name, and salary, where ID is the primary key. Rows are tuples, columns are attributes, row order means nothing, and every value is atomic—no lists stuffed inside a cell. Then the constraint that makes it trustworthy: referential integrity, illustrated by instructor.dept_name referencing department.dept_name, so you cannot assign a teacher to a department that doesn't exist. On top of this plain-table foundation, the book builds relational algebra—select, project, union, set difference, Cartesian product, rename—with join defined from those primitives. Every query you'll ever write is a composition of these six moves.

   The idea

   A relation is just a table: rows are tuples, columns are attributes, tuple order is irrelevant, and attribute values are atomic. The instructor relation—ID, name, dept_name, salary, with ID as primary key—is the running example for the entire book. Keys come in a family: superkey, candidate key, primary key, foreign key, and they enforce integrity, with referential integrity meaning instructor.dept_name must match a real department.dept_name. Relational algebra's six basic operations—select, project, union, set difference, Cartesian product, rename—define everything else, including join.

   Try this: Open any table you own and check: does every column hold one atomic value, and is there a column that uniquely identifies each row?

3. 3Chapters 3-5

   SELECT-FROM-WHERE: SQL from first words to subqueries

   The basic SQL query structure is SELECT-FROM-WHERE: the SELECT clause picks columns, FROM names the relations, WHERE filters tuples with predicates—for example, finding instructor names by department. SQL data definition uses CREATE TABLE to specify attributes, data types (such as char, varchar, int, numeric), and integrity constraints like PRIMARY KEY, FOREIGN KEY, and NOT NULL. Then the language deepens: aggregate functions (avg, min, max, sum, count) with group by and having, null values, set operations (union, intersect, except), nested subqueries (in, some, all, exists), join expressions (natural, inner, outer), views, the with clause, grant and revoke, triggers, and recursive queries with with recursive.

   The idea

   SQL's basic query structure is SELECT-FROM-WHERE: the SELECT clause picks columns, FROM names the relations, WHERE filters tuples with predicates. From there the book layers on aggregates (avg, min, max, sum, count) with group by and having, null values, set operations (union, intersect, except), and nested subqueries using in, some, all, exists. Intermediate SQL adds join variants—natural, inner, left/full outer—views via CREATE VIEW, the with clause for temporary named subqueries, transactions, integrity constraints up to CREATE ASSERTION, and grant authorization. Advanced SQL brings stored functions and procedures, triggers that fire before or after row or statement events, and recursive queries with with recursive.

   Try this: Write one query this week using group by with having—average something per group, then filter the groups.

4. 4Chapter 7

   Paper before code: the E-R model

   The E-R model uses entities, attributes, relationships, entity sets, and relationship sets to design databases before committing to tables, with the university database—instructor, student, course, department—as the running example. The constraints are the point: 'takes' is a many-to-many relationship between students and courses. Weak entity sets, such as 'section' dependent on 'course', are identified by a discriminator (sec_id) and have a primary key (course_id, sec_id, semester, year). Constraints in the E-R model include mapping cardinalities (one-to-one, one-to-many, many-to-one, many-to-many), participation constraints, and keys. Specialization, generalization, and aggregation handle hierarchies and relationships-between-relationships.

   The idea

   The E-R model lets you design with entities, attributes, and relationships before committing to tables, using the university database—instructor, student, course, department—as the running example. Constraints are the point: mapping cardinalities (one-to-one, one-to-many, many-to-many), participation constraints, and keys, with takes as a many-to-many relationship between students and courses. Weak entity sets like section depend on course, carrying a discriminator (sec_id) and a composite primary key (course_id, sec_id, semester, year). Specialization, generalization, and aggregation handle hierarchies and relationships-between-relationships.

   Try this: Sketch your next project's data as entities and relationships first, marking each relationship one-to-one, one-to-many, or many-to-many before writing any DDL.

5. 5Chapter 8

   Split the table until it hurts: normalization

   Functional dependencies (e.g., ID → name, dept_name, salary in the instructor relation) are used to decompose relations and avoid redundancy through normalization. Boyce-Codd Normal Form requires that for every nontrivial functional dependency X → Y, X is a superkey; Third Normal Form relaxes this to allow some redundancy so dependencies remain enforceable. Decomposition algorithms, using the canonical cover, convert non-BCNF schemas into BCNF relations—though BCNF decomposition may not preserve dependencies, while 3NF decomposition does. Design becomes a reasoned choice, not a habit.

   The idea

   Functional dependencies—like ID determining name, dept_name, and salary in the instructor relation—are the formal tool for spotting redundancy and guiding decomposition. BCNF requires that in every nontrivial dependency X → Y, X be a superkey; 3NF deliberately relaxes this, tolerating some redundancy so dependencies remain enforceable within single relations. Decomposition algorithms built on the canonical cover transform non-BCNF schemas, and the trade-off is real: BCNF decomposition may not preserve dependencies, while 3NF decomposition does. Design becomes a reasoned choice, not a habit.

   Try this: Take your widest table and list what determines what; if a non-key column determines another non-key column, you've found a decomposition candidate.

6. 6Chapters 9-10

   Where bytes live: disks and RAID

   Zoom out from logic to physics: the storage hierarchy includes cache, main memory, flash memory, magnetic disk, optical disk, and magnetic tape. Magnetic disks are physical objects with platters, tracks, and sectors, which is why access time is dominated by mechanical movement. Then the question every administrator faces—what happens when a disk dies?—meets RAID: levels 0 through 6, including combinations like RAID 1+0, provide redundancy across multiple disks. Every indexing and buffering decision later in the book is priced against this hierarchy.

   The idea

   The storage hierarchy runs from cache and main memory through flash memory, magnetic disk, optical disk, and magnetic tape, with volatility and cost per byte shaping where data belongs. Magnetic disks are physical objects—platters, tracks, sectors—which is why access time is dominated by mechanical movement. RAID levels 0 through 6, including combinations like RAID 1+0, provide redundancy across multiple disks, turning independent failure rates into a managed risk. Every indexing and buffering decision later in the book is priced against this hierarchy.

   Try this: Check what RAID level your production or backup storage actually uses, and whether it protects against a two-disk failure.

7. 7Chapters 10-11

   B-trees and buckets: finding data fast

   The book builds ordered indices first—primary and secondary, dense and sparse, stacked into multilevel indices—and then its star: the B+-tree, a balanced tree structure where all leaf nodes are at the same depth, supporting efficient range queries and equality searches. Hashing takes the other road: static hashing uses a fixed number of buckets and suffers when data grows, while dynamic schemes like extendable hashing let the hash table grow and shrink dynamically, with linear hashing as an alternative. Choosing between them is choosing between range scans and point lookups.

   The idea

   Ordered indices come in flavors—primary and secondary, dense and sparse, multilevel—and the B+-tree is the workhorse: a balanced tree with all leaf nodes at the same depth, which is exactly why it serves both equality lookups and range queries efficiently. Hashing trades order for speed: static hashing uses a fixed number of buckets, while dynamic schemes like extendable hashing let the hash table grow and shrink as data changes, with linear hashing as an alternative. Choosing between them is choosing between range scans and point lookups.

   Try this: Pick your slowest query and check whether its WHERE columns carry an index; if not, add one and time the difference.

8. 8Chapters 11-13

   How a query actually runs: processing and optimization

   Query processing involves parsing and translation, optimization, and evaluation. Algorithms for selection, sorting, and join operations—nested-loop, block nested-loop, indexed nested-loop, merge join, hash join—are measured by disk I/O cost, and picking wrong can cost orders of magnitude. The optimizer's trick is equivalence rules that transform one relational algebra expression into another, plus cost-based optimization that estimates the cost of different evaluation plans using statistics like histograms and selectivity—predicting each plan's price before running any of them.

   The idea

   Query processing has three stages: parsing and translation, optimization, and evaluation, with selection, sorting, and join algorithms—nested-loop, block nested-loop, indexed nested-loop, merge join, hash join—judged by disk I/O cost. Optimization is the interesting part: equivalence rules let the system rewrite a relational algebra expression into a cheaper shape, and cost-based optimization estimates plan costs from statistics like histograms and selectivity. The same SQL can differ by orders of magnitude in runtime depending on which plan the optimizer picks.

   Try this: Run EXPLAIN (or your database's equivalent) on a slow query this week and find the join method it chose.

9. 9Chapters 13-14

   ACID: why a failed transfer can't lose your money

   The canonical illustration is a bank transfer from account A to account B. A transaction is a unit of program execution that accesses and possibly updates various data items, and it must satisfy the ACID properties: atomicity (all steps happen or none do), consistency (the books stay balanced), isolation (no other transaction sees the half-done state), and durability (once confirmed, the transfer survives any later failure). The bank transfer from account A to account B is the book's illustration, and it's the same guarantee your bank gives you daily.

   The idea

   A transaction is a unit of program execution that accesses and possibly updates various data items, and it must satisfy ACID: atomicity, consistency, isolation, durability. The canonical illustration is a bank transfer from account A to account B—two updates that must succeed or fail together, never leave the books inconsistent, never be visible mid-flight to others, and never evaporate after a crash. Every concurrency protocol and recovery mechanism in the later chapters exists to deliver these four properties at speed.

   Try this: Wrap your next multi-step data change in a single transaction—BEGIN, both updates, COMMIT—and test that a failure rolls back both.

10. 10Chapters 14-15

    Locks, timestamps, and the deadlock dance

    Concurrency control covers serializability (conflict and view), the correctness bar for concurrent execution. Lock-based protocols such as two-phase locking (2PL) with shared and exclusive locks ensure serializability, though holding locks can lead to deadlocks. So the book teaches handling: prevention schemes (wait-die, wound-wait), detection using wait-for graphs (a cycle indicates deadlock), and resolution typically by rolling back one of the transactions. Beyond locks, timestamp-based protocols assign each transaction a unique timestamp, and validation-based (optimistic) protocols and multiversion concurrency control (MVCC) are also covered.

    The idea

    Serializability—conflict and view—is the correctness bar for concurrent execution, and two-phase locking reaches it with shared and exclusive locks, though holding locks in growing and shrinking phases can produce deadlocks. Deadlock handling splits into prevention (wait-die, wound-wait), detection via wait-for graphs where a cycle indicates deadlock, and resolution by rolling back one transaction. Alternatives trade locking for other assumptions: timestamp-based protocols give each transaction a unique timestamp and order work accordingly, while validation-based (optimistic) protocols and multiversion concurrency control keep readers lock-free.

    Try this: Check your database's current lock waits or deadlocks this week—most systems expose them—and identify which two queries collided.

11. 11Chapters 15-16

    The log that survives anything: recovery

    Log-based recovery records every update in a log on stable storage. The rule that makes it trustworthy is write-ahead logging (WAL): log records must be written to stable storage before the corresponding data page. Deferred and immediate database modification define the two strategies, and checkpoints limit how much log must be replayed. The ARIES recovery algorithm uses a log sequence number (LSN) and three phases: analysis (identifies dirty pages and active transactions), redo (reapplies all updates from the log), and undo (rolls back uncommitted transactions using compensation log records). Crash recovery stops being magic and becomes an algorithm you could implement.

    The idea

    Log-based recovery records every update in a log on stable storage, with deferred and immediate database modification as the two strategies, checkpoints limiting how much log must be replayed, and the write-ahead logging rule as the invariant: log records hit stable storage before the corresponding data page. ARIES turns the log into a precise recovery procedure using log sequence numbers and three phases—analysis to find dirty pages and active transactions, redo to reapply all updates, undo to roll back uncommitted transactions with compensation log records. Crash recovery stops being magic and becomes an algorithm you could implement.

    Try this: Verify that your database has full-page logging and recent checkpoints enabled—these two settings decide how fast you recover.

12. 12Chapters 16-18

    One machine is never enough: architectures, parallelism, distribution

    Database-system architectures include centralized and client-server systems, with the distinction between application servers and database servers, and the concept of a three-tier architecture. For raw throughput, parallel database architectures include shared memory, shared disk, and shared nothing, measured by speedup and scaleup, exploiting interquery and intraquery parallelism. When data spans multiple sites, distributed databases use data replication, fragmentation (horizontal and vertical), and partitioning (round-robin, hash, range); the two-phase commit protocol (2PC) ensures atomicity, with a coordinator asking all participants to prepare before committing, so a distributed transaction is atomic everywhere or nowhere.

    The idea

    Database-system architectures range from centralized to client-server, distinguishing application servers from database servers and adding the three-tier model. Parallel databases choose between shared memory, shared disk, and shared nothing designs, measured by speedup and scaleup, exploiting interquery and intraquery parallelism. Distributed databases spread data across sites using replication, horizontal and vertical fragmentation, and partitioning by round-robin, hash, or range—while the two-phase commit protocol, with a coordinator asking all participants to prepare before committing, is what keeps a transaction atomic across machines.

    Try this: Sketch which tier of your current system holds business logic and which holds data—if they're fused, you've found your scaling bottleneck.

Anyone who writes SQL and suspects the database is doing something magical underneath—application developers, data engineers, and CS students who want to know why their query is slow and why their bank transfer never half-happens. Also managers who sit in architecture meetings and nod along while secretly wishing someone would explain indexes, transactions, and normalization in plain words.

FAQ