1. Relational Algebra Part II Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

2. Announcements • Homework 1 is due NOW !!! • Homework 2 will be out today (Nov. 8) and due on Nov. 15 • Things to remember • Phase 2 of the project is due Nov. 11 • Make sure your Oracle account is working (if did not do so yet) -- You will need it soon CS3431

3. Relational-Algebra Operators (Recap) • Set operators • Union, Intersection, Difference • Selection & Projection & Extended Projection • Joins • Natural, Theta, Outer join • Rename & Assignment • Duplicate elimination • Grouping & Aggregation

4. Examples ofRelationships Among Operators

5. Relationships Among Operators (I) • Intersect as Difference • R ∩ S = R– (R–S) • Join as Cartesian Product + Select • R ⋈C S = (σC (R X S)) • Select is commutative • σC2 (σC1 (R)) = σC1 (σC2 (R)) = σC1^C2 (R) • Order between Select & Project • σC (πlist (R))  πlist (σC (R)) • πlist (σC (R))  σC (πlist (R)) Only if “list” contains all columns needed by conditions C

6. Relationships Among Operators (II) • Join is commutative • R ⋈C S = S ⋈C R • Left and Right outer joins are not commutative • Order between Select & Join • σR.x=“5”(R ⋈R.a = S.b S )  ((σR.x=“5”(R)) ⋈R.a = S.b S)

7. Operations On Bags

8. Operations on Bags • Most DBMSs allow relations to be bags(not limited to sets) • All previous relational algebra operators apply to both sets and bags • Bags allow duplicates • Duplicate elimination operator converts a bag into a set • Some properties may hold for sets but not bags • Example: R U R = R (True for sets, False for bags)

9. Example Operations on Bags: Union:  • Consider two relations Rand Sthat are union-compatible R S S R Suppose a tuple t appears in Rm times, and in Sntimes. Then in the union, t appears m + n times.

10. Example Operations on Bags: Intersection: ∩ • Consider two relations Rand Sthat are union-compatible R S R∩S Suppose tuple t appears in Rmtimes, and in Sntimes. Then in intersection, t appears min (m, n)times.

11. Example Operations on Bags: Difference: - • Suppose tuple t appears in Rmtimes & in Sntimes. • Then in R – S, t appears max (0, m - n)times. R S R– S

12. Project: πA1, A2, …, An (R) • πA1, A2, …, An (R) • returns tuples in R, but only columns A1, A2, …, An. πA, B (R) R • R is a set, but πA, B (R) is a bag cs3431

13. Exercise Query? Find customer name with the largest loan from a branch in “NY” city

14. Some Basic Rules for Algebraic Expressions(For Better Performance)

15. 1- Joins vs. Cartesian Product • Use Joins instead of Cartesian products (followed by selection) • R ⋈C S = (σC (R X S)) -- LHS is better • Intuition: There are efficient ways to do the L.H.S without going through the two-steps R.H.S CS3431

16. 2- Push Selection Down • Whenever possible, push the selection down • Selection is executed as early as possible • Intuition: Selection reduces the size of the data • Examples • σC (πlist (R))  πlist (σC (R)) -- RHS is better • σR.x=“5”(R ⋈R.a = S.b S )  ((σR.x=“5”(R)) ⋈R.a = S.b S) -- RHS is better CS3431

17. 3- Avoid Un-necessary Joins • Intuition: Joins can dramatically increase the size of the data Find customers having account balance below 100 and loans above 10,000 • R1  πcustomer_name (depositor ⋈ πaccount_number(σbalance <100 (account))) • R2  πcustomer_name (borrower ⋈ πloan_number(σamount >10,000 (loan))) • Result  R1 ∩ R2 Better than joining the 4 relations and then selecting