Saturday, June 6, 2026

Cohort 3 | Project CloudIgnite Topics: Amazon RDS (MySQL), Amazon Aurora, Amazon DynamoDB, SQL JOIN Operations, Database Normalization Duration: ~3 hours

Key Takeaways

• Amazon RDS: fully managed MySQL/PostgreSQL/Oracle/SQL Server; AWS handles OS, patching, backups
• RDS Security Group = bridge between EC2 and RDS (source = Web Security Group, not IP)
• DB Subnet Group: defines which private subnets RDS launches into; different from a regular subnet
• Aurora: AWS-native; automatic Multi-AZ replication; Writer endpoint (read/write) vs Reader endpoint (read-only)
• DynamoDB: NoSQL, schema-flexible, serverless; Partition Key + optional Sort Key; Query (fast, uses key) vs Scan (slow, reads all)
• SQL JOINs: INNER (matching only), LEFT (all left + matching right), RIGHT (opposite), FULL (all), CROSS (Cartesian product)
• Database normalization: separate tables eliminate redundancy and prevent data anomalies
• AWS DMS: migrate on-prem databases to AWS; mysqldump for small DBs (upload to S3, import to RDS)

Table of Contents

1. Amazon RDS — Lab 160 (MySQL)
2. Amazon Aurora — Lab 162 & 274
3. Amazon DynamoDB
4. SQL JOIN Operations
5. Database Normalization
6. Database Migration to AWS
7. CLF-C02 Exam Relevance

1. Amazon RDS — Lab 160 (MySQL)

Overview

Amazon Relational Database Service (RDS) is a managed service for launching and operating relational databases in the cloud. Lab 160 focused on launching a MySQL RDS instance and connecting to it from an EC2 instance.

Step-by-Step: Setting Up RDS (MySQL)

Step 1 — Create a Security Group (in VPC)

• Navigate to: VPC → Security Groups → Create Security Group
• Name it: DB Security Group
• VPC: Must select Lab VPC (not Default)
• Outbound rules: No changes needed
• Inbound rules: Add one rule:
  • Type: MySQL/Aurora
  • Source type: Custom
  • Source: select the Web Security Group (the one attached to your EC2 instance)

Why this matters: The security group acts as the bridge/link between the RDS database and the EC2 instance. Only EC2 instances that have the Web Security Group attached will be able to connect to the database. Any EC2 with a different security group will be blocked.

Step 2 — Create a DB Subnet Group (in RDS)

• Navigate to: RDS → Subnet Groups → Create DB Subnet Group
• Name: db-subnet-group
• VPC: Lab VPC
• Availability Zones: Select first two AZs
• Subnets: Select Private Subnet 1 and Private Subnet 2

Key distinction:

• Subnet → belongs to a VPC
• DB Subnet Group → belongs to RDS; determines which subnet the DB instance is launched into

Step 3 — Create the RDS Database (MySQL)

Step 4 — Connect to RDS from EC2 (Lab 162 flow)

# SSH into EC2 instance
ssh -i labuser.pem ec2-user@<EC2-PUBLIC-IP>

# Install MySQL client
sudo yum update
sudo yum install mysql

# Connect to RDS
mysql -u main -p -h <RDS-ENDPOINT>
# Enter password: labpassword

Step 5 — Connect via Browser (Lab 160 flow)

• Copy the RDS Endpoint from the AWS console
• Open the web app, enter: endpoint, database world, username main, password labpassword
• You can add, view, and delete records directly from the browser

2. Amazon Aurora — Lab 162 & 274

What is Aurora?

Amazon Aurora is an AWS-built, MySQL/PostgreSQL-compatible relational database engine. It is part of the RDS family but uses a different architecture — it handles high availability and replication differently from standard MySQL.

Aurora vs. MySQL — Key Differences

Important: Do NOT manually create an Aurora Replica when launching Aurora — Aurora handles this automatically.

Writer vs. Reader Endpoint

• Writer endpoint: Use for all read and write operations (INSERT, UPDATE, DELETE, SELECT)
• Reader endpoint: Used for read-only queries (SELECT only); cannot create tables or insert data

Aurora Setup Steps (same pre-requisites as MySQL)

1. Create DB Security Group in VPC (same process as MySQL)
2. Create DB Subnet Group in RDS (same process as MySQL)
3. Create database: Aurora MySQL-Compatible
   • Engine mode: Provisioned
   • Instance class: db.t3.medium
   • DB Cluster Identifier: any name (this is your "server name")
   • Initial Database Name: any name (this is one database inside the cluster)
   • Multi-AZ: Do not create Aurora Replica — Aurora manages this
   • VPC: Lab VPC
   • Security Group: DB Security Group (remove default)
   • Disable: Enhanced monitoring, Performance Insights, minor version upgrades

Cluster vs. Database: One Aurora cluster = one server. Inside one cluster, you can have multiple databases.

Connecting to Aurora

# After MySQL client is installed on EC2:
mysql -u admin -p -h <WRITER-ENDPOINT>
# Enter password

# Show databases
SHOW DATABASES;

# Use a specific database
USE <database_name>;

3. Amazon DynamoDB

What is DynamoDB?

Amazon DynamoDB is a fully managed NoSQL key-value and document database. It is serverless, highly scalable, and schema-flexible — equivalent to MongoDB in the open-source world.

Key Concepts

DynamoDB Lab Walkthrough

Create a Table

• Navigate to: DynamoDB → Tables → Create Table
• Table name: Music
• Partition key: Artist (String)
• Sort key: Song (String)

Add Items (Records)

• Go inside the table → Actions → Create Item
• Add required keys (Artist, Song) + optional attributes (Album, Year, Genre)
• Key insight: DynamoDB is schema-flexible — different items can have different attributes (e.g., one item has Year, another has a typo Lbam instead of Album) and it will still work without errors.

Query vs. Scan

Scan example: Filter by Year = 1971 (Number type) → returns only matching records.

Delete a Table

• DynamoDB → Tables → select table → Delete → confirm

DynamoDB Flexibility (Schema-less)

Unlike relational databases, DynamoDB does not enforce a schema per row. Each item can have its own set of attributes. This makes it excellent for unstructured or rapidly changing data models.

4. SQL JOIN Operations

Practiced using SQLite browser with a world.db database containing cities, countries, and continents tables.

The Core Idea

JOINs combine rows from two or more tables based on a related column (usually a shared ID/key).

Types of JOINs

INNER JOIN

• Returns only matching rows from both tables
• If no match → row is excluded entirely

SELECT cities.city_name, countries.country_name
FROM cities
INNER JOIN countries ON cities.country_id = countries.country_id;

LEFT JOIN

• Returns all rows from the left table + matching rows from the right table
• No match on right side → NULL appears for right-side columns

SELECT cities.city_name, countries.country_name
FROM cities
LEFT JOIN countries ON cities.country_id = countries.country_id;

RIGHT JOIN

• Returns all rows from the right table + matching rows from the left table
• No match on left side → NULL appears for left-side columns

FULL OUTER JOIN

• Returns everything: matching rows + unmatched rows from both left and right tables
• Unmatched sides show NULL

CROSS JOIN

• Returns every possible combination of rows from both tables (Cartesian product)
• No ON condition needed
• ⚠️ Avoid on large tables — 1,000 rows × 1,000 rows = 1,000,000 rows output

SELECT * FROM cities CROSS JOIN countries;

SELF JOIN / AUTO JOIN

• Joins a table with itself; returns matching rows within the same table

JOIN Summary Table

Important: NULL ≠ NULL

-- This will NOT match:
WHERE continent_id = NULL  -- incorrect

-- NULLs in JOIN columns do not match each other
-- A LEFT JOIN will show the row from the left table but the right side will be NULL

Joining 3 Tables

SELECT cities.city_name, countries.country_name, continents.continent_name
FROM cities
INNER JOIN countries ON cities.country_id = countries.country_id
INNER JOIN continents ON countries.continent_id = continents.continent_id;

5. Database Normalization

Why Multiple Small Tables Instead of One Big Table?

If you stored all city, country, and continent info in one big table:

• A country with 100 cities → country info (name, population, surface area, political structure) stored 100 times
• If someone updates population in one row but not others → data inconsistency (Tokyo and Kyoto show different populations for the same country)

Benefits of Normalization

• Eliminates redundant data — store each fact in exactly one place
• Prevents anomalies — update one record, it reflects everywhere
• Saves storage — reference IDs instead of duplicating full data
• Maintains data integrity — no conflicting values for the same entity

Bottom line: Store country_id in the city table, not the full country record. Use JOINs to retrieve the combined data when needed.

6. Database Migration to AWS

Came up as a question: "In the real world, if a company already has a DB on-premise, how do you import it?"

AWS Database Migration Service (DMS)

• AWS managed service for migrating existing on-premise databases to AWS
• Install an AWS application on your on-premise machine → AWS copies all data to AWS cloud
• Best for: large databases (hundreds of GB+)
• Supports continuous replication — migration happens while the old DB is still live

AWS Application Migration Service (MGN)

• Used for migrating full infrastructure (servers, code, applications) to AWS — not just databases

Manual Migration (Small Databases)

• Use mysqldump to export your entire database to a dump file
• Upload the dump file to Amazon S3
• Import the data from S3 into your new RDS/Aurora instance
• Best for: small databases (a few GB or less)

CLF-C02 Exam Relevance

The following topics from today's lecture are directly relevant to the AWS Certified Cloud Practitioner (CLF-C02) exam. They fall primarily under Domain 3: Cloud Technology and Services.

Key CLF-C02 Concepts to Remember

• RDS = managed relational database (SQL); you don't manage the underlying OS
• DynamoDB = managed NoSQL database; serverless, scales automatically
• Aurora = AWS proprietary engine; MySQL/PostgreSQL-compatible, higher performance and availability than standard RDS
• Multi-AZ = high availability (automatic failover to standby); different from Read Replicas (performance/scaling)
• Security Groups = stateful virtual firewall applied at the instance/resource level
• Private Subnet = no direct internet access; used for databases for security
• AWS DMS = Database Migration Service for moving existing DBs to AWS

Notes compiled from lecture transcript — June 6, 2026 | AWS re/Start Cohort 3: Project CloudIgnite