Learning Resources
This module is populated from the chunked books in library/raw/semester-06-databases-distributed/books. Use this page as a source map, not as an instruction to read everything.
Source Stack
| Book | Role | How to use it in this module |
|---|---|---|
| Database System Concepts (Silberschatz, Korth, Sudarshan) | Primary teaching source | Default escalation for relational model, relational algebra, SQL semantics, integrity constraints, normalization, views, transactions-at-SQL-level |
| Designing Data-Intensive Applications (Kleppmann) | Modern overview and context | Use for "why relational still wins for OLTP" framing, schema evolution in practice, and as the bridge into later distributed-systems modules |
| Database Internals (Petrov) | Low-level support | Peek under the hood: how B-trees and LSMs affect query plans, how storage layout shows up in EXPLAIN numbers |
| Distributed Systems Concepts and Design (Coulouris) | Peripheral | Reserve for later modules; not a primary source for relational / SQL mastery |
External Resource Gates
Open these only when you are stuck or preparing a production change:
| Tool / Site | Use |
|---|---|
| PostgreSQL docs | Canonical reference for every keyword, every operator, every ALTER TABLE variant, and EXPLAIN output format |
| SQLite docs | Second dialect reference; useful for checking "is this syntax actually standard?" |
| Use The Index, Luke (Markus Winand) | Best single resource on index-aware query writing and plan interpretation |
| PGExercises | Structured SQL practice, difficulty-graded, using PostgreSQL |
| SQLZoo | Lower-friction SQL practice with immediate feedback |
Resource Map by Cluster
Cluster 1: The Relational Model
| Need | Best local chunk | Why |
|---|---|---|
| relation / tuple / attribute formalism | Silberschatz: Structure of relational databases | Cleanest statement of the model |
| schemas and schema diagrams | Silberschatz: Database schema | Introduces the vocabulary the rest of the module uses |
| keys | Silberschatz: Keys | Superkey/candidate/primary, with worked examples |
| schema diagrams | Silberschatz: Schema diagrams | Shows FK arrows you will draw for your own designs |
| relational algebra, part 1 | Silberschatz: The relational algebra, part 1 | Selection, projection |
| relational algebra, parts 2-4 | Silberschatz: The relational algebra, parts 2-4 | Joins, set ops, rename |
Cluster 2: SQL as a Query Language
| Need | Best local chunk | Why |
|---|---|---|
| DDL | Silberschatz: SQL data definition | CREATE TABLE reference |
| query basic structure | Silberschatz: Basic structure of SQL queries, part 1 | SELECT FROM WHERE |
| query basic structure part 2 | Silberschatz: Basic structure of SQL queries, part 2 | Aliases, duplicates |
| additional operations | Silberschatz: Additional basic operations | DISTINCT, arithmetic, string ops |
| set operations | Silberschatz: Set operations | UNION / INTERSECT / EXCEPT |
| NULL semantics | Silberschatz: Null values | Three-valued logic |
| aggregation and GROUP BY | Silberschatz: Aggregate functions | GROUP BY / HAVING mechanics |
| nested subqueries | Silberschatz: Nested subqueries | Scalar, IN/EXISTS, derived tables |
| joins | Silberschatz: Join expressions | INNER / OUTER / NATURAL |
| modification DML | Silberschatz: Modification of the database | INSERT / UPDATE / DELETE |
Cluster 3: Schema Design and Normalization
| Need | Best local chunk | Why |
|---|---|---|
| features of good designs | Silberschatz: Features of good relational designs | Motivates the whole chapter |
| decomposition | Silberschatz: Decomposition using FDs, part 1 | Defines lossless decomposition |
| normal forms | Silberschatz: Normal forms, part 1 | 2NF, 3NF, BCNF |
| FD theory | Silberschatz: Functional dependency theory, part 1 | Armstrong's axioms, closure |
| decomposition algorithms | Silberschatz: Algorithms for decomposition | Mechanical synthesis |
| ER diagrams to tables | Silberschatz: Reducing ER diagrams to relational schemas | Translation rules |
| extended ER features | Silberschatz: Extended ER features, part 1 | Inheritance, specialization |
Cluster 4: Constraints, Integrity, and DDL
| Need | Best local chunk | Why |
|---|---|---|
| integrity constraints | Silberschatz: Integrity constraints, part 1 | Definitions of all constraint types |
| integrity constraints | Silberschatz: Integrity constraints, parts 2-3 | Referential integrity, cascade actions |
| data types | Silberschatz: SQL data types and schemas | Domains, user-defined types |
| views | Silberschatz: Views, part 1 | Definition and updatability |
| materialized views | Silberschatz: Materialized views | Trade-offs and refresh |
| functions and procedures | Silberschatz: Functions and procedures | PL/pgSQL-shaped examples |
| triggers | Silberschatz: Triggers | Enforcement and auditing |
| schema evolution in practice | DDIA: The merits of schemas | Evolution, backward/forward compat |
Cluster 5: SQL Performance Basics
| Need | Best local chunk | Why |
|---|---|---|
| query processing overview | Silberschatz: Query processing overview (15.1) | What a plan is, and how the optimizer fits |
| measures of query cost | Silberschatz: Measures of query cost, part 1 | What "cost" means in EXPLAIN |
| selection implementations | Silberschatz: Selection operation, part 1 | Scan vs seek at the operator level |
| join implementations | Silberschatz: Join operation, parts 1-6 | Nested loop, hash, merge joins |
| relational expression transformation | Silberschatz: Transformation of relational expressions | How the optimizer rewrites |
| statistics estimation | Silberschatz: Estimating statistics of expression results | Why estimates go wrong |
| plan choice | Silberschatz: Choice of evaluation plans | Cost-based plan selection |
| index definition | Silberschatz: Index definition in SQL | CREATE INDEX mechanics |
| transactions (concept) | Silberschatz: Transaction concept (17.1) | ACID, atomicity, durability |
| isolation levels | Silberschatz: Transaction isolation levels | Standard isolation levels |
| transactions at SQL level | Silberschatz: Transactions as SQL statements | BEGIN / COMMIT / ROLLBACK |
| transactions chapter | DDIA: Chapter 7 - Transactions | Modern operational framing |
How To Escalate
- Start with the concept page in the module.
- If the concept is still not clicking, open the matching Silberschatz chunk from the table above.
- If you need a second perspective or "why does this matter in a real system," open the DDIA chunk.
- If you need the lowest-level explanation of why a plan looks the way it does, open the Database Internals chunk (Petrov).
- If you need authoritative syntax or behavior, open the PostgreSQL docs linked on each concept page.
Required executable relational contracts
Complete Executable relational contracts, prediction practice and all SQL1–SQL6 gates. The 22–32-hour package supplies the implementation core for guided Labs 1, 2 and 4; reuse evidence rather than repeat them. Keep domain modelling, PostgreSQL plan measurements, migrations and multi-session practice.
For the executable lab, consult Python sqlite3 transaction control when explaining explicit transaction ownership. The harness uses legacy transaction control with isolation_level=None; changing the connection’s autocommit mode changes that setup contract. Read only the relevant transaction and parameter-binding sections.