Introduction to Databases

-- Example: Creating a Users table with fixed schema
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Every user must follow this exact structure
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 28);

// Example: Storing a user document (MongoDB)
{
    "_id": "507f1f77bcf86cd799439011",
    "name": "Alice",
    "email": "alice@example.com",
    "age": 28,
    "preferences": {
        "theme": "dark",
        "notifications": true
    },
    "tags": ["premium", "verified"]
}

// Another user can have completely different fields
{
    "_id": "507f1f77bcf86cd799439012",
    "name": "Bob",
    "email": "bob@example.com",
    "company": "Tech Corp",
    "role": "developer"
}

┌────┬──────────┬─────────────────────┬─────┬────────────┐
│ ID │ Name     │ Email               │ Age │ Created    │
├────┼──────────┼─────────────────────┼─────┼────────────┤
│ 1  │ Alice    │ alice@example.com   │ 28  │ 2024-01-15 │
│ 2  │ Bob      │ bob@example.com     │ 34  │ 2024-01-16 │
│ 3  │ Charlie  │ charlie@example.com │ 22  │ 2024-01-17 │
└────┴──────────┴─────────────────────┴─────┴────────────┘

# Key Concepts:
# - Each ROW is a unique user (a record)
# - Each COLUMN is a specific attribute (a field)
# - ID is the PRIMARY KEY (unique identifier)
# - Every table should have a primary key

-- SELECT: Retrieve data from the database
SELECT name, email, age
FROM users
WHERE age > 25
ORDER BY name;

-- Result:
┌──────────┬─────────────────────┬─────┐
│ Name     │ Email               │ Age │
├──────────┼─────────────────────┼─────┤
│ Alice    │ alice@example.com   │ 28  │
│ Bob      │ bob@example.com     │ 34  │
└──────────┴─────────────────────┴─────┘

-- Breaking it down:
-- SELECT: Which columns to retrieve
-- FROM: Which table to query
-- WHERE: Filter conditions (optional)
-- ORDER BY: Sort the results (optional)

# BAD: Everything in one table (denormalized)
orders_table:
┌────┬─────────────┬──────────────┬──────────────┬──────────┬───────┐
│ id │ customer    │ customer_email│ product_name │ price    │ qty   │
├────┼─────────────┼──────────────┼──────────────┼──────────┼───────┤
│ 1  │ Alice Smith │ alice@ex.com │ Laptop       │ 999.99   │ 1     │
│ 2  │ Alice Smith │ alice@ex.com │ Mouse        │ 29.99    │ 2     │
└────┴─────────────┴──────────────┴──────────────┴──────────┴───────┘
# Problem: Customer info repeated (redundancy, inconsistency risk)

# GOOD: Normalized with relationships
customers:
┌────┬─────────────┬──────────────┐
│ id │ name        │ email        │
├────┼─────────────┼──────────────┤
│ 1  │ Alice Smith │ alice@ex.com │
└────┴─────────────┴──────────────┘

products:
┌────┬──────────────┬────────┐
│ id │ name         │ price  │
├────┼──────────────┼────────┤
│ 1  │ Laptop       │ 999.99 │
│ 2  │ Mouse        │ 29.99  │
└────┴──────────────┴────────┘

orders:
┌────┬─────────────┬────────────┬─────────┬─────┐
│ id │ customer_id │ product_id │ qty     │ ... │
├────┼─────────────┼────────────┼─────────┼─────┤
│ 1  │ 1           │ 1          │ 1       │ ... │
│ 2  │ 1           │ 2          │ 2       │ ... │
└────┴─────────────┴────────────┴─────────┴─────┘
# Better: No redundancy, single source of truth for each entity