Database Modelling - Mapping to Programme Goals and Objectives - Engineering Assignment Help

Assignment Task :

Assessment Background and Scenario
This assessment is based on two scenarios as follows:
a) The scenario ‘Academic Information System (AiS)” of a fictitious university called University of Gharnata. The university wants to develop an information system to support its academic activities. The details of this scenario are provided in Appendix 1. 
b) 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 2.
 

Assessment 2 Brief
Assignment Questions
Part 1: Implementing object-relational database for AiS 
This part is based on the ‘AiS’ scenario as described in Appendix 1. (A) Using object-oriented / extended entity relationship modelling, produce a conceptual and logical database model for AiS.
You have the option to produce a fresh conceptual and logical database model using object-oriented or extended entity relationship modelling or incorporate advanced
entity modelling concepts (e.g., aggregation & composition, inheritance or generalisation / specialisation/, etc) into your entity relationship model for AiS from
your assignment 1.
(B) Create an object-based database for AiS using object-relational (O-R) features of Oracle 10g//11g/12c based on the conceptual and logical database model from (A) above.
Note that all relationships (e.g., one-to-one, one-to-many, many-to-many) must be bi-directional. Moreover, each to-many side of a relationship (e.g., in case of many-to- one and many-to-many) should be implemented using nested tables. In addition, your implementation should allow all objects to be shareable (i.e. all relationships should be REF based). Oracle (2005a) provides a more detailed documentation on Oracle object-relational features.
(C) Populate the O-R version of your database using data from relational version of the AiS database you developed in Assignment 1.
Using PL/SQL and/or SQL, populate your O-R database by querying the relational tables you implemented earlier during your Assignment 1 solution. This task can be
seen to contain the following sub-tasks:
(i) Creating / inserting objects in your object tables.
(ii) Populating one-to-many, many-to-one, and many-to-many relationships among the objects created in sub-task (i).

(A) Study the index definitions in sh_idx.sql. Discuss in detail (using cost-based analysis) why these indexes (at least two of them) are useful for answering queries over the SH2 and DWn versions of the database. You should not run the sh_idx.sql script at all.
(B) Identify two new indexes and justify why they could be useful. Write the SQL code for creating these indexes under your DWn user. Give example queries with cost-based analysis for both DWn (which will have the new indexes) and SH2 users (which will not have any of your new indexes).
(C) Given the 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 (cost based analysis, e.g., using Explain Plan for running sample queries on both SH2 and DWn) to illustrate your answer. You should not run the sh_idx.sql script at all.
(D) Identify two other MVs based on the base tables in the SH schema under your DWn user and justify why they would be useful. Write the SQL code for creating
these MVs.
(E) Prior to the introduction of the aggregation function ROLLUP, there was no possibility to express an aggregation over different levels within one SQL statement without using the set operation UNION ALL. Every different
 

Assessment  2 Brief
aggregation level needed its own SQL aggregation expression, operating on the exact same data set n times, once for each of the n different aggregation levels.
With the introduction of ROLLUP in the recent editions, Oracle provided a single SQL command for handling the aggregation over different levels within one single SQL statement, not only improving the runtime of this operation but also reducing the number of internal operations necessary and reducing the workload on the system.
i. Using ROLLUP, write an SQL query over the SH schema under your DWn user involving one fact table (SALES or COSTS) and at least two dimension tables. Provide reasons why your query may be useful for users of the SH data warehouse.

This Engineering Assignment has been solved by our Engineering Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.