MIS 582 Week 6 Task 3 Database Construction Using MySQL

MIS582 COURSE PROJECT

Week 6: Task 3: Database Construction Using MySQL (50 Points)

NOTE: MySQL is required for this project. Please ensure you can run MySQL via Omnymbus.

PROJECT OVERVIEW:

Scenario and Summary

In this assignment, you will create and populate a MySQL database based on the ERD you created in task2. Update the ERD you submitted for task2 with the corrections required by your instructor. To help you with this task, the data dictionary of the database is available in Doc Sharing. This will provide you information on every table that you must create for the ABC Company database. Every entity can be found listed in the data dictionary along with the PK/FK information as well as the data types for the attributes. These data types are MySQL data types so they can go directly in the create table statements. Using the corrected ERD and the data dictionary provided, write the necessary MySQL statements to create the ABC company database.

Deliverables

1. Script file as Lastname_task3.sql which includes all of the drop, create, alter, and insert statements needed to create the ABC Company database.

2. Text showing the copied and pasted run result of the script file saved as Lastname_task3Result.txt.

3. When you are done, upload the two files to the Week 6: Course Project Week 6 Task 3 Dropbox.

STEP 1

Revise the ERD you submitted for the previous task with the feedback provided by your instructor and the help of the given data dictionary document.

STEP 2

Create a file with the name Lastname_Task3.sql

In your sql file, to drop the tables in case you run the script repeatedly, please include the following at the top of your script (Please note that the capitalization must conform to YOUR table creates!):

o SET FOREIGN_KEY_CHECKS=0;

o DROP TABLE IF EXISTS Inventory CASCADE;

o DROP TABLE IF EXISTS inventory CASCADE;

o DROP TABLE IF EXISTS line_item CASCADE ;

o DROP TABLE IF EXISTS product CASCADE;

o DROP TABLE IF EXISTS orders CASCADE;

o DROP TABLE IF EXISTS store CASCADE;

o DROP TABLE IF EXISTS employee CASCADE;

o DROP TABLE IF EXISTS department CASCADE;

o DROP TABLE IF EXISTS Warehouse CASCADE;

o DROP TABLE IF EXISTS region CASCADE;

o SET FOREIGN_KEY_CHECKS=1;

In your sql file, write create table statements for each table in the database. You can try to create each table one at a time and then test the creation of the table before going on to creating the next table. Use the data types given in the data dictionary. Make sure every table has primary key(s), foreign keys if required, and not nulls if required. (Refer to data dictionary for this information)

Save the completed script file.

Create a database following the proper naming convention as specified in the tutorial Creating a Database in MySQL Omnymbus Environment.docx noted at the end of this document.

Click on the database name you just created.

Run the script file as specified in the tutorial Running SQL Scripts in MySQL Omnymbus Environment.docx noted at the end of this document. The technique is demonstrated in the Live Lecture. If there are no errors, all tables will be successfully created. You may check them by adding SHOW TABLES at the end of your script file.

If you have errors, correct those errors and execute the script again until it is error free.

STEP 3

Adding data to your database.

In your SQL Script, write insert statements to successfully insert data into each table.

A minimum of 3 rows are required for each table.

In your SQL Script, check the data in each table by running a SELECT * FROM table query.

Run your SQL Script to test it.

When you are satisfied with you’re the output of your SQL Script, copy the output to a Word File.

Rubrics

Points for will be awarded according to the following rubrics.

10 Points: Visio drawing: Revised E-R model provided as a Visio diagram.

15 Points: CREATE STATEMENTS: Successfully creating all tables.

5 Points: PRIMARY KEYS: Correct primary keys created for all tables.

5 Points: FOREIGN KEYS: All required foreign keys created.

5 Points: ALTER STATEMENT: Any alter statements required.

10 Points: INSERT STATEMENTS: Minimum three rows per table found.

Tutorials to Consult With

M10C_KROE8352_13_SE_WC10C.pdf in the folder titled My SQL Documents in Doc Sharing for all things to do with MySQL including example SQL.

Login MySQL Omnymbus Environment.docx in the folder titled Omnymbus Tutorial Files in Doc Sharing for how to log into the Omnymbus Environment.

Creating a Database in MySQL Omnymbus Environment.docx in the folder titled Omnymbus Tutorial Files in Doc Sharing for how to create a Database the Omnymbus Environment. Remember, all work must be done under a database your first create.

Running SQL Scripts in MySQL Omnymbus Environment.docx in the folder titled Omnymbus Tutorial Files in Doc Sharing for how to create and run your SQL Scripts.

DBP-e13-Appendix-F.pdf in the folder titled Visio Instructions Documents in Doc Sharing for how to use Visio 2013.

kroenke_dbp12e_appendix_f.pdf in the folder titled Visio Instructions Documents in Doc Sharing for how to use Visio 2010.

COURSE PROJECT Week6Task3DataDictonary.xlsx in the folder titled Course Project Documents in Doc Sharing.