{
  _id: ObjectId("507f1f77bcf86cd799439011"),
  name: "Alice Johnson",
  email: "alice@example.com",
  age: 28,
  address: {
    street: "123 Main St",
    city: "San Francisco",
    zip: "94102"
  },
  orders: [
    { orderId: 101, total: 150, date: ISODate("2024-01-15") },
    { orderId: 102, total: 220, date: ISODate("2024-02-20") }
  ]
}

// Single document contains nested data
// No need for JOIN operations

-- Users table
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  age INT
);

-- Addresses table
CREATE TABLE addresses (
  id INT PRIMARY KEY,
  user_id INT FOREIGN KEY REFERENCES users(id),
  street VARCHAR(200),
  city VARCHAR(100),
  zip VARCHAR(10)
);

-- Orders table
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT FOREIGN KEY REFERENCES users(id),
  total DECIMAL(10,2),
  date DATE
);

-- Requires JOINs to get complete user data
SELECT u.*, a.*, o.*
FROM users u
LEFT JOIN addresses a ON u.id = a.user_id
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

// MongoDB Multi-Document Transaction
const session = client.startSession();

try {
  session.startTransaction();
  
  // Transfer $100 from Alice to Bob
  await accounts.updateOne(
    { name: "Alice" },
    { $inc: { balance: -100 } },
    { session }
  );
  
  await accounts.updateOne(
    { name: "Bob" },
    { $inc: { balance: 100 } },
    { session }
  );
  
  await session.commitTransaction();
  console.log("Transfer successful");
  
} catch (error) {
  await session.abortTransaction();
  console.log("Transfer failed, rolled back");
} finally {
  session.endSession();
}

-- SQL Transaction (PostgreSQL)
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

COMMIT;  -- or ROLLBACK on error

-- Normalized SQL Design (3NF)
-- Users table
users:
  id | name        | email
  1  | Alice       | alice@example.com
  2  | Bob         | bob@example.com

-- Orders table
orders:
  id | user_id | total | date
  101| 1       | 150   | 2024-01-15
  102| 1       | 220   | 2024-02-20
  103| 2       | 180   | 2024-03-10

-- Products table
products:
  id | name      | price
  1  | Widget    | 50
  2  | Gadget    | 75

-- Order_Items table (junction table)
order_items:
  order_id | product_id | quantity
  101      | 1          | 3
  102      | 2          | 2
  103      | 1          | 1

Benefits:
✓ No data redundancy
✓ Easy to update (change product price once)
✓ Data integrity enforced

Drawbacks:
✗ Requires complex JOINs (4 tables to get user's orders with products)
✗ JOIN operations expensive

// Denormalized MongoDB Design
{
  _id: 1,
  name: "Alice",
  email: "alice@example.com",
  orders: [
    {
      orderId: 101,
      total: 150,
      date: ISODate("2024-01-15"),
      items: [
        { product: "Widget", price: 50, quantity: 3 }
      ]
    },
    {
      orderId: 102,
      total: 220,
      date: ISODate("2024-02-20"),
      items: [
        { product: "Gadget", price: 75, quantity: 2 }
      ]
    }
  ]
}

Benefits:
✓ Single query gets all data
✓ No JOINs needed
✓ Atomic updates on user document

Drawbacks:
✗ Data duplication (product info repeated)
✗ If product price changes, must update many documents
✗ Document size can grow large

SELECT name, email, age
FROM users
WHERE age >= 25 AND city = 'San Francisco'
ORDER BY name ASC
LIMIT 10;

db.users.find(
  { age: { $gte: 25 }, city: "San Francisco" },  // WHERE clause
  { name: 1, email: 1, age: 1, _id: 0 }          // SELECT clause (projection)
)
.sort({ name: 1 })                                // ORDER BY
.limit(10);                                       // LIMIT

SELECT u.name, u.email, o.id AS order_id, o.total, o.date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.age > 25;

db.users.aggregate([
  { $match: { age: { $gt: 25 } } },              // WHERE clause
  {
    $lookup: {
      from: "orders",                             // JOIN orders table
      localField: "_id",                          // users._id
      foreignField: "userId",                     // orders.userId
      as: "userOrders"                           // Result array
    }
  },
  {
    $project: {                                   // SELECT clause
      name: 1,
      email: 1,
      "userOrders.orderId": 1,
      "userOrders.total": 1,
      "userOrders.date": 1
    }
  }
]);

SELECT city, COUNT(*) AS user_count, AVG(age) AS avg_age
FROM users
GROUP BY city
HAVING COUNT(*) > 10
ORDER BY user_count DESC;

db.users.aggregate([
  {
    $group: {
      _id: "$city",                               // GROUP BY city
      user_count: { $sum: 1 },                    // COUNT(*)
      avg_age: { $avg: "$age" }                   // AVG(age)
    }
  },
  {
    $match: { user_count: { $gt: 10 } }          // HAVING
  },
  {
    $sort: { user_count: -1 }                     // ORDER BY
  }
]);

SELECT 
  u.name,
  COUNT(DISTINCT o.id) AS total_orders,
  SUM(o.total) AS total_spent,
  AVG(o.total) AS avg_order_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.date >= '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) >= 3
ORDER BY total_spent DESC
LIMIT 5;

db.users.aggregate([
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "userId",
      as: "orders"
    }
  },
  {
    $unwind: "$orders"                            // Flatten array
  },
  {
    $match: {
      "orders.date": { $gte: ISODate("2024-01-01") }
    }
  },
  {
    $group: {
      _id: "$_id",
      name: { $first: "$name" },
      total_orders: { $sum: 1 },
      total_spent: { $sum: "$orders.total" },
      avg_order_value: { $avg: "$orders.total" }
    }
  },
  {
    $match: { total_orders: { $gte: 3 } }
  },
  {
    $sort: { total_spent: -1 }
  },
  {
    $limit: 5
  }
]);

-- PostgreSQL Example: Lock Contention
-- Transaction 1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- Locks row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... processing ...
COMMIT;

-- Transaction 2 (concurrent)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- WAITS for lock
-- This transaction BLOCKS until Transaction 1 commits
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

-- Result: Transaction 2 waits (blocks) until Transaction 1 releases lock
-- No retry needed, but performance degrades with high contention

// MongoDB Example: Write Conflict
// Transaction 1
const session1 = client.startSession();
session1.startTransaction();

await accounts.updateOne(
    { _id: 1 },
    { $inc: { balance: -100 } },
    { session: session1 }
);
// ... processing ...
await session1.commitTransaction();

// Transaction 2 (concurrent)
const session2 = client.startSession();
session2.startTransaction();

try {
    await accounts.updateOne(
        { _id: 1 },
        { $inc: { balance: 50 } },
        { session: session2 }
    );
    await session2.commitTransaction();
} catch (error) {
    if (error.hasOwnProperty('errorLabels') && 
        error.errorLabels.includes('TransientTransactionError')) {
        // WriteConflict - Transaction 2 must RETRY
        console.log('Write conflict detected, retrying...');
        await session2.abortTransaction();
        // Application must implement retry logic
    }
}

// Result: Transaction 2 gets WriteConflict and must retry
// No blocking, but requires retry implementation

MongoDB Sharding Architecture:

Application
     │
     ▼
┌─────────┐
│ mongos  │ ← Query router (stateless)
└─────────┘
     │
     ├────────────┬────────────┬────────────┐
     ▼            ▼            ▼            ▼
┌─────────┐  ┌─────────┐  ┌─────────┐  ┌─────────┐
│ Shard 1 │  │ Shard 2 │  │ Shard 3 │  │ Shard 4 │
│(Replica │  │(Replica │  │(Replica │  │(Replica │
│  Set)   │  │  Set)   │  │  Set)   │  │  Set)   │
└─────────┘  └─────────┘  └─────────┘  └─────────┘
 userId      userId       userId       userId
 1-250K      250K-500K    500K-750K    750K-1M

Features:
✓ Automatic data distribution
✓ Automatic chunk splitting and balancing
✓ Transparent to application
✓ Add shards on the fly
✓ Each shard is a replica set (HA)

SQL Horizontal Scaling Options:

1. Read Replicas (Easy)
   Primary ──┬──> Replica 1 (read-only)
             ├──> Replica 2 (read-only)
             └──> Replica 3 (read-only)
   
   ✓ Scale reads only
   ✗ Writes still bottlenecked on primary

2. Manual Sharding (Hard)
   Application decides which DB to use
   
   if (userId % 4 == 0) → DB Server 1
   if (userId % 4 == 1) → DB Server 2
   if (userId % 4 == 2) → DB Server 3
   if (userId % 4 == 3) → DB Server 4
   
   ✓ Scales reads and writes
   ✗ Application manages routing
   ✗ Complex queries across shards
   ✗ Rebalancing requires manual migration

3. Clustering Solutions (PostgreSQL Citus, MySQL Cluster)
   ✓ More automated
   ✗ Less mature than MongoDB sharding
   ✗ Often limited compared to single-server SQL