In this assignment you will complete the creation of your OVS, Inc. data warehouse database tables and then perform a number of decision support queries using the tables. 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. 1) Create the TIMES star schema dimension table via SQL. This is “TIME” table in the star schema figure. 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). Set all occurrences of the following days for your date range to be holidays: New Year’s Day, Martin Luther King Jr’s Birthday, President’s Day, Memorial Day, 4th of July, Labor Day, Columbus Day, Veterans Day, Thanksgiving, and Christmas. Use a PL/SQL block to populate the TIMES table. After populating your TIMES 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 code for this step and the results. 2) Create the SALES_FACTS star schema fact table via SQL. Ensure that you have declared foreign keys of Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID to reference your TIMES, VEHICLES, FINANCING_PLANS, and DEALERSHIPS tables, respectfully. Ensure that you have a primary key for the SALES_FACTS table that is a composite of the Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID columns. Do a DESC (i.e. DESCRIBE) of your SALES_FACTS table after it’s created. 3) Ensure that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIMES dimension tables and the SALES_FACTS fact table are all created and populated as required. Do a SELECT COUNT(*) FROM for each of the five tables. 4) Using PL/SQL stored procedure populate the SALES_FACTS table. One way to do this is to use four nested cursor loops to get every possible combination of the dimension tables’ primary keys and then the total vehicles sold and gross sales amount for each combination. If these values for 2 Total_Vehicles_Sold and Gross_Sales_Amount for a combination are zero then don’t INSERT a row into the SALES_FACT table. Only insert rows for combinations of the four foreign key columns where there were some vehicles sold. Another approach besides nested cursor loops is to use a single INSERT statement with a GROUP BY clause. After populating your SALES_FACTS table execute the query “SELECT COUNT(*) FROM sales_facts;” to show the row count. Also execute the query “SELECT SUM(vehicles_sold) FROM sales_facts;” to ensure that you have included all of your 200 or more sales. 5) Create a user-defined function called VEHICLES_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total vehicles sold by that combination. Execute your function for a sample input value of your choosing to demonstrate that it works correctly. 6) Create a user-defined fu…

Looking for solution of this Assignment?


We deliver quality original papers

Our experts write quality original papers using academic databases.  

Free revisions

We offer our clients multiple free revisions just to ensure you get what you want.

Discounted prices

All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount

100% originality

We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity

On-time delivery

We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay