Click link for more description
CMIS-420 Project #2
In this assignment you will first perform a few more decision support queries as in Homework #3, but you will be creating PL/SQL user-defined functions and stored procedures. Then you will be creating a data warehouse database for Online Vehicle Sales (OVS), Inc. The data warehouse is comprised of 4 dimension tables and a fact table based on the Project #2 Star Schema diagram posted in LEO’s Week #6 area. Your OLTP database tables and star schema tables will reside in the same Oracle schema.
You can perform this assignment based on a database on Nova or any other Oracle system you wish, but you must use the Oracle RDBMS.
You should use one or more SQL script files to complete this assignment. Your script files should contain all your SQL and PL/SQL code. Do NOT submit your SQL script files. Doing so may result in confusion and will result in lost points.
Everything for this assignment must be in a single file. If you are using SQL*Plus you must put all your SQL, PL/SQL, and results together in a single SPOOL file. If you are using SQL Developer or other GUI, put all your screen snapshots in a single file for both your SQL statements and PL/SQL as they executed and the results. Failure to include all your SQL, PL/SQL, and all your Oracle execution results along with them will result in lost points.
Do NOT submit additional files as this only complicates the grading, and will result in lost points.
The specific assignment steps are listed below. In order to earn full credit you must keep your steps in the order shown, number your steps, and put everything in a single file.
Your FINANCING_PLANS table should already be created and populated. Your Plan_ID primary key is the Plan_Code column. Don't worry about changing this to Plan_Code or changing any other column names you already have.
1) Develop and execute a PL/SQL user-defined function ZIP_CODE_SALES that returns the total sales for a zip code when the zip code is supplied as the single input parameter. You may use any of your zip codes you wish. Show the PL/SQL statements in your function, the execution of your function, and the results returned.