Solved by a verified expert :MIS582 COURSE PROJECT
The Course Project will consist of three tasks, each worth
50 points due in Weeks 2, 4, and 6. Successful completion of the iLabs will
help you in completing the project tasks.
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,
your instructor will provide you with the data dictionary of the database. 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. To complete this assignment, you will need to be able to
run MySQL via Omnymbus.
Deliverables
1. Corrected ERD
based on the instructor feedback and data dictionary. The data dictionary
titled COURSE PROJECT Week6Task3DataDictonary.xlsx can be found in the folder
titled Course Project Documents. Save your file as
Lastname_Task3_revisedERD.vsd.
2. Script file
as Lastname_task3.sql which includes all the drop, create, alter, and insert
statements needed to create the ABC Company database.
3. Word document
showing the run result of the script file saved as Lastname_task3Result.docx.
4. When you are
done, upload a zip file containing the three 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:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS
Inventory CASCADE;
DROP TABLE IF EXISTS
inventory CASCADE;
DROP TABLE IF EXISTS
line_item CASCADE ;
DROP TABLE IF
EXISTS product CASCADE;
DROP TABLE IF EXISTS
orders CASCADE;
DROP TABLE IF EXISTS
store CASCADE;
DROP TABLE IF EXISTS
employee CASCADE;
DROP TABLE IF EXISTS
department CASCADE;
DROP TABLE IF EXISTS
Warehouse CASCADE;
DROP TABLE IF EXISTS
region CASCADE;
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 file with the name Lastname_Task3.sql.
Login to your MySQL account via Omnymbus as specified in the
tutorial Login MySQL Omnymbus Environment.docx noted at the bottom of this
document
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.
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.
