Loading learning path...
Study how relational data becomes storage pages, tree indexes, execution plans and recoverable transactions. Practice SQL semantics, B+ tree repairs, query reasoning and transactional workflows.
The curriculum and complete lessons are free with an account. Optional practice resources and Ono usage follow your plan’s allowances.
Start with the semantics a query must preserve, then study storage, indexes, execution and concurrency. The repository labs exercise specific mechanisms; production performance still needs measurements on representative data.
How to study: Work in order. Predict the result before opening a visualizer, implement before reading the editorial, and explain the invariant and edge cases after each exercise. Repository and review activities are paired: fix the behavior, then assess a proposed change independently. Choose a supported language or repository environment; you do not need to repeat every environment. Existing completion of shared content counts toward this path.
Check your understanding: • Explain why a query can be fast and still wrong because of multiplicity or null semantics. • State the B+ tree invariant preserved by each repair. • Compare plan, lock and recovery costs before recommending a database change.
The Relational Model
Relations and Tables: The Foundation of Relational Databases
Keys in the Relational Model
Integrity Constraints in the Relational Model
Relational Algebra Overview
Selection and Projection
Set Operations in Relational Algebra
Join Operations in Relational Algebra
NULL Handling in SQL
Customers Without Purchase Activity
Department Enrollment Totals
SQL join equality never matches null to null
relational proposal 1
Filtering preserves SQL three-valued negation, and projection preserves duplicate rows unless DISTINCT is explicitly requested
relational proposal 2
COUNT(column) skips nulls, COUNT(*) counts every row, and SUM over empty or all-null groups is null
relational proposal 3
Functional Dependency Concept
Functional Dependencies and Keys
Understanding Database Normalization
First Normal Form (1NF): Foundation of Normalization
Second Normal Form: Eliminating Partial Dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Lossless Join Decomposition
Understanding Denormalization
OLTP vs OLAP: Understanding Transaction and Analytical Processing
Storage Hierarchy in Database Systems
Disk Structure: Understanding Physical Storage
File Organization Methods
Page Layout and Organization
Buffer Management in Database Systems
Access Methods: How Databases Retrieve Data
Storage Virtualization
Understanding Database Indexes
Dense vs Sparse Indexes
Clustered vs Non-Clustered Indexes
Multi-Level Index Structures
B-Tree Concept
B-Tree Operations: Search, Insert, and Delete
B+-Tree Structure
B+-Tree Operations
B+-Tree Performance Analysis
Select the latest observation at the inclusive revision cutoff and remove keys whose latest observation is a tombstone
bplus proposal 1
Bulk-load balanced leaf groups so the final page is not underfilled
bplus proposal 2
A separator equals the right child minimum
bplus proposal 3
Range queries include both endpoints and follow every forward leaf link without losing later pages
bplus proposal 4
Observation identity is key plus revision; reject conflicting redelivery while preserving legitimate revisions, and sort integer keys numerically
bplus proposal 5
Hash Index Concept
Static Hashing: Fundamentals and Implementation
Dynamic Hashing
Extendible Hashing
Hash vs Tree Indexes: Comparative Analysis
Covering Indexes: Optimizing Query Performance Through Index Design
Multi-Column Indexes
Full-Text Indexing and Search
Index Selection: The Art and Science of Strategic Indexing
Latest Three Orders Per Customer
Query Processing Steps
Parsing and Validation
Query Representation
Query Execution Engine
Cost Models in Query Processing
Selection Implementation
Nested Loop Join Algorithms
Sort-Merge Join: Algorithm, Implementation, and Optimization
Hash Join Algorithms
Other Physical Operators
Optimization Overview
Equivalence Rules
Heuristic Optimization
Cost-Based Query Optimization
Join Ordering in Query Optimization
Statistics and Histograms
Understanding EXPLAIN Plans
Index Usage for Query Optimization
Common SQL Performance Issues
Query Profiling
Department High-Earner Bands
Employee Rolling Payroll Summary
Frequency-Weighted Median Estimator
Transaction Definition
ACID Properties: The Foundation of Reliable Transactions
Conflict Serializability
Recoverability
Understanding Concurrency in Database Systems
Understanding the Lost Update Problem
The Dirty Read Problem: Reading Uncommitted Data
Unrepeatable Read Problem
The Phantom Read Problem
Isolation Levels
Two-Phase Locking Protocol
Deadlock Handling in Database Systems
Multi-Version Concurrency Control (MVCC)
Snapshot Isolation: Consistent Views for Concurrent Transactions
Pending credit holds allow the account to overcommit
Subtract pending credit commitments
An old custody screen overwrites a newer sample revision
Transfer only the current sample revision
Understanding Database Failure Types
Log-Based Recovery
Write-Ahead Logging (WAL)
Checkpoints in Database Recovery
ARIES Recovery Algorithm Overview
ARIES Analysis Phase: Reconstructing System State
ARIES Redo Phase: Reconstructing Consistency
Undo Phase in ARIES Recovery
Point-in-Time Recovery
Backup Verification
An unsuccessful credit capture changes account balances
Capture reserved credit only once
Scope credit accounts and retry receipts to the tenant
Separate credit account keys while leaving retry receipts shared
Failed release journals leave a partially published room
Isolate the editorial transaction draft
Connection Pooling: Managing Database Connections at Scale
Monitoring and Alerting
Capacity Planning
Performance Tuning
Database Security
Leader-Follower Replication
Sharding: Horizontal Scaling for Distributed Databases
Distributed Transactions
CAP Theorem: Fundamental Trade-offs in Distributed Systems
Design a Key-Value Store
Design a Distributed Database (Cassandra / DynamoDB)
Complete this path to earn a personalised certificate. You choose whether to make it public.