Assignment 2

Purpose

This assignment has two tasks. Task one asks you to practice writing and running SQL statements that involve CRUD interactions. Task two is to practice building a database, with types and tables and populating the tables with data.

When done you will have created...

• A sql file containing six queries as described in Task One.
• A sql file containing all the code to create a PostgreSQL type, three tables and insert statements to populate two of the tables, built during the activities.
• A video demonstrating:
  1. The deletion of a database service and the reestablishment of the service, the remote connection to that service and the execution of SQL code to build the type and tables and populate the tables as described in Task Two.
  2. The results of the successful execution of the queries in Task 1. This means showing the tables with the modified data in the video to allow the graders to see that you completed Task 1. It is not necessary to record the actual execution of the queries. It will suffice to show the end result.

Video Demo

This video demonstrates and describes what the Task Two video could look like. This is the Transcript of the video.

Assumptions

This assignment assumes:

1. you successfully completed the lesson activities.
2. you created and saved the SQL file of CREATE, ALTER and INSERT statements for the course database.

Tasks

Task One - Write SQL Statements

The Introduction to SQL reading activity contains sample SQL statements for Selecting, Inserting, Updating and Deleting data from database table(s), you should have carefully reviewed these as part of this week's preparations. Based on the reading:

1. Create a new assignment2.sql file inside the database folder of your project.
2. Build and test your SQL statements in pgAdmin or SQLTools using the course database.
3. Whenever writing a SELECT, UPDATE or DELETE query for a single record, use the primary key value in the "WHERE" clause to avoid changing or selecting the incorrect record.
4. When your statements are working correctly, store them into the assignment2.sql file.
5. Write SQL statements to accomplish the following tasks. Each task should be performed using a single query per task:
   1. Insert the following new record to the account table Note: The account_id and account_type fields should handle their own values and do not need to be part of this query.:
      Tony, Stark, tony@starkent.com, Iam1ronM@n
   2. Modify the Tony Stark record to change the account_type to "Admin".
   3. Delete the Tony Stark record from the database.
   4. Modify the "GM Hummer" record to read "a huge interior" rather than "small interiors" using a single query. Explore the PostgreSQL Replace function Do NOT retype the entire description as part of the query.. It needs to be part of an Update query as shown in the code examples of the SQL Reading - Read Ch. 1, section 3.
   5. Use an inner join to select the make and model fields from the inventory table and the classification name field from the classification table for inventory items that belong to the "Sport" category. These resources may help you: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-joins/. Two records should be returned as a result of the query.
   6. Update all records in the inventory table to add "/vehicles" to the middle of the file path in the inv_image and inv_thumbnail columns using a single query. This reference may prove helpful - https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-replace/. When done the path for both inv_image and inv_thumbnail should resemble this example: /images/vehicles/a-car-name.jpg
   7. When done with the six queries, copy and paste queries 4 and 6 from the assignment 2 file to the database rebuild file, at the bottom of that file (this is the file that you began in the Team Activity that contains the SQL to create your database and the tables in it). Make sure these two queries are the last thing to run when the rebuild file is complete. By the end you should have two files - one with the 6 queries from Task 1, and the second with all the queries to rebuild your database, along with copies of the 4th and 6th queries from Task 1.

Task Two - Destroy and Rebuild the Database

You should already be aware that Render.com removes free-tier databases after 30 days. Therefore, it is critical that you know how to restore your database when that happens. Task two requires you to go through the entire process manually, so you know how to do it again when the time comes. You will probably need to do this at least twice. Once to make sure you know the process and the second time to record a video of you doing so. The video should be less than four minutes in length - so you need to know exactly what you're doing.

1. Destroy the database service in the Render.com dashboard by clicking on the database service, scrolling to the bottom and clicking "Delete Database" and following the provided directions.
2. Create a new database following the directions from this unit as needed. The database name cannot be the same as the old one, but can be similar.
3. Delete the existing connection to the remote database in the local tool: pgAdmin or SQLTools.
4. Create a new connection between the database and one of the local tools: pgAdmin or SQLTools.
5. Run the SQL code in your database rebuild file (you should already have all these queries stored in the database folder of your project), as a set of queries, run at the same time, to build the Type object, the three tables, and populate the classification and inventory tables. And, the copies of queries 4 and 6 from Task 1, should be run as the last items in the database rebuild file.
6. Show that the structures and data exist.

Submission

• Work with your learning team to help one another to accomplish each task and post to and read the weekly discussion board to help one another. Remember that each individual is responsible for her or his own work, this is not a group project.
• Check your work to ensure that it meets all the expectations listed.
• Create a video showing the process described in task 2. You will want to pause the video while the database is being created, which is usually less than five minutes.
• Post your video to your YouTube channel as "Unlisted" and copy the link to the video when published.
• Upload the project to your GitHub repository and copy the URL to the repository.
• Submit the YouTube video URL AND the GitHub repository URL as a comment in the assignment submission.
• Note: Because the production server does not change with this assignment, you do NOT need to submit the production URL for assignment 2. However, you will need to do so for all other assignments!

Grading Matrix

Objective 4

• All Task 1 SQL statements are in a single file, named assignment2.sql within a folder named database.
• The Tony Stark insert SQL statement works.
• The Tony Stark update SQL statement works.
• The delete Tony Stark SQL statement works.
• The description update SQL statement works.
• The select query using a JOIN SQL statement works.
• The inv_image and inv_thumbnail update query works.
• The SQL file containing all queries to build the type, tables and insert data is present and works.
• Obj. 4 value: Refer to grade book

Objective 6

• The Task 2 video showing the database delete and restoration process is present and complete, narrated using your voice, and less than 4 minutes in length. The video URL must be present and correct for Task 2 to be graded.
• The files for Tasks 1 and 2 are both present and complete in the "database" folder within the GitHub repository.
• Obj. 6 value: Refer to grade book