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
This
preview
has intentionally blurred sections.
Sign up to view the full version.
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.

This is the end of the preview.
Sign up
to
access the rest of the document.
- Spring '13
- Dr.Khansari
- Relational model, SQL schema definition, schema definition, Object-Based Databases
-
Click to edit the document details