Reference no: EM132302797

Tasks of the Assignment

Part 1

Scenario: Mechanical Production Factories (MPF) Database System

MPF is a company that produces customised mechanical products within Europe. The company produces a rang of mechanical products at several factories. Information about which work force are 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 an 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

Implementing Object-Relational version of the MPF Database

Implement the MPF Database using object-relational (O-R) features of Oracle 10g//11g. You need to discuss your approach to the implementation (2 marks) and provide complete code.

The discussion of your approach should highlight the choices you have made during implementation of your O-R version of the database. You need to justify your approach/choices (e.g., why object types / tables you created are appropriate for the database).

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 (2013) provides more detailed documentation on Oracle object-relational features.

Populating the O-R version of the MPF Database

Using PL/SQL and/or SQL, populate your O-R version of the MPF 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).

Querying the O-R version of the MPF Database

For this part, you must use PL/SQL procedures (2 procedures, each worth 4 marks). Answer the following questions after populating the object-relational database (i.e., after completing part 3.2):

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.

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.

NOTE the following important points for Parts 3.1 to 3.3:

- You must submit all the SQL and PL/SQL code used for creating, populating and querying the O-R database.
- You must submit the output of running your code (e.g., spool files).

Contrasting your O-R version with the relational version of the MPF Database

Compare and contrast the relational version of the MPF Database with your object- relational version from part 3.1. You should highlight the advantages and disadvantages of both versions. Comment on which version will best suit the MPF Database.

Part 2

1. The Sales History (SH) Data Warehouse

SH is a sample database schema provided by Oracle, which has been extensively used in the Oracle's Data Warehousing Guide (Lane, 2013) The SH schema, as shown in Figure 3, consists of a big fact table, SALES, and five relatively small dimension tables: TIMES, PROMOTIONS, CHANNELS, PRODUCTS and CUSTOMERS. The additional COUNTRIES table linked to CUSTOMERS creates a simple snowflake. The model and the attributes are aimed at demonstrating data warehousing functionality like star transformation and query rewrite. They do not necessarily represent the optimal approach for this kind of data warehouse in real productive environments; and such a design would be driven more by business requirements than by the star itself. Table 1 shows the cardinalities of the tables in the SH schema.

Table	Number of Rows
Customers	55,500
Countries	23
Products	72
Sales	9,18,843
Times	1826
Promotions	503
Channels	5

Table 1: Cardinalities of SH tables.

Usually, a star schema includes a single fact table and few dimensional tables, however, the SH schema designer identified another useful fact table called COSTS, which is linked to the TIMES and PRODUCTS dimensional tables. Figure 3 shows two fact tables: SALES and COSTS.

1. Implementation of SH schema

Table 2 shows the files that constitute the implementation of the SH schema using Oracle 10g/11g/12c.

File	Description
sh_cre.sql	Script for creating the base tables of the SH data warehouse.
sh_cons.sql	Script for enabling constraints.
sh_drop.sql	Script for dropping the SH schema objects.
sh_idx.sql	Script for creating indexes.
sh_pop1.sql	Script for loading data into COUNTRIES and CHANNELS tables.
sh_pop2.sql	Script for populating the TIMES dimensional table.
sh_schema_objects.sql	Script for calling other scripts e.g. sh_cre.sql for creating base tables and populating the small dimensional tables.
sh_cremv.sql	Script for creating materialized views.
sh_ext_table.sql	Script for creating an external table.
sh_main.sql	Script that calls several other scripts including e.g. sh_cremv.sql, sh_idx.sql etc.
sh_costs.sql	Script for populating the COSTS fact table.
sh_hiera.sql	Script for creating dimensional hierarchies, which are called DIMENSION in Oracle.

Table 2: Files used to implement the SH schema.

3. Two versions of SH schema/database

1) The default and optimized version of the SH database is created under the SH2 user. You have read-only access to query any of the tables of SH database by prefixing any table or view name by "SH2.", e.g., to query the contents of CHANNELS table, use:

SELECT * FROM SH2.CHANNELS;

2) An un-optimized version of the SH database has been created under your own username DWn. You will be given a special username and password for doing this part of the assignment (i.e. an Oracle account starting with DW and followed by a number e.g. DW1, DW2, etc). Throughout this part, you MUST use this special username. However, it is assumed that you will not create any indexes on any of the tables in your own personal version of the SH until asked to do specifically. Moreover, neither materialized views (see sh_cremv.sql) nor dimensional hierarchies (see sh_hiera.sql) should be created in your version of the SH database.

4. Important Note

Whenever, you need to assess the performance of existing database structures (e.g., materialized view, index, dimensional hierarchies) you must refer to SH2 as above (using the "SH2." prefix). Any new database structures that you need to create should be created under your username. Likewise, when you need to assess the performance of any new database structures that you create under your DWn username, you need to prefix all tables/views/materialised views accordingly, e.g., to query the contents of your CHANNELS table, use:

SELECT * FROM DWn.CHANNELS;

5. Tasks of the Assignment

Note: You must submit all the SQL queries and any other code that you wrote in answering any of the parts / questions (e.g., the use of Explain Plan statements for the queries and their output).

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

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

Attachment:- Database Modelling.rar