Welcome to Databases 101 to Advanced Architecture.

This complete course is broken down into

structured modules taking you from core concepts to enterprise production systems.

Module 1: Fundamentals & Relational Model (RDBMS)

What is a Database?

A database is an organized collection of structured information or data, typically stored electronically in

a computer system. It is managed by a Database Management System (DBMS), which serves as the
interface between the database and its end-users or programs.

The Relational Model

Proposed by Edgar F. Codd in 1970, the relational model structures data into tables (relations) consisting
of rows (tuples) and columns (attributes).

Primary Key (PK): A column (or set of columns) that uniquely identifies each row in a table.
Foreign Key (FK): A column that creates a link between two tables, pointing to the Primary Key of
another table.
Relationships:
1-to-1: E.g., User $\rightarrow$ User Profile.
1-to-Many ($1:N$): E.g., Customer $\rightarrow$ Orders.
Many-to-Many ($N:M$): E.g., Students $\leftrightarrow$ Courses (requires a junction/pivot table).

Module 2: Relational Schema & Normalization

Normalization reduces data redundancy and prevents data anomalies (insertion, update, and deletion
anomalies).

Unnormalized Data (UNF)

↓ (Remove repeating groups)
First Normal Form (1NF)
↓ (Remove partial key dependencies)
Second Normal Form (2NF)
↓ (Remove transitive dependencies)
Third Normal Form (3NF) / BCNF
 Normal
Rule Requirement
Form

Atomic values (no arrays/lists in a cell), unique column names, unique row order
1NF
independence.

In 1NF + every non-key attribute must depend fully on the entire primary key (no partial
2NF
dependencies).

In 2NF + no non-key attribute depends on another non-key attribute (no transitive

3NF
dependencies).

Boyce-Codd NF: A stricter version of 3NF where every determinant must be a

BCNF
candidate key.

Module 3: SQL Mastery (Structured Query Language)

SQL is divided into three primary categories:

1. DDL (Data Definition Language): Defines structure ( CREATE , ALTER , DROP , TRUNCATE ).
2. DML (Data Manipulation Language): Manages data ( INSERT , UPDATE , DELETE ).
3. DQL (Data Query Language): Queries data ( SELECT ).

Essential SQL Reference Pattern

SELECT
c.country_name,
COUNT(o.order_id) AS total_orders,
SUM(o.total_amount) AS revenue
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.country_name
HAVING SUM(o.total_amount) > 10000
ORDER BY revenue DESC
LIMIT 10;

JOIN Types Overview

INNER JOIN : Returns rows when there is a match in both tables.

LEFT JOIN : Returns all rows from the left table, and matched rows from
the right table.
RIGHT JOIN : Returns all rows from the right table, and matched rows from
the left table.
FULL JOIN : Returns rows when there is a match in one of the tables.

Module 4: Database Internals & Performance Tuning

Indexing Mechanisms

Indexes accelerate read operations at the cost of write speed and storage space.

B-Trees / B+ Trees: The gold standard for relational databases. Keeps data balanced and enables
fast $O(\log N)$ search, insert, delete, and range queries.
Hash Indexes: $O(1)$ lookups, but does not support range queries ( WHERE age > 25 ).
Clustered Index: Dictates the physical order of data on disk (usually the Primary Key). Only one per
table.
Non-Clustered Index: A separate structure storing index keys with pointers to the actual data
row.

ACID Properties

Every enterprise transaction engine guarantees four properties:

Atomicity: All statements in a transaction succeed, or the entire transaction is rolled back ("All or
Nothing").
Consistency: Data must move from one valid state to another, maintaining constraints and
invariants.
Isolation: Concurrent transactions execute without interfering with one another.
Durability: Once committed, changes survive system crashes or power failures (via Write-Ahead
Logging / WAL).

Module 5: Non-Relational Databases (NoSQL)

When scale, flexible schemas, or specific access patterns outweigh the need for strict relational
integrity, NoSQL databases are used.

┌────────────────────────┐
│ NoSQL Families │
└───────────┬────────────┘
┌─────────────────────┼─────────────────────┐
▼ ▼ ▼
┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ Document Store│ │ Key-Value │ │ Graph / Column│
│ (e.g. MongoDB)│ │ (e.g. Redis) │ │ (e.g. Neo4j) │
└───────────────┘ └───────────────┘ └───────────────┘

1. Document Databases (MongoDB, PostgreSQL JSONB): Store semi-structured data as

JSON/BSON documents. Ideal for rapidly changing domain models.
2. Key-Value Stores (Redis, Memcached): Ultra-low latency memory stores mapping unique keys to
blobs/objects. Ideal for caching and sessions.
3. Wide-Column Stores (Cassandra, ScyllaDB): Designed for extreme write workloads across
massive clusters without a single point of failure.
4. Graph Databases (Neo4j, Amazon Neptune): Nodes and edges represent complex, heavily
interconnected network relationships.

CAP Theorem

In a distributed system, you can only guarantee two out of three properties simultaneously:

Consistency (C): Every read receives the most recent write or an error.
Availability (A): Every request receives a non-error response (without guarantee that it contains
the latest write).
Partition Tolerance (P): The system continues operating despite network dropped messages.

Module 6: Scaling & Distributed Systems Architecture

When a single database server hits resource limits (CPU, RAM, Disk I/O), you scale using these patterns:

Read Replicas: Primary database handles all writes and replicates data to multiple read-only
secondary nodes.
Horizontal Partitioning (Sharding): Splitting a single logical table across multiple separate
physical databases based on a Shard Key (e.g., hash(user_id) % 4 ).
Connection Pooling: Reusing database connections (e.g., PgBouncer) to reduce connection
overhead under heavy web traffic.