COMP 5138 Relational Database Management Systems - PowerPoint PPT Presentation

Loading...

PPT – COMP 5138 Relational Database Management Systems PowerPoint presentation | free to download - id: bf1cf-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

COMP 5138 Relational Database Management Systems

Description:

... of rows from relation. Projection ( ) Extracts only desired columns from relation. ... Result relation can be the input for another relational algebra operation! ... – PowerPoint PPT presentation

Number of Views:14
Avg rating:3.0/5.0
Slides: 15
Provided by: kellie7
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: COMP 5138 Relational Database Management Systems


1
COMP 5138 Relational Database Management Systems
Semester 2, 2007 Lecture 5A Relational Algebra
2
Overview
  • There are many tasks that can be understood as
    calculating some relation by combining the
    information in one or more relation instances
  • Relational algebra defines some operators that
    can be used to express a calculation like that
  • A central insight a query that extracts
    information can be seen as calculating a relation
    from the current state of the database

3
Relational Algebra
  • Users request information from a database using a
    query language
  • Six basic and several additional operators
  • Basic operations
  • Selection ( ) Selects a subset of rows
    from relation.
  • Projection ( ) Extracts only desired
    columns from relation.
  • Cross-product ( ) Allows us to combine two
    relations.
  • Set-difference ( ) Tuples in reln. 1, but
    not in reln. 2.
  • Union ( ) Tuples in reln. 1 or in reln. 2.
  • Rename ( ) Allows us to rename one field to
    another name.
  • Additional operations
  • Intersection, join, division Not essential, but
    (very!) useful.
  • The operators take one or more relations as
    inputs and give a new relation as result

X
_
4
Running Example
  • Sailors and Reserves relations for our
    examples.

Instance S2 of sailors
Instance S1 of sailors
Instance R1 of reserves
5
Projection
  • Deletes attributes that are not in projection
    list.
  • Schema of result contains exactly the fields in
    the projection list, with the same names that
    they had in the (only) input relation.

(S2)
age
(S2)
sname, rating
6
Selection
  • Selects rows that satisfy selection condition.
  • Result relation can be the input for another
    relational algebra operation! (Operator
    composition.)

( )
sname, rating
7
Set Operation
  • All of these operations take two input relations
  • Same number of fields.
  • Corresponding fields have the same type.
  • Set Union R ? S
  • Definition R U S t t ? R ? t ? S
  • Set Intersection R ? S
  • Definition R ? S t t ? R ? t ? S
  • Set Difference R - S
  • Definition R - S t t ? R ? t ? S

(S2)
(S1)
(S2)
(S1)
_
(S2)
(S1)
8
Cross-Product
  • Each row of S1 is paired with each row of R1.
  • Result schema has one field per field of S1 and
    R1, with field names inherited if possible.
  • Conflict Both S1 and R1 have a field called
    sid.
  • Sometimes called Cartesian product

9
Renaming
  • Allows us to name, and therefore to refer to, the
    results of relational-algebra expressions.
  • Allows us to refer to a relation by more than one
    name.
  • Notation 1 ? x (E)
  • returns the expression E under the name X
  • Notation 2 ?x (A1, A2, , An) (E)
  • returns the result of expression E under the name
    X, and with the attributes renamed to A1, A2, .,
    An.
  • (assumes that the relational-algebra expression E
    has arity n)
  • Example

C( 1? sid1, 5? sid2)( S1XR1)
10
Joins
  • Condition Join
  • Result schema same as that of cross-product.
  • Fewer tuples than cross-product, might be able to
    compute more efficiently
  • Sometimes called a theta-join.

11
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.
  • Natural Join Equijoin on all common fields.

R S
S
A
B
C
D
E
B
D
E
? ? ? ? ?
1 1 1 1 2
? ? ? ? ?
a a a a b
  • ?
  • ? ?
  • ?
  • ?

? ? ? ? ?
1 2 4 1 2
? ? ? ? ?
a a b a b
1 3 1 2 3
a a a b b
? ? ? ? ?

12
Division
  • Not supported as a primitive operator, but useful
    for expressing queries like
  • Find sailors who have served on all boats.
  • Let A have 2 fields, x and y B have only field
    y
  • A/B
  • i.e., A/B contains all x tuples (sailors) such
    that for every y tuple (boat) in B, there is an
    xy tuple in A.
  • Or If the set of y values (boats) associated
    with an x value (sailor) in A contains all y
    values in B, the x value is in A/B.
  • In general, x and y can be any lists of fields y
    is the list of fields in B, and x y is the
    list of fields of A.

13
Examples of Division A/B
B1
B2
B3
A/B3
A/B2
A
A/B1
14
Equivalence Rules
  • The following equivalence rules hold
  • Commutation rules
  • pA ( ?p ( R ) ) ? ?p ( pA ( R ) )
  • R S ? S R
  • Association rule
  • R (S T) ? (R S) T
  • Idempotence rules
  • pA ( pB ( R ) ) ? pA ( R ) if A ?
    B
  • ?p1 (?p2 ( R )) ? ?p1 ? p2 ( R )
  • Distribution rules
  • pA ( R ? S ) ? pA ( R ) ? pA ( S )
  • ?P ( R ? S ) ? ?P ( R ) ? ?P ( S )
  • ?P ( R S ) ? ?P (R) S if P
    only references R
  • pA,B(R S ) ? pA ( R ) pB ( S ) if
    join-attr. in (A ? B)
  • R ( S ? T ) ? ( R S ) ? ( R T )
  • These rules are the basis for the automatic
    optimisation of relational algebra expressions
About PowerShow.com