AlgoPlusAlgoPlus
← All modules

Databases

From the ER model and SQL to normalization, transactions, and concurrency. · 17 topics

Foundations
DBMS Architecture→
The 3-schema architecture and data independence.
ER Model→
Entities, relationships, cardinality, and ER → tables.
Keys→
Super, candidate, primary, and foreign keys.
Generalization & Aggregation→
Abstraction in the extended ER model.
Relational Model & SQL
Relational Algebra→
σ, π, ⋈, ∪, − — the operators behind SQL.
CRUD & SQL Queries→
INSERT, SELECT, UPDATE, DELETE on a live table.
SQL Joins→
Inner, left, right, full, and cross joins.
Integrity Constraints→
Entity and referential integrity.
Query Plan→
Operators execute bottom-up.
Database Design & Normalization
Functional Dependencies→
FDs, attribute closure, canonical cover.
Normalization→
1NF → 2NF → 3NF → BCNF decomposition.
Indexing & Storage
B-Tree Index→
Splits and rebalances as rows insert.
Transactions & Recovery
Transactions / MVCC→
Concurrent reads and writes, isolation.
Schedules & Serializability→
Conflict serializability via the precedence graph.
Recovery & Logging→
Undo/redo logs, checkpoints, deadlock handling.
Concurrency Control
Two-Phase Locking→
Growing and shrinking lock phases (2PL).
Timestamp Ordering→
Timestamp-based concurrency control.