1 / 17

Relational Algebra

Relational Algebra. Part II. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. 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

conley
Download Presentation

Relational Algebra

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related