RELATIONAL ALGEBRA (II)

1
RELATIONAL ALGEBRA (II)
Lecture 9
CS157A

• Prof. Sin-Min LEE
• Department of Computer Science

2
(No Transcript)
3
(No Transcript)
4
Unary Relational Operations SELECT and PROJECT

• The PROJECT Operation
• Sequences of Operations and the RENAME Operation
• The SELECT Operation

5
Relational Algebra Operations from Set Theory

• The UNION, INTERSECTION, and MINUS Operations
• The CARTESIAN PRODUCT (or CROSS PRODUCT) Operation

6
Binary Relational Operations JOIN and DIVISION

• The JOIN Operation
• The EQUIJOIN and NATURAL JOIN Variations of JOIN
• A Complete Set of Relational Algebra Operations
• The DIVISION Operation

7
(No Transcript)
8
Additional Relational Operations

• Aggregate Functions and Grouping
• Recursive Closure Operations
• OUTER JOIN Operations
• The OUTER JOIN Operation

9
SPECIAL RELATIONAL OPERATORS

• The following operators are peculiar to
  relations
• - Join operators
• There are several kind of join operators. We only
  consider three of these here (others will be
  considered when we discuss null values)
• - (1) Condition Joins
• - (2) Equijoins
• - (3) Natural Joins
• - Division

10
JOIN OPERATORS

• Condition Joins
• - Defined as a cross-product followed by a
  selection
• R ?c S sc(R ? S)
  (? is called the bow-tie)
• where c is the condition.
• - Example
• Given the sample relational instances S1 and R1

The condition join S ?S1.sidltR1.sid R1 yields
11
JOIN OPERATORS

• Condition Joins
• - Defined as a cross-product followed by a
  selection
• R ?c S sc(R ? S)
  (? is called the bow-tie)
• where c is the condition.
• - Example
• Given the sample relational instances S1 and R1

The condition join S ?S1.sidltR1.sid R1 yields
12
(No Transcript)
13
(No Transcript)
14
(No Transcript)
15

• Equijoin
• Special case of the condition join where the join
  condition consists solely of equalities between
  two fields in R and S connected by the logical
  AND operator (?).
• Example Given the two sample relational
  instances S1 and R1

The operator S1 R.sidSsid R1 yields
16

• Natural Join
• - Special case of equijoin where equalities are
  implicitly specified on all fields having the
  same name in R and S.
• - The condition c is now left out, so that the
  bow tie operator by itself signifies a natural
  join.
• - N. B. If the two relations have no attributes
  in common, the natural join is simply the
  cross-product.

17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
(No Transcript)
21
(No Transcript)
22
DIVISION

• - The division operator is used for queries which
  involve the all
• qualifier such as Find the names of sailors who
  have reserved all boats.
• - The division operator is a bit tricky to
  explain, and perhaps best approached through
  examples as will be done here.

23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
EXAMPLES OF DIVISION

27
DIVISION

• Interpretation of the division operation A/B
• - Divide the attributes of A into 2 sets A1 and
  A2.
• - Divide the attributes of B into 2 sets B2 and
  B3.
• - Where the sets A2 and B2 have the same
  attributes.
• - For each set of values in B2
• - Search in A2 for the sets of rows (having the
  same A1 values) whose A2 values (taken together)
  form a set which is the same as the set of B2s.
• - For all the set of rows in A which satisfy the
  above search, pick out their A1 values and put
  them in the answer.

28
DIVISION

• Example Find the names of sailors who have
  reserved all boats
• (1) A ?sid,bid(Reserves). A1 ?sid(Reserves)
  A2 ?bid(Reserves)
• (2) B2 ?bid(Boats) B3 is the rest of B.
• Thus, B2 101, 102, 103, 104
• (3) Find the rows of A such that their A.sid is
  the same and their combined A.bid is the set B2.
• Thus we find A1 22
• (4) Get the set of A2 corresponding to A1 A2
  Dustin

29
FORMAL DEFINITION OF DIVISION

• The formal definition of division is as follows
• A/B ?x(A) - ?x((?x(A) ? B) A)

30
EXAMPLES OF ALGEBRA QUERIES

• In the rest of this chapter we shall illustrate
  queries using the following new instances S3 of
  sailors, R2 of Reserves and B1 of boats.

31
QUERY Q1

• Given the relational instances

(Q1) Find the names of sailors who have reserved
boat 103 ?sname((sbid103 Reserves) ? Sailors)
The answer is thus the following relational
instance ltDustingt, ltLubbergt, ltHoratiogt
32
QUERY Q1 (contd)

• There are of course several ways to express Q1 in
  relational algebra.
• Here is another
• ?sname(sbid103(Reserves? Sailors))

Which of these expressions should we use? That is
a question of optimization. Indeed, when we
describe how to state queries in SQL, we can
leave it to the optimizer in the DBMS to select
the nest approach.
33
QUERY Q2

• (Q2) Find the names of sailors who have reserved
  a red boat.

?sname((scolorredBoats) ? Reserves ? Sailors)

34
QUERY Q3

• (Q3) Find the colors of boats reserved by Lubber.

?color((ssnameLubberSailors)Sailors ? Reserves
? Boats)
35
QUERY Q4

• (Q4) Find the names of Sailors who have reserved
  at least one boat

?sname(Sailors ? Reserves)
36
QUERY Q5

• (Q5) Find the names of sailors who have reserved
  a red or a green boat.

?(Tempboats, (scolorredBoats) ?
(scolorgreenBoats))
?sname(Tempboats ? Reserves ? Sailors)
37
QUERY Q6

• (Q6) Find the names of Sailors who have reserved
  a red and a green boat.
• It seems tempting to use the expression used in
  Q5, replacing simply ? by n. However, this wont
  work, for such an expression is requesting the
  names of sailors who have requested a boat that
  is both red and green! The correct expression is
  as follows
• ?(Tempred, ?sid((scolorredBoats) ?
  Reserves))
• ?(Tempgreen, ?sid((scolorgreenBoats
  ) ? Reserves))
• ?sname ((Tempred n Tempgreen) ?
  Sailors)

38
QUERY Q7

• (Q7) Find the names of sailors who have reserved
  at least two boats.

?(Reservations, ?sid,sname,bid(Sailors ?
Reserves)) ?(Reservationpairs(1?sid1, 2?sname,
3?bid1, 4?sid2, 5?sname, 6?bid2),
Reservations?Reservations) ?sname1s(sid1sid2)?(bi
d1?bid2)Reservationpairs)
39
QUERY 8

• (Q8) Find the sids of sailors with age over 20
  who have not reserved a red boat.

?sid(sagegt20Sailors) - ?sid((scolorredBoats) ?
Reserves ? Sailors)
40
QUERY 9

• (Q) Find the names of sailors who have reserved
  all boats.

?(Tempsids, (?sid,bidReserves) /
(?bidBoats)) ?sname(Tempsids ? Sailors
41
QUERY Q10

• (Q10) Find the names of sailors who have reserved
  all boats called Interlake.

?(Tempsids, (?sid,bidReserves)/(?bid(sbnameInter
lakeBoats))) ?sname(Tempsids ? Sailors)
42

• Natural Join
• - combines s, p, ?
• - very commonly used
• Natural Join forms the cross product of its two
  arguments, does a selection to enforce equality
  of columns with the same name and removes
  duplicate columns.
• Eg show all transactions done by account owner
  Bob
• s ownerBob (account JOIN transaction)

43
Rename operation

• What if you need to access the same relation
  twice in a query?
• eg. person(ss, name, mother_ss, father_ss)
• Find the name of Bobs mother needs the
  person table to be accessed twice.
• The operation ? x (r) evaluates to a second
  logical copy of relation r renamed to x.

44
Rename operation (contd)

• eg
• p mother.name (
• (? mother (person))
• JOIN mother.ss person.mother_ss
• (s nameBob (person)))

45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
(No Transcript)
50
(No Transcript)
51
(No Transcript)