数据库CH (6)

Formal Relational Query Languages

In this chapter we study three additional formal relational languages.Relational

Algebra,tuple relational calculus and domain relational calculus.

Of these three formal languages,we suggest placing an emphasis on rela-tional algebra,which is used extensively in the chapters on query processing and

optimization,as well as in several other chapters.The relational calculi generally

do not merit as much emphasis.

Our notation for the tuple relational calculus makes it easy to present the concept of a safe query.The concept of safety for the domain relational calcu-

lus,though identical to that for the tuple calculus,is much more cumbersome

notationally and requires careful presentation.This consideration may suggest

placing somewhat less emphasis on the domain calculus for classes not focusing

on database theory.

Exercises

6.10Write the following queries in relational algebra,using the university

schema.

a.Find the names of all students who have taken at least one Comp.Sci.

course.

b.Find the ID s and names of all students who have not taken any course

offering before Spring2009.

c.For each department,?nd the maximum salary of instructors in that

department.You may assume that every department has at least one

instructor.

d.Find the lowest,across all departments,of the per-department maxi-

mum salary computed by the preceding query.

Answer:

43

44Chapter6Formal Relational Query Languages

employee(person name,street,city)

works(person name company name,salary)

company name,city)

manages(person name,manager name)

Figure6.22Relational database for Exercises6.2,6.8,6.11,6.13,and6.15

a. name(student1takes1 course id(?dept name=′Comp.Sci.′(course)))

Note that if we join student,takes,and course,only students from

the Comp.Sci.department would be present in the result;students

from other departments would be eliminated even if they had taken a

Comp.Sci.course since the attribute dept name appears in both student

and course.

b. I D,name(student)? I D,name(?year<2009(student1takes)Note that

Spring is the?rst semester of the year,so we do not need to perform

a comparison on semester.

c.dept name G max(salary)(instructor)

d.G min(maxsal)(dept name G max(salary)as maxsal(instructor))

6.11Consider the relational database of Figure6.22,where the primary keys are

underlined.Give an expression in the relational algebra to express each of

the following queries:

a.Find the names of all employees who work for“First Bank Corpora-

tion”.

b.Find the names and cities of residence of all employees who work for

“First Bank Corporation”.

c.Find the names,street addresses,and cities of residence of all em-

ployees who work for“First Bank Corporation”and earn more than

$10,000.

d.Find the names of all employees in this database who live in the same

city as the company for which they work.

e.Assume the companies may be located in several cities.Find all com-

panies located in every city in which“Small Bank Corporation”is

located.

Answer:

(w orks))

a. person name(?

company name=“First Bank Corporation”

b. person name,city(employee1

(w orks)))

(?

company name=“First Bank Corporation”

Exercises 45

c. person name ,street ,city (?(company name =“First Bank Corporation”∧salar y >10000)

w orks 1employee )

d. person name (employee 1w orks 1company )

e.

Note:Small Bank Corporation will be included in each answer.

company name (company ÷( city (?company name =“Small Bank Corporation”(company ))))6.12Using the university example,write relational-algebra queries to ?nd the

course sections taught by more than one instructor in the following ways:a.

Using an aggregate function.b.Without using any aggregate functions.

Answer:

a.

?instrcnt >1(course id ,section id ,year ,semester G count (?)as instrcnt (teaches ))b.

course id ,section id ,year ,semester (?I D <>ID2(takes 1?takes1(ID2,course id ,section id ,year ,semester )(takes )))6.13Consider the relational database of Figure 6.22.Give a relational-algebra

expression for each of the following queries:

a.

Find the company with the most employees.b.

Find the company with the smallest payroll.c.Find those companies whose employees earn a higher salary,on av-erage,than the average salary at First Bank Corporation.

Answer:

a.t 1←company name G count-distinct (person name )(w orks )t 2←G max (num employees)(?company strength (company name ,num employees )(t 1))

company name (?t 3(company name ,num employees )(t 1)1?t 4(num employees )(t 2))

b.t 1←company name G sum (salary )(w orks )t 2←G min (payroll )(?company payroll (company name ,payroll )(t 1)) company name (?t 3(company name ,payroll )(t 1)1?t 4(payroll )(t 2))

c.

t 1←company name G avg (salary )(w orks )t 2←?company name =“First Bank Corporation”(t 1) t https://www.360docs.net/doc/903032465.html,pany name ((?t 3(company name ,a v g salar y )(t 1))

1t 3.a v g salar y >f irst bank .a v g salar y (?f irst bank (company name ,a v g salar y )(t 2)))6.14Consider the following relational schema for a library:

46Chapter6Formal Relational Query Languages

member(memb no,name,dob)

books(isbn,title,authors,publisher)

borrowed(memb no,isbn,date)

Write the following queries in relational algebra.

a.Find the names of members who have borrowed any book published

by“McGraw-Hill”.

b.Find the name of members who have borrowed all books published

by“McGraw-Hill”.

c.Find the name and membership number of members who have bor-

rowed more than?ve different books published by“McGraw-Hill”.

d.For each publisher,?nd the name and membership number of mem-

bers who have borrowed more than?ve books of that publisher.

e.Find the average number of books borrowed per member.Take into

account that if an member does not borrow any books,then that

member does not appear in the borrowed relation at all.

Answer:

a.t1← isbn(?publisher=“McGra w?Hill′′(books))

name((member1borro w ed)1t1))

b.t1← isbn(?publisher=“McGra w?Hill′′(books))

name,isbn(member1borro w ed)÷t1

c.t1←member1borro w ed1(?publisher=“McGra w?Hill′′(books))

name(?countisbn>5((memb no G count-distinct(isbn)as countisbn(t1))))

d.t1←member1borro w ed1books

publisher,name(?countisbn>5((publisher,memb no G count-distinct(isbn)as countisbn(t1)))

6.15Consider the employee database of Figure6.22.Give expressions in tuple

relational calculus and domain relational calculus for each of the following

queries:

a.Find the names of all employees who work for“First Bank Corpora-

tion”.

b.Find the names and cities of residence of all employees who work for

“First Bank Corporation”.

c.Find the names,street addresses,and cities of residence of all em-

ployees who work for“First Bank Corporation”and earn more than

$10,000.

Exercises47 d.Find all employees who live in the same city as that in which the

company for which they work is located.

e.Find all employees who live in the same city and on the same street

as their managers.

f.Find all employees in the database who do not work for“First Bank

Corporation”.

g.Find all employees who earn more than every employee of“Small

Bank Corporation”.

h.Assume that the companies may be located in several cities.Find all

companies located in every city in which“Small Bank Corporation”

is located.

Answer:

a.Find the names of all employees who work for First Bank Corporation:

i.{t|?s∈w orks(t[person name]=s[person name]

∧s[company name]=“First Bank Corporation”)} ii.{

|?c,s(∈w orks∧c=“First Bank Corporation”)} b.Find the names and cities of residence of all employees who work for

First Bank Corporation:

i.{t|?r∈employee?s∈w orks(t[person name]=r[person name]

∧t[city]=r[city]∧r[person name]=s[person name]

∧s[company name]=’First Bank Corporation’)} ii.{|?co,sa,st(∈w orks

∧∈employee∧co=“First Bank Corporation”)}

c.Find the names,street address,and cities of residence of all employees

who work for First Bank Corporation and earn more than$10,000per

annum:

i.{t|t∈employee∧(?s∈w orks(s[person name]=

t[person name]

∧s[company name]=“First Bank Corporation”∧s[salar y]> 10000))}

ii.{|∈employee∧?co,sa(∈w orks

∧co=’First Bank Corporation’∧sa>10000)}

d.Find the names of all employees in this database who live in the same

city as the company for which they work:

48Chapter6Formal Relational Query Languages

i.{t|?e∈employee?w∈w orks?c∈company

(t[person name]=e[person name]

∧e[person name]=w[person name]

∧w[company name]=c[company name]∧e[city]=

c[city])}

ii.{

|?st,c,co,sa(∈employee

∧∈w orks∧∈company)}

e.Find the names of all employees who live in the same city and on the

same street as do their managers:

i.{t|?l∈employee?m∈manages?r∈employee

(l[person name]=m[person name]∧m[manager name]=

r[person name]

∧l[street]=r[street]∧l[city]=r[city]∧t[person name]=

l[person name])}

ii.{|?s,c,m(∈employee∧∈

manages∧∈employee)}

f.Find the names of all employees in this database who do not work

for First Bank Corporation:

If one allows people to appear in the database(e.g.in employee)but not

appear in works,the problem is more complicated.We give solutions

for this more realistic case later.

i.{t|?w∈w orks(w[company name]=“First Bank Corporation”

∧t[person name]=w[person name])}

ii.{

|?c,s(∈w orks∧c=“First Bank Corporation”)}

If people may not work for any company:

i.{t|?e∈employee(t[person name]=e[person name]∧??w∈

w orks

(w[company name]=“First Bank Corporation”

∧w[person name]=t[person name]))}

ii.{

|?s,c(∈employee)∧??x,y

(y=“First Bank Corporation”∧∈w orks)}

g.Find the names of all employees who earn more than every employee

of Small Bank Corporation:

i.{t|?w∈w orks(t[person name]=w[person name]∧?s∈

w orks

(s[company name]=“Small Bank Corporation”?w[salar y]>

s[salar y]))}

Exercises49 ii.{

|?c,s(∈w orks∧?p2,c2,s2

(∈w orks∨c2=“Small Bank Corporation”∨s>s2))}

h.Assume the companies may be located in several cities.Find all com-

panies located in every city in which Small Bank Corporation is lo-

cated.

Note:Small Bank Corporation will be included in each answer.

i.{t|?s∈company(s[company name]=“Small Bank Corporation”?

?r∈company(t[company name]=r[company name]∧r[city]=s[city]))}

ii.{|?co2,ci2(∈company

∨co2=“Small Bank Corporation”∨∈company)} 6.16Let R=(A,B)and S=(A,C),and let r(R)and s(S)be relations.

Write relational-algebra expressions equivalent to the following domain-

relational-calculus expressions:

a.{|?b(∈r∧b=17)}

b.{|∈r∧∈s}

c.{|?b(∈r)∨?c(?d(∈s)?∈s)}

d.{|?c(∈s∧?b1,b2(∈r∧

∈r∧b1>b2))}

Answer:

a. A(?B=17(r))

b.r1s

c. A(r)∪(r÷?B( C(s)))

d. r.A((r1s)1c=r2.A∧r.B>r2.B(?r

2(r)))

It is interesting to note that(d)is an abstraction of the notorious

query“Find all employees who earn more than their manager.”Let

R=(emp,sal),S=(emp,mgr)to observe this.

6.17Repeat Exercise6.16,writing SQL queries instead of relational-algebra ex-

pressions.

Answer:

a.select a

from r

where b=17

50Chapter6Formal Relational Query Languages

b.select a,b,c

from r,s

where r.a=s.a

c.(select a

from r)

union

(select a

from s)

d.select a

from r as r1,r as r2,s

where r1.a=s.a and r2.a=s.c and r1.b>r2.b

6.18Let R=(A,B)and S=(A,C),and let r(R)and s(S)be relations.

Using the special constant null,write tuple-relational-calculus expressions

equivalent to each of the following:

a.r1s

b.r1s

c.r1s

Answer:

a.{t|?r∈R?s∈S(r[A]=s[A]∧t[A]=r[A]∧t[B]=r[B]∧t[C]=

s[C])∨

?s∈S(??r∈R(r[A]=s[A])∧t[A]=s[A]∧t[C]=s[C]∧t[B]=

null)}

b.{t|?r∈R?s∈S(r[A]=s[A]∧t[A]=r[A]∧t[B]=r[B]∧t[C]=

s[C])∨

?r∈R(??s∈S(r[A]=s[A])∧t[A]=r[A]∧t[B]=r[B]∧t[C]=

null)∨

?s∈S(??r∈R(r[A]=s[A])∧t[A]=s[A]∧t[C]=s[C]∧t[B]=

null)}

c.{t|?r∈R?s∈S(r[A]=s[A]∧t[A]=r[A]∧t[B]=r[B]∧t[C]=

s[C])∨

?r∈R(??s∈S(r[A]=s[A])∧t[A]=r[A]∧t[B]=r[B]∧t[C]=

null)}

6.19Give a tuple-relational-calculus expression to?nd the maximum value in

relation r(A).

Answer:{|∈r∧?∈R a>=b}