Database Storage & Design Chapter 9 Homework However Have Already Seen That

Databas – Part III

52

Chapter 9 Object-Relational DBMSs

Review Questions

9.1 Discuss the general characteristics of advanced database applications.

Designs of this type have some common characteristics:

Design data is characterized by a large number of types, each with a small number of

instances. Conventional databases are typically the opposite.

9.2 Discuss why the weaknesses of the relational data model and relational DBMSs may make them

unsuitable for advanced database applications.

9.3 Discuss the difficulties involved in mapping objects created in an object–oriented

programming language to a relational database.

9.4 What functionality would typically be provided by an ORDBMS?

53

9.5 What are the advantages and disadvantages of extending the relational data model?

Advantages

Apart from the advantages of resolving many of the weaknesses of the relational data model

cited in Section 9.2, the main advantages of extending the relational data model come from

Disadvantages

The ORDBMS approach has the obvious disadvantages of complexity and associated

increased costs. Further, there are the proponents of the relational approach that believe the

Databas – Part III

54

See Section 9.4 under Advantages and Disadvantages.

9.6 What are the main features of the SQL:2011 standard?

New main features are:

o type constructors for row types and reference types;

9.7 Discuss how reference types and object identity can be used.

Until SQL:1999, the only way to define relationships between tables was using the primary

key/foreign key mechanism, which in SQL2 could be expressed using the referential table

constraint clause REFERENCES, as discussed in Section 7.2.4. Since SQL:1999, reference

9.8 Compare and contrast procedures, functions, and methods.

9.9 What is a trigger? Provide an example of a trigger.

A trigger is an SQL (compound) statement that is executed automatically by the DBMS as a

55

9.10 Discuss the collection types available in SQL:2011.

Collections are type constructors that are used to define collections of other types. Collections

9.11 What are the security problems associated with the introduction of user-defined methods and

suggest some solutions to these problems?

If the user-defined function (UDF) causes some fatal runtime error, then if the UDF code is

Exercises

9.12 Investigate one of the advanced database applications discussed in Section 9.1, or a similar

one that handles complex, interrelated data. In particular, examine its functionality, and the

data types and operations it uses. Map the data types and operations to the object-oriented

concepts discussed in Appendix K.

9.13 Analyze one of the relational DBMSs that you currently use. Discuss the object-oriented

features provided by the system. What additional functionality do these features provide?

9.14 Analyze the RDBMSs that you are currently using. Discuss the object-oriented facilities

provided by the system. What additional functionality do these facilities provide?

9.15 Consider the relational schema for the Hotel case study given in the Exercises at the end of

Chapter 4. Redesign this schema to take advantage of the new features of SQL:2011. Add

user-defined functions that you consider appropriate.

Databas – Part III

56

One possible solution as follows:

CREATE DOMAIN RoomType AS CHAR(1)

CREATE DOMAIN HotelNumber AS CHAR(4);

CREATE TYPE HotelType AS (

hotelNo HotelNumber NOT NULL,

CREATE TABLE Hotel OF HotelType(

CREATE TABLE Room (

roomNo RoomNumber NOT NULL,

57

CREATE TABLE Booking (

hotelID REF(HotelType) SCOPE Hotel

REFERENCES ARE CHECKED ON DELETE CASCADE,

9.16 Create SQL:2011 statements for the queries given in Chapter 6, Exercise 6.7 – 6.28.

Depends on the solution to the previous question. For example, using the above schema, the

solution to 6.16 would be:

9.17 Create an insert trigger that sets up a mailshot table recording the names and addresses of all

guests who have stayed at the hotel during the days before and after New Year for the past two

years.

Databas – Part III

58

CREATE TRIGGER InsertMailshotTable

9.18 Repeat Exercise 9.16 for the multinational engineering case study in the Exercises of Chapter

24.

9.19 Create an object-relational schema for the DreamHome case study documented in Appendix

A. Add user-defined functions that you consider appropriate. Implement the queries listed in

Appendix A using SQL:2011.

9.20 Create an object-relational schema for the University Accommodation Office case study

documented in Appendix B.1. Add user-defined functions that you consider appropriate.

9.21 Create an object-relational schema for the EasyDrive School of Motoring case study

documented in Appendix B.2. Add user-defined functions that you consider appropriate.

9.22 Create an object-relational schema for the Wellmeadows case study documented in Appendix

B.3. Add user-defined functions that you consider appropriate.

Databas – Part III

59

Partial solution:

CREATE TYPE WardType AS (

wardNo VARCHAR(4) NOT NULL,

);

CREATE TYPE QualificationType AS (

);

CREATE TYPE WorkExperienceType AS (

CREATE TYPE NameType AS (

CREATE TYPE StaffType AS (

PRIVATE

DOB DATE_CHECK(DOB < CURRENT_DATE),

PUBLIC

staffNo VARCHAR(5) NOT NULL,

Databas – Part III

60

sScale INTEGER NOT NULL,

CREATE TYPE NurseType UNDER StaffType (

);

CREATE TABLE Ward OF WardType(

9.23 You have been asked by the Managing Director of DreamHome to investigate and prepare a

report on the applicability of an object-relational DBMS for the organization. The report

should compare the technology of the RDBMS with that of the ORDBMS, and should address

the advantages and disadvantages of implementing an ORDBMS within the organization, and

any perceived problem areas. The report should also consider the applicability of an object–

oriented DBMS, and a comparison of the two types of systems for DreamHome should be

included. Finally, the report should contain a fully justified set of conclusions on the

applicability of the ORDBMS for DreamHome.

Databas – Part III

61