Database Systems Notes

Practical notes on database design, SQL, transactions, storage, indexing, distributed systems, performance, security, and database operations.

The opening chapters build a bookstore example from tables and relationships to working SQL and checkout transactions. Later chapters explain storage, concurrency, distribution, and operations. Use the glossary when a term is unfamiliar and the review questions to check what you can explain.

Contents

• Getting started
• Notes
• References
• Contributing
• License

Getting started

If you are new to databases, follow this route:

1. Introduction: read a customer table, distinguish a database from its software, and try a first query.
2. Data models and design: decide which facts belong together and which rules they must obey.
3. SQL introduction: create the shared SQLite bookstore. Work through data changes, transactions, joins, aggregates, and window functions. Start each note from a fresh setup unless it explicitly continues an exercise.
4. ACID: understand why checkout needs more than individually valid statements.
5. Storage and concurrency: learn how reads, writes, and competing operations work underneath SQL.
6. Distributed databases and replication: add machines only after understanding the new routing, timing, and failure questions.

Then explore performance, security, engine choices, analytics, and ORMs according to your needs. Use the contents as a reference map; you do not need to read every engine or cloud-service chapter before writing useful SQL.

The introductory exercises use SQLite. Procedure and permission exercises explicitly switch to PostgreSQL; later notes use several named engines. Run code in its stated engine, because syntax and guarantees differ. A code fragment illustrating a concept is not always a complete setup script. Expected failures are marked in the text.

Use the glossary as a reference rather than a prerequisite. For each exercise, predict the result, run it, and explain why those rows appear before moving on.

SQL playgrounds

• SQLite Online — Run SQLite queries in the browser.
• SQL Practice — Practice queries with immediate feedback.
• SQL Forever — Use a lightweight online SQL interpreter.
• DB Fiddle — Test queries across several database engines.

Sample databases

• PostgreSQL sample databases — Community-maintained datasets for PostgreSQL.
• MySQL sample databases — Official example datasets and supplementary downloads.
• Sakila — A small video-rental database for learning SQL and schema design.

Notes

Introduction to Databases

• Databases Introduction — Overview of database fundamentals and core concepts.
• Types of Databases — Exploring relational, NoSQL, and other database types.
• Database Management Systems — Understanding DBMS functions and types.
• Data Models — Methods of structuring and representing data.
• Glossary — Key terms and definitions.

Database Design

• Requirements Analysis — Determining user needs for database development.
• Normalization — Minimizing redundancy through proper organization.
• Denormalization — Optimizing performance through strategic redundancy.
• Indexing Strategies — Optimizing query performance with indexes.
• Data Integrity — Ensuring accuracy and consistency of data.

SQL

• Introduction to SQL — Build the SQLite bookstore and learn queries from expected results.

• DDL - Data Definition — CREATE, ALTER, DROP commands.

• DML - Data Manipulation — SELECT, INSERT, UPDATE, DELETE.

• DCL - Data Control — GRANT and REVOKE permissions.

• TCL - Transaction Control — COMMIT, ROLLBACK, SAVEPOINT.

• Joins, Subqueries & Views — Combining data from multiple tables.

• Stored Procedures — Reusable SQL code blocks.

• Triggers — Automated database actions.

• Hierarchical Data — Managing tree-like structures.

• Aggregate Functions — Grouping, totals, and null handling.

• Window Functions — Ranking and calculations without collapsing rows.

ACID Properties and Transactions

• What is a Transaction — Overview of database transactions.
• Atomicity — All-or-nothing transaction property.
• Consistency — Maintaining database integrity.
• Isolation — Independent transaction execution.
• Durability — Permanent transaction results.

Database Storage and Indexing

• Storage on Disk — How logical tables, indexes, pages, files, caches, and recovery logs reach storage.
• Row vs Column Storage — Compare row-oriented transactional layouts with column-oriented analytical storage.
• Primary vs Secondary Keys — Separate logical identification rules from physical access structures.
• Database Pages — Understand pages, buffer caches, row layout, free space, and I/O.
• Indexing Overview — B-tree, composite, covering, partial, expression, specialized, and maintenance concepts.
• B-Tree Internals & Page Splits — Tree levels, leaf pages, key width, insert patterns, fill factor, and page splits.
• Composite Indexes & Column Order — Leading prefixes, equality/range ordering, sorting, joins, and workload-driven column order.
• Covering, Partial & Expression Indexes — INCLUDE/index-only access, filtered subsets, function indexes, and combined designs.
• Clustered Indexes, Heaps & Row Locality — Compare PostgreSQL heaps, InnoDB clustering, SQL Server clustering, and SQLite WITHOUT ROWID.
• Specialized Index Structures — Hash, bitmap, inverted/GIN, GiST, SP-GiST, BRIN, spatial, and data-skipping structures.
• Index Maintenance, Bloat & Online Operations — Index lifecycle, redundancy, concurrent builds/rebuilds, monitoring, and safe removal.

Distributed Databases

• Distributed DB Introduction — Basic concepts and architectures overview.
• Partitioning — Methods of dividing and distributing data.
• Sharding — Breaking tables into distributed chunks.
• Partitioning vs Sharding — Understanding the differences.
• Consistent Hashing — Distributing data with minimal rehashing.
• CAP Theorem — Consistency, availability, partition tolerance.
• Eventual Consistency — Convergence model for distributed systems.

Concurrency Control and Locking

• Shared vs Exclusive Locks — Different locking mechanisms.
• Deadlocks — Understanding and resolving deadlock situations.
• Two-Phase Locking — Lock acquisition and release protocol.
• Double Booking Problem — Concurrent resource booking challenges.
• Isolation Levels — Serializable vs Repeatable Read.

Database Performance and Optimization

• Query Optimization — Diagnose and reduce unnecessary query work with measured plans.
• Indexing Strategies — Choose index access methods while accounting for write and maintenance cost.
• Database Caching — Reuse application results with explicit freshness and invalidation.
• Materialized Views — Precompute expensive reusable results with explicit refresh and staleness rules.
• Database Access in Code — Efficient drivers, transactions, parameters, streaming, timeouts, and retries.
• Working with a Billion-Row Table — Workload-driven strategies for very large tables.
• Execution Plans, Statistics & Cardinality — Read plans, diagnose estimate errors, skew, joins, sorts, and access paths.
• Connection Pooling, Batching & N+1 — Reduce setup and round-trip overhead while bounding concurrency.
• Pagination & Large Result Sets — Offset vs keyset pagination, projection, streaming, and stable cursors.
• Partitioning, Sharding & Data Locality — Pruning, shard keys, hotspots, cross-shard work, and locality.
• Benchmarking, Load Testing & Capacity — Build representative workloads, find saturation, and plan headroom.
• Write Performance, Vacuum & Bloat — Understand index write cost, MVCC cleanup, WAL, maintenance, and backfills.

Database Replication

• Replication Introduction — Overview of database replication concepts.
• Master-Standby — Primary-replica replication model.
• Multi-Master — Multiple active nodes replication.
• Sync vs Async — Replication timing strategies.

NoSQL Databases

• NoSQL Introduction — Choose models from access patterns rather than the NoSQL label.
• NoSQL Types — Key-value, document, wide-column, and graph model overview.
• Querying NoSQL — Compare MongoDB, Redis, Cassandra, and Neo4j query shapes with runnable examples.
• CRUD: SQL vs NoSQL — Compare relational CRUD with MongoDB document operations.
• Document Modeling — Embedding vs references, bounded aggregates, schema evolution, indexes, and concurrency.
• Key-Value Modeling — Key design, TTLs, caching, atomic commands, idempotency, and hotspots.
• Wide-Column Modeling — Query-first tables, partition keys, clustering, bucketing, denormalization, and tombstones.
• Graph Modeling — Nodes, relationships, path traversal, supernodes, graph projections, and Cypher.
• Consistency, Transactions & Replication — Atomicity boundaries, replication, quorums, conflicts, retries, and multi-region trade-offs.
• Operating NoSQL & Polyglot Persistence — Source-of-truth design, CDC/projections, backups, capacity, observability, and operational ownership.

Database Security and Best Practices

• Backup & Recovery — Backup types, RPO/RTO, point-in-time recovery, restore validation, and recovery planning.
• Database Security — Defense-in-depth overview across identity, networks, data, monitoring, and operations.
• Capacity Planning — Workload growth, resource forecasting, headroom, scaling, and cost.
• Database Migration — Moving schemas/data safely with validation, synchronization, rollback, and decommissioning.
• Performance Monitoring & Tuning — SLOs, query plans, waits, connections, indexes, storage, replication lag, and measured tuning.
• SQL Injection — Parameter binding, safe dynamic SQL, ORM/raw-query pitfalls, testing, and least-privilege containment.
• Crash Recovery — WAL/redo, checkpoints, recovery, and durability after process or host failure.
• Identity, Authentication & Access Control — Roles, least privilege, workload identities, RLS, service accounts, and privilege reviews.
• Encryption, Secrets & Key Management — TLS, encryption at rest/in the application, password hashing, KMS, rotation, and secret injection.
• Auditing, Compliance & Data Governance — Audit layers, data classification, retention, masking, log integrity, and governance evidence.
• Database Hardening & Patch Management — Network/host/container hardening, supported versions, patching, baselines, and resource limits.
• Incident Response & DR Drills — Containment, evidence, failover versus restore, RPO/RTO, restore tests, tabletops, and game days.

Database Engines

• SQLite — Lightweight, serverless SQL database.
• MySQL — Popular open-source RDBMS.
• PostgreSQL — Advanced open-source database system.
• MongoDB — Document-oriented NoSQL database.
• Neo4j — Property-graph database and Cypher query model.
• AWS Database Services — Choosing managed database services on AWS.
• Choosing a Database — Selection criteria, workload patterns, scaling, availability, and cost.
• GCP Database Services — Choosing managed database services on Google Cloud.
• Microsoft SQL Server — SQL Server setup, T-SQL, indexing, concurrency, columnstore, HA, and Microsoft ecosystem use.
• Redis — In-memory data structures, caching, TTLs, persistence, replication, and clustering.
• Apache Cassandra — Wide-column modeling, partition keys, tunable consistency, replication, and LSM storage.
• OpenSearch and Elasticsearch — Full-text search, mappings, analyzers, shards, replicas, and search-engine architecture.
• ClickHouse — Columnar OLAP, MergeTree design, sorting keys, partitions, and real-time analytics.
• Distributed SQL — Consensus, distributed transactions, ranges, multi-region placement, and CockroachDB examples.
• Azure Database Services — Choosing Azure SQL, PostgreSQL, MySQL, Cosmos DB, Managed Redis, and related services.

Big Data and Data Warehousing

• Data Warehousing — Architectures for large-scale analytics.
• Hadoop & HDFS — Distributed file system for big data.
• Spark SQL — Large-scale data processing with SQL.
• Data Lakes and Lakehouses — Object storage, Parquet, table formats, catalogs, snapshots, and lakehouse trade-offs.
• Streaming, Kafka, and CDC — Event logs, partitions, consumer groups, delivery semantics, CDC, and the outbox pattern.
• Data Pipelines, Orchestration, and Data Quality — DAGs, retries, backfills, dbt, lineage, quality tests, and production pipeline design.

Object-Relational Mapping (ORM)

• ORM Introduction — Bridging OOP and relational databases.
• Popular ORM Tools — Hibernate, Entity Framework, SQLAlchemy, Django ORM, Prisma, and related approaches.
• Schema Migrations — Alembic-style migrations, expand-and-contract deployments, backfills, and production safety.
• Relationship Loading and Query Performance — N+1 queries, eager loading, projections, pagination, generated SQL, and execution plans.
• Transactions, Concurrency, and Unit of Work — Flush/commit semantics, optimistic and pessimistic locking, retries, and concurrency-safe writes.

Exercises and demonstrations

• Review quiz — Questions and worked SQL exercises, also available as interactive flashcards.
• Quizzes — Multiple-choice self-checks: SQL Queries, Database Design, Indexing and Performance, Transactions and Concurrency, Distributed Databases, Replication and NoSQL. Take them interactively.
• Runnable database demonstrations — Engine-specific examples and setup instructions.
• SQL Server indexing walkthrough — A practical indexing project.

References

Books

• Database System Concepts, 7th Edition by Abraham Silberschatz, Henry Korth, and S. Sudarshan — A broad introduction to database theory and implementation.
• Database Systems: The Complete Book by Hector Garcia-Molina, Jeffrey Ullman, and Jennifer Widom — A detailed treatment of database systems and internals.
• SQL and Relational Theory by C. J. Date — A rigorous discussion of relational principles and SQL.
• Seven Databases in Seven Weeks by Luc Perkins, Eric Redmond, and Jim R. Wilson — A practical survey of different database models.
• NoSQL Distilled by Pramod Sadalage and Martin Fowler — A concise introduction to polyglot persistence and NoSQL systems.

Courses

• Cornell CS4320: Introduction to Database Systems
• CMU 15-445/645: Database Systems

Practice and visual guides

• PostgreSQL Exercises — Practical SQL exercises.
• SQLZoo — Interactive SQL tutorials.
• Understanding Joins — A visual guide to SQL joins.
• DataLemur SQL interview questions — Query practice for technical interviews.

Further reading

• How Databases Work — An introduction to database internals.
• SQL Tutorial in Russian — A comprehensive Russian-language SQL tutorial.
• Discussion: why NoSQL databases can scale differently

Contributing

Corrections, examples, and new topics are welcome.

1. Fork the repository.
2. Create a focused branch, for example git checkout -b improve/indexing-notes.
3. Make and review your changes.
4. Commit with a clear message.
5. Push the branch and open a pull request.

Please keep contributions accurate, concise, and consistent with the surrounding notes. Include practical examples and references where they improve clarity.

License

This project is available under the MIT License.