Relational Algebra

1
Relational Algebra

• Basic Operations
• Algebra of Bags

2
What is an Algebra

• Mathematical system consisting of
• Operands --- variables or values from which new
  values can be constructed.
• Operators --- symbols denoting procedures that
  construct new values from given values.

3
What is Relational Algebra?

• An algebra whose operands are relations or
  variables that represent relations.
• Operators are designed to do the most common
  things that we need to do with relations in a
  database.
• The result is an algebra that can be used as a
  query language for relations.

4
Core Relational Algebra

• Union, intersection, and difference.
• Usual set operations, but both operands must have
  the same relation schema.
• Selection picking certain rows.
• Projection picking certain columns.
• Products and joins compositions of relations.
• Renaming of relations and attributes.

5
Relational Operators
Core Relational Operators

• Selection sC
• R1 sC (R2)
• Projection pL
• R1 pL (R2)
• Product ?
• R3 R1 ? R2
• Union ?
• R3 R1 ? R2
• Difference
• R3 R1 R2

• Theta-Join ?C
• R3 R1 ?C R2
• Natural Join ?
• R3 R1 ? R2
• Renaming ?
• R1 ?R1(A1,,An)(R2)
• Intersection n
• R3 R1 n R2

Usual Set Operators
6
Selection

• R1 sC (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.

7
Example Selection
Relation Sells bar beer price Joes Bud 2.5
0 Joes Miller 2.75 Sues Bud 2.50 Sues M
iller 3.00
8
Projection

• R1 pL (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 Projection
Relation Sells bar beer price Joes Bud 2.5
0 Joes Miller 2.75 Sues Bud 2.50 Sues M
iller 3.00
10
Extended Projection

• Using the same pL operator, we allow the list L
  to contain arbitrary expressions involving
  attributes
• Arithmetic on attributes, e.g., AB-gtC.
• Duplicate occurrences of the same attribute.

11
Example Extended Projection
R ( A B ) 1 2 3 4
12
Product

• R3 R1 ? 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 beware attribute A of the same name in R1 and
  R2 use R1.A and R2.A.

13
Example R3 R1 ? R2
R1( A, B ) 1 2 3 4 R2( B, C ) 5 6 7 8 9
10
14
Theta-Join

• R3 R1 ?C R2
• Take the product R1 ? R2.
• Then apply sC to the result.
• As for s, C can be any boolean-valued condition.
• Historic versions of this operator allowed only A
  ? B, where ? is , lt, etc. hence the name
  theta-join.

15
Example Theta Join
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 ?Sells.bar Bars.name Bars
16
Natural Join

• A useful join variant (natural join) connects
  two relations by
• Equating attributes of the same name, and
• Projecting out one copy of each pair of equated
  attributes.
• Denoted R3 R1 ? R2.

17
Example Natural Join
Sells( bar, beer, price ) Bars( bar, 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 ? Bars Note Bars.name has
become Bars.bar to make the natural join work
because must have attributes of same name
18
Renaming

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

19
Example Renaming
Bars( name, addr ) Joes Maple
St. Sues River Rd.
R(bar, addr) Bars
20
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.

21
Sequences of Assignments

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

22
Expressions in a Single Assignment

• Example the theta-join R3 R1 ?C R2 can be
  written R3 sC (R1 ? R2)
• Precedence of relational operators
• s, p, ? (highest).
• ?, ?.
• n.
• ?,

23
Expression Trees

• Leaves are operands --- either variables standing
  for relations or particular, constant relations.
• Interior nodes are operators, applied to their
  child or children.

24
Example Tree for a Query

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

25
As a Tree
?
?R(name)
pname
pbar
saddr Maple St.
spricelt3 AND beerBud
Bars
Sells
26
Example Self-Join

• 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.

27
The Tree
pbar
sbeer ! beer1
?
?S(bar, beer1, price)
Sells
Sells
28
Schemas for Results

• Union, intersection, and difference the schemas
  of the two operands must be the same, so use that
  schema for the result.
• Selection schema of the result is the same as
  the schema of the operand.
• Projection list of attributes tells us the
  schema.

29
Schemas for Results --- (2)

• Product schema is the attributes of both
  relations.
• Use R.A, etc., to distinguish two attributes
  named A.
• Theta-join same as product.
• Natural join union of the attributes of the two
  relations.
• Renaming the operator tells the schema.

30
Relational Algebra on Bags

• A bag (or multiset ) is like a set, but an
  element may appear more than once.
• Example 1,2,1,3 is a bag.
• Example 1,2,3 is also a bag that happens to be
  a set.

31
Why Bags?

• SQL, the most important query language for
  relational databases, is actually a bag language.
• Some operations, like projection, are more
  efficient on bags than sets.

32
Operations on Bags

• Selection applies to each tuple, so its effect on
  bags is like its effect on sets.
• Projection also applies to each tuple, but as a
  bag operator, we do not eliminate duplicates.
• Products and joins are done on each pair of
  tuples, so duplicates in bags have no effect on
  how we operate.

33
Example Bag Selection
R( A, B ) 1 2 5 6 1 2
34
Example Bag Projection
R( A, B ) 1 2 5 6 1 2
35
Example Bag Product
R( A, B ) S( B, C ) 1 2 3 4 5 6 7 8 1 2
R ? S A R.B S.B C 1 2 3 4 1 2 7 8 5 6 3 4
5 6 7 8 1 2 3 4 1 2 7 8
36
Example Bag Theta-Join
R( A, B ) S( B, C ) 1 2 3 4 5 6 7 8 1 2
37
Bag Union

• An element appears in the union of two bags the
  sum of the number of times it appears in each
  bag.
• Example 1,2,1 ? 1,1,2,3,1 1,1,1,1,1,2,2,3

38
Bag Intersection

• An element appears in the intersection of two
  bags the minimum of the number of times it
  appears in either.
• Example 1,2,1,1 n 1,2,1,3 1,1,2.

39
Bag Difference

• An element appears in the difference A B of
  bags as many times as it appears in A, minus the
  number of times it appears in B.
• But never less than 0 times.
• Example 1,2,1,1 1,2,3 1,1.

40
Beware Bag Laws ! Set Laws

• Some, but not all algebraic laws that hold for
  sets also hold for bags.
• Example the commutative law for union (R ?S S
  ?R ) does hold for bags.
• Since addition is commutative, adding the number
  of times x appears in R and S doesnt depend on
  the order of R and S.

41
Example A Law That Fails

• Set union is idempotent, meaning that S ?S S.
• However, for bags, if x appears n times in S,
  then it appears 2n times in S ?S.
• Thus S ?S ! S in general.
• e.g., 1 ? 1 1,1 ! 1.