+61480015851
Mechanical Production Factories (MPF) Database System - Computer Science Assessment Answer
Download Solution
Order New Solution
Internal Code: 1AGCII
TASKS
(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.
(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).
(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.
(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.
(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 n times, once for each of the n different aggregation levels.
With the introduction of ROLLUP in the Faculty of Engineering & Environment – KC7013 Database Modelling (2018-19) 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.
b. Using set operation UNION ALL (and not ROLLUP), write an SQL query that produces the same result as the query in (a) above.
c. Using EXPLAIN PLAN, provide a detailed discussion analysing costs of evaluating the above queries (i.e. with and without ROLLUP).
Mechanical Production Factories (MPF) Database System - Computer Science Assessment Answer
Part 1 Scenario: Mechanical Production Factories (MPF) Database System MPF is a company that produces customised mechanical products within Europe. The company produces a range of mechanical products at several factories. Information about which workforce is assigned to which production orders and kept in the force usage register. In order to access information quickly and to ensure that all past records are available for audit purposes, the company developed a database. Figure 1 shows a UML class diagram, which provides a conceptual model of the database. Relational Design for MPF Database System A conceptual model of a database may be implemented using any database system (e.g. relational, object-relational, object-oriented). However, to start with, we have mapped the MPF’s conceptual model onto a relational logical model. Figure 2 details the relations for the implementation of the database using a relational database system. Note that Figure 2 uses shorthand / abbreviated notation for data types/domains for describing various attributes of the relations involved in the database.
TASKS
(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.
(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).
(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.
(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.
(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 n times, once for each of the n different aggregation levels.
With the introduction of ROLLUP in the Faculty of Engineering & Environment – KC7013 Database Modelling (2018-19) 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.
b. Using set operation UNION ALL (and not ROLLUP), write an SQL query that produces the same result as the query in (a) above.
c. Using EXPLAIN PLAN, provide a detailed discussion analysing costs of evaluating the above queries (i.e. with and without ROLLUP).
This Computer Science Assignment has been solved by our Computer Science 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.