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
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
- 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.
- Script file as Lastname_task3.sql which includes all the drop, create, alter, and insert statements needed to create the ABC Company database.
- Word document showing the run result of the script file saved as Lastname_task3Result.docx.
- 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 ;
- 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 ;
- 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)