Overview

A Database Management System (DBMS) is software that stores, organises, and retrieves data efficiently and reliably. Almost every application - from a simple to-do list to a global e-commerce platform - relies on a database. Understanding how databases work helps you design better schemas, write faster queries, and build systems that stay reliable under load.

The Relational Model

The relational model is the foundation of most databases used in production today.

• Relations (Tables) - data is organised in rows (tuples) and columns (attributes)
• Keys:
  • Primary key - uniquely identifies each row in a table
  • Foreign key - references the primary key of another table; enforces referential integrity
  • Candidate key - any column or set of columns that could serve as the primary key
  • Composite key - a primary key made of more than one column
  • Surrogate key - a system-generated key (often an auto-incrementing integer or UUID) with no business meaning
• Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY
• Entity-Relationship (ER) Diagrams - a visual way to model data and relationships before building a schema
• Cardinality: one-to-one, one-to-many, many-to-many relationships

SQL

Structured Query Language (SQL) is the standard language for querying and manipulating relational databases.

Data Definition Language (DDL)

CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    email    VARCHAR(255) UNIQUE NOT NULL,
    created  TIMESTAMP DEFAULT NOW()
);

ALTER TABLE users ADD COLUMN age INT;
DROP TABLE users;

Data Manipulation Language (DML)

-- Insert
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Read
SELECT name, email FROM users WHERE age > 18 ORDER BY name ASC LIMIT 10;

-- Update
UPDATE users SET age = 25 WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

Joins

• INNER JOIN - only rows where both sides match
• LEFT JOIN - all rows from the left table, matched rows from the right (NULL if no match)
• RIGHT JOIN - all rows from the right table, matched rows from the left
• FULL OUTER JOIN - all rows from both sides
• CROSS JOIN - every combination of rows from both tables (Cartesian product)
• SELF JOIN - joining a table to itself

Advanced SQL

• Aggregations: COUNT, SUM, AVG, MIN, MAX with GROUP BY and HAVING
• Subqueries - queries nested inside other queries
• Common Table Expressions (CTEs) - named subqueries using WITH for readability
• Window Functions - ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, AVG OVER
• Views - virtual tables defined by a query; useful for simplifying complex queries
• Stored Procedures and Functions - reusable SQL logic stored in the database
• Triggers - automatically run SQL in response to INSERT, UPDATE, or DELETE events

Normalisation

Normalisation is the process of structuring a database to reduce data redundancy and improve integrity.

• Anomalies - the problems that arise from poorly designed schemas:
  • Insert anomaly - cannot add data without adding other unrelated data
  • Update anomaly - updating one row may leave other rows inconsistent
  • Delete anomaly - deleting a row may unintentionally delete other data
• Normal Forms:
  • 1NF (First Normal Form) - every column has atomic (indivisible) values; no repeating groups
  • 2NF (Second Normal Form) - in 1NF and every non-key attribute depends on the whole primary key (no partial dependency)
  • 3NF (Third Normal Form) - in 2NF and no non-key attribute depends on another non-key attribute (no transitive dependency)
  • BCNF (Boyce-Codd Normal Form) - a stricter version of 3NF; every determinant is a candidate key
  • 4NF - eliminates multi-valued dependencies
• Denormalisation - intentionally introducing redundancy for read performance in data warehouses or reporting systems

Transactions and ACID

A transaction is a sequence of operations treated as a single unit of work.

• Why transactions - without them, partial updates can leave data in an inconsistent state
• ACID Properties:
  • Atomicity - all operations in a transaction succeed, or none of them do
  • Consistency - a transaction brings the database from one valid state to another
  • Isolation - concurrent transactions do not interfere with each other
  • Durability - once committed, the changes survive crashes and power failures

Transaction Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

• Dirty Read - reading uncommitted data from another transaction
• Non-Repeatable Read - reading the same row twice and getting different values
• Phantom Read - a query returns different rows when run twice because another transaction inserted or deleted rows

Concurrency Control

Multiple transactions running at the same time can cause conflicts. Concurrency control mechanisms prevent this.

• Locking:
  • Shared (S) lock - multiple readers can hold it simultaneously
  • Exclusive (X) lock - only one writer; no readers allowed
  • Two-Phase Locking (2PL) - transactions acquire all locks before releasing any
  • Deadlock in databases - two transactions waiting for each other’s locks
• Optimistic Concurrency Control - proceed without locks and check for conflicts at commit time
• MVCC (Multi-Version Concurrency Control) - keep multiple versions of rows so readers do not block writers; used by PostgreSQL and MySQL InnoDB
• Timestamp Ordering - assign timestamps and resolve conflicts based on transaction order

Indexing

Indexes speed up queries by allowing the database to find rows without scanning the entire table.

• How indexes work - a separate data structure (usually a B-Tree) that stores pointers to rows sorted by the indexed column
• B-Tree Index - the default index type; efficient for equality and range queries
• Hash Index - fast for exact equality lookups; not useful for range queries
• Composite Index - an index on multiple columns; column order matters
• Covering Index - an index that contains all the columns a query needs (no table lookup required)
• Full-Text Index - for searching text content with natural language queries
• When NOT to index - indexes slow down writes and take up space; avoid over-indexing
• Query planner / EXPLAIN - understanding how the database engine chooses to execute a query
• Index selectivity - how unique the values are; highly selective indexes are more useful

Query Optimisation

Database engines automatically optimise queries, but understanding the process helps you write better SQL.

• Query execution plan - the sequence of steps the database will use to execute a query
• EXPLAIN and EXPLAIN ANALYZE - commands to inspect the query plan and actual execution
• Cost-based optimisation - the engine estimates the cost of different plans and picks the cheapest
• Statistics - the engine uses column statistics (row counts, value distributions) to estimate costs
• Common performance problems:
  • N+1 queries - fetching data in a loop instead of a single join
  • Missing indexes on join columns or WHERE conditions
  • SELECT * - fetching more columns than needed
  • Functions on indexed columns - WHERE UPPER(email) = '...' cannot use an index on email
• Query rewriting - restructuring a query to help the optimiser (using CTEs, avoiding correlated subqueries)

NoSQL Databases

NoSQL databases sacrifice some relational features in exchange for horizontal scalability and flexible schemas.

• Document Stores - store data as JSON or BSON documents; schema-flexible; e.g., MongoDB, CouchDB
• Key-Value Stores - simple key-to-value lookup; extremely fast; e.g., Redis, DynamoDB
• Column-Family Stores - store data in columns rather than rows; optimised for write-heavy analytics; e.g., Apache Cassandra, HBase
• Graph Databases - model data as nodes and edges; ideal for social networks, recommendation engines; e.g., Neo4j
• Time-Series Databases - optimised for data points indexed by time; e.g., InfluxDB, TimescaleDB
• Search Engines - full-text and faceted search; e.g., Elasticsearch, Solr
• SQL vs NoSQL trade-offs - when to use each:
  • Use SQL for structured data, complex queries, and strong consistency requirements
  • Use NoSQL for unstructured data, horizontal scale, and flexible schemas

Distributed Databases

When a single machine is not enough, databases must be distributed across multiple nodes.

• Replication - copying data to multiple nodes for availability and read performance
  • Primary-Replica (Master-Slave) - writes go to the primary; reads can go to replicas
  • Multi-Primary - multiple nodes accept writes; conflict resolution becomes complex
  • Synchronous vs asynchronous replication - consistency vs latency trade-off
• Sharding (Horizontal Partitioning) - splitting data across multiple machines by a shard key
  • Range sharding, hash sharding, directory-based sharding
  • Hotspots - when too many requests hit the same shard
• CAP Theorem - a distributed system can guarantee at most two of three:
  • Consistency - every read sees the most recent write
  • Availability - every request gets a response
  • Partition Tolerance - the system keeps working when network splits occur

graph TD CAP["CAP Theorem"] C["Consistency All nodes see the same data at the same time"] A["Availability Every request gets a response"] P["Partition Tolerance Works despite network failures"] CAP --> C CAP --> A CAP --> P C --- CA["CA Systems PostgreSQL MySQL (no partition tolerance)"] C --- CP["CP Systems MongoDB HBase ZooKeeper"] A --- AP["AP Systems Cassandra DynamoDB CouchDB"]

• PACELC Theorem - extends CAP by also considering the latency vs consistency trade-off when the system is running normally (no partition)
• Consensus Algorithms - ensuring all nodes agree on a value even with failures: Paxos, Raft
• Two-Phase Commit (2PC) - a protocol for coordinating commits across multiple nodes in a distributed transaction
• Eventual Consistency - replicas may be temporarily out of sync but will converge; acceptable for many applications

Storage Engines

The storage engine is the component that actually reads and writes data to disk.

• B-Tree storage engines - the classic approach; used by MySQL InnoDB, PostgreSQL
• LSM-Tree (Log-Structured Merge-Tree) storage engines - write-optimised; used by Cassandra, RocksDB, LevelDB
  • Writes go to an in-memory memtable, then are flushed to disk as sorted SSTables
  • Compaction merges SSTables periodically to reclaim space and improve read performance
• OLTP vs OLAP:
  • OLTP (Online Transaction Processing) - many short, fast transactions; row-oriented storage
  • OLAP (Online Analytical Processing) - few complex queries scanning large amounts of data; column-oriented storage
• Column-oriented storage - storing each column’s values together on disk; much faster for analytical aggregations

Understanding databases deeply - from the SQL layer all the way down to how data is stored on disk - gives you the tools to design systems that are fast, reliable, and easy to evolve over time.