KC7013 Database Modelling.

Assessment Background and Scenario

This assessment is based on two scenarios as follows:

a) The scenario Academic Information System (AiS)"of a fictitious university called University of Gharnata. The university wants to develop an information system to support its academic activities. The details of this scenario are provided in Appendix 1.

b) The Sales History (SH) Data Warehouse scenario. SH is a sample database schema provided by Oracle, which has been extensively used in the Oracle`s Data Warehousing Guide (Lane, 2013).The details of this scenario are provided in Appendix 2.
Assignment Questions

Part 1: Implementing object-relational database for AiS

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 freshconceptual 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-baseddatabase for AiSusing 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 databaseby 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:

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.

q2) 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 versionsof 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.sqlscript at all under any circumstances as it will interfere with your cost-based analysisof 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.sqlscript at all under any circumstances as it will interfere with your cost-based analysis of query performance in Part 2 of this assignment.