Overview

This homework assignment is about applying DML statements and database logic in order to manage your data. You are given three two tables with sample data, you are to write the required SQL statements to complete each task. You must consider entity integrity and referential integrity when writing your queries.

Tables and Data

Below are two tables used to solve the problems below. The Course table contains the courseID and courseName attributes for each class offered. There are currently three records in this table. The Class table contains the sections of each course that are offered. The courseID serves as a foreign key in the class table creating the 1:M relationship. The courseID and sectionID together is a composite primary key for the Class table. There would be other tables in the database but for this assignment we will focus only on these two table. All attributes in both tables consist of string data types.

Task 1 - INSERT

UNM hired a new professor to teach a new class. The name of the class is MGMT498 and is called Big Data Analytics. Professor Lee will be teaching one section of this course on Tuesdays from 5:00 until 7:30 PM. Your task is to write the SQL script(s) that will insert this data into the database.

Task 2 - UPDATE

Dr. Flor is planning on having his sabbatical this semester so Dr. Lee will be teaching his class for him. The Business Programming class will still be taught on the same day and time. Your task is to write a query to reflect this change in the database.

Task 3 - DELETE

The university has asked the professors to evaluate the curriculum and make it more current. There are new classes that needed to be added to improve the currency of our MIS program. To make room for a new class we must remove an older class so that the number of credit hours required to graduate will remain unchanged. It was decided that we no longer need MGMT450, so this class should be removed from the course catalog. Your task is to write the SQL script(s) that will remove MGMT450 from the database.

Course courselD MGMT329 MGMT330 MGMT450 courseName Database Management Business Programmin Computer Based Information Svstems Class sectionID (PK) classTime classDays Instructor 01 02 courselD MGMT329 MGMT329 MGMT330 MGMT450 MGMT450 11:00-12:15 | 11:00-12:15 | .W 9:00-11:45 F 12:30-1:45 TR 12:30-1:45 French Bose Flor French Bose 01 02