01-introduction

Shared from "Study" on Inkdown

01 - Introduction to Databases

What is a Database?

At its core, a database is an organized collection of data that can be easily accessed, managed, and updated. Think of it as a digital filing system that's smarter, faster, and more reliable than storing data in files.

Why Not Just Use Files?

Python

# Without database - storing in files
# Problems:
# 1. Data duplication (John's email in 10 places)
# 2. No concurrent access (two people can't write simultaneously)
# 3. Searching is slow (read entire file)
# 4. No data validation (can insert "invalid" data)
# 5. No relationships between data

users.txt:
---
id|name|email|department_id
1|John|john@email.com|1
2|Jane|jane@email.com|2
3|Bob|bob@email.com|1

# To find John's department name:
# 1. Read users.txt to get department_id=1
# 2. Read departments.txt to find id=1
# 3. This is slow and error-prone

Plain text

┌─────────────────────────────────────┐
│           Application               │
│      (Your code / Backend)          │
└─────────────┬───────────────────────┘
              │ SQL/Query
              ▼
┌─────────────────────────────────────┐
│      Database Management System     │
│  ┌───────────────────────────────┐  │
│  │   Query Parser & Optimizer  │  │
│  └───────────────────────────────┘  │
│  ┌───────────────────────────────┐  │
│  │   Execution Engine            │  │
│  └───────────────────────────────┘  │
│  ┌───────────────────────────────┐  │
│  │   Storage Engine              │  │
│  │   (B-trees, LSM-trees, etc.)  │  │
│  └───────────────────────────────┘  │
│  ┌───────────────────────────────┐  │
│  │   Transaction Manager         │  │
│  └───────────────────────────────┘  │
└─────────────┬───────────────────────┘
              │
              ▼
┌─────────────────────────────────────┐
│         Physical Storage            │
│    (Disk / SSD / Memory / Network)  │
└─────────────────────────────────────┘

Plain text

         [50]
        /    \
    [20,30]  [60,70,80]
    /   |     /   |   \
 [10] [25] [55] [65] [75] [85]
 
# Balanced tree structure
# Good for: Read-heavy, range queries
# Every operation: O(log n)

Sql

-- Your SQL query
SELECT * FROM users WHERE age > 25 AND city = 'NYC';

-- Steps:
-- 1. Parse: Convert to internal representation (AST)
-- 2. Validate: Do tables/columns exist? Permissions?
-- 3. Optimize: What's the best way to execute?
-- 4. Execute: Run the plan

Plain text

        ┌──────────────┐
        │   Primary    │◄──── Writes
        │   (Master)   │
        └──────┬───────┘
               │ Replication
       ┌───────┼───────┐
       ▼       ▼       ▼
   ┌──────┐ ┌──────┐ ┌──────┐
   │Replica│ │Replica│ │Replica│◄──── Reads
   └──────┘ └──────┘ └──────┘

Plain text

                    ┌─────────┐
                    │ Router  │
                    └────┬────┘
         ┌───────────────┼───────────────┐
         ▼               ▼               ▼
    ┌─────────┐     ┌─────────┐     ┌─────────┐
    │ Shard 1 │     │ Shard 2 │     │ Shard 3 │
    │Users A-G│     │Users H-P│     │Users Q-Z│
    └─────────┘     └─────────┘     └─────────┘

Plain text

Is your data structure known upfront?
│
├─ YES ──► Need complex relationships & joins?
│          │
│          ├─ YES ──► PostgreSQL / MySQL (Relational)
│          │
│          └─ NO ──► Redis / DynamoDB (Key-Value)
│
└─ NO ──► Need to store massive unstructured data?
          │
          ├─ YES ──► MongoDB / Couchbase (Document)
          │
          └─ NO ──► Need to handle time-series data?
                    │
                    ├─ YES ──► InfluxDB / TimescaleDB
                    │
                    └─ NO ──► Need graph relationships?
                              │
                              ├─ YES ──► Neo4j
                              │
                              └─ NO ──► Start with PostgreSQL

Term	Definition	Analogy
Schema	Structure/blueprint of database	House floor plan
Table/Collection	Group of related data	Excel sheet
Row/Document	Single record	One row in Excel
Column/Field	Attribute of data	Column in Excel
Index	Data structure for fast lookup	Book index
Primary Key	Unique identifier for each row	Social Security Number
Foreign Key	Reference to another table's PK	Pointer to related data
Query	Request for data	Asking a question
Transaction	Group of operations treated as one	Bank transfer (debit + credit)

01-introduction

01 - Introduction to Databases

What is a Database?

Why Not Just Use Files?

01-introduction

01 - Introduction to Databases

What is a Database?

Why Not Just Use Files?

Types of Data Storage

1. Flat Files (CSV, JSON, XML)

2. Relational Databases (SQL)

3. NoSQL Databases

4. Specialized Databases

The Database Management System (DBMS)

Key Components Explained

1. Storage Engine

2. Query Processor

3. Transaction Manager

Database Architecture Patterns

1. Single Node (Monolith)

2. Primary-Replica (Read Replicas)

3. Sharded Architecture

Database Selection Flowchart

Important Terminology

Performance Metrics to Know

Next Steps