Exercises
3
b.
R
= {
(1
,
2)
,
(1
,
5)
}
The left hand side expression has an empty result, whereas the right
hand side one has the result
{
(1
,
2)
}
.
c.
Yes, on replacing the
max
by the
min
, the expressions will become
equivalent. Any tuple that the selection in the rhs eliminates would
not pass the selection on the lhs if it were the minimum value, and
would be eliminated anyway if it were not the minimum value.
d.
R
= {
(1
,
2)
}
,
S
= {
(2
,
3)
}
,
T
= {
(1
,
4)
}
. The left hand expression
gives
{
(1
,
2
,
null
,
4)
}
whereas the the right hand expression gives
{
(1
,
2
,
3
,
null
)
}
.
e.
Let
R
be of the schema (
A
,
B
) and
S
of (
A
,
C
). Let
R
= {
(1
,
2)
}
,
S
=
{
(2
,
3)
}
and let
H9258
be the expression
C
=
1. The left side expres-
sion’s result is empty, whereas the right side expression results in
{
(1
,
2
,
null
)
}
.
13.3
SQL
allows relations with duplicates (Chapter 3).
a.
Define versions of the basic relational-algebra operations
H9268
,
Pi1
,
×
,
a49
,
−
,
∪
, and
∩
that work on relations with duplicates, in a way
consistent with
SQL
.
b.
Check which of the equivalence rules 1 through 7.b hold for the
multiset version of the relational-algebra defined in part a.
Answer:
a.
We define the multiset versions of the relational-algebra operators
here. Given multiset relations
r
1
and
r
2
,
i.
H9268
Let there be
c
1
copies of tuple
t
1
in
r
1
. If
t
1
satisfies the selection
H9268
H9258
,
then there are
c
1
copies of
t
1
in
H9268
H9258
(
r
1
), otherwise there are none.
ii.
Pi1
For each copy of tuple
t
1
in
r
1
, there is a copy of tuple
Pi1
A
(
t
1
) in
Pi1
A
(
r
1
), where
Pi1
A
(
t
1
) denotes the projection of the single tuple
t
1
.
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.
a49
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.
∪