Section 1) Scenarios and Tasks
A. Part 1 (60 marks) LOs 2 & 3
1. Scenario: OO7SubDB
OO7 Benchmark (Carey, DeWitt and Naughton, 1994) has been used widely for performance evaluation of object-based database systems. Figure 1 shows a subset of the conceptual model of the OO7 database (henceforth referred to as OO7SubDB).
Figure 1: UML Class Diagram for the OO7SubDB’s Conceptual Model
In OO7SubDB, DesignObj is the root in the class hierarchy for most of the objects in the database because it models the common state that is inherited by Module, Assembly, and CompositePart classes. From a modelling perspective, a Module is a top-level object that contains all the other objects. In other words, creating an instance of Module requires, depending on certain parameters defined in the OO7 Benchmark, creating objects in the following hierarchical manner for a small size database:
· 1093 Assembly objects out of which 729 are BaseAssembly objects and 364 ComplexAssembly objects. These objects are arranged in seven levels such that the bottom level is made up of BaseAssembly objects (each of which is connected underneath to three shared CompositePart objects, and to a ComplexAssembly on top and the remaining six levels are composed of ComplexAssembly objects.
· 500 CompositePart objects
Figure 2: Organization of Objects in the OO7 database
Figure 2 shows how objects are organized in the OO7SubDB. The first ComplexAssembly object (e.g. having id of 1 or 365) of each Module (currently there are 2 module objects in the database) is designated as designRoot. Moreover, for each Module object, there are 500 CompositePart objects created in the database. That is if a Module object is created whose id is 1 then 500 CompositePart objects are created with unique ids ranging from 1 to 500. Similarly, if a Module object is created with id equal to 2 then 500 CompositePart objects are created with unique ids ranging from 501 to 1000.
2. Relational Implementation of OO7SubDB
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 OO7SubDB’s conceptual model onto a relational logical model. The Oracle SQL code that implements the logical model of the OO7SubDB is given in Figure 3.
| CREATE TABLE Rel_Module ( Id NUMBER (6) PRIMARY KEY, Type CHAR (7) NOT NULL, BuildDate NUMBER (4) NOT NULL, Designroot NUMBER (6) NOT NULL); CREATE TABLE REL_ComplexAssembly ( Id NUMBER (6) PRIMARY KEY, Type CHAR (7) NOT NULL, SuperAssemId NUMBER (6), ModId NUMBER (6) NOT NULL, AssmType NUMBER(1)); CREATE TABLE REL_BaseAssembly ( Id NUMBER (6) PRIMARY KEY, Type CHAR (7) NOT NULL, BuildDate NUMBER (4) NOT NULL, SuperAssemId NUMBER (6), ModId NUMBER (6) NOT NULL, AssmType NUMBER(1)); CREATE TABLE REL_CompositePart ( Id NUMBER (6) PRIMARY KEY, Type CHAR (7) NOT NULL, BuildDate NUMBER (4) NOT NULL) CREATE TABLE REL_ComponentsShar ( BaseId NUMBER (6) NOT NULL REFERENCES REL_BaseAssembly (Id)); ALTER TABLE REL_Module ADD CONSTRAINT FKEY_DESIGNROOT FOREIGN KEY (Designroot) REFERENCES REL_ComplexAssembly (Id); ALTER TABLE REL_ComplexAssembly ADD CONSTRAINT FKEY_COMSUPASSID FOREIGN KEY (SuperAssemId) REFERENCES REL_ComplexAssembly (Id); ALTER TABLE REL_ComplexAssembly ADD CONSTRAINT FKEY_COMMODID FOREIGN KEY (ModId) REFERENCES REL_Module (Id); ALTER TABLE REL_BaseAssembly ADD CONSTRAINT FKEY_BASMODID FOREIGN KEY (ModId) REFERENCES REL_Module (Id); ALTER TABLE REL_BaseAssembly ADD CONSTRAINT FKEY_BASSUPASSID FOREIGN KEY (SuperAssemId) REFERENCES REL_ComplexAssembly (Id); |
Figure 3: Relational Schema of OO7SubDB
3. Accessing the relational OO7SubDB
If you are working on the assignment on Campus using the School’s PCs and the INFORMATICS database, then you can access the relational version of OO7SubDB using OO7RDB.TableName format. For example, to retrieve data from the Module table, use:
SELECT * FROM OO7RDB.Rel_Module;
TASKS
Implementing Object-Relational OO7SubDB (24 MARKS)
Implement the OO7SubDB using object-relational (OR) features of Oracle 9i/10g. You need to discuss your approach to the implementation (4 marks) and provide complete code (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).
Also note that if a domain of an attribute/column is a nested table, before you insert any data in the nested tables of some objects, you must initialise the nested tables to a default empty nested table. For example, consider the following code:
CREATE TYPE Movie AS OBJECT (
Title varchar(15), Year number(4,0),
Lenght number(3, 0));
/
CREATE TYPE Movie_Tab AS TABLE OF REF Movie;
/
CREATE TABLE Star (
Name varchar(15),
Movies Movie_Tab)
NESTED TABLE Movies STORE AS Star_Movie;
Suppose that you have created Star objects without values for nested table column Movies. Before you can insert data into Movies nested table column of Star objects, you must run the following:
UPDATE STAR
SET Movies = Movie_Tab();
COMMIT;
In the above statement Movie_Tab() is a literal invocation of the constructor method for an empty Movie_Tab table. Alternatively, one can define DEFAULT value for object-valued columns. For example:
CREATE TABLE Star (
Name Varchar(15),
Movies Movie_Tab DEFAULT Movie_Tab())
NESTED TABLE Movies STORE AS Star_Movie;
Oracle (2005) provides more detailed documentation on Oracle object-relational features.
Populating the OR version of OO7SubDB (24 MARKS)
Populate your OR version of OO7SubDB using data from its relational implementation. This part can be seen to contain the following sub-tasks:
a) Loading scalar data into the object tables
b) Establishing one-to-one, one-to-many, many-to-one (also many-to-many) relationships
NOTE the following important points:
· All the data that you will need to populate your OR database is available from the shared database called OO7RDB. You do not need to generate/construct your own data set.
· You can make use of SQL and PL/SQL (e.g., Procedures, Functions, and Triggers).
· In your assignment report, you must discuss how the data is loaded into the OR version of the database providing all the code used for populating the OR database.
· Discussion about the choices you made and the approach you used for populating the database is worth 4 marks whereas the code is worth 20 marks.
Writing SQL Queries (12 MARKS)
For this part, you can use SQL or PL/SQL code, whichever is appropriate or applicable.
Answer the following questions after populating the object-relational database (after completing part 4.2):
Q a) Retrieve the details of the CompositePart objects that have been associated with the maximum number of Assembly objects (4 marks).
(i) Answer the query over the relational implementation of OO7SubDB.
(ii) Answer the query over your OR implementation of OO7SubDB.
(iii) Compare and contrast your answers to (i) and (ii).
Note that if you want to compare the performance of these queries you will need to run the following from the SQL* Plus prompt:
SET TIMING ON
Subsequently, whenever you run any query during that session, elapsed time will be displayed on the prompt, which can give you an idea of the efficiency of answering queries over the relational and object-relational implementation of OO7SubDB.
Q b) Find out the total number of all objects of the DesignObj type (2 marks).
Note that in object-based databases (i.e. object-relational and object-oriented) an “extent” of a class is a set containing all of its instances. Theoretically, the extent of a super-type/super-class subsumes the extent of all of its sub-types/sub-classes. For example, if Student and Lecturer are sub-classes of Person class and there are 500 students and 20 lecturers then we can say that there are 520 persons in the database.
Q c) Find out details of the top-10 DesignObj objects in ascending order of id and descending order of buildDate. The details should include id, type, buildDate and the specific object type (e.g. Module, BaseAssembly, CompositePart, etc) of each selected object (3 marks).
Note that in the object-based database literature (also in object-oriented programming languages) it is legitimate to use an instance of a sub-class where an instance of its super-class is expected i.e. the notion of substitutability.
Q d) Write a brief report (not more than one A4 size page) reflecting on your experiences of creating, populating and querying the object-relational database (3 marks).
You should reflect on the work you have done so far on the assignment including the above queries. You can talk about how effectiveness and usefulness of the knowledge and skills you have learned by doing all the preceding tasks of the assignment. You can also comment on the object-relational features of Oracle 9i/10g. Moreover, you may choose to discuss the sorts of activities one would perform in implementing the same database using relational database technology as opposed to the object-relational technology that you have been using in solving the assignment.
Part 2 (16 marks) LOs 3 & 5
In this task, you need to demonstrate the use of multimedia features of Oracle 10g to define a multimedia database for a company selling DVDs containing music. The requirements are as follows:
The company needs to store information on:
- musical works (serial number, title, composer, soloist, date recorded, short description (up to 256 bytes), long description (up to 32KB), playing time, sample clip, full recording)
- composer details(composer, date born, date died, biography, sample clip)
- soloist details(soloist, date born, date died, biography, sample clip)
Where appropriate, you should use object-relational features in Oracle 9i/10g for constructing your data structures. You may consider that REF-based foreign keys are an appropriate way for representing relationships. You may assume that there is one composer and one performer for each work so nesting does not need to be considered in this part.
Part 3 (24 marks) LO 4
In this part, you will demonstrate the application of interoperability/distributed databases. Two companies have independently established databases on residential telephone numbers, types of telephone and their owners as given below in relational definitions for databases A and B.
Database A
Tel_number (tel_id, country_code, city_code, user_number)
Owner (person_id, name, house_name, street, city, postcode)
Tel_owned (tel_id*, person_id*)
Tel_type (tel_id*, manufacturer, LCD, buttons, other_properties)
Database B
Tel_number (tel_id, town_code, number, tel_type)
Owner (person_id, name, address, postcode, tel_id*)
Primary keys are indicated by underlining and foreign keys by an *. You may assume that attributes with the same name are of the same type and that the names given have their natural meaning.
Your tasks are as follows:
1. For the purposes of interoperability, it is desirable to determine the metadata and meta-meta data for an application. Give both the meta- and meta-meta data for each database above and comment on significant differences.
2. What techniques may be used to explore the metadata in an attempt to achieve interoperability?
Hand In procedure
The answer should be in the form of a textual document, which MUST be printed out and submitted as hard copy. You must also provide your assignment together will all the script and output files electronically on a CD. Any part of your work, which is not provided in printed form will not be considered for marking.
Database schema is provided in
http://arjudba.blogspot.com/2010/12/database-schema-for-object-relational.html
