Database Management Systems

Relational databases, transactions, indexing, and query processing, aimed at technical interview prep and equivalent coursework.

All 31 topics

Every skill the diagnostic can check, grouped the way the map is. Open any topic to start a lesson on it.

Relational model: Data as tables of rows and columns, with no other structure implied.
Entity-relationship modeling: Sketching entities and their relationships as a diagram before turning them into tables.
Functional dependencies: One column's value fully determines another's, e.g. knowing the ID tells you the name.
Normalization: Splitting tables so each fact lives in exactly one place, to avoid update anomalies.

SQL filtering and projection: Picking which rows and columns you want back with SELECT and WHERE.
SQL joins: Combining rows from two or more tables based on a matching column.
SQL aggregation and grouping: Collapsing many rows into one summary number per group, like a total or average per category.
SQL subqueries: A query nested inside another query, used as a stand-in value or filter.
Relational algebra: The formal, math-like operations SQL actually compiles down to under the hood.
Views and materialized views: A saved query that acts like a table; materialized means the result is actually stored, not recomputed each time.
Stored procedures and triggers: Code that lives inside the database itself: called on demand (procedure) or fired automatically on an event (trigger).

Storage and file organization: How rows actually get laid out into pages and files on disk.
Indexes and B-trees: A sorted, tree-shaped shortcut structure that lets the database find rows without scanning the whole table.
Index selectivity: How narrowly a condition filters the table, the reason a low-selectivity index gets ignored in favor of a full scan.
Query processing and optimization: How the database decides which order to run joins and filters in, to answer a query as cheaply as possible.

Transactions and ACID properties: The four guarantees (atomicity, consistency, isolation, durability) that make a transaction feel like one indivisible unit.
Concurrency anomalies: The specific bad things that can happen when two transactions run at the same time: dirty, non-repeatable, and phantom reads.
Isolation levels: How much of those concurrency anomalies a database is willing to allow, chosen as a tradeoff against speed.
Conflict-serializability: Checking whether an interleaved schedule of transactions could have happened in some one-at-a-time order instead.
Lock-based concurrency control: Transactions acquiring then releasing locks on data, in two strict phases, to enforce serializability in practice.
Deadlock detection and prevention: Two transactions each waiting on a lock the other holds, and how the database detects or prevents that standoff.

Write-ahead logging and recovery: Writing what you're about to change to a log before touching the actual page, so a crash can still recover the right state.
ARIES recovery algorithm: The specific three-phase algorithm (analysis, redo, undo) real databases use to recover using the write-ahead log.

Distributed databases: A database whose data lives across multiple machines instead of one.
CAP theorem: You can't have perfect consistency, availability, and partition tolerance all at once, pick two.
Consistency models: How strictly a distributed database insists every read sees the latest write, versus allowing some staleness.
Replication and sharding: Copying data across machines for redundancy versus splitting it across machines for scale.
NoSQL data models: Alternatives to tables: documents, key-value pairs, wide columns, or graphs, each suited to a different access pattern.

Data warehousing and OLAP: A separate database optimized for big analytical queries over historical data, not fast day-to-day transactions.

Database security and access control: Who's allowed to see or change what, enforced through roles, privileges, and protection against attacks like SQL injection.
Database administration and performance tuning: The ongoing work of keeping a database backed up, monitored, and fast as real usage grows.