Exam 4: Object-Relational DBMSs, Database Planning, Design, and Administration

Compare and contrast the two manifestos: Object-Oriented Database System Manifesto based on the object-oriented paradigm (Atkinson et al., 1989a) and the Third Generation Database System Manifesto published by the Committee for Advanced DBMS Function (CADF).

Consider the following schema: Pet (petNo, petName, petDescription, dateRegistered, picture, surgeryNo, doctorStaffNo) Staff (staffNo, sName, sAddress, position, surgeryNo) Surgery (surgeryNo, surgeryAddress, surgeryTelNo) where: Pet $\quad$ contains details of pets and the pet number (petNo) is the key. The surgery where the pet is registered is given by the surgery number (surgeryNo). A pet can only be registered with one surgery at a time. The doctor who treats the pet is given by the doctorStaffNo. Picture contains an image of the pet. Staff $\quad$ contains details of staff and staff number (staffNo) is the key. Surgery $\quad$ contains details of each surgery and the surgery number (surgeryNo) is the key. Now consider the following SQL:2003 query: SELECT petNo, staffNo FROM Pet p, TABLE StaffDoctors s WHERE p.doctorStaffNo = s.staffNo AND p.brownShortHairedTerrier(picture) AND s.surgeryNo = 'S3' AND p.dateRegistered < '1-Jan-90'; The routine brownShortHairedTerrier() is an externally defined routine that searches the specified image for certain characteristics. The StaffDoctors() function is fully defined within SQL3 as: CREATE FUNCTION StaffDoctors() RETURNS SET(Staff) SELECT * FROM Staff WHERE position = 'Doctor'; Discuss how you would want a query optimiser within an ORDBMS to handle this type of query. Use a relational algebra tree to illustrate your answer.

Given the following example of an object type in Oracle: CREATE TYPE carType AS OBJECT ( regNo $\quad$ VARCHAR2 (8), make $\quad$ VARCHAR2 (20), model $\quad$ VARCHAR2 (20)); CREATE TABLE vehicles OF carType; State the two ways: (a) in which the 'vehicles' table can be viewed; (b) to insert a record in the 'vehicles' table; (c) to retrieve records of all Ford cars from the 'vehicles' table.

Explain why planning is important in the database lifecycle, highlighting particular points that are important during the process. Discuss the major functions of the Database Administration role, in particular indicating the skills most relevant to the role.

Explain the procedures and techniques needed to achieve a conceptual data model.

Explain what is meant by application prototyping and why the development of Fourth Generation software should support it. In what way(s) does it help software development?

The on-going debate between proponents of the relational data model and proponents of the object-oriented data model (if one truly exists), resembles that between the proponents of network/hierarchic systems and relational systems a couple of decades ago. However, another system is evolving that may have a significant impact on what the database management system of the future may be and that is the Object-Relational Database Management System (ORDBMS). Give your definition of an ORDBMS. Compare and contrast the ORDBMS and the Object-Oriented Database Management System (OODBMS).

Discuss the importance of planning in the database lifecycle, detailing the significant stages in the process. Once the design phase of the database lifecycle has been initiated, the objective is to achieve a conceptual database schema design. Explain the procedures necessary in order to achieve this objective.

In the Object Relational Model (ORM) an object-type has a name, attributes, and methods. • What is a method? And what is the principal use of methods? • What kinds of methods are supported by Oracle ORM.

Discuss how the proposed SQL:2011 standard will handle object identity and give an example of its intended use.

The application prototyping approach to software development gives users what they want by employing principles used in other engineering disciplines, i.e. build a working model and use it. Critically discuss the arguments for and against this approach showing how the software development life cycle is consequently affected. What are the necessary conditions for it to be successful and what are the dangers/problems that could arise?

Discuss how the new version of the SQL standard addresses object-oriented data management. Give examples to illustrate your answers.