Databases & SQL Engineering
From relational algebra & ACID guarantees to query engines, indexing, and distributed data systems.
Master the data backbone of modern software. Learn relational algebra, SQL DDL/DML, normalization, B-Tree storage engines, query planners, transactions, NoSQL topologies, and distributed consensus.
Course Overview
Welcome to Databases & SQL Engineering on codeworking.org. Data is the lifeblood of every modern application. Without reliable, performant, and consistent storage systems, software collapses under scale.
What You Will Master
- Relational Algebra & Normalization: The mathematical proofs (Codd’s relational model) and 1NF/2NF/3NF/BCNF schema design.
- SQL Mastery: Writing elegant ANSI SQL queries, aggregations, multi-table joins, subqueries, and analytical Window functions.
- Database Engine Internals: How storage engines organize 8KB disk pages, manage buffer pools, and utilize B+ Trees for instant indexing.
- ACID & Concurrency: Mastering transaction isolation levels, Multi-Version Concurrency Control (MVCC), and locking protocols.
- Distributed Scaling & NoSQL: Document stores, caching layers, columnar OLAP engines, sharding, replication, and CAP theorem trade-offs.
Prerequisites
- Basic familiarity with general programming logic. No prior database or SQL experience necessary.
Structured Learning Roadmap
Relational Foundations, Relational Algebra & SQL Basics
What is a DBMS, flat files vs databases, Codd's relational model, relational algebra operators, normalization, and core SQL DDL/DML.
Relational Database Foundations, DBMS Architecture & Relational Algebra
Discover why flat files fail at scale, the ACID transaction guarantees, Edgar Codd's Relational Model, and the formal mathematical operators of Relational Algebra that power SQL engines.
Database Normalization, Functional Dependencies & Relational Schema Design
Master relational schema design, the 3 destructive data anomalies, Functional Dependencies, and 1NF, 2NF, 3NF, and BCNF normalization rules.
Core SQL: DDL, DML, Data Types & Declarative Relational Constraints
Master Data Definition Language (DDL), Data Manipulation Language (DML), precise data types, and declarative constraints (CHECK, UNIQUE, FOREIGN KEY CASCADE, UPSERT).
Querying & Filtering: SELECT, WHERE, ORDER BY & Logical Predicates
PlannedThree-valued logic (TRUE, FALSE, UNKNOWN), NULL handling, LIKE, BETWEEN, IN, and execution order.
Intermediate SQL: Aggregations, Joins & Subqueries
Grouping sets, INNER/OUTER/CROSS joins, hash vs nested loop join algorithms, CTEs, and Window functions.
Aggregations & Grouping: COUNT, SUM, GROUP BY & HAVING Mechanics
PlannedAggregation semantics, filtering grouped rows with HAVING vs WHERE, and distinct counts.
Relational Joins: INNER, LEFT, RIGHT, FULL, CROSS & Join Algorithms
PlannedVenn diagrams vs tuple products, Nested Loop Join, Hash Join, and Sort-Merge Join.
Subqueries, Common Table Expressions (CTEs) & Recursive SQL
PlannedCorrelated subqueries, WITH clauses, temporary materialization, and hierarchical graph traversal.
Advanced Analytical SQL: Window Functions & Partitioning
PlannedOVER(PARTITION BY ... ORDER BY), ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, and rolling moving averages.
Database Internals, Storage Engines & Indexing
Pages, buffer pools, WAL, B-Trees, LSM trees, covering indexes, and query planner cost models.
Under the Hood: How a Database Executes a Query
PlannedParser, Analyzer, Query Optimizer, Cost Planner, and Execution Engine mechanics.
Disk Storage Architecture: Pages, Slotted Pages & Buffer Pools
Planned8KB/16KB page layouts, row offsets, dirty pages, LRU eviction, and Write-Ahead Logging (WAL).
Index Engineering: B-Trees, B+ Trees & Hash Indexes
PlannedB+ Tree fanout, logarithmic leaf lookups, clustered vs non-clustered indexes, and composite indexing.
Query Optimization, EXPLAIN ANALYZE & Cost Models
PlannedInterpreting EXPLAIN trees, Seq Scan vs Index Scan, cardinality estimation, and statistics.
Transactions, Concurrency Control & ACID Guarantees
Atomicity, Consistency, Isolation, Durability, Dirty Reads, 2PL, MVCC, and Deadlocks.
ACID Guarantees & Transaction Lifecycle
PlannedBEGIN, COMMIT, ROLLBACK, write-ahead logging (WAL), crash recovery, and ARIES algorithm.
Isolation Levels & Concurrency Anomalies
PlannedRead Uncommitted, Read Committed, Repeatable Read, Serializable, Dirty Reads, and Phantom Reads.
Locking Mechanisms, Two-Phase Locking (2PL) & Deadlocks
PlannedShared (S) vs Exclusive (X) locks, Intent locks, table/row locks, and deadlock detection graphs.
Multi-Version Concurrency Control (MVCC) in PostgreSQL & MySQL
PlannedNon-blocking reads, transaction snapshots, xmin/xmax tuple metadata, and VACUUM garbage collection.
NoSQL Architectures & Polyglot Persistence
Document, Key-Value, Columnar, and Graph databases, Redis, Cassandra, MongoDB, and Neo4j.
The NoSQL Paradigm: Polyglot Persistence & Tradeoffs
PlannedWhy NoSQL emerged, impedance mismatch, vertical vs horizontal scaling, and BASE semantics.
Document Stores: MongoDB, JSON/BSON & Flexible Schemas
PlannedBSON binary format, embedded documents vs references, indexing JSON attributes, and aggregations.
Key-Value Stores & In-Memory Caching: Redis & Memcached
PlannedIn-memory RAM speeds, Redis data structures (Strings, Hashes, Sets, Sorted Sets), and persistence.
Wide-Column & Analytical Databases: Cassandra & ClickHouse
PlannedColumnar storage layouts, LSM trees, SIMD vectorized analytics, and high-throughput aggregations.
Distributed Data Systems, Scaling & High Availability
Replication, Sharding, CAP theorem, PACELC, Two-Phase Commit (2PC), and Raft consensus.
Database Replication: Master-Replica, Multi-Master & Consensus
PlannedSynchronous vs asynchronous replication, replication lag, read replicas, and failover.
Horizontal Partitioning & Sharding Strategies
PlannedRange sharding, Hash sharding, Directory-based sharding, rebalancing, and cross-shard queries.
The CAP Theorem, PACELC & Consistency Models
PlannedConsistency vs Availability vs Partition tolerance, Strong vs Eventual consistency, and Read-Your-Writes.
Distributed Transactions: 2PC, Saga Pattern & Distributed SQL
PlannedTwo-Phase Commit protocol, Saga orchestrations, Google Spanner, TrueTime, and CockroachDB.