Relational Algebra - PowerPoint PPT Presentation

About This Presentation
Title:

Relational Algebra

Description:

Relational Algebra B term 2004: lecture 10, 11 – PowerPoint PPT presentation

Number of Views:90
Avg rating:3.0/5.0
Slides: 25
Provided by: pcguest
Learn more at: http://web.cs.wpi.edu
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com