iii.
×
If there are
c
1
copies of tuple
t
1
in
r
1
and
c
2
copies of tuple
t
2
in
r
2
, then there are
c
1
∗
c
2
copies of the tuple
t
1
.
t
2
in
r
1
×
r
2
.
iv.
a
The output will be the same as a cross product followed by a
selection.
v.
−
If there are
c
1
copies of tuple
t
in
r
1
and
c
2
copies of
t
in
r
2
, then
there will be
c
1
−
c
2
copies of
t
in
r
1
−
r
2
, provided that
c
1
−
c
2
is
positive.
vi.
∪
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Chapter 13
Query Optimization
If there are
c
1
copies of tuple
t
in
r
1
and
c
2
copies of
t
in
r
2
, then
there will be
c
1
+
c
2
copies of
t
in
r
1
∪
r
2
.
vii.
∩
If there are
c
1
copies of tuple
t
in
r
1
and
c
2
copies of
t
in
r
2
, then
there will be
min
(
c
1
,
c
2
) copies of
t
in
r
1
∩
r
2
.
b.
All the equivalence rules 1 through 7.b of section 13.2.1 hold for the
multiset version of the relationalalgebra deFned in the Frst part.
There exist equivalence rules which hold for the ordinary relational
algebra, but do not hold for the multiset version. ±or example con
sider the rule :
A
∩
B
=
A
∪
B
−
(
A
−
B
)
−
(
B
−
A
)
This is clearly valid in plain relationalalgebra. Consider a multiset
instance in which a tuple
t
occurs 4 times in
A
and 3 times in
B
.
t
will occur 3 times in the output of the left hand side expression, but
6 times in the output of the right hand side expression. The reason
for this rule to not hold in the multiset version is the asymmetry in
the semantics of multiset union and intersection.
13.4
Consider the relations
r
1
(
A
,
B
,
C
),
r
2
(
C
,
D
,
E
), and
r
3
(
E
,
F
), with primary
keys
A
,
C
, and
E
, respectively. Assume that
r
1
has 1000 tuples,
r
2
has 1500
tuples, and
r
3
has 750 tuples. Estimate the size of
r
1
a
r
2
a
r
3
, and give
an efFcient strategy for computing the join.
Answer:
•
The relation resulting from the join of
r
1
,
r
2
, and
r
3
will be the same
no matter which way we join them, due to the associative and com
mutative properties of joins. So we will consider the size based on the
strategy of ((
r
1
a
r
2
)
a
r
3
). Joining
r
1
with
r
2
will yield a relation
of at most 1000 tuples, since
C
is a key for
r
2
. Likewise, joining that
result with
r
3
will yield a relation of at most 1000 tuples because
E
is
a key for
r
3
. Therefore the Fnal relation will have at most 1000 tuples.
•
An efFcient strategy for computing this join would be to create an
index on attribute
C
for relation
r
2
and on
E
for
r
3
. Then for each tuple
in
r
1
, we do the following:
a. Use the index for
r
2
to look up at most one tuple which matches
the
C
value of
r
1
.
b. Use the created index on
E
to look up in
r
3
at most one tuple which
matches the unique value for
E
in
r
2
.
13.5
Consider the relations
r
1
(
A
,
B
,
C
),
r
2
(
C
,
D
,
E
), and
r
3
(
E
,
F
) of Practice
Exercise 13.4. Assume that there are no primary keys, except the entire
schema. Let
V
(
C
,
r
1
) be 900,
V
(
C
,
r
2
) be 1100,
V
(
E
,
r
2
) be 50, and
V
(
E
,
r
3
)
be 100. Assume that
r
1
has 1000 tuples,
r
2
has 1500 tuples, and
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