Identify three new indexes and justify why they could be useful.
LO1: Demonstrate deep knowledge of key concepts of data warehousing, data analytics, data standards, and data quality
Appraise, analyse, design, develop and evaluate data warehousing and data analytics solutions using Oracle database system
Develop critical awareness of the responsibilities of database developer with respect to professional, legal, security and ethical issues individually or as part of a team
Assessment Background and Scenario
This assessment is based on two scenarios as follows:
- The Sales History (SH) Data Warehouse scenario. SH is a sample database schema provided by Oracle, which has been extensively used in the Oracle`s Data Warehousing Guide (Lane, 2013). The details of this scenario are provided in Appendix 1.
- UniTel`s Customer Churn dataset described in Appendix 2.
Assignment Questions
Part 1: Data Warehousing Tasks
This part is based on the Sales History scenario as described in Appendix 1.
You must submit all the SQL queries and any other code that you wrote in answering any of the tasks / questions (e.g., the use of Explain Plan statements for the queries and their outputs using Spooling or other suitable means).
- Study the index definitions in sh_idx.sql. Discuss in detail (using cost-based analysis) why these indexes (choose three different ones) are useful for answering queries over the SH2 and DWU versions of the database. You should not run the sh_idx.sql script at all.
- Identify three new indexes and justify why they could be useful. Write the SQL code for creating these indexes under your DWU account. Give example queries with cost-based analysis for both DWU account (which will have the new indexes) and SH2 shared schema (which will NOT have any of your new indexes). Alternatively, you may choose to run the same queries on your DWU account before and after creating your proposed three indexes.
- Given the two materialized views (MVs) defined in sh_cremv.sql, discuss in detail why these MVs are useful for users of the SH database. You should provide detailed examples of cost based analysis, e.g., using Explain Plan for running sample queries on both SH2 and DWU to illustrate your answer. You should not run the sh_cremv.sql script at all.
- Identify three new MVs based on the base tables in the SH schema under your DWU account and justify why they would be useful for the users of your data warehouse. Write the SQL code for creating these MVs. Moreover, run sample queries on both SH2 and DWU to ensure that queries running on DWU will be re-written by Oracle to use your proposed three MVs instead of the base tables used in the sample queries. Note that you must not query your MVs directly in the FROM clause; let the Oracle Query Optimizer re-write the queries and answer them using your proposed MVs.