Create the DEALERSHIPS star schema dimension table via SQL CMIS-420 Project #2 (added Step #0 on 11/24/14) In this assignment 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 LEOs 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. 0) 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. 1) 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 tables 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. 2) 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 Years Day, Martin Luther King Jrs Birthday, Presidents Day, Memorial Day, 4 th 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. 3) Create the SALES_FACTS star schema fact table via SQL. Ensure that yo…
Looking for solution of this Assignment?
WHY CHOOSE US?
We deliver quality original papers |
Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected |
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.We have ZERO TOLERANCE TO USE OF AI |
On-time delivery |
We will deliver your paper on time even on short notice or short deadline, overnight essay or even an urgent essay |