The Database Box: Architecture & ACID

01 · Motivation

What DB2 adds to what you already know

Data Bases 1 taught you to use a database — the relational model, SQL, relational algebra. Data Bases 2 opens the box and asks how the thing actually works, and what it costs.

You already know how to write a query. What you do not yet know is what happens in the seconds after you press enter: which physical structures the data sits in, how the system decides between a hundred equivalent ways to compute your answer, what stops the other four hundred users from corrupting your rows while you read them, and what guarantees survive somebody tripping over the power cable.

That is the whole syllabus, and it splits into two halves that the course keeps returning to:

The prerequisites are exactly what you would guess: the relational model, SQL-92, relational algebra, plus basic object-oriented design in UML and Java for the second half.

02 · Architecture

Opening the database box

One figure organizes this entire course. Every later chapter lives inside one of its boxes, and it is worth being able to redraw it from memory.

A query descends a stack of managers, each one translating the request into the vocabulary of the layer below. Alongside that data path sit three managers whose job is not to move data but to make promises about it.

Read it top to bottom on the left:

• The Query Manager receives select, insert, delete, update. It parses, optimizes, and turns one declarative statement into a program of read/write requests. Chapter 4 is entirely about how it chooses.
• The Access Method Manager receives read and write on records and knows how each table is physically organized — sequentially, hashed, or in a B+ tree. Chapter 3.
• The Buffer Manager receives fix and unfix and owns main memory. It caches disk blocks as pages and decides when a modified page goes back to disk — a decision that turns out to determine what recovery must do. Chapters 3 and 8.
• The Secondary Store Manager talks to the disk in blocks, and is the only layer that actually performs I/O.

And on the right, the three guarantors:

• The Transaction Manager receives begin, commit, abort and delimits units of work.
• The Concurrency Control System holds the lock tables and timestamps that keep simultaneous transactions from interfering. Chapters 5, 6 and 7.
• The Reliability Manager owns the log and runs recovery after a crash. Chapter 8.

03 · Transactions

The transaction as a unit of work

A transaction is an elementary, atomic unit of work performed by an application — conceptually wrapped in begin transaction and end transaction.

Inside those brackets, exactly one of two commands ends the transaction:

• commit-work — end successfully; the effects become permanent.
• rollback-work — end with failure; the effects are undone as though the transaction never ran.

A system that supports the execution of transactions on behalf of many concurrent applications is called a transactional system, or OLTP — On-Line Transaction Processing.

Note the distinction between an application and a transaction: one application may open and close several transactions in sequence. The transaction is the unit of atomicity, not the program.

The textbook example is a bank transfer, and it repays a careful look:

begin transaction;
  update Account set Balance = Balance + 10 where AccNum = 12202;
  update Account set Balance = Balance - 10 where AccNum = 42177;
commit-work;

Two update statements, one transaction. The point is that no observer and no failure may ever see a state in which the first update happened and the second did not. A variant makes the decision conditional — read the balance back and choose:

begin transaction;
  update Account set Balance = Balance + 10 where AccNum = 12202;
  update Account set Balance = Balance - 10 where AccNum = 42177;
  select Balance into A from Account where AccNum = 42177;
  if (A >= 0) then commit-work;
  else rollback-work;

Here the application itself decides the outcome. A rollback may also be forced by the DBMS — for an integrity-constraint violation, or because concurrency control chose this transaction as the victim of a deadlock. Whether an aborted transaction should be retried is the application’s decision, not the system’s.

04 · Properties

ACID, and the module that guarantees each

Four properties define a transaction. Being able to name them is worth little; being able to name the module that enforces each one is the frame for half this course.

Consistency deserves a second reading. It is the only property of the four that is about the data’s own rules rather than about the machinery: the sum of hours booked to tasks should equal the project’s planned hours, an account balance should not go negative. The transaction may break such a rule halfway through — while shifting work from one task to another, the total is briefly wrong — and the guarantee is only that the rule holds again at the end.

The mapping from property to module is the part to memorize:

Two of the four land on the Reliability Manager, which is why chapter 8 covers atomicity and durability together; isolation gets three chapters of its own because that is where the exam lives.

05 · Exam

How the DB2 exam is actually built

The written exam is closed-book, two hours, no oral. A calculator is allowed; books, notes and devices are not. It contains three exercises drawn from five topics — and which five is not a coin flip.

The professors state the five examinable topics up front:

1. Triggers
2. Ranking and skylines
3. Physical DBs and query optimization
4. DB architecture and technologies — concurrency control and related
5. ORM and JPA

Each paper picks three, sometimes with a short theory question attached. What the syllabus does not tell you is the selection frequency. This is where the fourteen past papers on this site — every session from January 2024 to July 2026, with official solutions — earn their keep.

Two more facts about the format, both worth planning around:

• Every exercise is a written, multi-part procedure — classify this schedule, write these triggers, cost these plans, run this algorithm and count the accesses, fill this relationship table. Across 42 exercises there is not a single multiple-choice question. You are graded on the derivation, not the final number.
• Papers list what students on a reduced workload may omit, which doubles as the professors’ own difficulty ranking. Timestamp-multiversion concurrency control is exempted most often — in five sessions — followed by view-serializability and strict 2PL. Those are the parts they consider hardest, and they are still graded for everyone else.

One administrative rule catches people out every year: if you reject a mark and retake the exam, the previous mark is lost — and merely sitting down and reading the exam text counts as retaking it.

06 · Context

Where DB2 sits

A short orientation, so you know what this course deliberately does not cover.

Two neighbouring courses pick up threads DB2 leaves alone. Systems and Methods for Big and Unstructured Data covers NoSQL properly — graph, document, key-value, column stores, and the design methodologies for non-relational models. Streaming Data Analytics covers data streams, time-series and complex event processing. The NoSQL material that appears in the DB2 slide pack is a recap from Data Bases 1, included for context: the 3V framing of big data, the CAP theorem, the four-family taxonomy, NewSQL, and the recent arrival of vector databases for embedding search. It is background, not examinable here.

It is also worth knowing that the relational machinery you are about to study underneath is not young. Codd’s relational model dates to 1970, System R and the first SQL to the mid-1970s, and the cost-based optimizer you will meet in chapter 4 is a direct descendant of that work. The reading in the course pack — Chamberlin’s retrospective on fifty years of query languages — traces the line from punched cards through the RAMAC disk to today.

Finally, the closing lecture makes an argument worth repeating: the operational parts of database work are increasingly automated, and what remains human is architecture, defining the guarantees (ACID and its relaxations), and judging correctness, performance and risk. That is a fair description of what this course actually trains.