Polimi · 2025/26

Data Base 2

From physical storage, indexing and query optimization to transactions, concurrency control, reliability and active databases — the full Politecnico di Milano Data Base 2 course, rebuilt as an interactive, exam-focused study guide.

11 chapters~7 h reading 42 past-exam questions

Practice past exams 42 questions from 2024–2026 papers — filter by chapter, year and difficulty, with instant grading and worked solutions.

After this course you can

  • Compute the I/O cost of any access structure and of a complete query plan, cached and uncached
  • Name the anomaly behind any read/write pattern and decide VSR, CSR, 2PL and Strict 2PL membership
  • Run Obermarck's deadlock detection and fill hierarchical-locking and timestamp request tables
  • Execute a warm restart - build the UNDO and REDO sets and order every recovery action
  • Execute FA, TA and NRA round by round, and compute skylines and k-skybands with SFS
  • Write SQL:1999 triggers and JPA mappings, justifying owning side, fetch and cascade choices

Syllabus

  1. The Database Box: Architecture & ACID

    What Data Base 2 adds to the SQL you already know — the layered machine inside the DBMS, the transaction as an atomic unit of work, the four ACID properties, and exactly which module is on the hook for each. Plus the shape of the written exam, read off fourteen real papers.

     22 minutes reading.

  2. Triggers & Active Databases

    Event–Condition–Action rules that let the database react on its own. SQL:1999 syntax, transition variables, the BEFORE/AFTER × row/statement grid, execution order, cascading and termination — then the two things exams actually ask for: a correct event inventory and incrementally-maintained derived data.

     medium exam weight. 40 minutes reading. 5 past-exam questions.

  3. Physical Storage & Access Structures

    How tables actually sit on disk, and what each layout costs. The block-counting cost model, sequential and hash structures, the primary/secondary/clustering index taxonomy, B+ trees, composite indexes and the leftmost-prefix rule — the substrate every query-cost exercise is built on.

     medium exam weight. 45 minutes reading. 1 past-exam question.

  4. Query Optimization & Join Methods

    How one SQL statement becomes one execution plan, and how to price the alternatives. Relation profiles and selectivity, the four join methods and their cost formulas, external merge sort, and the discipline of writing a complete cost formula — the single largest points pool after concurrency.

     high exam weight. 48 minutes reading. 7 past-exam questions.

  5. Schedules, Serializability, VSR & CSR

    The five anomalies concurrency can produce, and the two formal notions of correctness that rule them out — view-serializability and conflict- serializability. The conflict graph, the class hierarchy, and the exam instruction that makes it all pay off: use class inclusion wherever possible.

     high exam weight. 42 minutes reading. 7 past-exam questions.

  6. Locking, 2PL, Isolation & Deadlock

    The pessimistic answer to concurrency: locks, the two-phase rule that makes them sufficient, strictness that makes aborts safe, the SQL isolation levels built from them, and what to do when transactions wait for each other forever — including Obermarck's distributed detection and hierarchical intention locks.

     high exam weight. 55 minutes reading. 6 past-exam questions.

  7. Timestamps, Multiversion & Snapshot Isolation

    The optimistic answer to concurrency: order transactions by birth date and kill whoever arrives out of order. RTM/WTM bookkeeping, Thomas rule, multiversion timestamps in the exact variant the exercises use, snapshot isolation and write skew — plus where each class sits against 2PL, CSR and VSR.

     high exam weight. 40 minutes reading. 1 past-exam question.

  8. Reliability: Buffer, Log & Recovery

    How atomicity and durability survive a crash. Stable memory, the buffer policies that decide what recovery must do, the log and its two write rules, checkpoints and dumps, and the warm-restart procedure that rebuilds a consistent database from the log.

     low exam weight. 30 minutes reading.

  9. Ranking Queries & Top-k Algorithms

    Finding the best k objects when "best" is a scoring function over several criteria. Rank aggregation and instance optimality, the geometry of weighted scores, and the four middleware algorithms — B0, FA, TA and NRA — with the stopping rule and access count each one is graded on.

     medium exam weight. 44 minutes reading. 3 past-exam questions.

  10. Skylines & Dominance

    Finding the good objects without inventing weights. Dominance and the skyline, BNL and Sort-Filter-Skyline with the sorting precondition that makes SFS correct, the k-skyband, and the sharp distinction recent exams keep testing — skyline points a weighted sum can never make win.

     medium exam weight. 32 minutes reading. 6 past-exam questions.

  11. ORM & JPA: From JDBC to the Entity Manager

    Mapping objects to tables without writing SQL. The JDBC boilerplate JPA removes, entities and the persistence context, and the four decisions every exam asks you to justify from data cardinalities — owner, mapped-by, fetch policy and cascade — plus the entity lifecycle and container transactions.

     medium exam weight. 42 minutes reading. 6 past-exam questions.