Formal Relational Query Languages
In this chapter we study three additional formal relational languages.Relational
Algebra,tuple relational calculus and domain relational calculus.
Of the three formal languages,we suggest placing an emphasis on rela-tional algebra,which is ud extensively in the chapters on query processing and
optimization,as well as in veral other chapters.The relational calculi generally
与花有关的诗句do not merit as much emphasis.
Our notation for the tuple relational calculus makes it easy to prent 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 prentation.This consideration may suggest
placing somewhat less emphasis on the domain calculus for class not focusing
on databa theory.
Exercis
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.
cour.
b.Find the ID s and names of all students who have not taken any cour
offering before Spring2009.
c.For each department,find 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 databa for Exercis6.2,6.8,6.11,6.13,and6.15
a. name(student1takes1 cour id(dept name=′Comp.Sci.′(cour)))
Note that if we join student,takes,and cour,only students from
the Comp.Sci.department would be prent in the result;students
from other departments would be eliminated even if they had taken a
ur since the attribute dept name appears in both student
and cour.
b. I D,name(student)− I D,name(year<2009(student1takes)Note that
Spring is thefirst mester of the year,so we do not need to perform
a comparison on mester.
c.dept name G max(salary)(instructor)
d.G min(maxsal)(dept name G max(salary)as maxsal(instructor))
6.11Consider the relational databa 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 address,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 databa who live in the same
city as the company for which they work.
e.Assume the companies may be located in veral 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”
Exercis 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 find the
cour ctions 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(cour id ,ction id ,year ,mester G count (∗)as instrcnt (teaches ))b.
cour id ,ction id ,year ,mester (I D <>ID2(takes 1takes1(ID2,cour id ,ction id ,year ,mester )(takes )))6.13Consider the relational databa 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 Find tho companies who 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)1t 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)1t 4(payroll )(t 2))
c.
t 1←company name G avg (salary )(w orks )t 2←company name =“First Bank Corporation”(t 1) 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 thanfive different books published by“McGraw-Hill”.
d.For each publisher,find the name and membership number of mem-
bers who have borrowed more thanfive 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 databa 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 address,and cities of residence of all em-
ployees who work for“First Bank Corporation”and earn more than
$10,000.
Exercis47 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 databa 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 veral 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.{<p>|∃c,s(<p,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.{<p,c>|∃co,sa,st(<p,co,sa>∈w orks
∧<p,st,c>∈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.{<p,s,c>|<p,s,c>∈employee∧∃co,sa(<p,co,sa>∈w orks
∧co=’First Bank Corporation’∧sa>10000)}
d.Find the names of all employees in this databa who live in the same
黄藤素city as the company for which they work:
