AiS Database: Object-Relational Modeling and Performance Analysis

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.

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.

Object types including nested table types creation code :

Object tables including nested tables creation code

(C) Populate the O-R version of your database using data from relational version of the AiS database you developed in Assignment 1.

(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)

Provide SQL or PL/SQL code for populating one-to-many, many-to-one, and many-to-many relationships among the objects created in sub-task (a)

Provide spool file output or other forms of output from running the above SQL and/or PL/SQL code for database population. If no correct output provided, then 2 marks will be subtracted

For this part, you must use PL/SQL procedures (2 procedures, each worth ). Answer the following questions after populating the object-relational database: q1) Display names of students, details of the course they study, details of the module they have studied and their marks for all postgraduate students.

Display details of all people of ‘AiS’ (students and academic staff), e.g., their names, their home addresses and name of the department where they work or study.

Compare and contrast your relational and object-relational versions of the AiSdatabase. You should highlight the advantages and disadvantages of both versions. Comment on which version will best suit the AiS database.

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.

Provide the details of the 2 indexes you are going to compare their performance impact on SH2 (i.e., name the indexes and on which tables those indexes were

created in SH2, these indexes must not exist in your DWn version) 1 Mark: Provide the 2 SQL queries you are going to run to compare the performance impact of the above 2 Indexes on SH2 and the version of the same queries on DWn :

Provide Explain Plan statements & outputs for the above 2 SQL queries you have run  to compare the performance impact of those 2 Indexes on SH2 and their version of the same queries on DWn :

Provide Discussion of the cost-based comparison of the above 2 sets of queries and their explain plan cost figures :

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 costbased analysis for both DWn (which will have the new indexes) and SH2 users (which will not have any of your new indexes).

Provide the SQL Code for the 2 new indexes you have created on your DWn database for comparing their performance impact on DWn (i.e., these indexes must not exist in SH2):

Provide 2 SQL queries you are going to run to compare the performance impact of your own 2 new Indexes on DWn and the version of the same queries on SH2 :

Provide Explain Plan statements & outputs for the above 2 SQL queries you have run to compare the performance impact of your 2 Indexes on DWn and their version of the same queries on SH2:

Provide Discussion of the cost-based comparison of the above 2 sets of queries and their explain plan cost figures :

(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.

Provide SQL code you used to create the 2 new MVs you created in your own DWn database (i.e., these MVs must not exist in SH2) :

Provide the 2 SQL queries you are going to run to compare the performance impact of your own 2 new MVs on DWn and the version of the same queries on SH2:

Provide Explain Plan statements & outputs for the above 2 SQL queries you have run to compare the performance impact of your 2 MVs on DWn and their version of the same queries on SH2 :

Provide Discussion of the cost-based comparison of the above 2 sets of queries and their explain plan cost figures :

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 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.

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.