Database Theory
Transaction & ACID
Fundamental database guarantees: Atomicity (all-or-nothing), Consistency (constraints), Isolation (concurrent), Durability (persistence).
- Atomicity for all-or-nothing
- Consistency maintains constraints
- Isolation for concurrent transactions
- Durability persists committed changes
- Bank fund transfers
- Order processing systems
- Inventory updates
- Financial transaction systems
Trade-offs between consistency and performance: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
- Dirty read prevention
- Non-repeatable read handling
- Phantom read protection
- Lock-based vs MVCC approaches
- Concurrent data updates
- Reporting queries
- Batch processing operations
- Critical financial transactions
Distributed transaction protocol with coordinator managing prepare and commit phases across multiple databases.
- Atomic distributed operations
- Prepare and commit phases
- Blocking protocol behavior
- Recovery mechanisms
- Microservice transactions
- Multi-database updates
- Distributed system consistency
- Cross-system operations
Conflict handling strategies: optimistic assumes low conflict with version checking, pessimistic uses locks to prevent conflicts.
- Timestamp or version-based checking
- Lock duration management
- Deadlock prevention strategies
- Rollback handling
- Collaborative document editing
- Inventory management
- Booking systems
- Counter increments
Indexing Strategies
Balanced tree structure for efficient range queries, most common index type, optimized for disk access patterns.
- Maintains sorted order
- Efficient range scans
- Covering index optimization
- Multi-column indexing
- Primary key indexing
- WHERE clause optimization
- ORDER BY performance
- Foreign key relationships
O(1) equality lookup using hash tables, fast for exact matches but no support for range queries or sorting.
- Constant time equality checks
- No ordering support
- Collision handling
- Memory efficiency
- Primary key exact lookups
- Exact match queries
- JOIN operation optimization
- In-memory caching
Inverted index for text search with stemming, relevance scoring, and phrase matching capabilities.
- Token-based indexing
- Relevance ranking algorithms
- Phrase and proximity search
- Language-specific support
- Search functionality
- Document content search
- Log analysis
- Content filtering
Efficient for low-cardinality columns using bitwise operations, excellent compression and fast AND/OR/NOT operations.
- Fast bitwise AND/OR/NOT
- Excellent compression
- Data warehouse optimization
- Low-cardinality columns
- Gender or status columns
- Boolean flag fields
- Categorical data
- Analytics queries
Query Optimization
Database optimizer decisions for query execution including cost estimation, join order, and index selection.
- EXPLAIN output analysis
- Scan type identification
- Join algorithm selection
- Statistics-based optimization
- Slow query diagnosis
- Index recommendation
- Query performance tuning
- Bottleneck identification
Strategies for improving query performance including index usage, query rewriting, and materialized views.
- Covering index usage
- Index hint specification
- Materialized view creation
- Statistics updates
- Complex query optimization
- Aggregation performance
- JOIN optimization
- Subquery rewriting
Database design process (1NF, 2NF, 3NF, BCNF) reducing data redundancy and ensuring data integrity through decomposition.
- Eliminate data duplicates
- Functional dependency management
- Update anomaly prevention
- Referential integrity
- Schema design
- Data modeling
- Migration planning
- Database refactoring
Performance optimization adding redundancy for read-heavy workloads through calculated fields and aggregate tables.
- Duplicate data strategically
- Aggregate table creation
- Materialized view usage
- Cache table patterns
- Reporting databases
- Analytics platforms
- Dashboard queries
- High-traffic read operations