Data Management Concepts

IS 361 Database Management Systems -- Assignment 6

Fall 2015

Due: 11:59pm, Monday October 26, 2015

Last modified: October 19, 2015

Purpose

The purpose of this exercise is to practice creating and filling tables using SQL under MySQL.

Assignment (20 points)

This assignment is done under your own database (under your account name), not PVFC11e. Double click on your account name in MySQL Workbench once logged in in order to use your own database.

0) Take a look of the HW6_smallExample.sql file to get a feel of the different SQL statements you will work with in this homework.

1) Now look into CREATEPVFC11e-MySQL.sql. Be sure to change the use PVFC11e; line to your own database name before running the script. Run the script CREATEPVFC11e-MySQL.sql to create tables and populate them.

2) Edit the script to fix the dates so they load correctly. The given dates are in Oracle format and we need to change those to MySQL format dates. See the table Order_T for an example of how to do this. Note that date formats are specific to each vendor’s implementation of SQL, and this is one of the areas where code isn’t really portable. See if you can find another way of specifying dates to fix the dates in the other tables.

3) Scroll down to the last INSERT statement, then add statements under it to create

1 new product line and 2 new products in that line

2 new customers

3 new orders, each with at least 2 different items. Be sure to include the new products on one of the orders.

This is an odd place to put your code, but I need it in one place in order to grade it.

4) Save your changes and rerun your script at least twice in succession. It should be able to create an entirely new set of tables with no errors each time. Put your name in the comment section at the top of the script.

5) Using your own version of the database, in a separate file, write SQL commands to:

a. Remove all skill records that involve skill Sander

b. Update the price of all items in Product_T that are made of maple so that no item is less than $700.

c. Update the price of all items in Product_T that are made of cherry by 15%

d. Change the name and phone number of the salesman in the Northwest territory to something different.

You will likely have to rerun your creation script to restore the database as you attempt these commands. Be thankful you have it!

Notes

The create tables script is in HW6.zip.

Be sure to run this under your own account. Be sure to set the default schema to be the schema with your name . You don’t have write permission to the other schema i.e. PVFC11e.

Look for MySQL version of things if you use additional resources to complete this assignment, since there are slight variations among vendors (Oracle, SQL Server, Microsoft Access, DB2, …). Our textbook (Ch6) follows Oracle syntax.

· data definition statements:

CREATE TABLE

DROP TABLE

· data manipulation statements:

INSERT

UPDATE

SELECT

DELETE

One such place to look for MySQL references:

· MySQL - data definition commands:

· CREATE TABLE: http://www.techonthenet.com/mysql/tables/create_table.php

· DROP TABLE: http://www.techonthenet.com/mysql/tables/drop_table.php

· MySQL - data manipulation commands

· INSERT: http://www.techonthenet.com/mysql/insert.php

· UPDATE: http://www.techonthenet.com/mysql/update.php

· DELETE: http://www.techonthenet.com/mysql/delete.php

· MySQL Data Types: http://www.techonthenet.com/mysql/datatypes.php

Rubric

Task	Points
Creating tables	2
Fixing dates	3
Adding new data	7
Changing data	8

Turn In

Upload both your .sql files (modified creation script, commands for question 5) to HW6 on Canvas. I may also check your MySQL account to verify your work.