C H A P T E R 6
Formal
R
e
l
a
t
i
o
n
a
l
Q
u
e
r
y
L
a
n
g
u
ag
e
s
Note: Exercises mentioning QBE and Datalog may be omitted, unless you are cover-
ing QBE and Datalog using the online appendices as part of your course.
E
x
e
r
c
i
s
e
s
6.2 Given relation
r(A,
B) give an expression to find all tuples with the maximum
A value.
. .
.2
6.3 Show how to express A
✶
B using the basic relational operations, plus
natural
join.
. . .2
6.4 Draw the truth table for the AND operation on three-valued logic.
. .
.3
6.6 Consider the following relational schema
FACULTY(empno,name,office,age)
BOOKS(isbn,title,authors,publisher)
LOAN(empno,isbn,date)
12 Chapter 6 Formal Relational Query Languages
b. Print names of faculty members who have borrowed all books published
by
Addison-Wesley.
c. For each publisher, print the names of faculty who have borrowed
more
than five books of that publisher.
. .
.8
6.7 Write extended relational algebra expressions to do the following. Assume you
are given two relations, student(name, rollno) and marks(rollno, exam, mark)
6.8 Consider a relation marks(rollno, course, examname, marks).
a. Suppose the course DBIS has two quizzes, and you want to find the max-
imum of the two quiz marks for each student of the course. Assume that
6.9 Let
r
and s be relations with the schema
R(a1,
a2) and
S(a1,
a3)
respectively.
The semijoin operation
r
⋉ s selects tuples from
r
that match with some tuple
in s. The semijoin operation can be defined as Πa1,a2
(r
✶
s). (Here,
✶
is the
natural join where common attributes appear only once.)
Consider the relational algebra with
duplicates.
a. Define (in words) the multiset versions of Π, σ and
✶
.
. . .5
b. Define the multiset version of
⋉
as containing the same
s
e
t
of tuples in
r
⋉
s,
but each tuple has the same multiplicity as it has in
r.
For example if
r =
{
(
1
,
2), (1, 2), (2,
3
)
}
and s
=
{
(
1
,
4), (1,
5
)
}
,
r
⋉ s
=
{
(
1
,
2), (1,
2
)
}
,
whereas
6.10 Given relations
r(A,
B), and s(A,
C
)
:
a. Give an expression in SQL that is equivalent to
B
G
(
Π
B
C
(r
✶
s
))
.
6.11 Given the schema
item(itemid, name, category, price)
Exercises
13
where primary keys are underlined, write the following queries in relational
algebra:
a. Find the name and price of the most expensive item (if more than one
item
is the most expensive, print them all).
. . .5
b. Print the total sales (in terms of units and total price) of every item
category
in every customer-city.
. . .5
6.12 Using the schema
a
cc
o
un
t
(
a
cc
o
un
t
–nu
m
b
e
r
,
b
r
a
n
c
h
-n
a
m
e
,
b
a
l
a
n
c
e
),
d
e
p
o
s
i
t
o
r
(n
a
m
e
,
a
cc
o
un
t
–nu
m
b
e
r
),
and
b
r
a
n
c
h
(
b
r
a
n
c
h
-n
a
m
e
,
b
r
a
n
c
h
–
c
i
t
y)
write a query in tuple re-
lational calculus to find all customers who have an account at every branch in
Mumbai.
. .
.5
6.13 Given the schema
item(itemid, name, category, price)
itemsale(transid, itemid, qty)
6.14 Write tuple and domain relational calculus expressions to do the
following.
Assume you are given two relations, student(name, rollno) and
marks(rollno,
exam, mark)
a. Show names of all students who have got marks in at least two exams.
. . .2
b. Find the names of the students with highest total marks (summed across
all
exams for each student).
. . .3
6.15 Suppose you are given relations
a
cc
o
un
t
(nu
m
b
e
r
,
b
a
l
a
n
c
e
)
and
o
wn
e
r
(n
a
m
e
,
num-
b
e
r
).
a. Write a query to find balances of all accounts owned by Bala using
6.16 Suppose you are given a relation
r(R,
M ), where R indicates roll number
and
M the marks scored. Write queries to compute the top 5 distinct marks using
14 Chapter 6 Formal Relational Query Languages
b. Tuple relational calculus
. . .3
6.17 Give a QBE expression equivalent to
{
<
a
>
| ∃
c
(<
a,
c
>
∈
s
∧ ∃
b
1
, b
2
(
<
a, b
1
>
∈
r
∧
<
c, b
2
>
∈
r
∧
b
1
>
b
2
))
}
. . .4
6.18 For each of the following give an example Datalog program: