22s

8/12/2019 22s

1/6

CHAPTER 22

Object-Based Databases

Practice Exercises

22.1 A car-rental company maintains a database for all vehicles in its cur-rent fleet. For all vehicles, it includes the vehicle identification number,license number, manufacturer, model, date of purchase, and color. Spe-cial data are included for certain types of vehicles:

Trucks: cargo capacity.

Sports cars: horsepower, renter age requirement.

Vans: number of passengers.

Off-road vehicles: ground clearance, drivetrain(four- or two-wheeldrive).

Construct anSQL schema definition for this database. Use inheritancewhere appropriate.Answer: For this problem, we use table inheritance. We assume thatMyDate,Colorand DriveTrainTypeare pre-defined types.

create typeVehicle(vehicle idinteger,license numberchar(15),manufacturer char(30),

modelchar(30),purchase dateMyDate,colorColor)

create tablevehicleof typeVehicle

create tabletruck(cargo capacityinteger)undervehicle

create tablesportsCar

1

8/12/2019 22s

2/6

2 Chapter 22 Object-Based Databases

(horsepowerintegerrenter age requirementinteger)

undervehicle

create tablevan(num passengersinteger)undervehicle

create tableoffRoadVehicle(ground clearancerealdriveTrain DriveTrainType)

undervehicle

22.2 Consider a database schema with a relationEmp whose attributes areas shown below, with types specified for multivalued attributes.

Emp = (ename, ChildrenSetmultiset(Children), SkillSetmultiset(Skills))Children = (name, birthday)Skills = (type, ExamSetsetof(Exams))Exams = (year, city)

a. Define the above schema inSQL

, with appropriate types for eachattribute.

b. Using the above schema, write the following queries inSQL.

i. Find the names of all employees who have a child born on orafter January 1, 2000.

ii. Find those employees who took an examination for the skilltype typingin the city Dayton.

iii. List all skill types in the relationEmp.

Answer:

a. No Answer.

b. Queries in SQL.

i. Program:

selectenamefromempase,e.ChildrenSetascwhereMarchin

(selectbirthday.monthfromc

)

ii. Program:

8/12/2019 22s

3/6

Practice Exercises 3

selecte.enamefromempase,e.SkillSetass,s.ExamSetasxwheres.type= typingandx.city= Dayton

iii. Program:

select distincts.typefromempase,e.SkillSetass

22.3 Consider the E-R diagram in Figure 22.5, which contains composite,multivalued, and derived attributes.

a. Give anSQLschema definition corresponding to theE-Rdiagram.

b. Give constructors for each of the structured types defined above.

Answer:

a. The corresponding SQL:1999 schema definition is given below.Note that the derived attribute age has been translated into amethod.

create typeName(first name varchar(15),middle initialchar,last namevarchar(15))

create typeStreet(street namevarchar(15),

street numbervarchar(4),apartment numbervarchar(7))create typeAddress

(street Street,cityvarchar(15),statevarchar(15),zip codechar(6))

create tablecustomer(name Name,customer idvarchar(10),address Adress,phones char(7) array[10],dob date)

method integerage()

b. create functionName(fvarchar(15),mchar,lvarchar(15))returnsNamebegin

setfirst name=f;setmiddle initial=m;setlast name=l;

endcreate functionStreet(snamevarchar(15),snovarchar(4),anovarchar(7))

8/12/2019 22s

4/6


returnsStreetbegin

setstreet name=sname;setstreet number=sno;setapartment number=ano;

endcreate functionAddress(s Street,cvarchar(15),stavarchar(15),zipvarchar(6))returnsAddressbegin

setstreet=s;setcity=c;setstate=sta;

setzip code=zip;end

22.4 Consider the relational schema shown in Figure 22.6.

a. Give a schema definition in SQLcorresponding to the relationalschema, but using references to express foreign-key relationships.

b. Write each of the queries given in Exercise 6.13 on the aboveschema, usingSQL.

Answer:

a. The schema definition is given below. Note that backward ref-erences can be addedbut they are not so important as in OODBS

because queries can be written in SQL and joins can take care ofintegrity constraints.

create typeEmployee(person namevarchar(30),streetvarchar(15),cityvarchar(15))

create typeCompany(company namevarchar(15),(cityvarchar(15))

create tableemployeeofEmployeecreate tablecompanyofCompanycreate typeWorks

(personref(Employee)scopeemployee,compref(Company)scopecompany,salaryint)

create tableworksofWorkscreate typeManages

(personref(Employee)scopeemployee,(managerref(Employee)scopeemployee)

create tablemanagesofManages

b. i. selectcomp >name

8/12/2019 22s

5/6

Practice Exercises 5

fromworksgroup bycomphaving count(person) all(select count(person)

fromworksgroup bycomp)

ii. selectcomp >namefromworksgroup bycomphaving sum(salary) all(select sum(salary)

fromworksgroup bycomp)

iii. selectcomp >

namefromworksgroup bycomphaving avg(salary) > (select avg(salary)

fromworkswherecomp >company name="First Bank Corporation")

22.5 Suppose that you have been hired as a consultant to choose a databasesystem for your clients application. For each of the following appli-cations, state what type of database system (relational, persistent pro-gramming language based OODB, object relational; do not specify acommercial product) you would recommend. Justify your recommen-dation.

a. A computer-aided design system for a manufacturer of airplanes.

b. A system to track contributions made to candidates for publicoffice.

c. An information system to support the making of movies.

Answer:

a. A computer-aided design system for a manufacturer of airplanes:AnOODBsystem would be suitable for this. That is because CADrequires complex data types, and being computation oriented,CAD tools are typically used in a programming language envi-

ronment needing to access the database.b. A system to track contributions made to candidates for public

office:A relational system would be apt for this, as data types are ex-pected to be simple, and a powerful querying mechanism is es-sential.

c. An information system to support the making of movies:Here there will be extensive use of multimedia and other complexdata types. But queries are probably simple, and thus an objectrelational system is suitable.

8/12/2019 22s

6/6


22.6 How does the concept of an object in the object-oriented model differfrom the concept of an entity in the entity-relationship model?Answer: An entity is simply a collection of variables or data items.An object is an encapsulation of data as well as the methods (code) tooperate on the data. The data members of an object are directly visibleonly to its methods. The outside world can gain access to the objectsdata only by passing pre-defined messages to it, and these messagesare implemented by the methods.

22s

Documents