Skip to main content

Database Foundations

Part 1: Foundational Concepts​

1. Introduction to Data and Databases​

  • Data vs. Information vs. Knowledge
  • Structured, Semi-structured, and Unstructured Data
  • Purpose of a Database and a DBMS
  • Evolution of Databases
  • Types of Databases

Read Chapter

Part 2: The Relational Model and Design​

2. Core Concepts of the Relational Model​

  • Tables, Rows, and Columns
  • Primary, Foreign, and Candidate Keys
  • Domain, Entity, and Referential Constraints

Read Chapter

3. Database Design and Data Modeling​

  • Database Development Life Cycle (DDLC)
  • Conceptual, Logical, and Physical Data Models
  • Entity-Relationship (ER) Modeling
  • Normalization and when to Denormalize

Read Chapter

Part 3: SQL — The Language of Databases​

4. SQL Fundamentals​

  • DDL: CREATE, ALTER, DROP, TRUNCATE
  • DML: INSERT, UPDATE, DELETE
  • DQL: SELECT, FROM, WHERE, ORDER BY, LIMIT
  • DCL and TCL essentials

Read Chapter

5. Intermediate and Advanced SQL​

  • Filtering, Operators, and Set Logic
  • Aggregate and Window Functions
  • Joins, Subqueries, and CTEs
  • Views, Stored Procedures, Triggers, and UDFs

Read Chapter

Part 4: Database Administration and Performance​

6. Core DBA Responsibilities​

  • Selecting and Installing a DBMS
  • Security, Backup, and Recovery Strategies
  • Monitoring, Tuning, and Log Analysis

Read Chapter

7. Performance Optimization​

  • Indexing Strategies and B-Tree Anatomy
  • Reading and Rewriting Execution Plans

Read Chapter

8. Transaction Management and Concurrency​

  • ACID Properties in Practice
  • Locking, Isolation Levels, and MVCC

Read Chapter

Part 5: Beyond Relational — NoSQL & Specialized Engines​

9. Introduction to NoSQL​

  • Why NoSQL Emerged
  • CAP Theorem and BASE Trade-offs

10. Types of NoSQL Databases​

  • Document Stores and Flexible Schemas
  • Key-Value Stores for Ultra-Low Latency
  • Column-Family Stores for Big Data
  • Graph Databases for Relationship-Heavy Workloads

Part 6: Advanced & Modern Architectures​

11. Data Warehousing & Business Intelligence (BI)​

  • OLTP vs. OLAP Workloads
  • ETL/ELT Patterns and Dimensional Modeling

12. Modern Database Technologies​

  • Cloud DBaaS Platforms
  • NewSQL Hybrids
  • Specialized Engines (Time-Series, Search, Spatial)

Part 7: Database Internals & Engineering​

13. How Databases Work Under the Hood​

  • Storage Architecture and Buffer Management
  • Index and Access Methods
  • Query Processing Pipeline
  • Transaction Logging and Recovery

Part 8: Distributed Data Systems​

14. Principles of Distributed Databases​

  • Replication Strategies
  • Sharding and Partitioning Models
  • Consistency Models
  • Distributed Transactions and Consensus (2PC, Paxos, Raft)
  • Cloud-Native Case Studies (Spanner, Aurora, Cosmos DB)

Part 9: Databases in Application Development​

15. Integrating Databases with Software​

  • Connectivity, Connection Pools, and Data Access Layers
  • ORM Patterns and Anti-patterns
  • Schema Migration and Versioning
  • Testing and CI/CD for Data Workflows

Part 10: Data Governance, Security, and Ethics​

16. Managing Data Responsibly​

  • Data Quality and Lifecycle Management
  • Metadata, Lineage, and Cataloging
  • Advanced Security: Row-Level Security, Masking, Encryption
  • Compliance, Privacy, and Ethics Frameworks

Part 11: Synthesis and Practice​

17. Architectural Case Studies​

  • High-Throughput E-commerce Systems
  • Social Feed Fan-out Architectures
  • Financial Ledger Requirements
  • Analytics Platforms and Columnar Storage