Relational Algebra

1
Relational Algebra

• B term 2004 lecture 10, 11

2
Basics

• Relational Algebra is defined on bags, rather
  than relations.
• Bag or multiset allows duplicate values but
  order is not significant.
• We can write an expression using relational
  algebra operators with parentheses we need
  closure an operator on bag returns a bag.
• Relational algebra includes set operators, and
  other operators specific to relational model.

3
Set Operators

• Union, Intersection, Difference, cross product
• Union, Intersection and Difference are defined
  only for union compatible relations.
• Two relations are union compatible if they have
  the same set of attributes and the types
  (domains) of the attributes are the same.
• Eg of two relations that are not union
  compatible
• Student (sNumber, sName)
• Course (cNumber, cName)

4
Union ?

• Consider two bags R1 and R2 that are
  union-compatible. Suppose a tuple t appears in R1
  m times, and in R2 n times. Then in the union, t
  appears m n times.

R1 ? R2
R1
R2
A B
1 2
1 2
1 2
3 4
3 4
5 6
A B
1 2
3 4
1 2
A B
1 2
3 4
5 6
5
Intersection n

• Consider two bags R1 and R2 that are
  union-compatible. Suppose a tuple t appears in R1
  m times, and in R2 n times. Then in the
  intersection, t appears min (m, n) times.

R1
R2
R1 n R2
A B
1 2
3 4
A B
1 2
3 4
1 2
A B
1 2
3 4
5 6
6
Difference -

• Consider two bags R1 and R2 that are
  union-compatible. Suppose a tuple t appears in R1
  m times, and in R2 n times. Then in R1 R2, t
  appears max (0, m - n) times.

R1
R2
R1 R2
A B
1 2
3 4
1 2
A B
1 2
3 4
5 6
A B
1 2
7
Bag semantics vs Set semantics

• Union is idempotent for sets (R1 ? R2) ? R2
  R1 ? R2
• Union is not idempotent for bags.
• Intersection and difference are idempotent for
  sets and bags.
• For sets and bags, R1 ? R2 R1 (R1 R2).

8
Cross Product (Cartesian Product) ?

• Consider two bags R1 and R2. Suppose a tuple t1
  appears in R1 m times, and a tuple t2 appears in
  R2 n times. Then in R1 X R2, t1t2 appears mn
  times.

R1 X R2
R1
R2
A R1.B R2.B C
1 2 2 3
1 2 2 3
1 2 4 5
1 2 4 5
1 2 4 5
1 2 4 5
A B
1 2
1 2
B C
2 3
4 5
4 5
9
Basic Relational Operations

• Select, Project, Join
• Select denoted sC (R) selects the subset of
  tuples of R that satisfies selection condition C.
  C can be any boolean expression, its clauses can
  be combined with AND, OR, NOT.

s(C 6) (R)
R
A B C
1 2 5
3 4 6
1 2 7
1 2 7
A B C
3 4 6
1 2 7
1 2 7
10
Select

• Select is commutative sC2 (sC1 (R)) sC1 (sC2
  (R))
• Select is idempotent sC (sC (R)) sC (R)
• We can combine multiple select conditions into
  one condition. sC1 (sC2 ( sCn (R))) sC1 AND
  C2 AND Cn (R)

11
Project pA1, A2, , An (R)

• Consider relation (bag) R with set of attributes
  AR. pA1, A2, , An (R), where A1, A2, , An ? AR
  returns the tuples in R, but only with columns
  A1, A2, , An.

pA, B (R)
R
A B C
1 2 5
3 4 6
1 2 7
1 2 8
A B
1 2
3 4
1 2
1 2
12
Project Bag Semantics vs Set Semantics

• For bags, the cardinality of R cardinality of
  pA1, A2, , An (R).
• For sets, cardinality of R cardinality of
  pA1,A2, , An (R).
• For sets and bags
• project is not commutative
• project is idempotent

13
Natural Join R ? S

• Consider relations (bags) R with attributes AR,
  and S with attributes AS. Let A AR n AS. R ? S
  can be defined as
• pAR A, A, AS - A (sR.A1 S.A1 AND R.A2 S.A2
  AND R.AnS.An (R X S))
• where A A1, A2, , An
• The above expression says select those tuples
  in R X S that agree in values for each of the A
  attributes, and project the resulting tuples such
  that we have only one value for each A attribute.

14
Natural Join example
R1
R2
R1 ? R2
A B
1 2
1 2
B C
2 3
4 5
4 5
A B C
1 2 3
1 2 3
15
Theta Join R ?C S

• Theta Join is similar to natural join, except
  that we can specify any condition C. It is
  defined as
• R ?C S (sC (R X S))

R1 ? R1.BltR2.BR2
A R1.B R2.B C
1 2 4 5
1 2 4 5
1 2 4 5
1 2 4 5
R1
R2
A B
1 2
1 2
B C
2 3
4 5
4 5
16
Outer Join R ?o S

• Similar to natural join, however, if there is a
  tuple in R, that has no matching tuple in S, or
  a tuple in S that has no matching tuple in R,
  then that tuple also appears, with null values
  for attributes in S (or R).

R1 ?o R2
A B C D
1 2 3 10
1 2 3 11
4 5 6 null
7 8 9 null
null 6 7 12
R1
R2
A B C
1 2 3
4 5 6
7 8 9
B C D
2 3 10
2 3 11
6 7 12
17
Left Outer Join R ?oLS

• Similar to natural join, however, if there is a
  tuple in R, that has no matching tuple in S,
  then that tuple also appears, with null values
  for attributes in S (note a tuple in S that has
  no matching tuple in R does not appear).

R1 ?oL R2
R1
R2
A B C D
1 2 3 10
1 2 3 11
4 5 6 null
7 8 9 null
A B C
1 2 3
4 5 6
7 8 9
B C D
2 3 10
2 3 11
6 7 12
18
Right Outer Join R ?oRS

• Similar to natural join, however, if there is a
  tuple in S, that has no matching tuple in R,
  then that tuple also appears, with null values
  for attributes in R (note a tuple in R that has
  no matching tuple in S does not appear).

R1 ?oR R2
R1
R2
A B C D
1 2 3 10
1 2 3 11
null 6 7 12
A B C
1 2 3
4 5 6
7 8 9
B C D
2 3 10
2 3 11
6 7 12
19
Renaming ?S(A1, A2, , An) (R)

• Rename relation R to S, attributes of R are
  renamed to A1, A2, , An

?S(X, C, D) (R2)
S
R2
X C D
2 3 10
2 3 11
6 7 12
B C D
2 3 10
2 3 11
6 7 12
20
Duplicate Elimination d (R)

• Convert a bag to a set.

d (R)
R
A B
1 2
3 4
A B
1 2
3 4
1 2
1 2
21
Aggregation operators

• MIN, MAX, COUNT, SUM, AVG
• The aggregate operators aggregate the values in
  one column of a relation.

R
MIN (B) 2 MAX (B) 4 COUNT (B) 4 SUM (B)
10 AVG (B) 2.5
A B
1 2
3 4
1 2
1 2
22
Grouping Operators
23
Sorting Operator
24
Extended Projection