Govern a MySQL Schema Change on AWS

Govern a private RDS MySQL schema change with integrity tests and cleanup.

Introduction

30 Second Summary

Regional dashboards depend on trustworthy geographic records. A misspelled field or a city tied to a country that does not exist can make every report harder to trust.

In this project, you will deliver change ticket WF-DB-001 against a private Amazon RDS for MySQL staging database in your own AWS account. Your evidence pack will make the complete change lifecycle reviewable.

What You'll Build

Your final demo lets a reviewer trace WF-DB-001 through a before-and-after schema comparison backed by a repeatable SQL runbook.

By the end of this project, you'll have:

  • A controlled database path that reaches private Amazon RDS for MySQL through an Amazon EC2 administration host.
  • A reviewable schema correction showing conitinent replaced by continent within a governed country-city model.
  • A recruiter-ready evidence pack that traces the full change lifecycle from business reason to verified cleanup.
  • Secret Mission: Rebuild the corrected schema for a two-sided foreign key test with fk_city_country that accepts Tokyo but rejects Atlantis.

Are there any prerequisites?

You need a personal AWS account with permission to create and delete the project's AWS resources.

The project assumes a default VPC plus basic terminal and SQL familiarity.

Before We Start

Before any hands-on work, this is your moment to commit to change ticket WF-DB-001 for the Market Intelligence Reporting Team. Success means correcting country.conitinent, enforcing city-to-country referential integrity, capturing acceptance evidence, accounting for potential AWS charges, and deleting every temporary SQL and AWS resource after evidence collection.

Provision a Controlled AWS Environment

Ticket WF-DB-001 needs an administration path that protects the database from direct public access. That path starts with a controlled host inside your personal AWS environment.

You will provision an Amazon EC2 administration host beside a private Amazon RDS for MySQL database. An IAM policy limits EC2 Instance Connect access to the project host.

In this step, get ready to:
  • Launch the controlled EC2 administration host.
  • Create the scoped EC2 Instance Connect policy.
  • Provision the private RDS for MySQL database.
Launch the controlled administration host

A security group controls which network traffic can reach your administration host. The inbound rule uses the regional managed prefix list so port 22 accepts traffic from EC2 Instance Connect.

AWS charges start here

EC2, EBS, RDS, storage, or backup usage can consume credits or generate charges. Free Tier eligibility depends on your account.

You committed to removing these resources after collecting your evidence. Keep the cleanup requirement visible throughout the project.

  • Go to the AWS Management Console sign-in page in your browser.
  • Sign in with the permitted personal IAM identity for this project.

You should reach the AWS Management Console without a permission warning.

  • Choose the AWS Region where you will build the project.
  • Record the selected Region as your AWS Region.

The Region selector should now show the location you recorded.

  • Use the console search bar to open Amazon EC2.
  • Select Security Groups under Network & Security.

You should see the security groups for the selected Region.

  • Choose Create security group.
  • Enter world-facts-eic-sg as the security group name.

The form should now identify the new group as world-facts-eic-sg.

  • Enter a description that identifies the group as the EC2 Instance Connect path.
  • Select the default VPC.

The selected VPC should be the default VPC in your chosen Region.

  • Add one inbound rule.
  • Set the inbound rule type to SSH.

The rule should show TCP port 22.

  • Set the rule source to the EC2 Instance Connect IPv4 managed prefix list for your Region.
  • Choose Create security group.

You should see world-facts-eic-sg with one inbound SSH rule sourced from the regional EC2 Instance Connect IPv4 managed prefix list.

Why use the managed prefix list?

The managed prefix list represents the regional EC2 Instance Connect service addresses. Using it limits the SSH rule to the service path used by this project.

The database receives its own private connection path later. Port 3306 stays off this public-facing group.

  • Select Instances in the EC2 console.
  • Choose Launch instances.

You should see the instance launch form.

  • Enter world-facts-admin as the instance name.
  • Select the standard Amazon Linux 2023 AMI.

The instance summary should show world-facts-admin with Amazon Linux 2023.

  • Select t3.micro as the instance type.
  • Select Proceed without a key pair under the key pair settings.

The launch summary should show instance type t3.micro with no key pair.

  • Edit the instance network settings.
  • Select the default VPC.

The network summary should match the VPC used by world-facts-eic-sg.

  • Enable a public IPv4 address for the administration host.
  • Choose the existing security group option.

The network summary should now include a public IPv4 address.

  • Select world-facts-eic-sg as the existing security group.
  • Choose Launch instance.

The instance can take a few minutes to initialize. A temporary pending state is expected while AWS starts the host.

  • Return to the EC2 instance list.
  • Select world-facts-admin.

You should see a public IPv4 address on the instance details page.

  • Wait until the instance state shows Running.
  • Record the instance ID as your EC2 instance ID.

The instance details should show world-facts-admin as Running with world-facts-eic-sg attached.

  • Open the AWS account menu.
  • Record the account ID as your 12-digit AWS account ID.
Create the scoped access policy

The console needs permission to send a temporary public key to the EC2 host. The policy restricts that permission to the project instance and the ec2-user Linux account.

  • Press the Windows key to open the Windows search bar.
  • Type Notepad and press Enter.

You should see a blank Notepad document.

  • Paste the policy template into Notepad:
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "ec2-instance-connect:SendSSHPublicKey",
      "Resource": "arn:aws:ec2:your-region:your-account-id:instance/your-instance-id",
      "Condition": {
        "StringEquals": {
          "ec2:osuser": "ec2-user"
        }
      }
    },
    {
      "Effect": "Allow",
      "Action": "ec2:DescribeInstances",
      "Resource": "*"
    }
  ]
}

What does this policy do?

  • The first statement permits ec2-instance-connect:SendSSHPublicKey for one EC2 instance.
  • The ec2:osuser condition limits the operating-system account to ec2-user.
  • The second statement permits ec2:DescribeInstances so the console can display the instance list.
  • Press Ctrl+Shift+S to open the Save As window.
  • Select Desktop as the save location.

The Save As window should now point to your Desktop.

  • Enter ec2-instance-connect-policy.json as the file name.
  • Select All files as the file type.

The file name should end with .json.

  • Choose Save.
  • Confirm the Notepad title bar shows ec2-instance-connect-policy.json.

Seeing a text file extension?

Reopen the Save As window if the title ends with .txt. Select All files before saving the exact ec2-instance-connect-policy.json filename.

Ask for help if the JSON does not save with the expected extension. Help me save this IAM policy as a JSON file in Windows Notepad.

  • Return to the AWS Management Console.
  • Use the console search bar to open IAM.

You should see the IAM dashboard for your account.

  • Select Policies.
  • Choose Create policy.

You should see the policy editor.

  • Select the JSON editor.
  • Paste the contents of ec2-instance-connect-policy.json into the editor.

The editor should show two policy statements.

  • Replace your-region in the resource ARN with your AWS Region.
  • Replace your-account-id with your 12-digit AWS account ID.

The ARN should now identify your Region and AWS account.

  • Replace your-instance-id with your EC2 instance ID.
  • Continue to the policy details page.

The policy editor should accept the JSON without a syntax warning.

  • Enter world-facts-eic-access as the policy name.
  • Choose Create policy.

You should see a confirmation that world-facts-eic-access was created.

  • Open the permissions page for the IAM identity used in this project.
  • Start the policy attachment flow.

You should see a list of policies that can be attached to the identity.

  • Search for world-facts-eic-access.
  • Select world-facts-eic-access.

The selected policy should show as the permission being added.

  • Confirm the policy attachment.
  • Return to the identity permissions summary.

You should see world-facts-eic-access in the identity's attached permissions.

✔️ Awesome, I've got everything!

Great. Keep ec2-instance-connect-policy.json saved on your Desktop as the reusable policy template.

ⓧ I'd like to double check the full code

Compare your complete ec2-instance-connect-policy.json file with this template:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "ec2-instance-connect:SendSSHPublicKey",
      "Resource": "arn:aws:ec2:your-region:your-account-id:instance/your-instance-id",
      "Condition": {
        "StringEquals": {
          "ec2:osuser": "ec2-user"
        }
      }
    },
    {
      "Effect": "Allow",
      "Action": "ec2:DescribeInstances",
      "Resource": "*"
    }
  ]
}
Create the private managed database

The database belongs on a private network path from the administration host. RDS can create the workload-specific security groups that connect the EC2 host to the database.

Database provisioning can take several minutes. Keep the console open until the DB instance reaches its ready state.

  • Use the AWS console search bar to open Amazon RDS.
  • Select Databases.

You should see the RDS database inventory for your selected Region.

  • Choose Create database.
  • Select Easy create.

The form should display the simplified database settings.

  • Select MySQL as the engine.
  • Select Free tier or Sandbox as shown for your account.

The configuration summary should show MySQL with the account-specific low-cost template you selected.

  • Enter world-facts-mysql as the DB instance identifier.
  • Enter dbadmin as the master username.

The database identity should now show world-facts-mysql with master username dbadmin.

The next value is a live credential. Use a new project-only password without placing it in code or screenshots.

  • Enter a new project-only password in the master password field.
  • Enter the same password in the confirmation field.

Both password fields should show masked characters.

  • Find Set up EC2 connection - optional.
  • Select Connect to an EC2 compute resource.

The connection settings should now offer EC2 resources from the selected VPC.

  • Select world-facts-admin as the EC2 compute resource.
  • Choose Create database.

RDS now provisions the database plus the EC2-to-RDS network path. This stage can remain in progress for several minutes.

  • Return to the RDS database inventory.
  • Select world-facts-mysql.

Before you check the details, which resource states should prove that the controlled path is ready?

  • Wait until the database status shows Available.
  • Open the Connectivity & security view.

You should see world-facts-admin listed as the connected EC2 resource.

  • Confirm public accessibility is disabled.
  • Record the endpoint as your RDS endpoint.

The connection details should show a private database endpoint with no public database access.

  • Return to the EC2 security group inventory.
  • Confirm the automatic connection created groups matching ec2-rds-<n> and rds-ec2-<n>.

That is the controlled path in place. Your EC2 host is running with scoped access while the RDS database remains private.

Your AWS staging environment is ready. Next, you will connect through world-facts-admin and verify the managed MySQL target before running any DDL.

Connect to the MySQL Target

Your private Amazon RDS for MySQL database is available behind the administration host you created earlier. The controlled path now needs to prove it can carry an authenticated database session.

EC2 Instance Connect gives you a browser terminal on the Amazon EC2 host without exposing the database to the internet. A single identity query confirms the server version plus the authenticated account before any DDL runs.

In this step, get ready to:
  • Connect to the administration host as ec2-user.
  • Install the MariaDB 10.5 command-line client.
  • Prove the MySQL target identity with an authenticated query.
Connect to the administration host

Your scoped IAM policy permits browser-based access to one EC2 instance plus one Linux user. This creates a controlled entry point for the private database path.

  • Enter EC2 in the AWS Management Console search bar.
  • Select EC2 from the search results.
  • Choose Instances.
  • Select world-facts-admin from the instance list.

Why use the administration host?

The RDS database has public accessibility disabled. The administration host provides the approved network path to its private endpoint.

  • Choose Connect.
  • Select the EC2 Instance Connect tab.
  • Choose Connect using a Public IP.
  • Verify that the displayed username is ec2-user.
  • Choose Connect to start the browser terminal.

You should see a browser terminal connected to world-facts-admin as ec2-user. That is the controlled entry point working.

Browser terminal not opening?

Confirm that world-facts-admin still shows Running. Check that you selected the EC2 Instance Connect tab plus the public IP connection option.

Verify that the displayed Linux user is ec2-user. Your scoped policy only permits that account.

Help me diagnose why EC2 Instance Connect cannot open the browser terminal.

Install the SQL client

The MariaDB 10.5 command-line client supplies the mysql command used to reach your RDS endpoint. A version check determines which setup path applies to the administration host.

  • Check the client state by running this command:
mysql --version

What does this command do?

The version check asks the installed mysql client to display its version information. Use the result to select the matching path below.

✔️ I see MariaDB 10.5

Your administration host already has the required client. The mysql command is ready for the private RDS connection.

ⓧ I see an older version

The displayed client is older than the project target. Install the mariadb105 package before connecting.

  • Approve the package installation if prompted.
  • Install the MariaDB 10.5 client by running this command:
sudo dnf install mariadb105

What does this command do?

The package manager installs the MariaDB 10.5 command-line client on world-facts-admin. The client provides the mysql command for connecting to RDS.

  • Verify the installed client by running this command:
mysql --version

What should the version check prove?

The output confirms that the mysql client is installed. You should see MariaDB version information that includes 10.5.

Client installation failing?

Confirm that the EC2 instance has network access for package downloads. Repeat the installation command after any temporary package download issue clears.

Help me troubleshoot the MariaDB client installation.

ⓧ Command not found

The administration host does not have the SQL client yet. Install the mariadb105 package to add it.

  • Approve the package installation if prompted.
  • Install the MariaDB 10.5 client by running this command:
sudo dnf install mariadb105

What does this command do?

The package manager installs the MariaDB 10.5 command-line client on world-facts-admin. The client provides the mysql command for connecting to RDS.

  • Verify the installed client by running this command:
mysql --version

What should the version check prove?

The output confirms that the mysql client is now available. You should see MariaDB version information that includes 10.5.

Client installation failing?

Confirm that the EC2 instance has network access for package downloads. Repeat the installation command after any temporary package download issue clears.

Help me troubleshoot the MariaDB client installation.

The administration host now has the client required to reach the managed MySQL server. Your next check proves that the private database accepts the intended account.

Authenticate to the private database

The RDS endpoint you recorded earlier identifies the private database host. The password prompt keeps the project credential out of the connection command.

  • Substitute the RDS endpoint you recorded earlier for <RDS-endpoint> in the command below.
  • Connect to world-facts-mysql as dbadmin by running this command:
mysql -h <RDS-endpoint> -P 3306 -u dbadmin -p

What does this command do?

  • The -h option points the client to your private RDS endpoint.
  • The -P 3306 option uses the MySQL database port.
  • The -u dbadmin option requests authentication as the RDS master user.
  • The -p option asks for the password interactively.
  • Enter the project-only database password when the client prompts you.

You should reach an authenticated MySQL prompt. This confirms that the browser terminal can traverse the private EC2-to-RDS path.

Before you run the identity check, do you expect both the required server version and the dbadmin account to appear? The result gives you the answer.

  • Confirm the server version plus authenticated account by running this query:
SELECT VERSION(), CURRENT_USER();

What does this query confirm?

  • The VERSION() result identifies the MySQL server version behind the RDS endpoint.
  • The CURRENT_USER() result identifies the account that MySQL used to authenticate the session.
  • MySQL 8.0.3 or newer supports the column-renaming syntax used in the approved schema change.

You should see a MySQL server version of 8.0.3 or newer plus an authenticated account containing dbadmin. You have now proved the target identity before changing its schema.

Connection or identity check failing?

Confirm that the command uses the endpoint recorded for world-facts-mysql. Verify that the DB instance still shows Available.

If authentication fails, reconnect with the project-only password plus the dbadmin username. Keep the password at the interactive prompt.

If the server version is older than 8.0.3, stop before running the next schema change. Confirm the MySQL engine version selected for world-facts-mysql.

Help me diagnose my private RDS connection or identity result.

Your authenticated session confirms the private network path plus the correct MySQL target. Next, you will reproduce the inherited country schema and capture its defect before making any changes.

Reproduce the Legacy Schema Defect

Your authenticated Amazon RDS for MySQL session now reaches the private database through the controlled Amazon EC2 path. That connection gives you a trustworthy place to reproduce the inherited defect.

Change control starts with evidence of the old schema contract. Reporting consumers need proof of the misspelled interface before any correction.

You will reproduce that contract inside an isolated staging database. The captured metadata becomes the baseline for ticket WF-DB-001.

In this step, get ready to:
  • Create an isolated staging database after a visible collision check.
  • Reproduce the inherited country schema with its intentional defect.
  • Capture the before-change definition and reporting-consumer risk.
Create the isolated staging database

A preflight check confirms that world_facts_lab does not already exist. This pause protects an unrelated database from being mistaken for your staging environment.

  • Press the Windows key to open the Windows search bar.
  • Type Notepad and press Enter to open it.
  • Select File in the Notepad menu.
  • Select Save as.
  • Choose your Desktop as the save location.
  • Enter world_schema_lifecycle.sql in the file name field.
  • Choose All files as the file type.
  • Select Save.

You should now see world_schema_lifecycle.sql in the Notepad title bar. This file becomes your repeatable implementation runbook.

  • Add the ticket header and preflight section to world_schema_lifecycle.sql by copying the following code:
-- Change ticket: WF-DB-001
-- Environment evidence
SELECT VERSION(), CURRENT_USER();

-- Preflight: this should return no rows before creation
SHOW DATABASES LIKE 'world_facts_lab';

Why a visible preflight?

  • The first query records the server version and authenticated database account.
  • The database lookup reveals whether the staging name is already in use.
  • A visible empty result forces a safety pause before creation.
  • Using IF NOT EXISTS could preserve an existing database whose structure you have not inspected.
  • Save world_schema_lifecycle.sql.
  • Copy the opening section from Notepad.
  • Switch back to the EC2 Instance Connect browser terminal from earlier.
  • Paste the copied section into the MySQL prompt.
  • Press Enter to execute the statements.

You should see your server and account evidence first. The lookup for world_facts_lab should return no row.

Does the staging database already exist?

  • Stop before running the creation statements.
  • Confirm whether the existing world_facts_lab belongs to an earlier attempt.
  • Remove the existing database only after confirming that it contains no work you need to keep.

Use this prompt if you need help interpreting the preflight result: Help me determine why world_facts_lab already exists before I continue.

  • Return to world_schema_lifecycle.sql in Notepad.
  • Add the database creation section below the preflight section:
CREATE DATABASE world_facts_lab;
SHOW DATABASES LIKE 'world_facts_lab';
USE world_facts_lab;

What does this section do?

  • The first statement creates the isolated staging database.
  • The second statement proves that the database now exists.
  • The final statement selects world_facts_lab for the table statements that follow.
  • Save world_schema_lifecycle.sql.
  • Copy the three new statements from Notepad.
  • Switch back to the MySQL prompt.
  • Paste the copied statements into the prompt.
  • Press Enter to execute them.

The database lookup should now list world_facts_lab. The client should also confirm that the database is selected.

Reproduce the inherited country table

The inherited table combines exact numeric measures with a primary key and a unique constraint. Its misspelled conitinent field deliberately recreates the interface that reporting consumers currently face.

Keeping the defect in this baseline makes the later DDL correction reviewable. Your before-change definition shows exactly what ticket WF-DB-001 starts from.

  • Return to world_schema_lifecycle.sql in Notepad.
  • Add the inherited table definition below the database creation section:
-- Reproduce the inherited schema contract before changing it
CREATE TABLE country (
    code CHAR(3) NOT NULL,
    name VARCHAR(100) NOT NULL,
    conitinent ENUM(
        'Asia',
        'Europe',
        'North America',
        'Africa',
        'Oceania',
        'Antarctica',
        'South America'
    ) NOT NULL DEFAULT 'Asia',
    region VARCHAR(100) NOT NULL,
    surface_area DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    independence_year SMALLINT NULL,
    population BIGINT NOT NULL DEFAULT 0,
    life_expectancy DECIMAL(4,1) NULL,
    gnp DECIMAL(15,2) NULL,
    gnp_old DECIMAL(15,2) NULL,
    local_name VARCHAR(100) NOT NULL,
    government_form VARCHAR(100) NOT NULL,
    head_of_state VARCHAR(100) NULL,
    code2 CHAR(2) NOT NULL,
    PRIMARY KEY (code),
    CONSTRAINT uq_country_code2 UNIQUE (code2)
) ENGINE=InnoDB;

What does this table model?

  • The code field gives each country a three-character primary identifier.
  • The uq_country_code2 constraint prevents duplicate two-character country codes.
  • The DECIMAL fields preserve exact fixed-point measures.
  • The BIGINT population field supports values beyond the smaller integer range.
  • The InnoDB engine supports the relationship added in the next step.
  • Save world_schema_lifecycle.sql.
  • Copy the inherited table section from Notepad.
  • Switch back to the MySQL prompt.
  • Paste the copied section into the prompt.
  • Press Enter to create the table.

The MySQL client should confirm that the table statement completed. Your staging database now contains the inherited contract.

Table creation did not complete?

  • Confirm that the earlier database-selection statement completed successfully.
  • Check that every quoted continent value matches the runbook.
  • Check that the final table definition ends after ENGINE=InnoDB.

Use this prompt with the message from your terminal: Help me debug the country table creation failure.

Capture the before-change evidence

Metadata queries turn the inherited defect into reviewable evidence. They show the table inventory as well as the column contract and complete table definition.

Before you run these checks, consider whether the risky field will appear only in the column list or in the complete table definition too.

  • Run the before-change metadata checks at the MySQL prompt by pasting the following statements:
SHOW TABLES;
SHOW COLUMNS FROM country;
SHOW COLUMNS FROM country LIKE 'conitinent';
SHOW CREATE TABLE country;

What do these checks prove?

  • The table inventory proves that country exists in the selected database.
  • The column output exposes each field with its type and key metadata.
  • The filtered column check isolates the misspelled contract field.
  • The complete definition preserves the engine and constraint details for review.

You should see country in the table list. The code field should be marked PRI.

You should also see conitinent in the column output. The complete definition should show uq_country_code2 and ENGINE=InnoDB.

Evidence output does not match?

  • Confirm that world_facts_lab is still the selected database.
  • Compare the table definition with the saved runbook for missing lines.
  • Check that the inherited field is spelled conitinent at this stage.

Use this prompt with the unexpected output: Help me compare my country schema with the expected before-change definition.

That baseline is now defensible. You can prove the defect existed before the approved correction.

  • Switch back to world_schema_lifecycle.sql in Notepad.
  • Add the four evidence queries to the bottom of the runbook by copying the block above.
  • Save world_schema_lifecycle.sql.
  • Record in your CHANGE_RECORD.md evidence notes that downstream consumers would inherit the misspelled conitinent interface without ticket WF-DB-001.

✔️ Awesome, I've got everything!

Your runbook now contains the complete before-change lifecycle. Your scoped access policy remains unchanged.

ⓧ I'd like to double check the full code

Here is the complete world_schema_lifecycle.sql file for this step.

-- Change ticket: WF-DB-001
-- Environment evidence
SELECT VERSION(), CURRENT_USER();

-- Preflight: this should return no rows before creation
SHOW DATABASES LIKE 'world_facts_lab';

CREATE DATABASE world_facts_lab;
SHOW DATABASES LIKE 'world_facts_lab';
USE world_facts_lab;

-- Reproduce the inherited schema contract before changing it
CREATE TABLE country (
    code CHAR(3) NOT NULL,
    name VARCHAR(100) NOT NULL,
    conitinent ENUM(
        'Asia',
        'Europe',
        'North America',
        'Africa',
        'Oceania',
        'Antarctica',
        'South America'
    ) NOT NULL DEFAULT 'Asia',
    region VARCHAR(100) NOT NULL,
    surface_area DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    independence_year SMALLINT NULL,
    population BIGINT NOT NULL DEFAULT 0,
    life_expectancy DECIMAL(4,1) NULL,
    gnp DECIMAL(15,2) NULL,
    gnp_old DECIMAL(15,2) NULL,
    local_name VARCHAR(100) NOT NULL,
    government_form VARCHAR(100) NOT NULL,
    head_of_state VARCHAR(100) NULL,
    code2 CHAR(2) NOT NULL,
    PRIMARY KEY (code),
    CONSTRAINT uq_country_code2 UNIQUE (code2)
) ENGINE=InnoDB;

SHOW TABLES;
SHOW COLUMNS FROM country;
SHOW COLUMNS FROM country LIKE 'conitinent';
SHOW CREATE TABLE country;

Your existing ec2-instance-connect-policy.json should still match this file.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "ec2-instance-connect:SendSSHPublicKey",
      "Resource": "arn:aws:ec2:your-region:your-account-id:instance/your-instance-id",
      "Condition": {
        "StringEquals": {
          "ec2:osuser": "ec2-user"
        }
      }
    },
    {
      "Effect": "Allow",
      "Action": "ec2:DescribeInstances",
      "Resource": "*"
    }
  ]
}

You now have an isolated staging database with a documented legacy defect. Next, you will execute the approved correction and add the governed country-to-city relationship.

Execute the Approved Schema Change

Your before-change evidence now proves the inherited schema defect. The approved change can proceed from a known baseline.

The approved MySQL change corrects the reporting contract. A named foreign key blocks orphaned city records.

Matching parent and child key types keeps the relationship valid. The remaining country fields stay unchanged.

In this step, get ready to:
  • Rename conitinent to continent while preserving its existing definition.
  • Create the related city table with the approved relationship.
  • Capture the after-change metadata for ticket WF-DB-001.
Rename the inherited column

The approved rename changes the column name without restating its definition. This keeps the alteration focused on the reporting contract.

  • Return to world_schema_lifecycle.sql from earlier.
  • Add the approved rename section to the end of the file by copying this SQL:
-- Execute the approved contract correction
ALTER TABLE country RENAME COLUMN conitinent TO continent;
SHOW COLUMNS FROM country;
SHOW CREATE TABLE country;

What does this SQL do?

  • The rename preserves the column definition while changing its name to continent.
  • The SHOW COLUMNS output confirms the field exposed by the table.
  • The SHOW CREATE TABLE output captures the corrected table definition for review.
  • Save world_schema_lifecycle.sql.
  • Switch back to the MySQL prompt from earlier.

Before you run this section, make a quick prediction about the column name the metadata will report.

  • Run the saved rename section by pasting it into the MySQL prompt.

You should see continent in the column list. The old conitinent name should be absent.

That is the contract correction complete. Reporting consumers now have the approved field name.

Column name still incorrect?

Confirm that world_facts_lab is still selected. The inherited country table must exist in that database.

Check the rename statement for the exact old name conitinent and the approved new name continent.

Help me diagnose the column rename using my MySQL output.

Create the related city table

The child key uses the same CHAR(3) definition as country.code. Matching definitions allow MySQL to enforce the relationship.

  • Return to world_schema_lifecycle.sql from earlier.
  • Add the governed child table section to the end of the file by copying this SQL:
-- Add the governed child entity and relationship
CREATE TABLE city (
    city_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    country_code CHAR(3) NOT NULL,
    region VARCHAR(100) NOT NULL,
    population BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (city_id),
    CONSTRAINT fk_city_country
        FOREIGN KEY (country_code)
        REFERENCES country (code)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;

SHOW TABLES;
SHOW CREATE TABLE city;

What does this SQL do?

  • The city_id column gives each city an automatically generated primary key.
  • The country_code column stores the parent country code for each city.
  • The named fk_city_country constraint makes the relationship easy to inspect.
  • The update action follows parent key changes. The delete action protects countries that still have child cities.
  • Save world_schema_lifecycle.sql.
  • Switch back to the MySQL prompt from earlier.
  • Run the saved child table section by pasting it into the MySQL prompt.

The SHOW TABLES output should list country plus city.

The city definition should show fk_city_country referencing country.code. It should also show ON UPDATE CASCADE plus ON DELETE RESTRICT.

City table creation failed?

Confirm that country.code uses CHAR(3). The child country_code must use a compatible definition.

Check that country still exists before creating city.

Help me troubleshoot the city table using my MySQL output.

Capture the after-change evidence

Metadata provides reviewable proof that the approved structure exists. These checks cover both tables plus the corrected contract.

Before you run these checks, predict which tables and constraints MySQL will report.

  • Capture the final schema state by running this SQL at the MySQL prompt:
SHOW TABLES;
SHOW COLUMNS FROM country;
SHOW CREATE TABLE country;
SHOW CREATE TABLE city;

What should I see?

  • The table list should contain country plus city.
  • The country columns should include continent.
  • The country definition should preserve the code primary key plus the uq_country_code2 unique constraint.
  • The city definition should include fk_city_country plus both approved referential actions.
  • Capture the metadata output in 04-after-change-schema.png.
  • Record the five structural acceptance criteria as passed in CHANGE_RECORD.md.

Metadata does not match?

Confirm that the prompt is using world_facts_lab before repeating the checks.

Compare the table names and constraint names character by character. A spelling difference creates a different database object.

Help me compare my metadata with the approved schema.

  • Compare world_schema_lifecycle.sql with the approved cumulative reference in the second tab.
  • Update any differences before saving the file.

✔️ Awesome, I've got everything!

Strong work. Your runbook now contains the inherited schema plus the approved contract correction and relationship.

ⓧ I'd like to double check the full code

Compare your cumulative world_schema_lifecycle.sql with this approved version:

-- Change ticket: WF-DB-001
SELECT VERSION(), CURRENT_USER();
SHOW DATABASES LIKE 'world_facts_lab';
CREATE DATABASE world_facts_lab;
SHOW DATABASES LIKE 'world_facts_lab';
USE world_facts_lab;

CREATE TABLE country (
    code CHAR(3) NOT NULL,
    name VARCHAR(100) NOT NULL,
    conitinent ENUM('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
    region VARCHAR(100) NOT NULL,
    surface_area DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    independence_year SMALLINT NULL,
    population BIGINT NOT NULL DEFAULT 0,
    life_expectancy DECIMAL(4,1) NULL,
    gnp DECIMAL(15,2) NULL,
    gnp_old DECIMAL(15,2) NULL,
    local_name VARCHAR(100) NOT NULL,
    government_form VARCHAR(100) NOT NULL,
    head_of_state VARCHAR(100) NULL,
    code2 CHAR(2) NOT NULL,
    PRIMARY KEY (code),
    CONSTRAINT uq_country_code2 UNIQUE (code2)
) ENGINE=InnoDB;
SHOW TABLES;
SHOW COLUMNS FROM country;
SHOW COLUMNS FROM country LIKE 'conitinent';
SHOW CREATE TABLE country;

ALTER TABLE country RENAME COLUMN conitinent TO continent;
SHOW COLUMNS FROM country;
SHOW CREATE TABLE country;

CREATE TABLE city (
    city_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    country_code CHAR(3) NOT NULL,
    region VARCHAR(100) NOT NULL,
    population BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (city_id),
    CONSTRAINT fk_city_country
        FOREIGN KEY (country_code)
        REFERENCES country (code)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;
SHOW TABLES;
SHOW CREATE TABLE city;

The approved schema change is now implemented with reviewable evidence. Next, you will close the ticket and remove the temporary SQL objects in dependency order.

Close the Change and Publish Evidence

Your corrected MySQL schema now meets the structural acceptance criteria for ticket WF-DB-001. The change still needs teardown evidence before the ticket can close.

A controlled close records the final state before deleting temporary objects. It also documents that MySQL DDL implicitly commits.

Your evidence pack brings the implementation details into one reviewable case study. It gives each decision a visible result.

In this step, get ready to:
  • Capture the final database inventory before cleanup.
  • Remove the temporary schema in dependency order.
  • Complete the change record and portfolio evidence pack.
Capture the final inventory

A final inventory proves which objects existed immediately before teardown. It preserves the corrected contract after the live staging objects disappear.

Before you run these checks, which table definition do you expect to contain fk_city_country?

  • Capture the selected database and final table definitions at the MySQL prompt by running:
SELECT DATABASE();
SHOW TABLES;
SHOW CREATE TABLE city;
SHOW CREATE TABLE country;

What does this inventory prove?

  • The SELECT DATABASE() result confirms that the inventory comes from world_facts_lab.
  • The table list confirms that country and city both exist before cleanup.
  • The table definitions preserve the corrected continent contract and the named fk_city_country relationship.
  • Review the returned definitions for the corrected column and named foreign key.

You should see world_facts_lab as the selected database. You should also see continent in country and fk_city_country in city.

Inventory missing an object?

Confirm that the prompt is still connected as dbadmin to world-facts-mysql. Check that world_facts_lab is still selected.

If a definition differs from the after-change evidence, stop before cleanup. Compare the live definition with the completed schema-change section of world_schema_lifecycle.sql.

Help me diagnose my final MySQL inventory before cleanup.

Remove the temporary schema

The foreign key makes cleanup order important. Removing city first clears the child dependency before country is removed.

Dropping these objects is deliberate. Your private AWS infrastructure remains available for the optional Secret Mission.

  • Remove the child table before the parent table by running:
DROP TABLE city;
DROP TABLE country;
SHOW TABLES;

Why does the order matter?

The city table depends on country through fk_city_country. Dropping the child first respects that dependency.

The final table check turns the destructive action into observable evidence. An empty result proves both temporary tables are gone.

  • Review the table list returned after both drop statements.

You should see an empty table list for world_facts_lab.

Table cleanup blocked?

Check that DROP TABLE city ran before DROP TABLE country. The child table must release its reference first.

If a table is still listed, compare its name with the two table names in the runbook. Keep the MySQL session open while you correct the mismatch.

Help me resolve a dependency-aware MySQL table cleanup problem.

Before you run the final cleanup, do you expect MySQL to keep world_facts_lab selected after that database is dropped?

  • Remove the staging database and run the closing checks by entering:
DROP DATABASE world_facts_lab;
SHOW DATABASES LIKE 'world_facts_lab';
SELECT DATABASE();

What do the closing checks prove?

  • The drop statement removes the temporary database after its tables are gone.
  • The database search checks that world_facts_lab no longer exists.
  • The final SELECT DATABASE() confirms that the dropped database is no longer selected.

The database search should return no row. The final selected-database result should be NULL.

Cleanup result looks different?

If the database search still finds world_facts_lab, review the result of the drop statement immediately above it. Confirm that the database name matches the runbook.

If the selected-database result is not NULL, confirm that the drop statement completed in the same MySQL session.

Help me verify why my MySQL database cleanup result differs from the runbook.

  • Capture the final database search and selected-database result in a screenshot.
  • Save the screenshot as 05-sql-cleanup-verified.png.

That is the destructive part complete. Your screenshot now proves that the temporary SQL environment was decommissioned.

  • Switch back to the file workspace that contains world_schema_lifecycle.sql.
  • Select world_schema_lifecycle.sql in that workspace.
  • Append the three final inventory and cleanup SQL blocks from this step in the order you ran them.
  • Save world_schema_lifecycle.sql.

✔️ Awesome, I've got everything!

Your lifecycle runbook now covers the full path from environment verification through SQL cleanup.

ⓧ I'd like to double check the full code

Compare your completed world_schema_lifecycle.sql with this reference.

-- Change ticket: WF-DB-001
SELECT VERSION(), CURRENT_USER();
SHOW DATABASES LIKE 'world_facts_lab';
CREATE DATABASE world_facts_lab;
SHOW DATABASES LIKE 'world_facts_lab';
USE world_facts_lab;
CREATE TABLE country (
    code CHAR(3) NOT NULL,
    name VARCHAR(100) NOT NULL,
    conitinent ENUM('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
    region VARCHAR(100) NOT NULL,
    surface_area DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    independence_year SMALLINT NULL,
    population BIGINT NOT NULL DEFAULT 0,
    life_expectancy DECIMAL(4,1) NULL,
    gnp DECIMAL(15,2) NULL,
    gnp_old DECIMAL(15,2) NULL,
    local_name VARCHAR(100) NOT NULL,
    government_form VARCHAR(100) NOT NULL,
    head_of_state VARCHAR(100) NULL,
    code2 CHAR(2) NOT NULL,
    PRIMARY KEY (code),
    CONSTRAINT uq_country_code2 UNIQUE (code2)
) ENGINE=InnoDB;
SHOW TABLES;
SHOW COLUMNS FROM country;
SHOW COLUMNS FROM country LIKE 'conitinent';
SHOW CREATE TABLE country;
ALTER TABLE country RENAME COLUMN conitinent TO continent;
SHOW COLUMNS FROM country;
SHOW CREATE TABLE country;
CREATE TABLE city (
    city_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    country_code CHAR(3) NOT NULL,
    region VARCHAR(100) NOT NULL,
    population BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (city_id),
    CONSTRAINT fk_city_country FOREIGN KEY (country_code) REFERENCES country (code) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;
SHOW TABLES;
SHOW CREATE TABLE city;
SELECT DATABASE();
SHOW TABLES;
SHOW CREATE TABLE city;
SHOW CREATE TABLE country;
DROP TABLE city;
DROP TABLE country;
SHOW TABLES;
DROP DATABASE world_facts_lab;
SHOW DATABASES LIKE 'world_facts_lab';
SELECT DATABASE();

How to use this reference

Compare statement order as well as spelling. The final section must inventory the live schema before it drops city, country, and world_facts_lab.

Assemble the evidence pack

The change record explains why the work happened. It also connects each risk to evidence and recovery planning.

  • Create CHANGE_RECORD.md beside world_schema_lifecycle.sql using your workspace's new-file control.
  • Add the ticket context and risk assessment by pasting:
# WF-DB-001: Geographic Reference Schema Correction

Status: Validated in staging and decommissioned
Owner: Database engineer
Stakeholder: Market Intelligence Reporting Team
Environment: Personal AWS staging environment

## Business reason

Regional dashboards need a stable geographic reference model. The inherited country schema exposes a misspelled `conitinent` field, and city records are not constrained to valid countries. The change corrects the reporting contract and adds database-enforced referential integrity.

## Scope

In scope: reproduce and capture `country`; rename `conitinent` to `continent`; add `city`; add `fk_city_country`; validate metadata and relationship behavior; and remove temporary SQL and AWS resources after evidence collection.

Out of scope: production deployment, application-code migration, historical data backfill, high availability, performance tuning, and disaster-recovery implementation.

## Risk assessment

Column renaming can break consumers. Mismatched parent and child key definitions can prevent foreign-key creation. MySQL DDL implicitly commits, so assumed transaction rollback is not a recovery plan. EC2, RDS, storage, and retained backups can continue generating charges until removed.

What does this section capture?

The opening identifies the owner and reporting stakeholder. The scope keeps the staging exercise separate from production deployment.

The risk assessment records contract, integrity, recovery, and cost concerns. This turns the lab into a governed change.

  • Save CHANGE_RECORD.md.
  • Confirm that the file begins with ticket WF-DB-001 and ends this chunk with the cost risk.

Ticket context incomplete?

Check that the status reads Validated in staging and decommissioned. Confirm that all four opening fields appear before the business reason.

Help me compare the opening of my change record with the required ticket context.

  • Add the validation checklist and recovery approach below the risk assessment by pasting:
## Validation

- [x] Private RDS target is reachable only through the project network path
- [x] `country.code` is the primary key
- [x] `country.code2` is unique
- [x] `continent` exists and `conitinent` does not
- [x] `city.city_id` is an auto-incrementing primary key
- [x] `fk_city_country` references `country.code`
- [x] SQL cleanup removes the temporary database
- [ ] Secret Mission: valid city insert succeeds
- [ ] Secret Mission: orphaned city insert is rejected

## Recovery approach

This staging exercise uses a forward-fix and rebuild approach. For production, verify an approved recovery point before DDL, test in a lower environment, coordinate consumer updates, and prepare a corrective migration or restore procedure.

Why document recovery here?

MySQL DDL implicitly commits. The recovery section therefore names a tested forward-fix or restore procedure as the production approach.

The two unchecked items remain reserved for the optional Secret Mission. The main-path acceptance criteria are already complete.

  • Save CHANGE_RECORD.md.
  • Confirm that seven main-path validation items are checked.

Validation status does not match?

Compare each checked item with the evidence captured in the preceding steps. Leave both Secret Mission items unchecked until you complete that optional test.

Help me reconcile the validation checklist with my captured MySQL evidence.

  • Finish the change record with the evidence register and closure statement by pasting:
## Evidence

`01-personal-aws-resources.png`, `02-private-rds-connection.png`, `03-before-change-schema.png`, `04-after-change-schema.png`, `05-sql-cleanup-verified.png`, optional `06-referential-integrity.png`, and `07-aws-cleanup-verified.png`.

## Closure

The staging acceptance criteria passed, the temporary SQL objects were removed, and the personal AWS resources will be deleted after evidence collection.

What closes the ticket?

The evidence register gives each proof artifact a stable name. The closure statement records the completed SQL teardown and pending AWS cleanup.

  • Save CHANGE_RECORD.md.
  • Confirm that the evidence list includes 05-sql-cleanup-verified.png.

Evidence names do not line up?

Match each screenshot filename character for character. Keep 06-referential-integrity.png marked as optional until the Secret Mission is complete.

Help me reconcile the evidence filenames in my change record.

✔️ Awesome, I've got everything!

Your change record now covers the business reason, scope, risks, validation, recovery, evidence, and closure.

ⓧ I'd like to double check the full code

Compare your completed CHANGE_RECORD.md with this reference.

# WF-DB-001: Geographic Reference Schema Correction

Status: Validated in staging and decommissioned
Owner: Database engineer
Stakeholder: Market Intelligence Reporting Team
Environment: Personal AWS staging environment

## Business reason

Regional dashboards need a stable geographic reference model. The inherited country schema exposes a misspelled `conitinent` field, and city records are not constrained to valid countries. The change corrects the reporting contract and adds database-enforced referential integrity.

## Scope

In scope: reproduce and capture `country`; rename `conitinent` to `continent`; add `city`; add `fk_city_country`; validate metadata and relationship behavior; and remove temporary SQL and AWS resources after evidence collection.

Out of scope: production deployment, application-code migration, historical data backfill, high availability, performance tuning, and disaster-recovery implementation.

## Risk assessment

Column renaming can break consumers. Mismatched parent and child key definitions can prevent foreign-key creation. MySQL DDL implicitly commits, so assumed transaction rollback is not a recovery plan. EC2, RDS, storage, and retained backups can continue generating charges until removed.

## Validation

- [x] Private RDS target is reachable only through the project network path
- [x] `country.code` is the primary key
- [x] `country.code2` is unique
- [x] `continent` exists and `conitinent` does not
- [x] `city.city_id` is an auto-incrementing primary key
- [x] `fk_city_country` references `country.code`
- [x] SQL cleanup removes the temporary database
- [ ] Secret Mission: valid city insert succeeds
- [ ] Secret Mission: orphaned city insert is rejected

## Recovery approach

This staging exercise uses a forward-fix and rebuild approach. For production, verify an approved recovery point before DDL, test in a lower environment, coordinate consumer updates, and prepare a corrective migration or restore procedure.

## Evidence

`01-personal-aws-resources.png`, `02-private-rds-connection.png`, `03-before-change-schema.png`, `04-after-change-schema.png`, `05-sql-cleanup-verified.png`, optional `06-referential-integrity.png`, and `07-aws-cleanup-verified.png`.

## Closure

The staging acceptance criteria passed, the temporary SQL objects were removed, and the personal AWS resources will be deleted after evidence collection.

How to use this reference

Check the status and validation marks first. Then compare the evidence filenames and closure wording.

The README translates the implementation into an architecture story. It gives reviewers a concise path through the business outcome and engineering controls.

  • Create README.md beside CHANGE_RECORD.md using your workspace's new-file control.
  • Add the executive summary and architecture decision by pasting:
# Govern a Production-Style MySQL Schema Change on AWS

## Executive summary

I delivered change ticket `WF-DB-001` for a global market-intelligence reporting scenario. I provisioned a private Amazon RDS for MySQL staging target in my personal AWS account, scoped administrative access through Amazon EC2, reproduced a legacy schema defect, corrected the reporting contract, added country-city referential integrity, validated the result, documented recovery considerations, and removed the temporary infrastructure.

## Architecture decision

Amazon RDS for MySQL keeps the database private and avoids self-managed database-server maintenance. An Amazon Linux 2023 EC2 administration host provides the SQL client, EC2 Instance Connect provides browser-based access, and workload-specific security groups control the EC2-to-RDS path.

What does this opening communicate?

The executive summary states the full change lifecycle in one place. The architecture section explains why managed MySQL and a controlled administration host fit the scenario.

  • Save README.md.
  • Confirm that the opening names ticket WF-DB-001 and the private EC2-to-RDS path.

Architecture story unclear?

Check that the executive summary covers provisioning, schema correction, validation, documentation, and cleanup. Confirm that the architecture decision identifies the private database path.

Help me compare my README opening with the required architecture narrative.

  • Add the delivered change and risk controls below the architecture decision by pasting:
## Change delivered

- Reproduced and documented the inherited schema before modification
- Renamed `conitinent` to `continent`
- Added normalized `city` and named `fk_city_country`
- Captured before-change, after-change, and cleanup evidence

## Risk controls

Scoped EC2 Instance Connect to `world-facts-admin` and `ec2-user`; kept RDS private; used project security groups; entered the database password interactively; captured metadata before DDL; dropped child objects before parent objects; treated DDL recovery as forward-fix or restore; and planned deletion of EC2, RDS, backups, IAM policy, and security groups.

Why pair outcomes with controls?

The change list shows what the project delivered. The controls explain how access, evidence, dependency order, recovery, and cleanup reduced risk.

  • Save README.md.
  • Confirm that the change list names continent and fk_city_country.

Missing a risk control?

Compare the risk paragraph with the controls you used during the project. Pay special attention to interactive password entry and dependency-aware cleanup.

Help me check the change outcomes and risk controls in my README.

  • Finish the README with the evidence register and interview talking points by pasting:
## Evidence

`01-personal-aws-resources.png`, `02-private-rds-connection.png`, `03-before-change-schema.png`, `04-after-change-schema.png`, `05-sql-cleanup-verified.png`, `06-referential-integrity.png`, and `07-aws-cleanup-verified.png`.

## Interview talking points

Why managed MySQL fit the change; how private RDS and scoped Instance Connect reduce exposure; why metadata capture matters; why matching foreign-key types matters; why implicit DDL commits affect recovery; and how evidence and cleanup make a lab auditable.

How does this support a walkthrough?

The evidence register gives reviewers a route through the proof. The talking points connect architecture, schema design, recovery, and cleanup to engineering judgment.

  • Save README.md.
  • Confirm that the final section ends with the auditability talking point.

README evidence list incomplete?

Check that the evidence sequence runs from 01-personal-aws-resources.png through 07-aws-cleanup-verified.png.

Help me reconcile the evidence register and interview talking points in my README.

✔️ Awesome, I've got everything!

Your README now explains the outcome, architecture, controls, evidence, and interview narrative.

ⓧ I'd like to double check the full code

Compare your completed README.md with this reference.

# Govern a Production-Style MySQL Schema Change on AWS

## Executive summary

I delivered change ticket `WF-DB-001` for a global market-intelligence reporting scenario. I provisioned a private Amazon RDS for MySQL staging target in my personal AWS account, scoped administrative access through Amazon EC2, reproduced a legacy schema defect, corrected the reporting contract, added country-city referential integrity, validated the result, documented recovery considerations, and removed the temporary infrastructure.

## Architecture decision

Amazon RDS for MySQL keeps the database private and avoids self-managed database-server maintenance. An Amazon Linux 2023 EC2 administration host provides the SQL client, EC2 Instance Connect provides browser-based access, and workload-specific security groups control the EC2-to-RDS path.

## Change delivered

- Reproduced and documented the inherited schema before modification
- Renamed `conitinent` to `continent`
- Added normalized `city` and named `fk_city_country`
- Captured before-change, after-change, and cleanup evidence

## Risk controls

Scoped EC2 Instance Connect to `world-facts-admin` and `ec2-user`; kept RDS private; used project security groups; entered the database password interactively; captured metadata before DDL; dropped child objects before parent objects; treated DDL recovery as forward-fix or restore; and planned deletion of EC2, RDS, backups, IAM policy, and security groups.

## Evidence

`01-personal-aws-resources.png`, `02-private-rds-connection.png`, `03-before-change-schema.png`, `04-after-change-schema.png`, `05-sql-cleanup-verified.png`, `06-referential-integrity.png`, and `07-aws-cleanup-verified.png`.

## Interview talking points

Why managed MySQL fit the change; how private RDS and scoped Instance Connect reduce exposure; why metadata capture matters; why matching foreign-key types matters; why implicit DDL commits affect recovery; and how evidence and cleanup make a lab auditable.

How to use this reference

Compare each heading and filename. Preserve the concise architecture and recovery language so the document remains easy to review.

The current evidence pack now has four core files. It also has five main-path screenshots.

  • Keep the completed world_schema_lifecycle.sql in the evidence pack.
  • Keep the unchanged ec2-instance-connect-policy.json in the evidence pack.
  • Include CHANGE_RECORD.md in the evidence pack.
  • Include README.md in the evidence pack.
  • Include screenshots 01-personal-aws-resources.png through 05-sql-cleanup-verified.png.
  • Publish the completed files and current screenshots together in your project evidence location.

Before your final review, which two MySQL results prove that the temporary staging environment is gone?

  • Review 05-sql-cleanup-verified.png for the final SQL results.
  • Review CHANGE_RECORD.md for the closed ticket status.
  • Review the evidence location for all four core files and five current screenshots.

You should see no row for world_facts_lab and NULL as the selected database. The change record should show Validated in staging and decommissioned.

Strong finish. Ticket WF-DB-001 now has implementation evidence, cleanup proof, and a documented recovery position.

Your private AWS infrastructure remains available only for the optional Secret Mission. That challenge tests whether the foreign key protects real data as convincingly as it protects the schema.

Secret mission

Prove Referential Integrity Under Failure

Rebuild the corrected geographic schema to test its most important relationship. You will prove that a valid Tokyo record succeeds while an orphaned Atlantis record is rejected.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep the remaining AWS resources active, pause compute for a short break, or delete the environment entirely. The remaining cloud resources can continue consuming credits or generating charges.

Cost warning

Keeping world-facts-admin active can continue generating compute and storage charges.

Keeping world-facts-mysql active can continue generating database charges. Storage plus retained backups can continue generating charges after compute stops.

Resources awaiting cleanup:

  • One Amazon EC2 t3.micro instance named world-facts-admin with its EBS root volume.
  • One private Amazon RDS for MySQL db.t3.micro DB instance named world-facts-mysql.
  • One EC2 Instance Connect security group named world-facts-eic-sg plus the automatic groups matching ec2-rds-<n> and rds-ec2-<n>.
  • One AWS Identity and Access Management (IAM) policy named world-facts-eic-access.

Keep everything running

No action is needed. Choose this if you are continuing with the environment immediately.

  • The world-facts-admin instance remains available as your administration host.
  • The private world-facts-mysql DB instance remains available for more schema exercises.
  • Your IAM policy plus project security groups remain attached to the controlled access path.
  • Your compute, database, storage, and backup usage can continue consuming credits or generating charges.

Pause - I'll come back to this later

Stop the compute resources to reduce active usage while preserving the environment for a short break.

  • Stop world-facts-admin in Amazon EC2.
  • Return to Amazon RDS.
  • Select world-facts-mysql.
  • Choose Actions.
  • Choose Stop temporarily.
  • Acknowledge the automatic restart notice.
  • Complete the temporary stop request.

Amazon RDS restarts the DB instance automatically after 7 consecutive days. Storage and backup usage can continue generating charges while the instance is stopped.

Delete - I don't want to use this again

Deletion permanently removes the temporary AWS environment. Your SQL files, documentation, and screenshots remain available as evidence.

  • Terminate world-facts-admin in Amazon EC2 to remove the instance plus its EBS root volume.
  • Select world-facts-mysql in Amazon RDS.
  • Choose Actions.
  • Choose Delete.
  • Clear Create final snapshot?.
  • Clear Retain automated backups.
  • Enter delete me in the confirmation field.
  • Choose Delete.

The deletion request now removes the DB instance without retaining a final snapshot or its automated backups.

  • Wait until world-facts-admin shows Terminated.
  • Wait until world-facts-mysql is absent from Databases.
  • Return to Security Groups in Amazon EC2.
  • Delete world-facts-eic-sg.
  • Delete the project-only security group matching ec2-rds-<n>.
  • Delete the project-only security group matching rds-ec2-<n>.
  • Detach world-facts-eic-access from your project IAM identity.
  • Delete world-facts-eic-access from IAM.

Complete a final audit to prove that the billable resources plus their access controls are gone.

  • Confirm world-facts-admin shows Terminated.
  • Confirm the EBS root volume attached to world-facts-admin no longer exists.
  • Confirm world-facts-mysql is absent from Databases.
  • Confirm no project snapshots exist.
  • Confirm no retained automated backups exist.
  • Confirm world-facts-eic-access is absent from IAM.
  • Confirm world-facts-eic-sg is absent from Security Groups.
  • Confirm no project-only groups matching ec2-rds-<n> or rds-ec2-<n> remain.

That closes the cleanup loop. Your evidence pack remains available without leaving the temporary AWS environment running.

Nice Work!

Nice Work!

Project complete! You governed change ticket WF-DB-001 on a private Amazon RDS for MySQL database through an Amazon EC2 administration host.

What you accomplished:

  • Provisioned a private database path between world-facts-admin and world-facts-mysql. Scoped EC2 Instance Connect access to the project host with a least-privilege IAM policy.
  • Delivered the controlled schema change for ticket WF-DB-001. Replaced conitinent with continent. Added the named fk_city_country relationship.
  • Produced a recruiter-ready evidence pack through world_schema_lifecycle.sql. Documented the decision trail in CHANGE_RECORD.md. Turned the business outcome into interview talking points in README.md.
  • Secret Mission: proved the positive integrity path by inserting Tokyo for JPN. Proved the negative integrity path when fk_city_country rejected Atlantis with XXX. Removed world_facts_lab again after the test.

Ready to quiz yourself?