How to Create a AWS RDS MySQL Database and Connect to it using MySQL Workbench

Mohammad Abu Mattar
Cloud Computing
07 Nov, 2022
07 Mins read

Introduction

RDS is a managed service that makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups. It frees you to focus on your applications so you can give them the fast performance, high availability, security, and compatibility they need.

Prerequisites

AWS Account
MySQL Workbench

Create a AWS RDS MySQL Database

AWS Console

Click on Services and then click on RDS.

RDS

Click on Create Database.

Create Database

Select MySQL as the database engine.

Select MySQL

Select the MySQL version.

Select MySQL Version

Select the template.

Select Template

DB instance identifier.

DB Instance Identifier

Master username and password.

Master Username and Password

DB instance class.

DB Instance Class

Storage.

Storage

Public access

Public Access

VPC security group.

VPC Security Group

Additional configuration.

Additional Configuration

Click on Create database.

Connect to the AWS RDS MySQL Database

Get the endpoint of the database.

Get Endpoint

Open MySQL Workbench.

Open MySQL Workbench

Click on the + icon to add a new connection.

Add New Connection

Enter the connection details.
- Connection Name: Enter a name for the connection.
- Hostname: Enter the endpoint of the database.
- Username: Enter the username of the database.
- Password: Enter the password of the database.

Enter Connection Details

Click on Test Connection to test the connection.
Click on OK to save the connection.
Click on the connection to connect to the database.

You have successfully connected to the AWS RDS MySQL database.

Exercise 1: Create a Table

Create a 3 tables in the database, the tables are:

SAILORS - Contains the details of the sailors.
- SID - The ID of the sailor.
- SName - The name of the sailor.
- SRating - The rating of the sailor.
- SAge - The age of the sailor.
BOATS - Contains the details of the boats.
- BID - The ID of the boat.
- BName - The name of the boat.
- BColor - The color of the boat.
RESERVES - Contains the details of the reservations.
- SID - The ID of the sailor.
- BID - The ID of the boat.
- Day - The day of the reservation.

Click to expand the SQL solution.

Create the SAILORS table

1
CREATE TABLE `SAI`.`SAILORS` (
2
    SID int auto_increment primary key,
3
    SName varchar(30) not null,
4
    SRating int default 0 check (SRating >= 0 and SRating <= 10),
5
    SAge real default 18 check (SAge >= 18 and SAge <= 70)
6
);

Explanation:

Click to expand the explanation.

SID - The ID of the sailor.
SName - The name of the sailor.
SRating - The rating of the sailor.
SAge - The age of the sailor.
auto_increment - The ID will be automatically incremented.
primary key - The ID is the primary key of the table.
not null - The name of the sailor cannot be null.
default 0 - The rating of the sailor will be 0 by default.
check (SRating >= 0 and SRating <= 10) - The rating of the sailor must be between 0 and 10.
default 18 - The age of the sailor will be 18 by default.
check (SAge >= 18 and SAge <= 70) - The age of the sailor must be between 18 and 70.

Create the BOATS table

1
CREATE TABLE `SAI`.`BOATS` (
2
    BID int auto_increment primary key,
3
    BName varchar(30) not null,
4
    BColor varchar(30) not null
5
);

Explanation:

Click to expand the explanation.

BID - The ID of the boat.
BName - The name of the boat.
BColor - The color of the boat.
auto_increment - The ID will be automatically incremented.
primary key - The ID is the primary key of the table.
not null - The name of the boat cannot be null.

Create the RESERVES table

1
CREATE TABLE `SAI`.`RESERVES` (
2
    SID int not null,
3
    BID int not null,
4
    Day date not null,
5
    primary key (SID, BID, Day),
6
);
7

8
alter table `SAI`.`RESERVES` add constraint `RESERVES_SAILORS_FK` foreign key (SID) references `SAI`.`SAILORS` (SID);
9
alter table `SAI`.`RESERVES` add constraint `RESERVES_BOATS_FK` foreign key (BID) references `SAI`.`BOATS` (BID);

Explanation:

Click to expand the explanation.

SID - The ID of the sailor.
BID - The ID of the boat.
Day - The day of the reservation.
primary key (SID, BID, Day) - The combination of the ID of the sailor, the ID of the boat and the day is the primary key of the table.
not null - The ID of the sailor, the ID of the boat and the day cannot be null.
alter table - Add a foreign key to the table.
RESERVES_SAILORS_FK - The name of the foreign key.
foreign key (SID) - The ID of the sailor is the foreign key.
references - References the SAILORS table.
RESERVES_BOATS_FK - The name of the foreign key.
foreign key (BID) - The ID of the boat is the foreign key.
references - References the BOATS table.

Insert data into the tables

Click to expand the SQL solution.

1
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (22, 'Dustin', 7, 45);
2
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (29, 'Brutus', 1, 33);
3
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (31, 'Lubber', 8, 55.5);
4
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (32, 'Andy', 8, 25);
5
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (58, 'Rusty', 10, 35);
6
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (64, 'Horatio', 7, 35);
7
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (71, 'Zorba', 10, 18);
8
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (74, 'Horatio', 9, 40);
9
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (85, 'Art', 3, 25.5);
10
insert into `SAI`.`SAILORS` (SID, SName, SRating, SAge) values (88, 'Bob', 3, 63.5);
11

12
insert into `SAI`.`BOATS` (BID, BName, BColor) values (101, 'Interlake', 'blue');
13
insert into `SAI`.`BOATS` (BID, BName, BColor) values (102, 'Interlake', 'red');
14
insert into `SAI`.`BOATS` (BID, BName, BColor) values (103, 'Clipper', 'green');
15
insert into `SAI`.`BOATS` (BID, BName, BColor) values (104, 'Marine', 'red');
16

17
insert into `SAI`.`RESERVES` (SID, BID, Day) values (22, 101, '1998-10-10');
18
insert into `SAI`.`RESERVES` (SID, BID, Day) values (22, 102, '1998-10-10');
19
insert into `SAI`.`RESERVES` (SID, BID, Day) values (22, 103, '1998-10-8');
20
insert into `SAI`.`RESERVES` (SID, BID, Day) values (22, 104, '1998-10-7');
21
insert into `SAI`.`RESERVES` (SID, BID, Day) values (31, 102, '1998-11-10');
22
insert into `SAI`.`RESERVES` (SID, BID, Day) values (31, 103, '1998-11-6');
23
insert into `SAI`.`RESERVES` (SID, BID, Day) values (31, 104, '1998-11-12');
24
insert into `SAI`.`RESERVES` (SID, BID, Day) values (64, 101, '1998-9-5');
25
insert into `SAI`.`RESERVES` (SID, BID, Day) values (64, 102, '1998-9-8');
26
insert into `SAI`.`RESERVES` (SID, BID, Day) values (64, 103, '1998-9-8');

Exercise 2: Write a Querys

Find all information of sailors who have reserved boat number 101.
Find the names of sailors who have reserved a red boat, and list in the order of age.
Find the names of sailors who have reserved at least one boat.
Find the ids and names of sailors who have reserved two different boats on the same day.
Find the ids of sailors who have reserved a red boat or a green boat

Click to expand the SQL solution.

1
select * from `SAI`.`SAILORS`
2
where SID in (
3
    select SID from `SAI`.`RESERVES`
4
    where BID = 101
5
);

Explanation:

Click to expand the explanation.

select * - Select all columns.
from - From the SAILORS table.
where - Where the ID of the sailor is in the list of IDs of sailors who have reserved boat number 101.
SID in - The ID of the sailor is in the list.
select SID - Select the ID of the sailor.
from - From the RESERVES table.
where - Where the ID of the boat is 101.

1
select SName from `SAI`.`SAILORS`
2
WHERE SID in (
3
    select SID from `SAI`.`RESERVES`
4
    WHERE BID in (
5
        select BID from `SAI`.`BOATS`
6
        WHERE BColor = 'red'
7
    )
8
)
9
order by SAge;

Explanation:

Click to expand the explanation.

select SName - Select the name of the sailor.
from - From the SAILORS table.
where - Where the ID of the sailor is in the list of IDs of sailors who have reserved a red boat.
SID in - The ID of the sailor is in the list.
select SID - Select the ID of the sailor.
from - From the RESERVES table.
where - Where the ID of the boat is in the list of IDs of red boats.
BID in - The ID of the boat is in the list.
select BID - Select the ID of the boat.
from - From the BOATS table.
where - Where the color of the boat is red.
order by - Order by the age of the sailor.

1
select SName from `SAI`.`SAILORS`
2
where SID in (
3
    select SID from `SAI`.`RESERVES`
4
);

Explanation:

Click to expand the explanation.

select SName - Select the name of the sailor.
from - From the SAILORS table.
where - Where the ID of the sailor is in the list of IDs of sailors who have reserved a boat.
SID in - The ID of the sailor is in the list.
select SID - Select the ID of the sailor.
from - From the RESERVES table.

1
select SID from `SAI`.`SAILORS`
2
where SID in (
3
    select SID from `SAI`.`RESERVES`
4
    group by SID, Day
5
    having count(*) = 2
6
);

Explanation:

Click to expand the explanation.

select SID - Select the ID of the sailor.
from - From the SAILORS table.
where - Where the ID of the sailor is in the list of IDs of sailors who have reserved two boats on the same day.
SID in - The ID of the sailor is in the list.
select SID - Select the ID of the sailor.
from - From the RESERVES table.
group by - Group by the ID of the sailor and the day.
having - Having the count of the number of boats reserved is 2.

1
select SID from `SAI`.`SAILORS`
2
where SID in (
3
    select SID from `SAI`.`RESERVES`
4
    where BID in (
5
        select BID from `SAI`.`BOATS`
6
        where BColor = 'red'
7
    )
8
)
9
or SID in (
10
    select SID from `SAI`.`RESERVES`
11
    where BID in (
12
        select BID from `SAI`.`BOATS`
13
        where BColor = 'green'
14
    )
15
);

Explanation:

Click to expand the explanation.

select SID - Select the ID of the sailor.
from - From the SAILORS table.
where - Where the ID of the sailor is in the list of IDs of sailors who have reserved a red boat or a green boat.
SID in - The ID of the sailor is in the list.
select SID - Select the ID of the sailor.
from - From the RESERVES table.
where - Where the ID of the boat is in the list of IDs of red boats or green boats.
BID in - The ID of the boat is in the list.
select BID - Select the ID of the boat.
from - From the BOATS table.
where - Where the color of the boat is red or green.

Conclusion

In this tutorial, you learned how to create a RDS instance, how to connect to it, and how to create a database and tables. You also learned how to insert data into the tables and how to write queries to retrieve data from the tables. You can use these skills to create a database for your own projects.