CMIS 420 Final Project Advanced Relational Database Concepts and Applications

Description

CMIS 420 Final Project Advanced Relational Database Concepts and Applications

1. Description:
In the final project, you will demonstrate your ability to design, build, and populate a small data warehouse to support decision-making, business modeling, and operations research. Using data supplied by me, prepare a star schema and several queries that support decision-making. Submit the following deliverables:
1) SQL scripts showing the design, loading, and queries of the database.
2) results of running the script with the SQL*Plus spool command.
3) presentation to your fellow classmates describing the data warehouse, its design, and possible uses and applications for critical business decisions.

2. Tasks to be completed:
Based on our operational database, movie distributing and renting system (focus on movie distribution and sale), build a data mart (star schema) which can answer the following questions:
1) Which movie and\or movie category have the better sale by months and\or quarters?
2) Which distributor had best or better earnings (suppose profit margin is 12% for all the distributors) by months and\or quarters??
3) Which store should be the better sale target for movie sale?
The following link provides zip file containing a script to create a PL/SQL package which adds ordering records to your database. and another script to execute the procedures in the package to complete the tasks. At the end of execution, you will expect 500 to 1000 records in orders table and 3000 to 6000 records in order_items table. These data plus the existing data would serve you as operational data for your data warehousing project. The data in your dimension and fact tables should be derived from this database.
Run create_package.sql first, and then run_all.sql to execute the procedures in the package.