SQL Databases

Relational databases organize data into tables with relationships.

Relational Concepts

Table (Relation):
┌────┬─────────┬───────────┬──────┐
│ ID │ Name    │ Email     │ Age  │
├────┼─────────┼───────────┼──────┤
│ 1  │ Alice   │ a@b.com   │ 25   │
│ 2  │ Bob     │ b@c.com   │ 30   │
│ 3  │ Charlie │ c@d.com   │ 28   │
└────┴─────────┴───────────┴──────┘

Row (Tuple): A single record
Column (Attribute): A single field
Primary Key: Unique identifier (ID)
Foreign Key: Reference to another table

Relationships

One-to-One:
User (1) ←──→ (1) UserProfile

One-to-Many:
User (1) ←──→ (Many) Orders

Many-to-Many:
Student (Many) ←──→ (Many) Courses
(through junction table)

SQL Schema Example

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    price DECIMAL(10,2)
);

SQL Operations

-- CRUD Operations
INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com');
SELECT * FROM users WHERE id = 1;
UPDATE users SET name = 'Alice Smith' WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- Joins
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Aggregation
SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

ACID ensures reliable database transactions.

ACID Overview

ACID:
A - Atomicity: All or nothing
C - Consistency: Valid state transitions
I - Isolation: Concurrent transactions don't interfere
D - Durability: Committed data persists

Atomicity

Transaction: Transfer $100 from A to B

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If either operation fails:
→ Both operations rolled back
→ Database returns to original state

Consistency

Constraint: balance >= 0

Before: A=200, B=100
Transfer: $150 from A to B

A=200-150=50 ✓
B=100+150=250 ✓

If A would go negative:
→ Transaction rejected
→ Constraint violated

Isolation

Concurrent transactions:

T1: Read balance = 100
T2: Read balance = 100
T1: Write balance = 150 (100+50)
T2: Write balance = 80 (100-20)

Without isolation: Lost update (T1's write lost)
With isolation: Correct result

Durability

After COMMIT:
- Data written to disk
- survives crash/power failure
- WAL (Write-Ahead Logging)
- Replication

Transaction Example

BEGIN TRANSACTION;

-- Debit account
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Credit account
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Log transaction
INSERT INTO transactions (from_id, to_id, amount) VALUES (1, 2, 100);

COMMIT;
-- If any operation fails, ROLLBACK

Different SQL databases have different strengths.

Database Comparison

Database	Strengths	Use Case
PostgreSQL	Features, extensibility	Complex queries, GIS, JSON
MySQL	Simplicity, speed	Web applications
SQLite	Embedded, zero-config	Mobile, embedded
MariaDB	MySQL fork, open source	Drop-in MySQL replacement
SQL Server	Enterprise features	.NET ecosystem

PostgreSQL

Features:
- Advanced SQL (CTEs, window functions)
- JSON/JSONB support
- Full-text search
- PostGIS (geospatial)
- Extensions (TimescaleDB, Citus)
- Strong ACID compliance

Use Cases:
- Complex queries
- Data warehousing
- Geospatial applications
- Systems requiring extensibility

MySQL

Features:
- Simple, fast
- Replication support
- Large ecosystem
- InnoDB (ACID)
- MyISAM (fast reads)

Use Cases:
- Web applications
- CMS (WordPress)
- Read-heavy workloads

When to Choose SQL

Choose SQL when:
- Data is structured and relational
- ACID transactions required
- Complex queries (JOINs, aggregations)
- Data integrity critical
- Schema is stable

Examples:
- Banking systems
- E-commerce (orders, inventory)
- User management
- Content management

SQL Scaling Options

Vertical Scaling:
- More CPU, RAM, storage
- Simple but limited

Read Replicas:
- Primary for writes
- Replicas for reads
- Async replication

Sharding:
- Split data across databases
- Complex but scalable

Connection Pooling:
- Reuse connections
- Reduce overhead