January 9, 2006

---

Introduction to CPS 432/562: Database Management Systems II

---

• course objectives, outline, and policies
• this course is more about the `how' than the `what' (in contrast to CPS 430/542)

---

Motivating Example

---

• consider relational schemas Students(id, name, gpa) and EnrolledIn(id, class)

• SELECT name
  FROM Students NATURAL_JOIN EnrolledIn
  WHERE gpa=4.0 AND class=432;
  

• which of the following is better?
  or
  

---

Overview of Query Processing

---

• elements of the query processing: query compilation query execution

(courtesy [DBCB])


• outline of query compilation
  
  (courtesy [DBCB])
  
  1. parse query
  2. select logical query plan (LQP)
  3. select physical query plan (PQP)
  4. execute plan

---

Physical Query Plan Selection

---

• which operations to perform?
• order of those operations?
• algorithms for those operations?
• transfer of data?

---

Our Plan (:-)

---

1. we will spend 2 weeks (4 classes) on LQP selection
2. we will spend 2 weeks (4 classes) on PQP selection

---

Components of Logical Query Plan Selection

---

(courtesy [DBCB])


1. parser
2. preprocessor - replaces views, checks types
3. logical query plan generator
4. query rewriter

---

Simple SQL Grammar

---

<Query> := <SFW>
<Query> := ( <Query> )
<SFW>   := SELECT <SelList> FROM <FromList> WHERE <Condition>

---

Little Bit of Notation

---

• we will use the subscripts S and B on relational operators to denote sets and bags, resp.
• e.g., ,
• an omission implies both

---

Commutative and Associative Rewrite Rules for Improving LQPs

---

• in algebra, + and * are both commutative and associative
  • commutativity, e.g., a + b = b + a
  • associativity, e.g., (a + b) + c = c + (b + a)
• What about - ?
• Which relational algebra operators are commutative and associative?
  • , , , and (for sets and bags)
  • what about ?
    • example (courtesy [DBCB] example 16.4, p. 796): consider relational schemas R(a, b), S(c, d, and T(c, d),
      
      
    • new arguments to theta-join may not contain the required attributes

---

Distributive Law of Intersection over Union

---

• 
• Does it hold for bags?

---

Some Trivial Laws

---

• 
• , e.g.,
• 

---

Golden Rule

---

• push selections down the expression tree as far as possible with altering the semantics of the expression
• why? selection removes tuples and expression trees are evaluated bottom-up

---

Laws Involving Selection

---

• Splitting Laws
  • 
  • 
• Commutativity
  • 
• Example (courtesy [DBCB] example 16.5, p. 798): consider relation schema R(a, b, c),
  
  
  
  

---

Just Started Pushing Selection through , , , , , and

---

---

References

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