Implementing Object Relational Database for AiS (50 Marks)

Creating a conceptual and logical database model for AiS using object-oriented or extended entity relationship modelling

(A) Using object-oriented / extended entity relationship modelling, produce a conceptual and logical database model for AiS.  (8 marks)

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

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

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 (8 marks)

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.

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

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

Creating an object-based database for AiS using Oracle's object-relational features

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

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

(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_idx.sql script at all. (10 marks)
    
(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. (10 marks)

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

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

Populating the object-relational database with data from the relational version of the AiS database

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

‘Academic Information System (AiS)” Scenario

The scenario described here is that of a fictitious university called University of Gharnata.  The university wants to develop an information system to support its academic activities.

The university has several academic departments. Each department provides one or more academic courses. Each course is composed of several modules, where a module may be part of more than one course. A student enrolls on a course and every year takes a specified number of modules. Note that several students are usually registered for a course. Every student is assigned a tutor at the start of the course, who is a faculty member (e.g., lecturer) in the department providing the course. A faculty member works for a department and usually teaches on several modules. Each module has a module tutor who is also a faculty member. A faculty member may be tutor for several modules.

Each department is chaired by a professor, who is a faculty member and works for the same department. It is important that the system makes a note of when a professor became the chair of a department. The details of a department include its name, a primary location / address where its main building is located, a secondary address where it may provide its services when needed, telephone and fax numbers, etc. 

Each course is assigned a course leader (a faculty member), who manages the day-to-day issues of the course. Details of a course include a name, whether it is undergraduate/postgraduate/research course, standard duration of the course in months and the number of credit hours to complete to pass the course.

For each module, the system needs to store its details (e.g., title, number of credit hours, level (1, 2, 3, M, R), etc). For each student who is doing a module, the system needs to record marks.

For each student, the system needs to store details like name (first, middle, last), term address (street, city, region, postal code, etc), home address, telephone numbers, email address, date of birth, gender, and details of next-of-kin (e.g., name, address, relationship, telephone numbers). The system needs to record whether a student is from the UK, EU, Commonwealth or other.

Each faculty member is assigned a line manager, who is also a faculty member and works in the same department. Details of a faculty member include name, home address, office location and room number, telephone extension, email address, home and mobile phone numbers, data of birth, gender, next-of-kin details, date joined the university, salary, and details of qualifications. When a faculty member is assigned to teach on a module, the system needs to record how many hours a week he/she is expected to be teaching on that module.