January 18, 2006

---

Warm-up

---

• why cannot a projection be pushed below a set-intersection?
• counterexample: consider relation schemas and instances R(a, b) = {4, 6} and S(a, b) with {4, 8}
  
  
  

---

Relations Review

---

• a relation is the subset of the Cartesian product, e.g.,
• a struct in C is a relation
• struct example_relation {int x; float y;};
  • Cartesian product of set of all possible values for int and set of all possible values for float

---

Three Types of Relations

---

• stored relation (as known as a `table' or `base relation')
• virtual relation (as known as a `view')
• temporary results

---

Closure

---

• closure property - a function is said to be closed if the result of the function can be feed back into the function, e.g.,
  • f(x)=x+1
  • int f(int) {...}
  • is compilation closed?
• closure property in SQL
• results of any query can be used in another query, e.g.,
• subqueries
• queries (SFWs) in the FROM clause

---

Review of Properties of Query Languages

---

• Relational algebra
  • procedural
• SQL
  • declarative
  • implemented
• Datalog
  • declarative
  • most expressive

---

More on Pushing Projections

---

• Example (courtesy [DBCB] example 16.11, pp. 803): consider relation schemas R(a, b, c) and S(c, d, e)
  
  
  
  
  how might this expression fail to be equivalent to the original?
• pushing a projection below a selection
  , M contains attributes in C and input attributes of L
• cannot apply transformations blindly
  • Example (courtesy [DBCB] example 16.12, pp. 804-805): consider relation schema StarsIn(movieTitle, movieYear, starName)
    
    
    versus
    
    
    • former is advisable if StarsIn is a stored relation
    • latter is advisable if StarsIn is not a stored relation
    • if StarsIn is stored, the lowest projection can waste time, esp. if there is an index on movieYear

---

Rewrite Rules for Joins and Products

---

• 
• , where C contains only the join attributes and L contains all attributes in C and all other attributes from R and S
• usually applies these rules from right to left
  • why?

---

Rules for Pushing Duplicate Elimination

---

• , if R is a set
• 
• 
• 
• 
• 
• 
• in general, cannot be pushed below , , or

---

More Extended Relational Algebra (Aggregation and Grouping)

---

• is the grouping operator
• Example (courtesy [DBCB] example 5.23, pp. 225): consider relation schema StarsIn(title, year, starName) and query for each star who has appeared in a least three movies, the earliest year in which they appeared
  
  
• is a special case of
  • why?
• can view as an extension of the projection operator on sets
  • why?
  • sometime referred to as generalized projection

---

Rewrite Rules for Grouping and Aggregation

---

• in general, transformation depends on the aggregate operators involved
  • MIN and MAX have same semantics on sets and bags
  • contrast with SUM, COUNT, and AVG
  • is duplicate impervious if the only aggregations in L are MIN and/or MAX
• 
• , if M contains all attributes of R in L
• , if is duplicate impervious
• Example (courtesy [DBCB] example 16.14, pp. 807-809): consider relation schemas MovieStar(name, addr, gender, birthdate) and StarsIn(movieTitle, movieYear, starName) and query for the birthdate of the youngest star in a movie each year

  • SELECT movieYear, MAX(birthdate)
    FROM MovieStar, StarsIn
    WHERE name=starName
    GROUP BY movieYear;
    

  • 
    
    

---

References

[DBCB] H. Garcia-Molina, J. D. Ullman, and J. Widom. Database Systems: The Complete Book. Prentice Hall, 2002.