City c set state sta set zip code zip end 224

city = c ;
set state = sta ;
set zip code = zip ;
end
22.4 Consider the relational schema shown in Figure 22.6.
a. Give a schema definition in
SQL
corresponding to the relational
schema, but using references to express foreign-key relationships.
b. Write each of the queries given in Exercise 6.13 on the above
schema, using
SQL
.
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 of
integrity constraints.
create type Employee
( person name varchar(30) ,
street varchar(15) ,
city varchar(15) )
create type Company
( company name varchar(15) ,
( city varchar(15) )
create table employee of Employee
create table company of Company
create type Works
( person ref ( Employee ) scope employee ,
comp ref ( Company ) scope company ,
salary int )
create table works of Works
create type Manages
( person ref ( Employee ) scope employee ,
( manager ref ( Employee ) scope employee )
create table manages of Manages
b. i. select comp - > name

Practice Exercises
5
from works
group by comp
having count ( person ) ≥ all(select count( person )
from works
group by comp )
ii. select comp - > name
from works
group by comp
having sum ( salary ) ≤ all(select sum( salary )
from works
group by comp )
iii. select comp - > name
from works
group by comp
having avg ( salary ) > (select avg( salary )
from works
where comp - > company name ="First Bank Corporation")
22.5 Suppose that you have been hired as a consultant to choose a database
system for your client’s 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 a
commercial product) you would recommend. Justify your recommen-
dation.