Database Systems

Table of contents

Transaction Management

• ACID (Atomicity, Consistency, Isolation, Durability)
• BASE (Basically Available, Soft state, Eventually consistent) - NoSQL alternative
• Transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable)
• Concurrency control
• Two-phase commit (2PC)

Concurrency Control

• Locking mechanisms (pessimistic locking)
• Optimistic concurrency control
• MVCC (Multi-Version Concurrency Control)
• Deadlock detection and prevention
• Lock granularity (row-level, page-level, table-level)

Storage & Indexing

• B-Tree, B+Tree indexes
• Hash indexes
• LSM trees (Log-Structured Merge-trees)
• Clustered vs non-clustered indexes
• Index selectivity and cardinality

Query Processing & Optimization

• Query execution plans
• Cost-based optimization
• Join algorithms (nested loop, hash join, merge join)
• Query parsing and compilation
• Statistics and histograms

Data Integrity & Constraints

• Referential integrity
• Entity integrity
• Domain constraints
• Normalization (1NF, 2NF, 3NF, BCNF)
• Denormalization trade-offs

Recovery & Durability

• Write-Ahead Logging (WAL)
• Checkpointing
• Crash recovery
• Point-in-time recovery (PITR)
• Redo/Undo logging

Distributed Database Concepts

• CAP Theorem (Consistency, Availability, Partition tolerance)
• Replication (master-slave, multi-master)
• Sharding/Partitioning
• Distributed transactions
• Consensus algorithms (Paxos, Raft)