Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Download: http://solutionzip.com/downloads/hw6-cmis420/
- 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 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) 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.
- 2) 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.
- 3) 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.
- 4) 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.
- Your submission MUST be in a single Word or PDF file with all steps numbered and in order.
- Download: http://solutionzip.com/downloads/hw6-cmis420/
Add Comment
Please, Sign In to add comment