web assignment 2

Get Ready for Exams

Learn how to create secure exams, prevent cheating and more!

LMS Integration Update!

This fall, you’ll be able to push a button and send a 0 to your LMS gradebook for un-attempted student work in WebAssign .

Get the Details

New Instructor Experience

We're making teaching in WebAssign easier with instructor experience improvements, including a more intuitive site navigation and assignment-creation process.

Boost Student Motivation & Engagement

Do your students need motivation? Find new ways to engage them in your classroom with active learning.

The New Instructor Experience is Coming!

Teaching will be easier this fall with instructor experience improvements including a more intuitive site navigation and assignment-creation process. Learn More

Create the Path to Student Success

In the spotlight.

Stats Personality Quiz

Are you a Meanie, Super Anova, or a Bootstrapper? Find out your Stats personality and which title is right for you! Take Quiz

Events & Recordings

Get the most out of your course in WebAssign with live and recorded webinars that provide peers tips and best practices. Explore More

Cengage Unlimited

Unlimited access to quality, affordable course materials.

Join a Community Of Your Peers

Become a Power User

Learn new tips and stay-in-the know with expert advice , platform updates and current news on the WebAssign blog. CHECK IT OUT

Stand Out as a Student Ambassador

Make an impact on campus and in your community—while standing out to future employers—by becoming a Student Ambassador. LEARN MORE

Ready to Get Started?

WebAssign/Cengage
Centennial Campus, 1791 Varsity Dr., Suite 200
Terms of Use
Our Partners

Subscribe to our Newsletter

Get the latest tips, news, and developments.

Assignment 2

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.

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

Assumptions

This assignment assumes:

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

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:

Create a new assignment2.sql file inside the database folder of your project.
Build and test your SQL statements in pgAdmin or SQLTools using the course database.
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.
When your statements are working correctly, store them into the assignment2.sql file.
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, [email protected], Iam1ronM@n
Modify the Tony Stark record to change the account_type to " Admin ".
Delete the Tony Stark record from the database.
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.
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.
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
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. 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 90 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. This is entirely for your benefit. Be sure you do the process on your own.

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.
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.
Delete the existing connection to the remote database in the local tool: pgAdmin or SQLTools.
Create a new connection between the database and one of the local tools: pgAdmin or SQLTools.
Run the SQL code (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 restore file.
Test that the structures and data exist.
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.
Upload the project to your GitHub repository and copy the URL to the repository.
Submit the production URL AND the GitHub repository URL as a comment in the assignment submission.
Note: Even though the production server does not change with this assignment, submit the production URL for consistency.

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 GitHub and production URL's must be present and correct for assignment 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

Mastery in WebAssign

Build student confidence with multiple opportunities to practice course topics with the latest WebAssign improvement: Mastery .

Join the Summer of Learning

Join us for a variety of sessions on July 16 - 18, 2024, where you’ll gain valuable insights and best practices to address your top teaching challenges such as readiness gaps, Artificial Intelligence, and student engagement with WebAssign.