be sorted on the result of applying the upper() function on
r
.
A
and
s
.
A
. The hash or merge join algorithms can then be used unchanged.
13.8
Give conditions under which the following expressions are equivalent
A
,
B
G
agg
(
C
)
(
E
1
a
E
2
)
and
(
A
G
agg
(
C
)
(
E
1
))
a
E
2
where
agg
denotes any aggregation operation. How can the above condi
tions be relaxed if
agg
is one of
min
or
max
?
Answer:
The above expressions are equivalent provided
E
2
contains only
attributes
A
and
B
, with
A
as the primary key (so there are no duplicates).
It is OK if
E
2
does not contain some
A
values that exist in the result of
E
1
, since such values will get Fltered out in either expression. However, if
there are duplicate values in
E
2
.
A
, the aggregate results in the two cases
would be different.
If the aggregate function is min or max, duplicate
A
values do not have
any effect. However, there should be no duplicates on (
A
,
B
); the Frst
expression removes such duplicates, while the second does not.
13.9
Consider the issue of interesting orders in optimization. Suppose you are
given a query that computes the natural join of a set of relations
S
. Given
a subset
S
1 of
S
, what are the interesting orders of
S
1?
Answer:
The interesting orders are all orders on subsets of attributes that
can potentially participate in join conditions in further joins. Thus, let
T
be the set of all attributes of
S
1 that also occur in any relation in
S
−
S
1.
Then every ordering of every subset of
T
is an interesting order.
13.10
Show that, with
n
relations, there are (2(
n
−
1))!
/
(
n
−
1)! different join
orders.
Hint:
A
complete binary tree
is one where every internal node has
exactly two children. Use the fact that the number of different complete
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentExercises
7
binary trees with
n
leaf nodes is:
1
n
p
2(
n
−
1)
(
n
−
1)
P
If you wish, you can derive the formula for the number of complete binary
trees with
n
nodes from the formula for the number of binary trees with
n
nodes. The number of binary trees with
n
nodes is:
1
n
+
1
p
2
n
n
P
This number is known as the
Catalan number
, and its derivation can be
found in any standard textbook on data structures or algorithms.
Answer:
Each join order is a complete binary tree (every nonleaf node
has exactly two children) with the relations as the leaves. The number
of different complete binary trees with
n
leaf nodes is
1
n
(
2(
n
−
1)
(
n
−
1)
)
. This is
because there is a bijection between the number of complete binary trees
with
n
leaves and number of binary trees with
n
−
1 nodes. Any complete
binary tree with
n
leaves has
n
−
1 internal nodes. Removing all the leaf
nodes, we get a binary tree with
n
−
1 nodes. Conversely, given any binary
tree with
n
−
1 nodes, it can be converted to a complete binary tree by
adding
n
leaves in a unique way. The number of binary trees with
n
−
1
nodes is given by
1
n
(
2(
n
−
1)
(
n
−
1)
)
, known as the Catalan number. Multiplying this
by
n
! for the number of permutations of the
n
leaves, we get the desired
result.
13.11
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '13
 Dr.Khansari
 right hand

Click to edit the document details