Relational Algebra Unary and Binary Operators Lecture 2

1
Relational AlgebraUnary and Binary
OperatorsLecture 2

• Instructor Haya sammaneh

2
What is an Algebra

• Mathematical system consisting of
• Operands --- variables or values
• Operators --- symbols denoting procedures

3
Core Relational Algebra

• Unary Operations
• Selection picking certain rowstuples.
• Projection picking certain columns.
• Renaming of relations and attributes.
• Binary Operations
• Union , intersection , and difference
  .
• require both operands have the same relation
  schema.
• Products and joins compositions of
  relations.

4
Example
R1

• Sailors and Reserves relations for our
  examples.

S1
S2
5
Selection, sigma

• R1 SELECTC (R2)
• C is a condition (as in if statements) that
  refers to attributes of R2.
• R1 is all those tuples of R2 that satisfy C.
• Condition may have , ?, gt,lt,,,
• ? (not), ? (and), ? (or)?

6
Example
R1 bar beer price Joes Bud 2.50 Joes Mi
ller 2.75 Sues Bud 2.50 Sues Miller 3.00
7
Selection
S2

• Selects rows that satisfy selection condition.
• No duplicates in result!

8
Projection, Pi

• R1 PROJL (R2)
• L is a list of attributes from the schema of R2.
• R1 is constructed by looking at each tuple of R2,
  extracting the attributes on list L, in the order
  specified, and creating from those components a
  tuple for R1.
• Eliminate duplicate tuples, if any.

9
Example
Relation Sells bar beer price Joes Bud 2.5
0 Joes Miller 2.75 Sues Bud 2.50 Sues M
iller 3.00
10
Projection
S2
11
Selection Projection
S2
12
Selection and projection
Find the largest account balance in the bank?
account
Solution compute a temporary relation consisting
of those balances that are not the large, then
take the set difference between the relation ?
balance (account) and the temporary relation ?
account.balance(account) (? account.balance(s
account.balanceltnew_account.balance(account X ?
new_account (account) )))
13
Union, Intersection, Set-Difference

• All of these operations take two input relations,
  which must be union-compatible
• Same number of fields.
• Corresponding fields have the same type.

14
Union,
S1
S2
15
Intersection,
S1
S2
16
Set difference,
S1
Find the relation that are in one relation but
not in another
S2
17
Cross Product

• R3 R1 X R2
• Pair each tuple t1 of R1 with each tuple t2 of
  R2.
• Concatenation t1t2 is a tuple of R3.
• Schema of R3 is the attributes of R1 and then R2,
  in order.
• But be ware attribute A of the same name in R1
  and R2 use R1.A and R2.A.

18
Example R3 R1 X R2
R1( A, B ) 1 2 3 4 R2( B, C ) 5 6 7 8 9 10
19
Renaming operator, roh
Return the result of expression E under the name x
Return the result of expression E under the name
x and with attributes renamed to A1,A2..An
20
Renaming

• The RENAME operator gives a new schema to a
  relation.
• R1 RENAMER1(A1,,An)(R2) makes R1 be a
  relation with attributes A1,,An and the same
  tuples as R2.
• Simplified notation R1(A1,,An) R2.

21
Example
Bars( name, addr ) Joes Maple
St. Sues River Rd.
R(bar, addr) Bars
22
Theta-Join

• R3 R1 JOINC R2
• Take the product R1 X R2.
• Then apply SELECTC to the result.

23
Example
R1
S1
24
Theta-Joins

• Condition Join
• Result schema same as that of cross-product.
• Fewer tuples than cross-product

25
Example
Sells( bar, beer, price ) Bars( name, addr
) Joes Bud 2.50 Joes Maple
St. Joes Miller 2.75 Sues River
Rd. Sues Bud 2.50 Sues Coors 3.00
BarInfo Sells JOIN Sells.bar Bars.name Bars
26
Natural Join

• A frequent type of join connects two relations
  by
• Equating attributes of the same name, and
• Projecting out one copy of each pair of equated
  attributes.
• Called natural join.
• Denoted R3 R1 JOIN R2.

27
Natural Joins

• Equi-Join A special case of condition join
  where the condition c contains only equalities.
• Result schema similar to cross-product, but only
  one copy of fields for which equality is
  specified.

28
Division

• useful for expressing queries like
  
  Find
  sailors who have reserved all boats.
• Suited for queries that include the phrase for
  all

29
Examples of Division A/B
B1
B2
B3
A/B1
A/B2
A/B3
A
30
Building Complex Expressions

• Combine operators with parentheses and precedence
  rules.
• Three notations, just as in arithmetic
• Sequences of assignment statements.
• Expressions with several operators.
• Expression trees.

31
Sequences of Assignments

• Create temporary relation names.
• Renaming can be implied by giving relations a
  list of attributes.
• Example R3 R1 JOINC R2 can be written
• R4 R1 X R2
• R3 SELECTC (R4)

32
Expressions in a Single Assignment

• Example the theta-join R3 R1 JOINC R2 can be
  written R3 SELECTC (R1 X R2)
• Precedence of relational operators
• SELECT, PROJECT, RENAME (highest).
• PRODUCT, JOIN.
• INTERSECTION.
• UNION, --

33
Expression Trees

• Leaves are operands
• Interior nodes are operators

34
Example

• Using the relations Bars(name, addr) and
  Sells(bar, beer, price), find the names of all
  the bars that are either on Maple Street or sell
  Bud for less than 3.

35
As a Tree
Bars
Sells
36
Example

• Using Sells(bar, beer, price), find the bars that
  sell two different beers at the same price.
• Strategy by renaming, define a copy of Sells,
  called S(bar, beer1, price). The natural join of
  Sells and S consists of quadruples (bar, beer,
  beer1, price) such that the bar sells both beers
  at this price.

37
The Tree
Sells
Sells