Relational Algebra : - PowerPoint PPT Presentation

About This Presentation
Title:

Relational Algebra :

Description:

Then in the union, t appears m n times. cs3431. Intersection: Consider two bags R1 and R2 that are union-compatible. ... in R1 m times, and in R2 n times. ... – PowerPoint PPT presentation

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

less

Transcript and Presenter's Notes

Title: Relational Algebra :


1
Relational Algebra I
  • Based on Chapter 2.4 5.1

2
Relational Languages
  • Query Language
  • Define data retrieval operations for relational
    model
  • Express easy access to large data sets in
    high-level language, not complex application
    programs
  • Languages
  • Relational Algebra procedural semantics based
    on set or bag theory
  • Relational Calculus logic-based language of
    denoting what is to be retrieved (but not how)
  • SQL syntactic sugar for relational calculus.

3
Basics
  • Relational Algebra is defined on bags ---
    versions for relations (sets) exist too.
  • Bag or multi-set
  • allow duplicate values but order is not
    significant.

4
Basics
  • Query Algebra
  • nested expression of algebra operators that
  • accept as input relations and outputs a
    relation
  • Example Query
  • SELECT gpa gt 3.0 ( UNION (Ugrads,Grads) )
  • Closure of Relational Algebra
  • operators work on relations and returns a relation

5
Relational Algebra Basics
  • Relational algebra includes
  • set operators, and
  • other operators specific to relational model.

6
Set Operators
  • Union, Intersection, Difference
  • Defined only for union compatible relations.
  • Relations are union compatible if
  • they have same sets of attributes and
  • the same types (domains) of attributes
  • Example Union compatible or not?
  • Student (sNumber, sName)
  • Course (cNumber, cName)

7
Union ?
  • Consider two bags R1 and R2 that are
    union-compatible.

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

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

R1
R2
R1 R2
10
Idempotent property
  • Idempotent property
  • Operation applied twice gives same result as when
    applied once
  • Example
  • Filter-BLUE ( Filter-BLUE ( images ))

11
Bag vs Set Semantics
  • Union is idempotent for sets
  • (R1 ? R2) ? R2 R1 ? R2
  • What about union for bags ?
  • Union is not idempotent for bags.
  • What about intersection ?
  • Intersection is idempotent for sets
  • Intersection is idempotent for bags
  • What about difference ?
  • Difference is idempotent for sets
  • Difference is not idempotent for bags

12
Bag vs Set Semantics
  • R1 ? R2 R1 (R1 R2 ).
  • For sets ?
  • For bags ?
  • Yes. True for both.

13
Cross Product (Cartesian Product) X
  • 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
14
Basic Relational Operations
  • Select, Project

15
Basic Relational Operations
  • Select sC (R)
  • selects subset of tuples of R that satisfies
    selection condition C.

s(C 6) (R)
R
16
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)

17
Project pA1, A2, , An (R)
  • pA1, A2, , An (R), with A1, A2, , An ?
    attributes AR
  • returns tuples in R, but only columns A1, A2, ,
    An.

pA, B (R)
R
18
Project Bag vs Set Semantics
  • For bags, cardinality of R cardinality of
    pA1, A2, , An (R).
  • For sets, cardinality of R cardinality of
    pA1,A2, , An (R).
  • For sets and bags
  • Is project commutative ? pApB ( Relation )
  • NO !
  • Is project idempotent ? pA pA ( Relation )
  • YES !

19
Equivalences with Select/Project
  • s salgt100k) (p sal ( Employee ))
    psal ( s salgt100k) (
    Employee ))
  • s salgt100k) (psal,name ( Employee ))
    psal,name ( s salgt100k)
    (psal,name ( Employee ))
  • s salgt100k) (p name ( Employee ))
    pname ( s salgt100k) (
    Employee ))

20
Summary So Far
  • Key Property
  • Closure of Relational Algebra
  • Basic Operators
  • Set Operators Union, Intersection, Difference
  • Cartesian Product (simple form of Join)
  • Select, Project
  • Logical Rewrite Rules
  • Idempotent, commutative, associative.
Write a Comment
User Comments (0)
About PowerShow.com