AWS Database Fundamentals

Complete module (~35 min) covering the main AWS database services: RDS, DynamoDB, and Aurora.
Enriched with official AWS documentation, code examples, and architecture diagrams.

Table of Contents

1. Relational Database Basics
2. Demo — Create an RDS Instance
3. Non-Relational Databases (NoSQL)
4. Demo — Create a DynamoDB Table
5. RDS vs DynamoDB — Detailed Comparison
6. Amazon Aurora
7. Code Samples — AWS CLI, SQL, boto3
8. Quick Reference and Final Recap

1. Relational Database Basics

Core Concepts

A relational database relies on the ability to relate information from one table to another. A table is a structured grouping of information composed of two basic elements:

Definition: A relational database defines relationships between sets of rows using unique keys, allowing at least one table of rows and columns to be linked.

Example: Aurora’s Bakery

To illustrate the concept, consider Aurora who opens a bakery. She has a database containing several interconnected tables:

Table: Customers
┌────────────┬───────────┬──────────────────┬──────────────┐
│ CustomerID │ Name      │ ShippingAddress  │ Phone        │
├────────────┼───────────┼──────────────────┼──────────────┤
│ 1          │ Alice     │ 123 Main St      │ 555-0101     │
│ 2          │ Bob       │ 456 Oak Ave      │ 555-0202     │
└────────────┴───────────┴──────────────────┴──────────────┘

Table: Orders
┌─────────┬────────────┬───────────┬────────┐
│ OrderID │ CustomerID │ ProductID │ Amount │
├─────────┼────────────┼───────────┼────────┤
│ 101     │ 1          │ 50        │ 25.00  │
│ 102     │ 2          │ 51        │ 40.00  │
└─────────┴────────────┴───────────┴────────┘

Table: Products
┌───────────┬─────────────────┬────────┬──────────┐
│ ProductID │ ProductName     │ Price  │ Category │
├───────────┼─────────────────┼────────┼──────────┤
│ 50        │ Croissant       │  2.50  │ Pastry   │
│ 51        │ Sourdough Bread │  8.00  │ Bread    │
└───────────┴─────────────────┴────────┴──────────┘

Tables are linked by keys:

• Primary key: unique identifier within its own table (e.g., CustomerID in Customers)
• Foreign key: reference to the primary key of another table (e.g., CustomerID in Orders)

Relational Architecture — ER Diagram

erDiagram
    CUSTOMERS {
        int CustomerID PK
        string Name
        string ShippingAddress
        string BillingAddress
        string Phone
        string Email
    }
    ORDERS {
        int OrderID PK
        int CustomerID FK
        int ProductID FK
        float Amount
        date OrderDate
        string Status
    }
    PRODUCTS {
        int ProductID PK
        string ProductName
        float Price
        string Category
        int StockQuantity
    }

    CUSTOMERS ||--o{ ORDERS : "place"
    PRODUCTS ||--o{ ORDERS : "included in"

ACID Properties

Relational databases comply with ACID properties, which guarantee data integrity:

Shared Responsibility Model — On-premises vs EC2 vs RDS

Amazon RDS — Overview

Amazon RDS (Relational Database Service) is a fully managed AWS service that lets you deploy and operate relational databases in the cloud without managing the underlying infrastructure.

Supported engines (7 total):

mindmap
  root((Amazon RDS))
    MySQL
      Versions 5.7 / 8.0
    PostgreSQL
      Versions 13 to 16
    MariaDB
      Versions 10.x
    Oracle Database
      SE2 / EE
    Microsoft SQL Server
      Express / Web / Standard / Enterprise
    IBM Db2
      Standard / Advanced
    Amazon Aurora
      MySQL-compatible
      PostgreSQL-compatible

Key advantages of Amazon RDS:

• Automated backups with configurable retention (0 to 35 days)
• Automatic database engine patching
• Failure detection and automatic recovery
• Multi-AZ for high availability (synchronous replication)
• Read Replicas for read scalability
• Native integration with VPC, IAM, CloudWatch, KMS

Typical RDS Architecture in a VPC

graph TB
    Internet((Internet)) --> ELB[Elastic Load Balancer]
    ELB --> EC2a[EC2 App Server AZ-1a]
    ELB --> EC2b[EC2 App Server AZ-1b]

    subgraph VPC["VPC - Region us-east-1"]
        subgraph PublicSubnet["Public Subnets"]
            EC2a
            EC2b
        end
        subgraph PrivateSubnet["Private Subnets"]
            RDSprimary["(RDS Primary AZ-1a)"]
            RDSstandby["(RDS Standby AZ-1b)"]
            RDSreadReplica["(Read Replica AZ-1c)"]
        end
    end

    EC2a --> RDSprimary
    EC2b --> RDSprimary
    RDSprimary -- "Synchronous replication" --> RDSstandby
    RDSprimary -- "Asynchronous replication" --> RDSreadReplica

2. Demo — Create an RDS Instance

Steps in the AWS Console

Step 1 — Access the Service

1. Log in to the AWS console
2. Search for RDS in the search bar
3. Open the Amazon RDS service
4. Click Create database

Step 2 — Creation Mode

Use Standard create to master all options.

Step 3 — Select the Engine

• Engine type: MySQL
• Engine Version: 8.0 (major MySQL version installed on the instance)
• New minor versions are applied automatically during maintenance windows

Step 4 — Choose the Template (Environment)

The Free tier includes 750 hours/month of a db.t3.micro or db.t4g.micro instance for MySQL, MariaDB, or PostgreSQL.

Step 5 — DB Instance Classes

graph LR
    A[DB Instance Classes] --> B[Standard db.m*]
    A --> C[Memory Optimized db.r* / db.x* / db.z*]
    A --> D[Compute Optimized db.c*]
    A --> E[Burstable Performance db.t*]

    B --> B1["db.m7g, db.m6i - General purpose - Graviton3"]
    C --> C1["db.r7g, db.r6i - Memory-intensive workloads"]
    D --> D1["db.c7g - CPU-intensive workloads"]
    E --> E1["db.t3.micro, db.t4g - Dev/test - Cost-effective"]

Step 6 — Storage Settings

Storage autoscaling: RDS can automatically increase storage size without downtime.

Step 7 — Network and Security Configuration

graph TB
    EC2[EC2 Instance - SG: ec2-rds-1] -->|Port 3306 MySQL| SG[Security Group rds-ec2-1]
    SG --> RDS["(RDS Instance - Private Subnet)"]

    subgraph VPC
        subgraph PublicSubnet["Public Subnet"]
            EC2
        end
        subgraph PrivateSubnet["Private Subnet"]
            RDS
        end
    end

Key parameters:

• VPC: Virtual Private Cloud where the instance will be deployed
• Subnet group: set of subnets in different AZs (required for Multi-AZ)
• Public access: No recommended in production (VPC access only)
• Security group: port 3306 for MySQL, 5432 for PostgreSQL
• Password authentication: password for the master user
• IAM database authentication: connect via IAM tokens without a password

Step 8 — Additional Configuration

Step 9 — Connecting from EC2

sequenceDiagram
    participant User as User
    participant EC2 as EC2 Instance
    participant SG as RDS Security Group
    participant RDS as RDS MySQL

    User->>EC2: SSH or Session Manager
    EC2->>SG: Outbound TCP:3306 request
    Note over SG: Verifies inbound rules - Source = EC2 SG
    SG->>RDS: Connection authorized
    RDS-->>EC2: Connection established
    EC2-->>User: mysql prompt

Connection command from EC2:

# Connect to the RDS MySQL instance from EC2
mysql -h <rds-endpoint>.rds.amazonaws.com \
      -P 3306 \
      -u admin \
      -p

# Example usage
mysql> SHOW DATABASES;
mysql> USE aurora_bakery;
mysql> SELECT * FROM customers LIMIT 10;

3. Non-Relational Databases (NoSQL)

The Four Main NoSQL Types on AWS

graph TD
    NoSQL[Non-Relational Databases NoSQL] --> Graph[Graph Databases - Amazon Neptune]
    NoSQL --> Document[Document Stores - Amazon DocumentDB]
    NoSQL --> KV[Key-Value Pairs - Amazon DynamoDB]
    NoSQL --> Wide[Wide Column - Amazon Keyspaces]

    Graph --> GraphDesc["Complex relational data\nSocial networks, graphs\nRecommendations"]
    Document --> DocDesc["JSON / BSON documents\nE-commerce catalog\nContent management"]
    KV --> KVDesc["Key-value pairs\nSessions, carts, leaderboards\nHigh frequency"]
    Wide --> WideDesc["Wide tables, low latency\nIoT, time series\nCassandra-compatible"]

3.1 Graph Databases — Amazon Neptune

“A systematic collection of data that emphasizes the relationships between the different data entities.” — AWS

Concept: Think of a group of friends. Harold is friends with Melissa, Anthony, and Tina — but Anthony is not friends with Tina. These relationships and their degrees (friend-of-a-friend) are exactly what graph databases model.

Harold ──── friend ──→ Melissa
  │                      │
  ├── friend ──→ Anthony  └── friend ──→ Maria
  │               │
  └── friend ──→ Tina   Anthony ──── friend ──→ Steve

Use cases:

• Social networks (LinkedIn, Facebook)
• Fraud detection (links between accounts)
• Recommendation engines
• Knowledge graphs

Amazon Neptune:

• Supports Gremlin (TinkerPop) and SPARQL (RDF) query languages
• High availability: replication across 3 AZs, up to 15 read replicas
• Storage: starts at 10 GB, automatically scales up to 128 TiB

3.2 Document Stores — Amazon DocumentDB

A document store stores data as JSON or BSON documents in a catalog:

// Document 1 - Aurora Bakery Product
{
  "productId": "P001",
  "name": "Butter Croissant",
  "category": "Pastry",
  "price": 2.50,
  "allergens": ["gluten", "dairy", "eggs"],
  "available": true,
  "nutrition": {
    "calories": 272,
    "fat": 14,
    "carbs": 31
  }
}

// Document 2 - Same collection, different structure (flexible schema)
{
  "productId": "P002",
  "name": "Sourdough Loaf",
  "category": "Bread",
  "price": 8.00,
  "weight_grams": 750
}

Advantages of document stores:

• Flexible schema: each document can have a different structure
• Development ease: data maps to objects in code
• Scalability: works at any scale (small shop → global publisher)

Amazon DocumentDB:

• MongoDB-compatible (API, drivers, tools)
• Up to 15 read replicas
• Distributed, fault-tolerant storage (6 copies in 3 AZs)

3.3 Key-Value Databases — Amazon DynamoDB

The key-value store is the simplest and most performant form of NoSQL. Each entry is a key → value pair:

Key (Partition Key)             Value (Item)
────────────────────────        ────────────────────────────────────────
"session:user:4821"         →   { "userId": 4821, "cart": [...] }
"product:P001"              →   { "name": "Croissant", "price": 2.50 }
"leaderboard:game1"         →   { "rank1": "Alice", "score": 9850 }

Why DynamoDB is the default AWS key-value store choice:

• Single-digit millisecond latency at any scale
• Fully serverless — no servers to manage
• Automatically scales from 0 to millions of requests/second
• Used by Amazon Prime Day, Alexa, Amazon.com

Comparison of NoSQL Types

4. Demo — Create a DynamoDB Table

What Is DynamoDB?

Amazon DynamoDB is a fully serverless, managed, distributed NoSQL service with single-digit millisecond performance at any scale. Launched in 2012 by AWS, it powers workloads like Amazon Prime Day (trillions of API calls), with tables exceeding 200 TB and processing more than one billion requests per hour.

DynamoDB Internal Architecture

graph TB
    App[Application] --> DAX[DynamoDB Accelerator DAX - In-memory cache - Microsecond latency]
    DAX --> DDB[Amazon DynamoDB]
    App --> DDB

    DDB --> AZ1["(Replica AZ-1a Primary)"]
    DDB --> AZ2["(Replica AZ-1b)"]
    DDB --> AZ3["(Replica AZ-1c)"]

    DDB --> Streams[DynamoDB Streams - Change Data Capture]
    Streams --> Lambda[AWS Lambda - Event-driven triggers]

    DDB --> S3[Export to S3 - Analytics / ML]
    DDB --> Redshift[Zero-ETL to Redshift - SQL analytics]

By default, DynamoDB replicates data across 3 Availability Zones → 99.99% SLA (single-region), and 99.999% with Global Tables.

Core DynamoDB Concepts

Steps in the AWS Console

Step 1 — Access the Service

1. Search for DynamoDB in the AWS search bar
2. Click Create table

Step 2 — Name the Table and Define Keys

For the Aurora’s bakery example:

The partition key determines which physical shard the item is stored on. The sort key allows sorting and filtering items within the same partition.

Step 3 — Table Class

Step 4 — Read/Write Capacity Settings

graph LR
    Capacity[Capacity Mode] --> Provisioned[Provisioned Capacity]
    Capacity --> OnDemand[On-Demand Capacity]

    Provisioned --> P1["Define in advance - WCU Write Capacity Units - RCU Read Capacity Units\n1 WCU = 1 write/s of 1 KB - 1 RCU = 1 strong read/s of 4 KB"]
    Provisioned --> P2["Auto Scaling available - Predictable pricing - Ideal for stable traffic"]

    OnDemand --> O1["DynamoDB scales automatically - No planning required - Pay-per-request"]
    OnDemand --> O2["Ideal for unpredictable traffic - New workloads - Traffic spikes"]

Cost calculation (provisioned):

WCU required = item size (KB, rounded up) x writes/second
RCU required = item size (rounded to 4 KB) / 4 x reads/second

Example:
- 2 KB items, 100 writes/second -> 2 WCU x 100 = 200 WCU
- 6 KB items, 50 reads/second (strong) -> ceil(6/4) x 50 = 2 x 50 = 100 RCU

Step 5 — Secondary Indexes

DynamoDB Technical Characteristics

DynamoDB Streams and Serverless Integrations

sequenceDiagram
    participant App as Application
    participant DDB as DynamoDB
    participant Stream as DynamoDB Streams
    participant Lambda as AWS Lambda
    participant SNS as Amazon SNS

    App->>DDB: PutItem / UpdateItem / DeleteItem
    DDB->>Stream: CDC change event record
    Stream->>Lambda: Automatic trigger
    Lambda->>SNS: Order received notification
    Lambda->>DDB: Update another table

5. RDS vs DynamoDB — Detailed Comparison

Full Comparison Table

When to Use RDS?

Choose RDS if:

• Data has a defined and stable structure
• Tables need to be joined (frequent JOINs)
• Developers know SQL
• Strict ACID compliance required (e.g., complex financial transactions)
• Reporting and analytics with complex SQL queries
• Migration from an existing on-premises database

Examples: ERP system, banking application, CRM, e-commerce with structured catalog

When to Use DynamoDB?

Choose DynamoDB if:

• Need latency < 10 ms at large scale
• Variable or semi-structured data
• Serverless architecture (Lambda + DynamoDB)
• Unpredictable traffic or large spikes
• No complex SQL queries (no JOINs)
• Global scale required (multi-region Global Tables)

Examples: shopping cart (Amazon.com), video game leaderboards, user sessions, IoT metadata

Decision Diagram — Which Service to Choose?

flowchart TD
    Q1{Is the data structure\nfixed and relational?}
    Q1 -->|Yes| Q2{Need complex JOINs\nor SQL?}
    Q1 -->|No| Q3{Need latency\nbelow 10 ms at large scale?}

    Q2 -->|Yes| Q4{Very high-volume workload?}
    Q2 -->|No| RDS[Amazon RDS]

    Q3 -->|Yes| DDB[Amazon DynamoDB]
    Q3 -->|No| Q5{Data type?}

    Q4 -->|Yes - billions of records| Aurora[Amazon Aurora]
    Q4 -->|No - standard| RDS

    Q5 -->|Relationship graph| Neptune[Amazon Neptune]
    Q5 -->|JSON documents| DocumentDB[Amazon DocumentDB]
    Q5 -->|Wide column IoT| Keyspaces[Amazon Keyspaces]

6. Amazon Aurora

What Is Amazon Aurora?

Amazon Aurora is a proprietary AWS database engine, fully managed and cloud-native. It is compatible with MySQL and PostgreSQL — meaning existing code, tools, and drivers work without modification.

Aurora is an engine option within Amazon RDS, but with a radically different storage architecture.

• Delivers up to 5x the throughput of standard MySQL and 6x the throughput of standard PostgreSQL
• The cluster volume can grow up to 128 TiB automatically
• Simplifies and automates clustering and replication — the most difficult aspects of database administration

Aurora Architecture — Distributed Storage

graph TB
    subgraph Aurora Cluster
        Writer[Writer Instance AZ-1a] --> Storage
        Reader1[Reader Instance AZ-1b] --> Storage
        Reader2[Reader Instance AZ-1c] --> Storage
        Reader3[Reader Instance AZ-2a] --> Storage
    end

    subgraph Storage["Aurora Cluster Volume - Distributed Storage"]
        S1["(Copy 1 AZ-1a)"] --- S2["(Copy 2 AZ-1a)"]
        S3["(Copy 3 AZ-1b)"] --- S4["(Copy 4 AZ-1b)"]
        S5["(Copy 5 AZ-1c)"] --- S6["(Copy 6 AZ-1c)"]
    end

    App[Application] --> ClusterEndpoint[Cluster Endpoint Writer]
    App --> ReaderEndpoint[Reader Endpoint Load-balanced]

Key architecture points:

• 6 copies of data in 3 Availability Zones — fault-tolerant by design
• Storage starts at 10 GB and automatically expands in 10 GB increments up to 128 TiB
• Aurora cluster volume = virtual volume shared by all nodes (writer + readers)
• Data is not copied between instances — all read from the same volume

Performance and Limits

Aurora Serverless v2

Aurora offers a Serverless v2 mode that automatically scales capacity in fractions of ACUs (Aurora Capacity Units):

graph LR
    Traffic[Variable application traffic] --> AS2[Aurora Serverless v2]
    AS2 --> Scale["Auto-scaling\n0.5 ACU → 128 ACU\nIn seconds"]
    Scale --> Cost["Pay-as-you-go\nper ACU-hour"]

Aurora Serverless v2 advantages:

• Scales in fractions of ACUs (more granular than v1)
• Supports Multi-AZ connections
• No significant cold-start time
• Ideal for: variable workloads, development, multi-tenant SaaS

Comparison: Aurora vs. Standard RDS

Aurora Global Database

graph TB
    subgraph Primary["Primary Region us-east-1"]
        PW[Writer Instance] --> PStorage["(Primary Cluster Volume)"]
        PR1[Reader 1] --> PStorage
        PR2[Reader 2] --> PStorage
    end

    subgraph Secondary1["Secondary Region 1 eu-west-1"]
        SR1[Reader Instance] --> SStorage1["(Replicated Cluster Volume)"]
    end

    subgraph Secondary2["Secondary Region 2 ap-southeast-1"]
        SR2[Reader Instance] --> SStorage2["(Replicated Cluster Volume)"]
    end

    PStorage -->|Async replication less than 1 second lag| SStorage1
    PStorage -->|Async replication less than 1 second lag| SStorage2

Aurora Global Database:

• Up to 5 secondary read regions
• Typical replication lag: < 1 second
• RPO = 1 second, RTO < 1 minute in case of region failover
• Use case: global applications, multi-region DR resilience

When to Choose Aurora?

Choose Aurora if:

• MySQL or PostgreSQL workloads in large-scale production
• Need for high availability with fast failover (< 30s)
• Many concurrent reads (> 5 read replicas needed)
• Global multi-region architecture (Aurora Global Database)
• Variable automatic scalability (Aurora Serverless v2)

Choose standard RDS if:

• Need Oracle, SQL Server, MariaDB, or IBM Db2
• Limited budget on small workloads
• Configuration simplicity is a priority

7. Code Samples — AWS CLI, SQL, boto3

7.1 AWS CLI — Amazon RDS

# List RDS instances
aws rds describe-db-instances \
    --query 'DBInstances[*].[DBInstanceIdentifier,DBInstanceStatus,Endpoint.Address]' \
    --output table

# Create an RDS MySQL instance (Free Tier)
aws rds create-db-instance \
    --db-instance-identifier bakery-orders-db \
    --db-instance-class db.t3.micro \
    --engine mysql \
    --engine-version 8.0 \
    --master-username admin \
    --master-user-password "MySecureP@ssw0rd!" \
    --allocated-storage 20 \
    --storage-type gp2 \
    --no-multi-az \
    --no-publicly-accessible \
    --backup-retention-period 7 \
    --tags Key=Environment,Value=dev

# Create a manual snapshot
aws rds create-db-snapshot \
    --db-instance-identifier bakery-orders-db \
    --db-snapshot-identifier bakery-orders-db-snapshot-20240115

# Delete an instance (with final snapshot)
aws rds delete-db-instance \
    --db-instance-identifier bakery-orders-db \
    --final-db-snapshot-identifier bakery-orders-db-final-snapshot

# View CloudWatch metrics for RDS
aws cloudwatch get-metric-statistics \
    --namespace AWS/RDS \
    --metric-name CPUUtilization \
    --dimensions Name=DBInstanceIdentifier,Value=bakery-orders-db \
    --start-time 2024-01-01T00:00:00Z \
    --end-time 2024-01-01T23:59:59Z \
    --period 300 \
    --statistics Average

7.2 SQL — Queries for Aurora’s Bakery

-- Create the database
CREATE DATABASE aurora_bakery CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE aurora_bakery;

-- Customers table
CREATE TABLE Customers (
    CustomerID   INT          NOT NULL AUTO_INCREMENT,
    Name         VARCHAR(100) NOT NULL,
    Email        VARCHAR(150) UNIQUE,
    Phone        VARCHAR(20),
    ShippingAddr VARCHAR(255),
    CreatedAt    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (CustomerID)
);

-- Products table
CREATE TABLE Products (
    ProductID   INT           NOT NULL AUTO_INCREMENT,
    ProductName VARCHAR(200)  NOT NULL,
    Category    VARCHAR(50)   NOT NULL,
    Price       DECIMAL(8,2)  NOT NULL,
    StockQty    INT           DEFAULT 0,
    PRIMARY KEY (ProductID),
    INDEX idx_category (Category)
);

-- Orders table (with foreign keys)
CREATE TABLE Orders (
    OrderID     INT           NOT NULL AUTO_INCREMENT,
    CustomerID  INT           NOT NULL,
    ProductID   INT           NOT NULL,
    Quantity    INT           DEFAULT 1,
    UnitPrice   DECIMAL(8,2)  NOT NULL,
    OrderDate   DATETIME      DEFAULT CURRENT_TIMESTAMP,
    Status      ENUM('pending','confirmed','shipped','delivered') DEFAULT 'pending',
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (ProductID)  REFERENCES Products(ProductID),
    INDEX idx_customer  (CustomerID),
    INDEX idx_orderdate (OrderDate)
);

-- Insert test data
INSERT INTO Customers (Name, Email, Phone, ShippingAddr) VALUES
    ('Alice Thompson', 'alice@example.com', '514-555-0101', '123 Main Street, Montreal'),
    ('Bob Harrison',   'bob@example.com',   '514-555-0202', '456 Maple Ave, Laval');

INSERT INTO Products (ProductName, Category, Price, StockQty) VALUES
    ('Butter Croissant',  'Pastry',   2.50, 48),
    ('Sourdough Loaf 750g', 'Bread',  8.00, 12),
    ('Apple Pie',          'Pastry', 14.00,  6);

INSERT INTO Orders (CustomerID, ProductID, Quantity, UnitPrice) VALUES
    (1, 1, 4, 2.50),
    (1, 3, 1, 14.00),
    (2, 2, 2, 8.00);

-- JOIN query - Order details
SELECT
    o.OrderID,
    c.Name        AS Customer,
    p.ProductName,
    o.Quantity,
    o.UnitPrice,
    (o.Quantity * o.UnitPrice) AS Total,
    o.OrderDate,
    o.Status
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products  p ON o.ProductID  = p.ProductID
ORDER BY o.OrderDate DESC;

-- Revenue by category
SELECT
    p.Category,
    COUNT(o.OrderID)               AS NumOrders,
    SUM(o.Quantity)                AS UnitsSold,
    SUM(o.Quantity * o.UnitPrice)  AS TotalRevenue
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY p.Category
ORDER BY TotalRevenue DESC;

-- Customers with multiple orders
SELECT
    c.CustomerID,
    c.Name,
    c.Email,
    COUNT(o.OrderID)               AS NumOrders,
    SUM(o.Quantity * o.UnitPrice)  AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name, c.Email
HAVING NumOrders > 1
ORDER BY TotalSpent DESC;

7.3 AWS CLI — Amazon DynamoDB

# Create a DynamoDB table
aws dynamodb create-table \
    --table-name Auroras-bakery \
    --attribute-definitions \
        AttributeName=OrderID,AttributeType=N \
        AttributeName=ProductType,AttributeType=S \
    --key-schema \
        AttributeName=OrderID,KeyType=HASH \
        AttributeName=ProductType,KeyType=RANGE \
    --billing-mode PAY_PER_REQUEST \
    --table-class STANDARD \
    --tags Key=Project,Value=aurora-bakery

# Insert an item
aws dynamodb put-item \
    --table-name Auroras-bakery \
    --item '{
        "OrderID":      {"N": "1001"},
        "ProductType":  {"S": "Pies"},
        "CustomerName": {"S": "Alice Thompson"},
        "Quantity":     {"N": "2"},
        "UnitPrice":    {"N": "14.00"},
        "OrderDate":    {"S": "2024-01-15T10:30:00Z"},
        "Status":       {"S": "confirmed"}
    }'

# Read an item by primary key (GetItem)
aws dynamodb get-item \
    --table-name Auroras-bakery \
    --key '{
        "OrderID":     {"N": "1001"},
        "ProductType": {"S": "Pies"}
    }'

# Query by partition key (Query)
aws dynamodb query \
    --table-name Auroras-bakery \
    --key-condition-expression "OrderID = :oid" \
    --expression-attribute-values '{":oid": {"N": "1001"}}'

# Scan with filter
aws dynamodb scan \
    --table-name Auroras-bakery \
    --filter-expression "Status = :s" \
    --expression-attribute-values '{":s": {"S": "confirmed"}}'

# List tables
aws dynamodb list-tables

# Describe a table
aws dynamodb describe-table --table-name Auroras-bakery

# Delete an item
aws dynamodb delete-item \
    --table-name Auroras-bakery \
    --key '{
        "OrderID":     {"N": "1001"},
        "ProductType": {"S": "Pies"}
    }'

7.4 Python boto3 — DynamoDB

import boto3
from boto3.dynamodb.conditions import Key, Attr
from decimal import Decimal
import json

# Initialize the DynamoDB client
dynamodb = boto3.resource('dynamodb', region_name='us-east-1')
table = dynamodb.Table('Auroras-bakery')

# ─── CREATE (PutItem) ────────────────────────────────────────────────────────
def create_order(order_id, product_type, customer, quantity, unit_price):
    """Create a new order in DynamoDB."""
    item = {
        'OrderID':      order_id,
        'ProductType':  product_type,
        'CustomerName': customer,
        'Quantity':     quantity,
        'UnitPrice':    Decimal(str(unit_price)),
        'Total':        Decimal(str(quantity * unit_price)),
        'Status':       'pending'
    }
    return table.put_item(Item=item)

# ─── READ (GetItem) ──────────────────────────────────────────────────────────
def get_order(order_id, product_type):
    """Retrieve an order by its primary key."""
    response = table.get_item(
        Key={
            'OrderID':     order_id,
            'ProductType': product_type
        }
    )
    return response.get('Item')

# ─── QUERY (by partition key) ────────────────────────────────────────────────
def get_orders_by_id(order_id):
    """Retrieve all lines of an order (all ProductTypes)."""
    response = table.query(
        KeyConditionExpression=Key('OrderID').eq(order_id)
    )
    return response.get('Items', [])

# ─── UPDATE (UpdateItem) ─────────────────────────────────────────────────────
def update_order_status(order_id, product_type, new_status):
    """Update the status of an order."""
    table.update_item(
        Key={
            'OrderID':     order_id,
            'ProductType': product_type
        },
        UpdateExpression='SET #s = :status',
        ExpressionAttributeNames={'#s': 'Status'},  # 'Status' is a reserved word
        ExpressionAttributeValues={':status': new_status}
    )

# ─── DELETE (DeleteItem) ─────────────────────────────────────────────────────
def delete_order(order_id, product_type):
    """Delete an order."""
    table.delete_item(
        Key={
            'OrderID':     order_id,
            'ProductType': product_type
        }
    )

# ─── SCAN with filter ────────────────────────────────────────────────────────
def get_pending_orders():
    """Scan all orders with 'pending' status."""
    response = table.scan(
        FilterExpression=Attr('Status').eq('pending')
    )
    return response.get('Items', [])

# ─── TRANSACTIONS (ACID) ─────────────────────────────────────────────────────
def atomic_order_and_inventory_update(order_id, product_type, customer,
                                       quantity, unit_price):
    """
    Atomic transaction: create the order AND update inventory.
    If either operation fails, both are rolled back.
    """
    client = boto3.client('dynamodb', region_name='us-east-1')
    client.transact_write_items(
        TransactItems=[
            {
                'Put': {
                    'TableName': 'Auroras-bakery',
                    'Item': {
                        'OrderID':      {'N': str(order_id)},
                        'ProductType':  {'S': product_type},
                        'CustomerName': {'S': customer},
                        'Quantity':     {'N': str(quantity)},
                        'UnitPrice':    {'N': str(unit_price)},
                        'Status':       {'S': 'confirmed'}
                    },
                    'ConditionExpression': 'attribute_not_exists(OrderID)'
                }
            },
            {
                'Update': {
                    'TableName': 'Products-inventory',
                    'Key': {'ProductType': {'S': product_type}},
                    'UpdateExpression': 'SET StockQty = StockQty - :qty',
                    'ExpressionAttributeValues': {':qty': {'N': str(quantity)}},
                    'ConditionExpression': 'StockQty >= :qty'
                }
            }
        ]
    )

# ─── Usage example ───────────────────────────────────────────────────────────
if __name__ == '__main__':
    create_order(1001, 'Pies', 'Alice Thompson', 2, 14.00)
    print("Order 1001 created")

    order = get_order(1001, 'Pies')
    print(f"Order: {json.dumps(order, default=str, indent=2)}")

    update_order_status(1001, 'Pies', 'shipped')
    print("Status updated -> shipped")

    pending = get_pending_orders()
    print(f"{len(pending)} orders pending")

7.5 Python boto3 — Amazon RDS

import boto3

def create_rds_instance(identifier, password):
    """Create an RDS MySQL Free Tier instance."""
    rds = boto3.client('rds', region_name='us-east-1')
    response = rds.create_db_instance(
        DBInstanceIdentifier=identifier,
        DBInstanceClass='db.t3.micro',
        Engine='mysql',
        EngineVersion='8.0',
        MasterUsername='admin',
        MasterUserPassword=password,
        AllocatedStorage=20,
        StorageType='gp2',
        MultiAZ=False,
        PubliclyAccessible=False,
        BackupRetentionPeriod=7,
        Tags=[
            {'Key': 'Environment', 'Value': 'dev'},
            {'Key': 'Project',     'Value': 'aurora-bakery'}
        ]
    )
    return response['DBInstance']

def get_instance_endpoint(identifier):
    """Retrieve the endpoint of an RDS instance."""
    rds = boto3.client('rds', region_name='us-east-1')
    response = rds.describe_db_instances(DBInstanceIdentifier=identifier)
    instances = response['DBInstances']
    if instances and instances[0].get('Endpoint'):
        return instances[0]['Endpoint']['Address']
    return None

def list_instances():
    """List all RDS instances with their status."""
    rds = boto3.client('rds', region_name='us-east-1')
    response = rds.describe_db_instances()
    return [
        {
            'id':       db['DBInstanceIdentifier'],
            'engine':   db['Engine'],
            'status':   db['DBInstanceStatus'],
            'class':    db['DBInstanceClass'],
            'endpoint': db.get('Endpoint', {}).get('Address', 'N/A')
        }
        for db in response['DBInstances']
    ]

8. Quick Reference and Final Recap

Overview of AWS Database Services

graph TB
    subgraph Relational["Relational Databases SQL"]
        RDS[Amazon RDS\nMySQL PG MariaDB Oracle SQL Server Db2]
        Aurora[Amazon Aurora\nMySQL and PG cloud-native\n5-6x performance]
    end

    subgraph NonRelational["Non-Relational Databases NoSQL"]
        DDB[Amazon DynamoDB\nKey-Value plus Document\nServerless]
        DocumentDB[Amazon DocumentDB\nDocument Store\nMongoDB-compatible]
        Neptune[Amazon Neptune\nGraph Database\nGremlin plus SPARQL]
        Keyspaces[Amazon Keyspaces\nWide Column\nCassandra-compatible]
        ElastiCache[Amazon ElastiCache\nIn-memory cache\nRedis plus Memcached]
    end

    subgraph Analytics["Analytics and Data Warehouse"]
        Redshift[Amazon Redshift\nData Warehouse\nPetabyte scale]
    end

Key Characteristics Summary

Important CloudWatch Metrics for RDS

Important CloudWatch Metrics for DynamoDB

Key Takeaways — Final Summary

┌──────────────────────────────────────────────────────────────────────┐
│  RELATIONAL DATABASES (SQL)                                          │
│  • Tables linked by primary/foreign keys                             │
│  • ACID properties guaranteed                                        │
│  • SQL as the query language                                         │
│  • RDS supports 7 engines (MySQL, PG, MariaDB, Oracle, SQL Svr, Db2) │
│  • Aurora = MySQL/PG cloud-native with 5-6x performance              │
├──────────────────────────────────────────────────────────────────────┤
│  NON-RELATIONAL DATABASES (NoSQL)                                    │
│  • 3 main types: Graph, Document, Key-Value                          │
│  • Flexible schema, unstructured/semi-structured data supported      │
│  • DynamoDB = key-value + document, serverless, single-digit ms      │
│  • DynamoDB: SLA 99.99% (region) / 99.999% (Global Tables)           │
├──────────────────────────────────────────────────────────────────────┤
│  CHOOSING THE RIGHT SERVICE                                          │
│  • Structured data + JOINs → RDS                                     │
│  • Large scale + latency < 10 ms → DynamoDB                          │
│  • MySQL/PG high performance in production → Aurora                  │
│  • Relationship graphs → Neptune                                     │
│  • Flexible JSON documents → DocumentDB                              │
│  • High-speed cache → ElastiCache                                    │
└──────────────────────────────────────────────────────────────────────┘

Useful Links

Search Terms

aws · database · fundamentals · core · services · amazon · web · dynamodb · rds · aurora · architecture · comparison · boto3 · choose · cli · databases · nosql · service · access · bakery · characteristics · cloudwatch · concepts · configuration