1. (20 points) Critical to using AWS RDS and DynamoDB services is the ability to differentiate between key terms and processes between SQL and NoSQL database. Assuming you are more familiar with a relational database, describe, differentiate with Syntax and commands, how you would create in DynamoDB, the equivalent of the following SQL Schema.
CREATE TABLE sensors (
Sensor varchar(50) NOT NULL,
Sensor_Description varchar(50),
Image_Filename varchar(50),
Filepath_ID int(11),
PRIMARY KEY (`Sensor`)
);
Insert into sensors (Sensor, Sensor_Description) values (‘Magnetic Sensor’, ‘Company X new Dev Sensor’);
Insert into sensors (Sensor, Filepath_ID) values (‘IR Sensor’, 3);
Be sure to compare and discuss tables, records (row), and fields (columns) in the relational database schema to the equivalent in DynamoDB. In addition, provide the AWS CLI commands and associated JSON files required to create the equivalent in DynamoDB.
Demonstrate you successfully created this table and items in the DynamoDB by screen captures of your AWS Management console.
2. (35 points) Using your non-root, admin AWS account, write AWS CLI commands that will create a RDS instance running MySQL.
The MySQL instance should use the following parameters. For the values listed as “user-supplied” you should supply a value that seems appropriate.
 db-name = SDEV400RDSTest
 DB instance identifier = “user-supplied”
 Master Username = “user-supplied”
 Master user Password = “user-supplied”
 instance-class = db.t2.micro
 Storage = 20 GB
 Engine = MySQL
After successfully creating the RDS instance, use the AWS CLI describe command to show all of the parameters of the database. Be sure to cut and paste the entire output string from the JSON response to your report. Also, provide a screen shot from the AWS management console showing the instance running.
Using the MySQL Shell, or any tool you have installed on your desktop, create the following schema objects:
1. Table named Courses to hold fields including Subject (e.g. SDEV), CatalogNbr (e.g. 400), Title (e.g. Secure Programming in the Cloud), NumCredits (e.g. 3), and a CourseID (e.g. 001). The CourseID should represent the Primary Key for the Courses table.
2. Table named Students to hold fields including Firstname (e.g. Jim), Lastname (e.g. Robertson), email (e.g. james.robertson@umuc.edu), Year of enrollment (e.g. 2015), and StudentID (e.g. 0012). The StudentID should represent the Primary Key for the Students table
3. Table named StudentCourses with fields to hold a StudentID (references the Students table) and a CourseiD (references the Course table). The primary key for this table should be a composite key of the StudentID and the CourseID.
4. 5 Course records of your choice.
5. 5 Student records of your choice.
6. 10 StudentCourse records of your choice.
Be sure to prepare a SQL script for the above that can be run from the MySQL Shell using one command.
Include in your report, the SQL script along with the command used to run the SQL script. You should use the appropriate “select” statements for each table showing the records were successfully inserted and provide the results of running the select statements in your report.
3. (35 points) Using the DynamoDB service and the AWS CLI interface, create identical table structures that you created for the AWS RDS MySQL instance.
You should create the tables, define the HASH (and Range) keys as needed, and populate the items with appropriate attributes.
Be sure to include all AWS CLI commands used along with the exact JSON files used to create and populate the tables in your report.
Using the AWS CLI, add two additional Student items to your DynamoDB Student table. For these students, add additional attributes including GPA (e.g. 3.82) and ExpectedGraduationYear( e.g. 2019).
Next, take one of those two students and update the email and lastname attributes based on a recent life event where the name was changed to “Johnson-Smith” due to marriage. (Feel free to take liberties with the actual email and lastname values.)
Finally, use the AWS CLI to delete all of the DynamoDB tables that were created for this exercise. Do the same for the RDS tables. Provide AWS CLI responses and the commands used to successfully complete this task in your report.