Practice Exercises
3
Describe how the theta-join operation can be extended so that tuples from
the left, right, or both relations are not lost from the result of a theta join.
Answer:
a.
The left outer theta join of
r
(
R
)and
s
(
S
)(
r
1
u
s
) can be deFned as
(
r
1
u
s
)
∪
((
r
−
5
R
(
r
1
u
s
))
×
(
null
,
null
,...,
null
))
The tuple of nulls is of size equal to the number of attributes in
S
.
b.
The right outer theta join of
r
(
R
)and
s
(
S
)(
r
1
u
s
) can be deFned as
(
r
1
u
s
)
∪
((
null
,
null
,...,
null
)
×
(
s
−
5
S
(
r
1
u
s
)))
The tuple of nulls is of size equal to the number of attributes in
R
.
c.
The full outer theta join of
r
(
R
)and
s
(
S
)(
r
1
u
s
) can be deFned as
(
r
1
u
s
)
∪
((
null
,
null
,...,
null
)
×
(
s
−
5
S
(
r
1
u
s
)))
∪
((
r
−
5
R
(
r
1
u
s
))
×
(
null
,
null
,...,
null
))
The Frst tuple of nulls is of size equal to the number of attributes in
R
, and the second one is of size equal to the number of attributes in
S
.
6.4
(
Division operation
): The division operator of relational algebra,
“
÷
”
,is
deFned as follows. Let
r
(
R
)and
s
(
S
) be relations, and let
S
⊆
R
;thatis
,
every attribute of schema
S
is also in schema
R
.Then
r
÷
s
is a relation on
schema
R
−
S
(that is, on the schema containing all attributes of schema
R
that are not in schema
S
). A tuple
t
is in
r
÷
s
ifandonlyifbothoftwo
conditions hold:
•
t
is in
5
R
−
S
(
r
)
•
±or every tuple
t
s
in
s
, there is a tuple
t
r
in
r
satisfying both of the
following:
a.
t
r
[
S
]
=
t
s
[
S
]
b.
t
r
[
R
−
S
]
=
t
Given the above deFnition:
a.
Write a relational algebra expression using the division operator to
Fnd the IDs of all students who have taken all Comp. Sci. courses.
(Hint: project
takes
to just ID and
course
id
, and generate the set of
all Comp. Sci.
course
id
s using a select expression, before doing the
division.)
b.
Show how to write the above query in relational algebra, without
using division. (By doing so, you would have shown how to deFne
the division operation using the other relational algebra operations.)
Answer:
a.
5
ID
(
5
ID
,
course
id
(
takes
)
÷
5
course
id
(
s
dept
name
=
’Comp. Sci’
(
course
))
b.
The required expression is as follows:
r
←
5
ID
,
course
id
(
takes