Assignment on Conceptual Data Modeling and Data Warehousing Tasks

Part 1: Implementing Object-Relational Database for AiS (50 Marks)

¨You are required to use the Harvard Style of referencing and citation. The “Cite them right” guide is recommended for referencing and citation (Pears and Shields, 2008) which should be followed throughout your answer especially Part 3. A good alternative is Northumbria (2018). Please do not include references to lecture notes.

Intellectual / Professional skills & abilities:

3.Conceptual data modelling, relational database design and implementation in SQL & PL/SQL, and object-based databases.

4.Design and Implementation of a data warehouse using Oracle database system.

Students must retain an electronic copy of this assignment (including ALL appendices) and it must be made available within 24hours of them requesting it be submitted.

Academic Misconduct

The Assessment Regulations for Taught Awards (ARTA) contain the Regulations and procedures applying to cheating, plagiarism and other forms of academic misconduct.

The full policy is available at here

You are reminded that plagiarism, collusion and other forms of academic misconduct as referred to in the Academic Misconduct procedure of the assessment regulations, which are taken very seriously. Assignments in which evidence of plagiarism or other forms of academic misconduct is found may receive a mark of zero.

For textual components:

80-100% - The description will excellently cover all the specific topics requested. The written work will be fluent, clearly presented and of out-standing quality.

70-79% - The description will comprehensively cover all the specific topics requested. The written work will be fluent and clearly presented and of distinctive quality.

60-69% - The student will show a very good knowledge of the specific topics, with very good presentation skills and quality.

50-59% - The student will show an above average knowledge of the specific topics, with above average presentation skills and quality.

40-49% - There will be an adequate description of a significant proportion of the topics requested. There will be no major failures in presentation clarity.

There will be little or no information conveyed in an intelligible manner on the specific topics requested.

80-100% - The students will produce exceptional models (conceptual/logical/physical), and will demonstrate the use of notation/language, which have outstanding syntactic accuracy (e.g., adhering to ER modelling standards, error free SQL code), with exceptional semantic relevance (e.g., are relevant to the requirements of the particular scenario).

70-79% - The students will produce fully complete models (conceptual/ logical/ physical), and will demonstrate the use of notation/language, which have high syntactic accuracy, with high semantic relevance.

60-69% - The students will produce almost complete models (conceptual/ logical/ physical), and will demonstrate the use of notation/language, which have appropriate syntactic accuracy with reasonably well semantic relevance.

Part 2: Data Warehousing Tasks

50-59% - The students will produce fairly complete models (conceptual/ logical/ physical), and will demonstrate the use of notation/language, which have adequate syntactic accuracy with reasonable semantic relevance.

40-49% - The students will produce models, and will demonstrate the use of notation/language, which have some syntactic accuracy and semantic relevance.

Less than 40% - The students will not produce sufficient models, and/or will be unable to demonstrate the use of notation/language with significant syntactic accuracy and/or significant semantic relevance.

Assignment Questions

Part 1: Implementing object-relational database for AiS (50 Marks)

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

(D) Query the O-R version of your AiS 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:

1)Display names of students, details of the course they study, details of the module they have studied and their marks for all postgraduate students.

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

NOTE the following important points for Part 1 (A to D):

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

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

Part 2:  Data Warehousing Tasks

This part is based on the Sales History scenario as described in Appendix 2.

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

(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 under any circumstances as it will interfere with your cost-based analysis of query performance in Part 2 of this assignment.                                                                

(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_cremv.sql script at all under any circumstances as it will interfere with your cost-based analysis of query performance in Part 2 of this assignment.                                                                

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

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

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

You should hand in your answer for this assignment as a single word-processed document electronically on the ELP. A sample assignment template file will be uploaded on the ELP near the submission deadline.