Database Management Systems (DBMS)

Understand what databases are, why we need dedicated management systems instead of flat files, and the core responsibilities a DBMS handles — data integrity, concurrent access, crash recovery, and security.

Survey the major data models — relational, document, key-value, graph, and columnar — and understand when each is appropriate and what trade-offs they make.

Learn how data is organized into relations (tables), tuples (rows), and attributes (columns), and understand concepts like domains, keys, and relational integrity constraints.

Design databases visually using ER diagrams — entities, attributes, relationships, cardinality, and participation constraints — before translating them into tables.

Write CREATE, INSERT, UPDATE, DELETE, and SELECT statements. Understand the difference between data definition and data manipulation, and how schemas are declared.

Combine data from multiple tables using INNER, LEFT, RIGHT, FULL joins, correlated and non-correlated subqueries, and UNION/INTERSECT/EXCEPT operations.

Summarize data with GROUP BY, HAVING, and aggregate functions (COUNT, SUM, AVG), then go beyond with window functions like ROW_NUMBER, RANK, and running totals.

Create reusable query abstractions with views and common table expressions (CTEs), and understand when to use each for readability, maintainability, and performance.

Understand how one set of attributes uniquely determines another, and how functional dependencies form the theoretical foundation for normalization.

Walk through each normal form — 1NF, 2NF, 3NF, and BCNF — understanding what anomalies each eliminates and how to decompose relations without losing information.

Learn when and why to intentionally violate normal forms for performance, and understand the read-vs-write trade-off in real production systems.

Understand pages, buffer pools, heap files, and how the storage engine bridges the gap between fast in-memory operations and durable on-disk persistence.

Learn how B+ trees organize data for fast lookups, range scans, and ordered access, and why they are the default index structure in most relational databases.

Understand how hash-based indexing provides O(1) point lookups and why it trades away range query support — good for equality-only access patterns.

Follow a SQL query through parsing, planning, and execution. Understand cost-based optimization, join algorithms (nested loop, hash, merge), and how to read EXPLAIN plans.

Understand the four guarantees every transaction system provides — Atomicity, Consistency, Isolation, Durability — and what breaks when any one of them is violated.

Learn how databases allow multiple transactions to run simultaneously using locks (shared, exclusive), two-phase locking, and deadlock detection/prevention.

Understand Read Uncommitted, Read Committed, Repeatable Read, and Serializable isolation levels, and the anomalies each permits — dirty reads, non-repeatable reads, and phantoms.

Learn how modern databases like PostgreSQL avoid locking readers by maintaining multiple versions of each row, enabling high-concurrency read-write workloads.

Understand how WAL ensures durability by writing changes to a log before applying them, and how the database recovers to a consistent state after a crash.

Understand server-side programming with stored procedures and automatic event-driven logic with triggers — their use cases, benefits, and the maintenance trade-offs.

Go beyond primary and foreign keys to CHECK constraints, UNIQUE constraints, cascading actions, and deferrable constraints for maintaining complex data invariants.

Learn the formal mathematical operations — select, project, join, union, difference, rename — that underlie SQL and query optimization.

Understand how databases copy data across machines for fault tolerance and read scaling, and the consistency trade-offs each replication topology introduces.

Learn how large datasets are split across multiple nodes using range, hash, or directory-based partitioning, and the challenges of cross-partition queries.

Understand the fundamental impossibility result for distributed systems and the spectrum of consistency models from linearizability to eventual consistency.

Explore non-relational databases like MongoDB, Redis, and Neo4j — their data models, query patterns, and when they outperform relational systems.