1 / 18

Advanced Operators of Relational Algebra

Learn about division, natural join, and outer join operations in relational algebra with examples and queries.

leejonathan
Télécharger la présentation

Advanced Operators of 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. Session 3 Welcome: To session 3-the fifth learning sequence “ Relational algebra “ Recap : In the previous learning sequences, we discussed the eight operators of relational algebra. Present learning: We shall explore the following topic: - RA: Division and Joins

  2. Relational Algebra

  3. Relational Algebra • Two Advanced RA operators: • Division • Natural-Join Operation • The operators take two or more relations as inputs and give a new relation as a result.

  4. Division Operation Suited to queries that include the phrase “for all”. Let r and s be relations on schemas R and S respectively where R = (A1, …, Am, B1, …, Bn) S = (B1, …, Bn) The result of r  s is a relation on schema R – S = (A1, …, Am) r  s = { t | t   R-S(r)   u  s ( tu  r ) } r  s

  5. Division Operation – Example A B B Relations r, s:            1 2 3 1 1 1 3 4 6 1 2 1 2 s r  s: A r  

  6. Another Division Example Relations r, s: A B C D E D E         a a a a a a a a         a a b a b a b b 1 1 1 1 3 1 1 1 a b 1 1 s r A B C r  s:   a a  

  7. Natural-Join Operation Let r and s be relations on schemas R and S respectively. Then, r s is a relation on schema R S obtained as follows: Consider each pair of tuples tr from r and ts from s. If tr and ts have the same value on each of the attributes in RS, add a tuple t to the result, where t has the same value as tr on r t has the same value as ts on s • Notation: r s

  8. Natural-Join Operation Example: R = (A, B, C, D) S = (E, B, D) Result schema = (A, B, C, D, E) rs is defined as:r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s)) • Notation: r s

  9. Natural Join Operation – Example Relations r, s: r s B D E A B C D 1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b r s A B C D E      1 1 1 1 2      a a a a b     

  10. Outer Join An extension of the join operation that avoids loss of information. Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. Uses null values: null signifies that the value is unknown or does not exist All comparisons involving null are (roughly speaking) false by definition. Will study precise meaning of comparisons with nulls later

  11. Outer Join – Example Relation loan branch-name loan-number amount Downtown Redwood Perryridge L-170 L-230 L-260 3000 4000 1700 customer-name loan-number Jones Smith Hayes L-170 L-230 L-155 • Relation borrower

  12. Outer Join – Example Inner JoinloanBorrower loan-number branch-name amount customer-name L-170 L-230 Downtown Redwood 3000 4000 Jones Smith • Left Outer Join loan Borrower loan-number branch-name amount customer-name L-170 L-230 L-260 Downtown Redwood Perryridge 3000 4000 1700 Jones Smith null

  13. Outer Join – Example Right Outer Join loanborrower loan-number branch-name amount customer-name L-170 L-230 L-155 Downtown Redwood null 3000 4000 null Jones Smith Hayes loan-number branch-name amount customer-name L-170 L-230 L-260 L-155 Downtown Redwood Perryridge null 3000 4000 1700 null Jones Smith null Hayes • Full Outer Join Loanborrower

  14. Example Queries (1) Find all customers who have an account from at least the “Downtown” and the Uptown” branches. Query 1 CN(BN=“Downtown”(depositoraccount))  CN(BN=“Uptown”(depositoraccount)) where CN denotes customer-name and BN denotes branch-name.

  15. Example Queries (2) Find all customers who have an account from at least the “Downtown” and the Uptown” branches. Query 2 customer-name, branch-name(depositoraccount)  temp(branch-name) ({(“Downtown”), (“Uptown”)})

  16. Find all customers who have an account at all branches located in Brooklyn city. Example Queries (3) customer-name, branch-name(depositoraccount)  branch-name (branch-city = “Brooklyn” (branch))

  17. Relational Algebra Summary: In this learning sequence, we discussed another basic operators of the topic relational algebra.

  18. END

More Related