ics 321 fall 2010 algebraic and logical query languages n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
ICS 321 Fall 2010 Algebraic and Logical Query Languages PowerPoint Presentation
Download Presentation
ICS 321 Fall 2010 Algebraic and Logical Query Languages

Loading in 2 Seconds...

play fullscreen
1 / 12

ICS 321 Fall 2010 Algebraic and Logical Query Languages - PowerPoint PPT Presentation


  • 66 Views
  • Uploaded on

ICS 321 Fall 2010 Algebraic and Logical Query Languages. Asst. Prof. Lipyeow Lim Information & Computer Science Department University of Hawaii at Manoa. Relational Algebra Review. Relations are sets of tuples – no duplicates allowed Basic operations:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'ICS 321 Fall 2010 Algebraic and Logical Query Languages' - madison


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
ics 321 fall 2010 algebraic and logical query languages

ICS 321 Fall 2010Algebraic and Logical Query Languages

Asst. Prof. Lipyeow Lim

Information & Computer Science Department

University of Hawaii at Manoa

Lipyeow Lim -- University of Hawaii at Manoa

relational algebra review
Relational Algebra Review
  • Relations are sets of tuples – no duplicates allowed
  • Basic operations:
    • Selection (σ) Selects a subset of rows from relation.
    • Projection (π) Deletes unwanted columns from relation.
    • Cross-product(×) Allows us to combine two relations.
    • Set-difference (−) Tuples in reln. 1, but not in reln. 2.
    • Union(U) Tuples in reln. 1 and in reln. 2.
  • Additional operations:
    • Intersection, join, division, renaming: Not essential, but (very!) useful.
  • Each operation returns a relation, operationscan be composed! (Algebra is “closed”.)

Lipyeow Lim -- University of Hawaii at Manoa

bag semantics
Bag Semantics

Bag

Set

  • Commercial DBMS implements relations as bags
  • Avoid duplicate elimination
  • Support aggregations

Can relational algebra work with bags ?

Lipyeow Lim -- University of Hawaii at Manoa

selection projection
Selection & Projection
  • Expected behavior
  • No duplicate elimination of results

πage (S2)

πage (σrating>6 (S2))

Lipyeow Lim -- University of Hawaii at Manoa

cross product joins
Cross Product & Joins

S

R  S

R

Cross

Product

Join

Treat duplicates like non-duplicates

Lipyeow Lim -- University of Hawaii at Manoa

bag union intersection difference
Bag Union, Intersection & Difference

S

R

R  S

Bag Union

Tuple t appears (n+m) times

Bag

Intersection

Bag

Difference

R  S

R - S

S - R

Treat duplicates like non-duplicates

Tuple t appears min(n,m) times

Tuple t appears max(0, n-m) times

Lipyeow Lim -- University of Hawaii at Manoa

extended operators
Extended Operators
  • Duplicate elimination 
    • turns a bag into a set
  • Aggregation
    • calculates an aggregate (sum, average etc) over the values in a column
  • Grouping 
    • partitions tuples in a relation into groups based on values in some columns
  • Extended projection 
    • allow computation on column values to produce new values
  • Sorting 
    • sorts a relation according to the values in some column(s)
  • Outer join
    • preserves dangling pointers in the results of joins

Lipyeow Lim -- University of Hawaii at Manoa

aggregation
Aggregation
  • Standard: SUM, AVG, MIN, MAX, COUNT
  • DBMS supports more sophisticated functions like Variance, standard deviation etc.
  • SUM(B) = 2+4+2+2 = 10
  • AVG(A) = (1+3+1+1)/4 = 1.5
  • MIN(A) = 1
  • MAX(B) = 4
  • COUNT(A) = 4

Lipyeow Lim -- University of Hawaii at Manoa

grouping
Grouping

Movies

  • Grouping operator 
    • Groups tuples by some columns
    • Apply aggregation function to each group
    • Generate a result tuple per group

For each studio, find the total lengths of movies produced

Lipyeow Lim -- University of Hawaii at Manoa

grouping operator arguments
Grouping Operator Arguments

Movies

studioName, SUM(length) sumOfLengths

Aggregation functions on aggregated attributes

with optional renaming

Grouping attributes

Lipyeow Lim -- University of Hawaii at Manoa

extended projection
Extended Projection

AX, B+CY (R)

Renaming of attributes

Expressions that compute new values from attributes and naming the new values

Lipyeow Lim -- University of Hawaii at Manoa

outer join
Outer Join

R

S

Discard right & left dangling pointers

R S

R RS

R L S

Keep right dangling pointers

Keep left dangling pointers

Lipyeow Lim -- University of Hawaii at Manoa