CLD3_TSK

Activity 3-2 Tutorial

In this tutorial, you will be creating an RDS/MySQL instance through AWS Academy for serverless storage. After setting it up, you connect to the RDS/MySQL instance through MySQL Workbench (an application to manage MySQL databases remotely).

To start, open the AWS console through your AWS Academy Learner Lab- Foundation Services course. To complete this, log into your account, navigate to the ‘Learner Lab’ under modules in your AWS Academy Learner Lab – Foundation Services course.

Select ‘Start Lab’. The lab with launch. This may take a couple of minutes. Once complete, select ‘AWS’ in the top left. This will have a green mark next to it when ready.

This brings you to the AWS Management Console. Then, click the All services arrow and choose RDS as shown in the following screenshot:

In the console, you will see a few options to create a database. Click Create database in the middle of the page to continue.

You will be creating an RDS/MySQL instance with MySQL so make sure this option is chosen. The version you want to choose is MySQL 5.6.46 compatibility.

In the Templates section, select Free Tier.

Make sure that the name you choose for your database instance includes your last name.

Make sure to note the Master username and to unclick Auto generate password if you wish to create your own.

For most of this setup, you can use the default values provided; however, when you get to the Connectivity section, click the triangle beside Additional Connectivity Configuration, and check the box next to Publically accessible - Yes.

In the section on VPC Security Group, select Choose Existing, then use the dropdown to select Default. There is more about this later.

Click Create database when finished. Then wait for the RDS/MySQL instance to be created. You should see the following when it is ready (this step may take a few minutes):

You will need to make a few adjustments to allow remote access to the RDS/MySQL instance. Click on the instance you created to pull up more information about it. In the bottom right side of the page you will see the Security section. Click on the link to the default security group or select VPC security groups from the left side menu.

Edit both inbound and outbound rules to allow remote connections. To do this, right-click the security group and choose edit.

Then add a rule for both that allows MySQL/Aurora connections on port 3306 from anywhere. (Normally you would restrict this to your personal

IP address for security reasons.)

Once you have completed this, you can record the public domain name system (DNS) of the RDS/MySQL instance for use later. This is found by going back to the RDS section and choosing Databases from the left side menu. Once again, click on your newly created RDS/MySQL instance.

Then record both the endpoint and port in the bottom left on the page.

Next, you are tasked with performing some data entry and manipulation to test that the system is working as expected. To do this, you will need to establish a remote connection to the database. This can be done in many ways, but this tutorial will cover the use of MySQL Workbench.

If you do not wish to use your personal computer to install this software, or you are not on a personal computer, make use of the SNHU virtual environment by following these instructions: SNHU VDI . You can choose the correct software version and download the application here: MySQL Workbench .

Note: You do not have to create an account. Simply click No thanks, just start my download underneath the sign-up.

For the setup, you can click Next all the way through to accept the default setup and then click Install.

Once you are done, click Finish, and Workbench should load automatically for you to use.

Note: If it does not start, try to run the program yourself. If nothing happens or an error message appears about versioning, your PC might need one or both of the following updates, depending on the current configuration of the computer on which you are loading MySQL Workbench.

Microsoft .NET Framework 4.5.2

Microsoft Visual C++ 2019 Redistributable for Visual Studio 2019

If you continue to have issues, consider using the instructions above to access SNHU VDI, which is updated and ready to install MySQL Workbench.

Once you have the application running, you should see the welcome screen shown in the following screenshot.

Click Database in the top menu and then Connect to Database.

Here you will enter the database connection information. Choose Standard (TCP/IP) for a connection type, then enter the endpoint for your RDS/MySQL instance, and the username and password you made when creating it. When you are done, click OK.

Note: If you have issues connecting to the RDS/MySQL instance, click Modify from the RDS settings page in the top right side. Scroll down to the Security section and make sure that you select Yes under Public accessibility. Then click Modify and choose to make changes immediately. Wait for the instance to become available and try again to connect.

Once you have connected, your screen will look like the following screenshot.

There are no databases created by default for an RDS/MySQL instance so we must create one. Right-click in the Schemas window (the left side of the screen). Then create a new schema.

When you are done entering your database name (which should include your last name) for the schema, click Apply and then Apply again.

When you are done, click Finish. Now you can create or alter any tables that you need. To do so, reference MySQL Workbench for more information. Make sure to make all of the needed changes using the following entity relationship diagram (ERD) to test your new RDS/MySQL instance.

Once you are done creating the entities, make the adjustments shown below. When you are done, include a screenshot of the database tables with your submitted work. This should include a screenshot of your running RDS/MySQL instance (with your last name included in the name) and a screenshot of the running MySQL Workbench connection showing the new database, tables, and data. Make sure that you are using

AUTO_INCREMENT for the ID fields and using DEFAULT for the value in the records so that you establish the correct ID field for each entity. Also, be sure that the field sizes that you define are large enough to accommodate the data that will be inserted into them.

Customers – default, John Doe, [email protected], 123 Test Drive, NewCity, NewState, NewCountry, 12345

Managers – default, Jill Manager, [email protected], 234 Manage Street, NewCity, NewState, NewCountry, 12345

Employees – default, John Emp, [email protected], 444 Employ Square, NewCity, NewState, NewCountry, 12345

Jill Employ, [email protected], 293 Employ Square, NewCity, NewState, NewCountry, 12345

Last, make sure to assign Jill Employ and John Emp to have Jill Manager as their manager in the Manager_Employee table. To do this, you will have to obtain the employee_id and manager_id for each from a SELECT query.

1

1

1