1 / 45

Relational Algebra

Relational Algebra. Procedural language Six basic operators select:  project:  union:  set difference: – Cartesian product: x rename:  closure. Select Operation. Notation:  p ( r ) p : selection predicate Defined as:  p ( r ) = { t | t  r and p(t) }

druce
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 • Procedural language • Six basic operators • select:  • project:  • union:  • set difference: – • Cartesian product: x • rename:  • closure

  2. Select Operation • Notation: p(r) • p : selection predicate • Defined as:p(r) = {t | t  rand p(t)} p: a formula in propositional calculusconsisting of termsconnected by : ,  ,  Each term is one of: <attribute> op <attribute> or <constant> Op : =, , >, . <.  • Example of selection:city=“mashad”(S)

  3. A B C D         1 5 12 23 7 7 3 10 Relation r A B C D • A=B ^ D > 5(r)     1 23 7 10

  4. Project Operation • Notation: where A1, A2 are attribute names and r is a relation name. • Duplicate rows removed from result, since relations are sets

  5. A B C     10 20 30 40 1 1 1 2 • Relation r: A C A C     1 1 1 2    1 1 2 = A,C (r)

  6. Union Operation • Notation: r s • Defined as: r s = {t | t  r or t  s} • For r s to be valid. 1. r,s must have the same arity (same number of attributes) 2. The attribute domains must be compatible

  7. A B A B • Relations r, s:    1 2 1 r s   2 3 A B     1 2 1 3 r  s:

  8. Set Difference Operation • Notation r – s • Defined as: r – s = {t | t rand t  s} • compatible relations: • r and s must have the samearity • attribute domains of r and s must be compatible

  9. A B A B • Relations r, s:    1 2 1   2 3 s r A B   1 1 r – s:

  10. Cartesian-Product Operation • Notation r x s • Defined as: r x s = {t q | t  r and q  s} • Assume that attributes of r(R) and s(S) are disjoint. • If attributes of r(R) and s(S) are not disjoint, then renaming must be used.

  11. Relations r, s: C D E A B     10 10 20 10 a a b b   1 2 s r A B C D E         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b r xs:

  12. Rename Operation • Allows us to name, and therefore to refer to, the results of relational-algebra expressions. • Allows us to refer to a relation by more than one name.

  13. Additional Operations • do not add any power to the relational algebra, but that simplify common queries. • Set intersection • Natural join • Division • Assignment

  14. Set-Intersection Operation • Notation: r s • Defined as: • rs = { t | trandts } • Assume: • r, s have the same arity • attributes of r and s are compatible

  15. Relation r, s: A B A B    1 2 1   2 3 r s A B  2 r s

  16. Natural-Join Operation Notation: r s R1 JOIN R2

  17. r s B D E A B C D • Relations r, s: 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     

  18. Division Operation r  s • Notation: • Suited to queries that include the phrase “for all” . R = (A1, …, Am , B1, …, Bn) S = (B1, …, Bn) R  S = (A1, …, Am) r  s = { t | t   R-S (r)   u  s (tu  r ) }

  19. Relations r, s: A B            1 2 3 1 1 1 3 4 6 1 2 B A 1 2   s r s: r

  20. Relations r, s: A B C 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 C D E   a a   a b 1 1 s r s: r

  21. Assignment Operation • provides a convenient way to express complex queries. • Write query as a sequential program consisting of a series of assignments • to a temporary relation variable. • Example: temp1 R-S (r )temp2 R-S ((temp1 x s ) – R-S,S (r ))result = temp1 – temp2

  22. Extended Relational-Algebra-Operations • Extend and Summarize • Outer Join

  23. Banking Example branch (branch_name, branch_city, assets) customer (customer_name,customer_street,custom_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) Depositor (customer_name,account_number) borrower (customer_name, loan_number)

  24. Aggregate Functions and Operations • Aggregation function takes a collection of values and returns a single value as a result. avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values

  25. Aggregate operation in relational algebra E is any relational-algebra expression • G1, G2 …, Gn is a list of attributes on which to group (can be empty) • Each Fiis an aggregate function • Each Aiis an attribute name

  26. Example: 1) Relation r: A B C         7 7 3 10 sum(c ) gsum(c) (r) 27

  27. branch_name account_number balance 2) Relation account grouped by branch-name: Perryridge Perryridge Brighton Brighton Redwood A-102 A-201 A-217 A-215 A-222 400 900 750 750 700 branch_nameg sum(balance) (account) branch_name sum(balance) Perryridge Brighton Redwood 1300 1500 700

  28. Result of aggregation does not have a name • Can use rename operation to give it a name • we permit renaming as part of aggregate operation branch_nameg sum(balance) assum_balance(account)

  29. Extend • EXTEND term ADD scalar-expression AS attribute • (EXTEND PADD(WEIGHT*454) ASGMWT) WHERE GMWT>1000

  30. Summarize • SUMMARIZE term BY (attribute-commalist)ADDaggregate-expressionAS attribute • SUMMARIZE (P JOIN SP) BY (CITY) ADD COUNT AS NSP • SUMMARIZE SP BY (P#) ADD SUM(QTY) AS TOTQTY,AVG(QTY) AS AVGQTY

  31. Outer Join • An extension of the join operation that avoids loss of information. • Computes the join and then adds tuples from one relation that does not match tuples in the other relation to the result of the join. • Uses null values(unknown or does not exist )

  32. 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 Example • Relation loan Relation borrower

  33. 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 Example Inner Joinloan Borrower

  34. Right Outer Join loan borrower loan_number branch_name amount customer_name L-170 L-230 L-155 Downtown Redwood null 3000 4000 null Jones Smith Hayes Full Outer Join loan borrower 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 Example

  35. Null Values • null signifies an unknown value or that a value does not exist. • The result of any arithmetic expression involving null is null. • Aggregate functions simply ignore null values • Comparisons with null values return the special truth value: unknown

  36. Modification of the Database • operations: • Deletion • Insertion • Updating • All these operations are expressed using the assignment operator.

  37. Deletion r r – E where r is a relation and E is a relational algebra expression. SP:=SP MINUS {{<s#:’s1’>,<p#:’p3’>,<qty:200>}}

  38. Insertion r  r  E where r is a relation and E is a relational algebra expression. SP:=SP UNION {{<s#:’s4’>,<p#:’p2’>,<qty:150>}}

  39. Updating UPDATE target assignment–commalist Attribute:=scalar-expression UPDATEpwhere color=‘red’ city:=‘mashad’

  40. مقايسه هاي رابطه اي • expression Θ expression • Where Θ : <> = ≥ > ≤ < S[city] = P[city] S[s#] > SP[s#]

  41. حساب رابطه‌اي حساب رابطه‌اي، با جبر رابطه‌اي منطقا معادل است، يعني براي هر عبارت جبر رابطه‌اي، يك عبارت معادل در حساب رابطه‌اي وجود دارد و برعكس. تفاوت آنها اين است كه جبر رابطه‌اي، دستوري است، اما حساب رابطه‌اي توصيفي است.

  42. حساب تاپلي: در اين حساب مفهوم مهمي به نام متغير تاپلي وجود دارد كه تنها مقادير مجازش، تاپلهاي رابطه هستند. • حساب ميداني: در اين حساب، متغير ميداني وجود دارد كه از يك ميدان مقدار مي‌گيرد. در اين حساب يك شرط اضافي به نام شرط عضويت وجود دارد.

  43. سور (Quantifier) 1-سور وجودي: به صورت نوشته مي‌شود، به اين معنا كه حداقل يك مقدار براي متغير T وجود دارد به نحوي كه f به ”درست“ ارزيابي شود. 2- سور همگاني: به صورت نوشته مي‌شود. يعني به ازای تمام مقادير متغير T، f به ”درست“ ارزيابي مي‌شود.

  44. شكل كلي يك عبارت حساب رابطه اي تاپلي ( target-item (s) ) [ WHERE f]

More Related