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.
2) Develop and execute a PL/SQL user-defined function ZIP_CODE_SALES_WINNER that returns the zip code with the largest total count of car purchases. Since there can be a tie with two or more zip codes, ensure that the lowest numeric zip code is returned. Your returned value should match the results of Step #3 of Homework #3. Show the PL/SQL statements in your function, the execution of your function, and the results returned.
3) Develop and execute a PL/SQL stored procedure ZIP_CODE_MAKE_SALES_WINNER that returns the zip code and make with the largest total count of car purchases. Since there can be a tie with two or more zip codes and makes, ensure that the lowest numeric zip code and character make combination is returned, but as two separate output parameters. Your returned value should match the results of Step #3 in Homework #3. Show the PL/SQL statements in your stored procedure, the execution of your stored procedure, and the results returned.
4) Create the DEALERSHIPS star schema dimension table via SQL. Add at least 2 rows of data via INSERT statement(s). After populating your DEALERSHIPS table execute a "SELECT * FROM dealerships;” SQL statement to display the entire contents. Show all your SQL code for this step and the Oracle results from executing it.
5) Create the VEHICLES star schema dimension table via SQL. Change your existing OLTP VEHICLES table to OLTP_VEHICLES via the SQL RENAME command and change your SALES table’s foreign key to reference this new table name. For the Vehicle_Code primary key column use an Oracle sequence to populate the values. For the Description column use all concatenated combinations of Make and Model of vehicles you have. Use a PL/SQL block to populate the Description column by SELECTing the combinations from your OLTP_VEHICLES table and then INSERTing the combinations into your new VEHICLES table, which would best be performed via a cursor in a loop. After populating your VEHICLES table execute a "SELECT * FROM vehicles ORDER BY vehicle_code" SQL statement to display the entire contents. Show all your SQL and PL/SQL code for this step and the Oracle results from executing it.