HW4: CRUD Operations

Due: Monday, Feb 19th

Image source: MySQL Workbench

Objectives

• Write an SQL script (file) that creates a table and imports data.
• Modify data using INSERT, UPDATE, and DELETE statements.

Important

This assignment must be completed individually. Your work must comply with the JMU Honor Code. Authorized help is limited to asking questions in class, during office hours, or via email. Copying code from someone else, including the use of generative AI, is prohibited and will be grounds for a reduced or failing grade in the course.

Part 0: Getting the Data

For this assignment, you will create a small database about cars. The data you will use comes from the CORGIS Datasets Project.

Visit the Cars CSV File page on the CORGIS website. Download cars.csv and open the file using a spreadsheet app (like Microsoft Excel or LibreOffice Calc) to become familiar with the data. The data has 18 columns and 5076 rows (not counting the header row).

Read the "Key Descriptions" on the download page to interpret the data. Notice that some of the comments are incorrect. For example, the comments for height, length, and width all say: "Unknown values are stored as 0. Unfortunately, many cars do not report this data." However, none of the rows in cars.csv have a value of 0 for height, length, or width.

Part 1: Create and Import

Complete the following steps using MySQL Workbench. Save your work in a file named hw4-create.sql.

1. Begin the file with the following comment. (Replace Ada's name with your own.)

   -- CS 374 HW 4 Part 1
   -- Name: Ada Lovelace
   

2. Write a CREATE TABLE statement for the cars.csv file. You may shorten the column names for convenience. For example, instead of "Engine Information.Engine Type" you may write EngineType. Use appropriate data types for each column. Declare columns as NOT NULL whenever possible.

3. Add a "DROP TABLE IF EXISTS" statement above your CREATE TABLE statement. Make sure you can run the script again and again without error. The script should both drop and create the table, allowing you to start over.

4. Write a "LOAD DATA LOCAL INFILE" statement to import the contents of cars.csv into the table you created.

   Note: In order for LOCAL INFILE to work, you must add a setting to your MySQL Workbench connection. Right-click and edit your connection, go to the Advanced tab, and add the following line under Others:

   OPT_LOCAL_INFILE=1
   

5. Write a SELECT statement to show the contents of your table. The query should return the same number of rows as are in the original cars.csv file.

Part 2: Data Modifications

Complete the following steps using MySQL Workbench. Save your work in a file named hw4-modify.sql.

1. Begin the file with the following comment. (Replace Ada's name with your own.)

   -- CS 374 HW 4 Part 2
   -- Name: Ada Lovelace
   

2. Write an INSERT statement to add the following car to the database:

   Column	Value
   Dimensions.Height	56
   Dimensions.Length	198
   Dimensions.Width	78
   Engine Information.Driveline	All-wheel drive
   Engine Information.Engine Type	3-phase AC induction motor
   Engine Information.Hybrid	False
   Engine Information.Number of Forward Gears	1
   Engine Information.Transmission	Direct Drive
   Fuel Information.City mpg	124
   Fuel Information.Fuel Type	Electric
   Fuel Information.Highway mpg	115
   Identification.Classification	Electric Car
   Identification.ID	2023 Tesla Model S
   Identification.Make	Tesla
   Identification.Model Year	2023 Model S
   Identification.Year	2023
   Engine Information.Engine Statistics.Horsepower	1020
   Engine Information.Engine Statistics.Torque	1050

3. Write an UPDATE statement to change all Engine Information.Hybrid values to False, unless the engine type ends with the word Hybrid.

   • Hint: The following query shows how to search for Hybrid cars. When used with LIKE, the % character is a wildcard meaning "zero or more characters."

     SELECT * FROM car
     WHERE EngineType LIKE '%Hybrid';
     

   • Note: In order for this query to run, you need to disable a setting in MySQL Workbench. Go to Edit → Preferences, click SQL Editor (on the left), and uncheck Safe Updates (at the bottom).

4. Write a DELETE statement that removes all cars that use E85 fuel but get less than 15 miles per gallon on the highway.

5. Write a SELECT statement to show all the cars from 2010 or 2023. The query should return 1706 rows.

Note

Test your scripts before submitting! You should be able to run all of hw4-create.sql, followed by all of hw4-modify.sql, without any errors. If you make a mistake during Part 2, be sure to run hw4-create.sql again to reset the database.

Submission

Submit both files to the HW4 assignment on Gradescope. We will grade this assignment manually by looking at your code.