DBMS Tutorial: Learn Database Systems from Scratch (2026)
Database management systems power the persistent state of virtually every application — from social media feeds to banking ledgers. After designing schemas for high-traffic platforms and debugging deadlocks in production, I have developed a deep respect for the engineering behind ACID transactions, query optimization, and storage engines. This tutorial covers relational database fundamentals, SQL, indexing, normalization, transaction isolation, and the internal architecture that makes queries fast.
We will work through practical examples: designing a normalized schema for an e-commerce platform, analyzing query plans to eliminate slow full-table scans, and choosing between B+ trees and LSM trees for write-heavy workloads. Each concept is grounded in real database internals rather than abstract theory.
Relational Model and SQL
The relational model organizes data into tables (relations) with rows (tuples) and columns (attributes). Each table has a primary key uniquely identifying rows, and foreign keys establish relationships between tables. SQL (Structured Query Language) provides declarative operations: SELECT for querying, INSERT/UPDATE/DELETE for modification, and DDL statements for schema management. The relational algebra — selection, projection, join, union, set difference — forms the formal foundation for SQL query execution.
-- Schema for an e-commerce order system
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100;Normalization and Denormalization
Normalization eliminates data redundancy and update anomalies by decomposing tables into smaller, well-structured relations. First Normal Form (1NF) requires atomic columns. Second Normal Form (2NF) removes partial dependencies on composite keys. Third Normal Form (3NF) removes transitive dependencies. Boyce-Codd Normal Form (BCNF) is a stricter version where every determinant is a candidate key. In practice, strict normalization may lead to excessive joins, so denormalization — intentionally reintroducing redundancy — is used for read-heavy workloads where join performance is critical.
-- Before: Denormalized (repeating vendor info per order item)
CREATE TABLE order_items (
order_id INT, product_name VARCHAR(100),
vendor_name VARCHAR(100), vendor_phone VARCHAR(20), price DECIMAL(10,2)
);
-- After: 3NF — separate vendors table
CREATE TABLE vendors (
vendor_id INT PRIMARY KEY, name VARCHAR(100), phone VARCHAR(20)
);
CREATE TABLE products (
product_id INT PRIMARY KEY, name VARCHAR(100),
vendor_id INT REFERENCES vendors(vendor_id), price DECIMAL(10,2)
);
CREATE TABLE order_items (
order_id INT, product_id INT, quantity INT,
PRIMARY KEY (order_id, product_id)
);Indexing: B+ Trees and Hash Indexes
Indexes accelerate data access by providing efficient lookup paths without scanning entire tables. B+ trees are the most common index structure in relational databases: leaf nodes store key-pointer pairs linked in a sorted list, and internal nodes guide searches with O(log n) fan-out. The high branching factor (typically hundreds of keys per node) keeps the tree shallow. Hash indexes provide O(1) lookups for equality predicates but do not support range queries. Choosing the right index — composite indexes, covering indexes, partial indexes — is the single most impactful optimization for query performance.
# B+ tree index simulation (conceptual leaf traversal)
class BTreeLeaf:
def __init__(self):
self.keys = []
self.ptrs = []
self.next_leaf = None
def range_scan(self, low, high):
results = []
leaf = self
while leaf:
for k, p in zip(leaf.keys, leaf.ptrs):
if low <= k <= high:
results.append(p)
elif k > high:
return results
leaf = leaf.next_leaf
return resultsTransactions and ACID
A transaction is a logical unit of work that must satisfy ACID properties: Atomicity (all-or-nothing execution), Consistency (valid state transitions), Isolation (concurrent transactions appear serial), and Durability (committed changes survive failures). The Write-Ahead Log (WAL) ensures atomicity and durability by recording changes before applying them to data pages. Isolation levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — trade consistency for concurrency. Multi-Version Concurrency Control (MVCC) allows readers to see a consistent snapshot without blocking writers, which PostgreSQL and MySQL InnoDB implement.
class WAL:
def __init__(self):
self.log = []
def log_update(self, page_id, old_data, new_data, tx_id):
entry = {"LSN": len(self.log), "tx": tx_id,
"page": page_id, "old": old_data, "new": new_data}
self.log.append(entry)
return entry["LSN"]
def checkpoint(self, dirty_pages):
print(f"Checkpoint: flushing {len(dirty_pages)} pages")
def recover(self):
print(f"Recovery: scanning {len(self.log)} log entries")Query Optimization and Execution Plans
The query optimizer transforms SQL into an efficient execution plan. It enumerates join orders, chooses access methods (index scan vs. sequential scan), and applies transformations like predicate pushdown and join elimination. The cost model estimates cardinality and I/O costs using table statistics (row counts, data distribution histograms). A well-optimized query can be thousands of times faster than a naive one. The EXPLAIN command reveals the plan: look for sequential scans (Seq Scan) on large tables — they signal missing indexes.
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.order_id)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.created_at > '2025-01-01'
GROUP BY c.customer_id
HAVING COUNT(o.order_id) > 5;
-- Look for: Index Scan vs Seq Scan, Hash Join vs Nested LoopStorage Engines: B-Tree vs LSM-Tree
The storage engine manages how data is physically stored and accessed on disk. InnoDB (MySQL) uses a B+ tree clustered index where data rows live in the leaf nodes. Writes modify pages in place after WAL logging, which can cause random I/O. LSM-tree engines (LevelDB, RocksDB, Cassandra) batch writes into an in-memory memtable and flush sorted immutable SSTables to disk. Compaction merges SSTables in the background. LSM-trees excel at write-heavy workloads because all writes are sequential, but reads may need to probe multiple levels.
# LSM-tree write path (simplified)
class LSMTree:
def __init__(self):
self.memtable = {}
self.sstables = []
def put(self, key, value):
self.memtable[key] = value
if len(self.memtable) >= 10000:
self.flush()
def flush(self):
sorted_pairs = sorted(self.memtable.items())
self.sstables.append(sorted_pairs)
self.memtable = {}
def get(self, key):
if key in self.memtable:
return self.memtable[key]
for level in reversed(self.sstables):
pass
return NoneFrequently Asked Questions
What is the difference between a clustered and non-clustered index?
A clustered index determines the physical order of rows on disk — the table data is stored at the index's leaf nodes. A non-clustered index stores pointers (row IDs or clustered key values) at the leaves, requiring a lookup to fetch actual data.
How do you handle database deadlocks?
Deadlocks occur when two transactions each hold a lock the other needs. The DBMS detects cycles using a wait-for graph and aborts one transaction (victim). Prevention strategies include locking tables in a fixed order, using short transactions, and lowering isolation levels.
What is the CAP theorem and how does it apply to databases?
CAP states that a distributed data store can guarantee at most two of Consistency, Availability, and Partition Tolerance. Traditional RDBMS choose consistency and availability (CA), while NoSQL systems like Cassandra choose availability and partition tolerance (AP), accepting eventual consistency.
When should I use NoSQL over a relational database?
Use NoSQL for flexible schemas, horizontal scaling, high-velocity writes (time-series), or storing hierarchical/denormalized data. Use relational databases when you need ACID transactions, complex joins, and strong consistency guarantees.
Originally published on Ayodhyyya. Last updated June 1, 2026.