The Course Project will consist of three tasks, due in Weeks 2, 4, and 6. Successful completion of the labs will help you in completing the project tasks.
Week 6: Task 3: Database Construction Using MySQL and Research Paper (120 points)
Note: MySQL is required for this project. Please ensure you can run MySQL via Omnymbus.
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 you must create for the ABC Company database. Every entity is 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.
1. Corrected ERD based on the instructor feedback and data dictionary. The data dictionary titled “COURSE PROJECT Week6Task3DataDictonary.xlsx” is found in the folder titled Course Project Documents. Save your file as Lastname_Task3_revisedERD.vsd.
2. Script file saved 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. Word document consisting of a research paper as described Step 4 below. This document should be 2–3 pages (double spaced).
5. When done, upload zipped file containing three files
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.
• 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.
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 file with the name Lastname_Task3.sql.
• Log in 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.
Add data to your database.
• In your SQL Script, write insert statements to successfully insert data into each table.
• A minimum of three 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.
Writing a research paper on the role of databases in real-life business systems.
• Paper topic description: Databases have many applications in businesses. Discuss the role of databases in one of the following applications.
o Business intelligence
o Enterprise-wide applications (e.g., ERP, CRM, SCM, etc.)
o Search engines (e.g., Google’s Bigtable)
o A specific industry (e.g., banking)
o Another topic (approved by the instructor)
• The paper should be 2–3 pages, double spaced.
• You must use at least three authoritative resources other than your textbook. These must be displayed in a bibliography and must be clearly cited in the body of the paper.
Points for will be awarded according to the following rubrics.
• 10 points: Visio drawing: revised E-R model provided as a Visio diagram
• 20 points: CREATE statements: successfully creating all tables
• 10 points: Primary keys: correct primary keys created for all tables
• 10 points: Foreign keys: all required foreign keys created
• 10 points: ALTER statement: any alter statements required
• 10 points: INSERT statements: minimum three rows per table found
• 50 points: Research paper
Tutorials to Consult With
M10C_KROE8352_13_SE_WC10C.pdf in the folder titled My SQL Documents in Files section for all things to do with MySQL, including example SQL
Login MySQL Omnymbus Environment.docx in the folder titled Omnymbus Tutorial Files in Files section for how to log in to the Omnymbus Environment
Creating a Database in MySQL Omnymbus Environment.docx in the folder titled Omnymbus Tutorial Files in Files section for how to create a database in 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 Files section for how to create and run your SQL Scripts
DBP-e13-Appendix-F.pdf in the folder titled Visio Instructions Documents in Files section for how to use Visio 2013
kroenke_dbp12e_appendix_f.pdf in the folder titled Visio Instructions Documents in Files section for how to use Visio 2010
COURSE PROJECT Week6Task3DataDictonary.xlsx in the folder titled Course Project Documents in Files section
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at firstname.lastname@example.org.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at email@example.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at firstname.lastname@example.org.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.