CMIS 420 Homework 5 Online Vehicle Sales Data Warehouse

Description

CMIS 420 Homework 5 Online Vehicle Sales Data Warehouse

This is a database design for the data warehouse you will create, populate, and query for Project #2 and the Authentic Assessment final project.
This week you will ensure that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIME tables are created and populated.
1) 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 or any other column names you already have.

2) Your DEALERSHIPS table should already be created and populated. Don’t worry about changing any column names you already have.

3) Create the VEHICLES table via SQL. If you already have a table with this name, use the SQL RENAME command to change it to something else. 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 current 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 and the results.

4) Create the TIME table via SQL. The Sale_Day primary key column values should be all dates from your first sale date through and including your last sale date from your SALES table. The Day_Type values should be ‘Weekday’, ‘Weekend’, or ‘Holiday’ (this trumps Weekday and Weekend). Use a PL/SQL block to populate the TIME table. After populating your TIME table execute the SQL statement “SELECT day_type,COUNT(*),MIN(sale_day),MAX(sale_day) FROM time GROUP BY day_type ORDER BY day_type” to show the summarized contents of your table. Show all your SQL and PL/SQL and the results.

All SQL and PL/SQL should be executed via one or more SQL script files. The best approach is to have separate SQL script files for Steps #3 and #4 and then an SQL script file that calls both of them. Submit SQL*Plus SPOOL files produced by your SQL and PL/SQL showing all your SQL and PL/SQL code and the results, or if using iSQL*Plus or other GUI (e.g. SQL Developer), a single Word or PDF file of screen snapshots showing both your SQL and PL/SQL and the results. Do NOT submit your SQL script file.