Computing and Networks
Databases
The complete student field guide
AI generated by Nikhil Kumar Rajput
In the reading list Networks and systems (book 6 of 7)
9 chapters
Contents¶
Front matter
Chapters
Chapter 1: Database Foundations
- 1.1 Data, Information and Why We Store It
- 1.2 What a Database and a DBMS Are
- 1.3 Why Not Just Files or Spreadsheets?
- 1.4 A Short History of Databases
- 1.5 The Main Data Models at a Glance
- 1.6 Tables, Rows, Columns, Keys and Schemas
- 1.7 Transactions and ACID in Plain English
- 1.8 OLTP vs OLAP: Running the Business vs Understanding It
- 1.9 Database Architectures: Embedded to Cloud-Native
- 1.10 The 2026 Database Landscape
- 1.11 How an Application Talks to a Database
- 1.12 Follow a 'Buy' Click Through the Database
- 1.13 Who Works With the Database?
Chapter 2: The Relational Model & SQL
- 2.1 The Relational Model: Relations, Tuples, Attributes and Domains
- 2.2 Relational Algebra and Set Operations
- 2.3 SQL's Sublanguages: DDL, DML, DCL and TCL
- 2.4 SELECT in Depth
- 2.5 Joins: Inner, Outer, Cross, Self, Semi and Anti
- 2.6 Aggregation: GROUP BY and HAVING
- 2.7 Subqueries, CTEs and Recursive Queries
- 2.8 Window Functions: Ranking, LAG/LEAD and Running Totals
- 2.9 NULL and Three-Valued Logic
- 2.10 Constraints: Rules the Database Enforces
- 2.11 Views, Materialized Views, Procedures, Functions and Triggers
- 2.12 JSON in SQL
- 2.13 The SQL Standard and Its Dialects
- 2.14 Common SQL Interview Problems
Chapter 3: Data Modelling & Database Design
- 3.1 From Requirements to Conceptual, Logical and Physical Models
- 3.2 ER Diagrams: Entities, Relationships, Cardinality and Notations
- 3.3 Keys and Identifiers: Natural, Surrogate, bigint, UUIDv4 and UUIDv7
- 3.4 Functional Dependencies and Update Anomalies
- 3.5 Normalisation Step by Step: 1NF, 2NF and 3NF
- 3.6 Beyond 3NF: BCNF, 4NF, 5NF and 6NF
- 3.7 Denormalisation: Breaking the Rules on Purpose
- 3.8 Dimensional Modelling: Facts, Dimensions, Star and Snowflake Schemas
- 3.9 Slowly Changing Dimensions and Keeping History
- 3.10 Kimball vs Inmon vs Data Vault
- 3.11 Modelling for NoSQL: Access Patterns First
- 3.12 Schema Evolution and Migrations
- 3.13 Naming Conventions and Design Anti-Patterns
- 3.14 Choosing Data Types Well
Chapter 4: How Databases Work Inside
- 4.1 Inside a DBMS: Parser, Optimiser, Executor and Storage Engine
- 4.2 Storage Layout: Pages, Slotted Pages and Heap Files
- 4.3 Buffer Pool Management
- 4.4 B+Tree Indexes in Depth
- 4.5 Beyond B-trees: Hash, Bitmap, GiST, GIN, BRIN and the Cost of Indexes
- 4.6 LSM-Trees vs B-Trees
- 4.7 Query Processing and Cost-Based Optimisation (EXPLAIN Explained)
- 4.8 Join Algorithms: Nested Loop, Hash and Sort-Merge
- 4.9 Locking, Two-Phase Locking and Deadlocks
- 4.10 MVCC, Snapshot Isolation and Serializable Snapshot Isolation
- 4.11 Isolation Levels and Anomalies
- 4.12 Logging and Recovery: WAL, ARIES, Checkpoints and fsync
- 4.13 Row vs Column Storage, Vectorized Execution and Compression
- 4.14 How Vector Indexes Work: HNSW, IVF and Product Quantization
Chapter 5: Relational & Distributed SQL Databases
- 5.1 PostgreSQL In Depth — Architecture, Versions and Community
- 5.2 PostgreSQL MVCC, VACUUM and Bloat in Practice
- 5.3 PostgreSQL Extensions — PostGIS, pgvector, TimescaleDB, Citus and More
- 5.4 MySQL, InnoDB, HeatWave and MariaDB
- 5.5 Oracle Database — 19c, 23ai, 26ai, RAC and Exadata
- 5.6 Microsoft SQL Server 2025 and Azure SQL
- 5.7 SQLite — The Database Embedded Everywhere
- 5.8 IBM Db2 and Other Relational Engines You Will Meet
- 5.9 DuckDB — Embedded Analytics on Your Laptop
- 5.10 Distributed SQL and Google Spanner
- 5.11 CockroachDB, YugabyteDB, TiDB and Amazon Aurora DSQL
- 5.12 Cloud-Managed Relational Services — RDS, Aurora, Cloud SQL, AlloyDB, Azure
- 5.13 Serverless and Developer-Platform Postgres — Neon, Supabase, PlanetScale
- 5.14 Choosing a Relational Database — Fit, Licences and the Business Landscape
Chapter 6: NoSQL & Specialised Databases
- 6.1 Why NoSQL Appeared — and What BASE Means
- 6.2 Redis, Valkey, Memcached and the Licence Split
- 6.3 DynamoDB, etcd, FoundationDB and Other Key-Value Stores
- 6.4 Document Databases — MongoDB and Its Alternatives
- 6.5 Wide-Column Stores — Cassandra 5, ScyllaDB, HBase and Bigtable
- 6.6 Graph Databases and Graph Query Languages
- 6.7 Time-Series Databases — InfluxDB 3, TimescaleDB, Prometheus, QuestDB
- 6.8 Search Engines — Elasticsearch, OpenSearch, Solr, Meilisearch, Typesense
- 6.9 Vector Databases — pgvector, Pinecone, Milvus, Weaviate, Qdrant, Chroma
- 6.10 In-Memory Databases and Data Grids
- 6.11 Multi-Model Databases — Cosmos DB, ArangoDB, SurrealDB and More
- 6.12 Ledger, Immutable and Versioned Databases
- 6.13 Embedded, Mobile and In-Browser Databases
- 6.14 Choosing the Right Data Model — Polyglot Persistence
Chapter 7: Distributed Data, Scaling & Reliability
- 7.1 Scaling Up vs Scaling Out
- 7.2 Replication: Single-Leader, Multi-Leader & Leaderless
- 7.3 Replication in Practice: Lag, Read-Your-Writes & Real Systems
- 7.4 Partitioning & Sharding
- 7.5 CAP & PACELC, Explained Honestly
- 7.6 Consistency Models: From Linearizable to Eventual
- 7.7 Consensus: Paxos, Raft & Where Databases Use Them
- 7.8 Distributed Transactions: 2PC, Percolator, TrueTime, Sagas & the Outbox
- 7.9 Caching Strategies: Cache-Aside, Write-Through, TTLs & Invalidation
- 7.10 Connection Pooling: PgBouncer, ProxySQL & RDS Proxy
- 7.11 High Availability & Failover: Patroni, Orchestrator, Always On
- 7.12 Backups, Point-in-Time Recovery & Disaster Recovery
- 7.13 Change Data Capture, Event Streaming, Event Sourcing & CQRS
- 7.14 Multi-Region & Global Databases
Chapter 8: Analytics, Warehouses, Lakehouses & Data Engineering
- 8.1 OLAP: Why Analytics Needs Different Engines
- 8.2 Cloud Data Warehouses: Snowflake, BigQuery, Redshift, Fabric & Databricks SQL
- 8.3 Data Lakes, Lakehouses & the Medallion Architecture
- 8.4 Open Table Formats: Iceberg, Delta Lake, Hudi, Paimon & DuckLake
- 8.5 Lakehouse Catalogs: Iceberg REST, Polaris, Unity Catalog & Glue
- 8.6 File Formats: Parquet, ORC, Avro, Arrow & Why Columnar Wins
- 8.7 Real-Time OLAP: ClickHouse, Druid, Pinot, StarRocks & Doris
- 8.8 Query Engines: Trino, Presto, Spark SQL, DuckDB & Athena
- 8.9 ETL vs ELT: Ingestion & Transformation with Fivetran, Airbyte & dbt
- 8.10 Orchestration: Airflow, Dagster, Prefect & Friends
- 8.11 Stream Processing: Flink, Spark Structured Streaming, Materialize & RisingWave
- 8.12 BI Tools & Semantic Layers: Power BI, Tableau, Looker, Superset & Metrics
- 8.13 Data Quality, Lineage, Contracts & Governance
- 8.14 Data Mesh, Data Products & the Modern Data Stack in 2026
Chapter 9: Operations, Security, AI, Careers & Learning
- 9.1 The DBA's Job: Provisioning, Upgrades, Patching & Capacity
- 9.2 Performance Tuning Workflow: From Slow Query to Fix
- 9.3 Monitoring & Observability for Databases
- 9.4 Database Security I: Authentication, Least Privilege, RLS & Auditing
- 9.5 Database Security II: Encryption, Key Management & Secrets
- 9.6 SQL Injection & Parameterised Queries
- 9.7 Compliance & Privacy: GDPR, HIPAA, PCI DSS, Residency & Retention
- 9.8 ORMs & Application Integration: SQLAlchemy, Hibernate, EF Core, Prisma, Django
- 9.9 Databases & AI: Vector Search, RAG, Text-to-SQL & MCP
- 9.10 Careers in Databases: DBA, Data Engineer, DRE & More
- 9.11 Database Certifications in 2026: Which Are Current
- 9.12 Learning Resources: Courses, Books & Practice Sites
- 9.13 Hands-On Labs & a 16-Week Study Plan
- 9.14 Interview Questions with Model Answers
Back matter
Ten Things Every Database Student Should Master
- 1. Data outlives code
- 2. SQL is the common language
- 3. Model for the questions you will ask
- 4. Indexes are the biggest lever — and a cost
- 5. Transactions protect correctness
- 6. Durability comes from logs and tested backups
- 7. Scale up first, then out — with eyes open
- 8. Pick the right model, not the trendy one
- 9. Security and privacy are part of the design
- 10. Measure, automate and keep learning
About this book¶
A student's plain-English guide to databases — SQL, data modelling, internals, PostgreSQL to MongoDB, distributed and vector databases, analytics, operations and careers. Every topic is explained with an everyday analogy, a worked example, step-by-step mechanics, a diagram, and a table of the databases, concepts, languages, tools & standards you need to know.