Relational Algebra

1
Relational Algebra

• An Algebra is a pair (set of values, set of
  operations)
• Note that an algebra is the same idea as an ADT
• Relational Algebra (relations, relational
  operators)
• set of values relations
• set of operations relational operators
• Relational Operators ?, ?, ?, ?, ?, ?, ?, ?,
  ?, ?, ?

2
? Selection
General Form ?ltconditiongt ltrelationgt
Examples
?Cost gt 75 r
RoomNr Name NrBeds Cost ------------------------
--------------- 1 Kennedy 2
90 2 Nixon 2 80 3
Carter 2 80
?ArrivalDate 10 May ? NrDays gt 2 s
GuestNr RoomNr ArrivalDate NrDays -------------
--------------------------------------- 102
3 10 May 5
3
? Projection
General Form ?ltattributesgt ltrelationgt
Examples
?City g
?GuestNr,RoomNr s
City ------ Boston Hartford Providence
GuestNr RoomNr ------------------------ 101
1 101 2 101
3 102 3 103 1 104
4 105 1 106
2
4
Closed Set of Operators

• Results are relations
• Closed implies we can nest operations in
  expressions
• Example

? GuestNr,RoomNr ?ArrivalDate 10 May s
GuestNr RoomNr ------------------------ 101
1 102 3 104 4
5
Set Operators ?, ?, ?
?Name r ? ?Name g
Name -------- Carter Green
?RoomNr r ? ?RoomNr s
RoomNr ----------- 5
?RoomNr ?Cost lt 75 r ? ?RoomNr ?ArrivalDate 10
May s
RoomNr ----------- 4 5 1 3
Note schemes must be union compatible.
6
? Renaming
General Form ?ltold attributegt ? ltnew attributegt
ltrelationgt
Examples
?RoomName ?Name ? RoomName ?Cost lt 75 r
RoomName ---------------- Blue Green
?Nr, Name ?RoomNr ? Nr r ? ?Nr, Name ?GuestNr ?
Nr g
Nr Name --------------
Note The old attribute must be in the scheme and
the new attribute must not be in the scheme.
7
? Cross Product
?NrBeds r ? ?RoomNr s
?
NrBeds ---------- 2 1
RoomNr ----------- 1 2 3 4
NrBeds RoomNr ----------------------- 2
1 2 2 2 3
2 4 1 1 1
2 1 3 1 4

Note The intersection of the schemes must be
empty.
8
? Natural Join
r ? g ?RoomNr,Name,NrBeds,Cost,GuestNr,StreetN
r,City ?Name Name? (r ? ?Name ? Name? g)
RoomNr Name NrBeds Cost GuestNr StreetNr
City ---------------------------------------------
---------------------------------- 3
Carter 2 80 102 10 Main
Hartford 5 Green 1 50
105 10 Main Boston
9
Natural Join Examples
A B ------ 1 2 3 2 4 5 6 7
B C ------ 1 2 2 3 5 7 5 8
A B C ---------- 1 2 3 3 2 3 4 5
7 4 5 8
?

A B ------ 1 2 3 4
C D ------ 1 3 2 4
A B C D -------------- 1 2 1 3 1 2 2
4 3 4 1 3 3 4 2 4
?

A B C ---------- 1 2 3 2 2 3 4 5 6
B C D ---------- 2 3 4 5 6 7 2 6 0
A B C D -------------- 1 2 3 4 2 2 3
4 4 5 6 7
?

10
?? Theta-Join
?NrBeds r ?NrBedsltRoomNr ?RoomNr s

?NrBedsltRoomNr
NrBeds ---------- 2 1
RoomNr ----------- 1 2 3 4
NrBeds RoomNr ----------------------- 2
3 2 4 1 2
1 3 1 4
Note Like cross-product, the intersection of the
schemes must be empty.
11
?, ?, ? Outerjoins
?GuestNr,RoomNr?ArrivalDate 10 May s ?
?RoomNr,Name?NrBeds 2 r
GuestNr RoomNr ------------------------ 101
1 102 3 104 4
RoomNr Name ------------------------ 1
Kennedy 2 Nixon 3 Carter
GuestNr RoomNr Name -------------------------
------------ 101 1 Kennedy
102 3 Carter 104 4
? ? 2 Nixon

?
Note The outer join retains all dangling
tuples.
12
?, ?, ? Outerjoins
?GuestNr,RoomNr?ArrivalDate 10 May s ?
?RoomNr,Name?NrBeds 2 r
GuestNr RoomNr ------------------------ 101
1 102 3 104 4
RoomNr Name ------------------------ 1
Kennedy 2 Nixon 3 Carter
GuestNr RoomNr Name -------------------------
------------ 101 1 Kennedy
102 3 Carter 104 4
?

?
Note The left outer join retains dangling
tuples on the left.
13
?, ?, ? Outerjoins
?GuestNr,RoomNr?ArrivalDate 10 May s ?
?RoomNr,Name?NrBeds 2 r
GuestNr RoomNr ------------------------ 101
1 102 3 104 4
RoomNr Name ------------------------ 1
Kennedy 2 Nixon 3 Carter
GuestNr RoomNr Name -------------------------
------------ 101 1 Kennedy
102 3 Carter ? 2
Nixon

?
Note The right outer join retains dangling
tuples on the right.
14
Semijoin
?GuestNr,RoomNr?ArrivalDate 10 May s
?RoomNr,Name?NrBeds 2 r
GuestNr RoomNr ------------------------ 101
1 102 3 104 4
RoomNr Name ------------------------ 1
Kennedy 2 Nixon 3 Carter
GuestNr RoomNr -------------------------
101 1 102 3


Where might the semijoin be useful?
(optimization of distributed DB systems)
15
? Division
?GuestNr,RoomNr s ? ?RoomNr?NrBeds 2 r
GuestNr RoomNr ------------------------ 101
1 101 2 101 3
102 3 103 1 104
4 105 1 106 2
RoomNr ----------- 1 2 3
GuestNr ----------- 101
?

?
A point easy to overlook The division into
groups is by the attributes in the dividend that
are not in the divisor ? GuestNr in our example.
As an example of incorrectly setting this up,
suppose we dont project on GuestNr,RoomNr, then
the division would be by GuestNr,ArrivalDate,NrDay
s, which would make the answer empty.
Note The scheme of the divisor must be a proper
subset of the scheme of the dividend. The scheme
of the quotient is the difference.
16
Query Examples
List names and cities of guests arriving on 15
May.
?Name,City ?ArrivalDate 15 May (g ? s)
List names of each guest who has a reservation
for a room that has the same name as the guests
name.
?Name (g ? s ? r)
List names of guests who have a reservation for
rooms with two beds.
?Name (g ? s ? ?RoomNr ? NrBeds 2 r)
17
More Query Examples
List the names of guests from Hartford who are
arriving after 10 May.
?Name (?GuestNr,Name ?City Hartford g ?
?GuestNr ?ArrivalDate gt 10 May s)
List names of rooms for which no guest is
arriving on 10 May.
?Name (r ? (?RoomNr r ? ?RoomNr ?ArrivalDate
10 May s))
List all rooms along with their reservations for
May 10th (if any).
?RoomNr r ? ?ArrivalDate 10 May s
18
Even More Query Examples
List the names of guests who have reservations
for all presidential suites (i.e., those that
have two beds).
guests who do
all possible GuestNr-RoomNr combinations
minus the reservations we have
?Name (g ? (?GuestNr s ?
?GuestNr((?GuestNr s ? ?RoomNr?NrBeds 2 r) ?
?GuestNr,RoomNr s)))
guests who dont have reservations for all
presidential suites
GuestNrs whose RoomNr includes all RoomNrs with
2 beds
?Name (g ? ( ?GuestNr,RoomNr s ? ?RoomNr?NrBeds
2 r))
19
Are All Operators Necessary?

• Relational Operators ?, ?, ?, ?, ?, ?, ?, ?,
  ??, ?, ?
• A Complete Set of Operators ?, ?, ?, ?, ?, ?
• Can do ? with ?, ?, ?
• Can do ? with ?, ?? with ? and ?, and ?
  with ? and ?
• Can do ? with ?, also with ?, i.e., (r ? s r
  ? (r ? s))
• Why might this be of value?
• Fewer operators to implement
• Query rewriting for optimization