Computer Science

TASKS Part 1

3.1 Implementing Object-Relational version of the MPF Database (25 MARKS)

Provide a logical approach to implementation / conceptual view of nested or object relational structure (2 marks):

Provide SQL Code for creating object types and object-tables

(23 marks):

Provide spool file output or other forms of output from Running the above SQL code:

3.2 Populating the O-R version of the MPF Database (12 MARKS)

Provide SQL code for populating your Object-Relational Database with some sample data. This part can be seen to contain the following sub-tasks:

(a) Creating / inserting objects in your object tables.

(b) Establishing one-to-many, many-to-one, and many-to-many relationships (see the UML class diagram) among the objects created in sub-task (a).

Provide spool file output or other forms of output from Running the above SQL code for populating the database:

3.3 Querying the O-R version of the MPF Database (8 MARKS)

Q a) For each of the production order, display production details, dates and hours of their workforce usage and names and ids of the associated workforce staff.

Provide the PL/SQL code for creating the above procedure (4 marks):

Provide spool file output or other forms of output from Executing the above PL/SQL procedure:

Q b) For each of the product, display product details, details of the associated production orders and details of the factories where the products are produced.

Provide the PL/SQL code for creating the above procedure (4 marks):

Provide spool file output or other forms of output from Executing the above PL/SQL procedure

3.4 Contrasting your O-R version with the relational version of the MPF Database (5 MARKS)

Provide your answer here:

Part 2 (50 marks)

(i) 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 do not need to run the sh_idx.sql script at all.

(10 Marks)

Answer Part 2 (2.i)

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 (2 marks):

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 (4 marks):

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

(ii) 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). (10 Marks)

Answer Part 2 (2.ii)

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

2 Marks:

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

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 (3 marks):

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

(iii) 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 do not need to run the sh_idx.sql script at all. (10 Marks)

Answer Part 2 (2.iii)

Provide 2 SQL queries you are going to run to compare the performance impact of the 2 MVs in SH2 and the version of the same queries on DWn (4 marks):

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

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

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

(10 Marks)

Answer Part 2 (2.iv)

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

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

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 (2 marks):

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

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

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

(3 Marks)

Provide the SQL Query here (using ROLLUP):

b. Using set operation UNION ALL (and not ROLLUP), write an SQL query that produces the same result as the query in (a) above.

(3 Marks)

Provide the SQL Query here (WITHOUT using ROLLUP, i.e., using UNION ALL):

c. Using EXPLAIN PLAN, provide a detailed discussion analysing costs of evaluating the above queries (i.e. with and without ROLLUP).

(4 Marks)

Provide your answer here:

Order now and get 10% discount on all orders above $50 now!!The professional are ready and willing handle your assignment.

ORDER NOW »»