Business 2053 – DIM
Individual Assignment 2
MySQL and AWS
Due: March 13, 11:59PM
1. AWS Course LMS and Lab
Before working on this assignment, make sure you have received an email from AWS Academy regarding course invitation. Read the emails carefully to accept the invitation and register an account on AWS Academy. Make sure you use the university’s email address in this process.
After login (or use this link to login next time: https://www.awsacademy.com), click LMS at the top of the webpage, click Courses, select AWS Academy Learner Lab [55754]. On the course page, click Modules, Launch AWS Academy Learner Lab. Then you will see a lab status bar like the one below (The webpage may not be compatible with the Safari browser. Use Google Chrome instead):
The red dot suggests that the AWS lab is OFF. Click Start Lab to enable it. Then the red dot becomes green (may takes a few minutes), click the green dot to open the AWS lab, which will go to the AWS Management Console, where you can find many AWS services (like the screenshot below).
2. AWS RDS and MySQL Workbench
Go to Tutorial: Create and Connect to a MySQL Database with Amazon RDS:
https://aws.amazon.com/getting-started/hands-on/create-mysql-db/
and read it carefully to understand how to create a database in the AWS Management Console.
In the tutorial, click the Create a MySQL DB Instance section to start.
- Step a will help you locate where to find the RDS database service. You can also use the search field at the top of the webpage to search for RDS.
- Skip Step b and continue with Step c and the following steps.
- In Step e, you can decide the name of your DB instance identifier, your Master username and password. Remember your own username and password, because this information will be used later to connect to the database instance.
- In Step f, for VPC security group, after selecting Create new, remember to enter a name (can be any name) in the “New VPNC security group name” field.
The tutorial also covers other sections like Download a SQL client and Connect to the MySQL Database. You should have been familiar with these steps because of working on Exercise 2. Anyway, make sure you know them because the assignment requires you to 1) create a database instance on AWS, 2) have MySQL Workbench installed, and 3) use MySQL Workbench to connect to this database instance that you created and then write SQL statements.
Note:
1) If you have multiple databases created while following the tutorial, remember to only keep one database and delete all unnecessary databases. Otherwise, they will use all your AWS credits very quickly, making you unable to access AWS and work on the assignment anymore.
2) You will need to use your own hostname Endpoint & port, Master username, and password to create the database connection in MySQL Workbench. Do NOT use the information provided in Exercise 2. Refer to the screenshot below to find the endpoint & port information in your own RDS.
3) If you somehow cannot use MySQL Workbench to connect to the database instance after correctly providing your own hostname endpoint, username, and password, a main reason would be related to the IP address. The solution is to update your VPC security group setting in RDS by following these steps:
(a) click the VPC security group.
(b) click the Security group ID
(c) Edit inbound rules
(d) Add rules.
For a new rule, select MYSQL/Aurora in Type, Anywhere-IPv4 in Source, click Save rules. Create another similar rule but with Anywhere-IPv6 in Source. The two rules will allow inbound connections from any IP addresses to connect to your RDS instance.
3. Query with MySQL Workbench (Example)
Remember to use MySQL Workbench to connect to your database instance by providing your own hostname endpoint, username, and password. Click to use the new connection you created.
Next, click Menu File, Open SQL Script, in the popup window, select HAFHMORE_MySQL.sql, which can be downloaded from D2L, then click Menu Query, Execute (All or Selection). This will create a database named as HAFHMORE with actual data (click 
to refresh the Schemas panel and show the newly created database).
To use the HAFHMORE database, right click it and select Set as Default Schema. Under Menu File, click New Query Tab to start writing queries. For example, you can retrieve all records from the building table, try the following query and execute it.
SELECT * FROM building;
To provide the result of the query in a table format, click the Export icon above the result, select HTML format and save the HTML file with the name and location you like in your computer, then open the file with a browser, select the whole table (you may use keyboard shortcut: CMD+A in MacOS or CTRL+A in Windows to Select ALL), copy and paste into a Word document as shown below (this is the table format we use for this assignment):
Provide Answers to the Following Requests (IMPORTANT)
Request 1: Retrieve the managerid, mfname, mlname, and the sum of msalary and mbonus for each manager who receives a bonus.
(1) Provide the SQL query for the request.
(2) Provide the corresponding result in a table format (NOT a screenshot).
(3) Identify the number of managers in the table and the full name(s) of the manager(s) with the least and the largest sum of msalary and mbonus (Not a SQL query).
Request 2: For each building, retrieve the buildingid, number of apartments in the building, and number of bedrooms in the building.
(1) Provide the SQL query for the request.
(2) Provide the corresponding result in a table format.
(3) Identify the buildingid(s) with the least apartments, and the buildingid(s) with the most bedrooms.
Request 3: For each staff member, retrieve the smemberid, smembername, and number of buildings where s/he cleans apartments.
(1) Provide the SQL query for the request.
(2) Provide the corresponding result in a table format.
(3) Identify the smembername(s) with the most buildings where he/she cleans apartments, and the smembername(s) with the least apartments that he/she cleans.
Request 4: For all corporate clients that were referred by other corporate clients and that are also renting one or more apartments from us, retrieve the name of the corporate client, the client’s industry, location, building id, apartment number, and the name of the corporate client that referred it.
(1) Provide the SQL query for the request.
(2) Provide the corresponding result in a table format.
(3) Identify the client(s) that recommends the most other clients (how many?) and identify the client(s) that is not in the airline industry.
4. Submission Instructions
This is an individual assignment, do not cooperate with anyone.
Submit a MS Word document (.docx) online through D2L. In this document, provide clear answers to each of the requests. Make sure to:
1) copy the SQL queries from MySQL Workbench and paste them to the document (SQL screenshots are not accepted);
2) indicate the request number for each query;
3) use Export to get the table results;
4) provide a screenshot to show your database on AWS, and the screenshot should show the endpoint address and the black top bar of the webpage (refer to the appended screenshot for the reference); and
5) add your full name and student ID at the end of the document.
Naming and Late Submission Policies:
The document should follow this naming policy: yourstudentnumber_yourlastname_Assign2, followed by the file extension. For example, 012345_Smith_Assign2.docx. You will lose 5% out of your grade if you fail to do so.
Refer to the course outline for the late submission policy.
AWS Database Screenshot:
