1 / 21

5.1 Relational Operations on Bags

5.1 Relational Operations on Bags. Bags or multisets (See Fig. 5.1) 5.1.1 Why Bags? Some relational operations are considerably more efficient if we use the bag model; e.g., union or projection. (See Fig. 5.1 and Fig. 5.2) 5.1.2 Union, Intersection, and Difference of Bags

kellan
Download Presentation

5.1 Relational Operations on Bags

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. 5.1 Relational Operations on Bags • Bags or multisets (See Fig. 5.1) 5.1.1 Why Bags? • Some relational operations are considerably more efficient if we use the bag model; e.g., union or projection. (See Fig. 5.1 and Fig. 5.2) 5.1.2 Union, Intersection, and Difference of Bags • R in which tuple t appears n times S in which tuple t appears m times Database Systems

  2. 5.1 Relational Operations on Bags Database Systems

  3. 5.1 Relational Operations on Bags Database Systems

  4. 5.1 Relational Operations on Bags • R∪S: n+m times • R∩S: min(n, m) times • R - S: max(0, n-m) times • Example • R A B S A B R∪S: A B • 1 2 1 2 1 2 • 3 4 3 4 1 2 • 1 2 3 4 1 2 • 1 2 5 6 1 2 • 3 4 • R∩S: A B R - S: A B 3 4 • 1 2 1 2 3 4 • 3 4 1 2 5 6 Database Systems

  5. 5.1 Relational Operations on Bags • 5.1.3 Projection of Bags • πA,B(R) • (See Fig. 5.1 and Fig. 5.2) • 5.1.4 Selection of Bags • Example • R A B C A B C • 1 2 5 3 4 6 • 3 4 6 σC≧6(R) ⇒ 1 2 7 • 1 2 7 1 2 7 • 1 2 7 Database Systems

  6. 5.1 Relational Operations on Bags Database Systems

  7. 5.1 Relational Operations on Bags Database Systems

  8. 5.1 Relational Operations on Bags • 5.1.5 Product of Bags • Example • (See Fig. 5.3) • 5.1.6 Joins of Bags • Example • R∞S A B C R∞R.B<S.BS A R.B S.B C • 1 2 3 1 2 4 5 • 1 2 3 1 2 4 5 • 1 2 4 5 • 1 2 4 5 Database Systems

  9. 5.1 Relational Operations on Bags Database Systems

  10. 5.2 Extended Operators of Relational Algebra • 5.2.1 Duplicate Elimination • δ(R) • 5.2.2 Aggregation Operators • SUM, AVG, MIN, MAX, COUNT • Example • R A B SUM(B)=10 • 1 2 AVG(A)=1.5 • 3 4 MIN(A)=1 • 1 2 MAX(B)=4 • 1 2 COUNT(A)=4 Database Systems

  11. 5.2 Extended Operators of Relational Algebra • 5.2.3 Grouping • Example • (See Fig. 5.4) • 5.2.4 The Grouping Operator • γL(R) • A list L of elements, each of which is either: • 1) A grouping attribute: an attribute of the relation R to • which the γ is applied. • 2) An aggregated attribute: an attribute of the relation R to • which an aggregate operator is applied. Database Systems

  12. 5.2 Extended Operators of Relational Algebra Database Systems

  13. 5.2 Extended Operators of Relational Algebra • Constructing as follows: • 1) Partition the tuples of R into groups. • 2) For each group, produce one tuple consisting of: • i) The grouping attributes' values for that group and • ii) The aggregations, over all tuples of that group, for the • aggregated attributes on list L. • Example • γ starName, MIN(year)→minYear, COUNT(title)→ctTitle (StarsIN) • (See Fig. 5.5) Database Systems

  14. 5.2 Extended Operators of Relational Algebra Database Systems

  15. 5.2 Extended Operators of Relational Algebra • 5.2.5 Extending the Projection Operator • πL(R) • Projection lists can have the following kinds of elements: • 1) A single attribute of R • 2) An expression x → y • 3) An expression E → z • Example • πA, B+C→X(R) • πB-A→X, C-B→Y(R) Database Systems

  16. 5.2 Extended Operators of Relational Algebra • 5.2.6 The Sorting Operator • τL(R) • Example • τC,B(R) • 5.2.7 Outerjoins • R⊙S • The dangling tuples are padded with a special null symbol, ⊥, in all the attributes that they do not possess but that appear in the join result. • Example • (See Fig. 5.6) Database Systems

  17. 5.2 Extended Operators of Relational Algebra Database Systems

  18. 5.2 Extended Operators of Relational Algebra • Left outerjoin: R⊙LS • Right outerjoin: R⊙RS • Example • R⊙A >V.CS • (See Fig. 5.7) Database Systems

  19. 5.2 Extended Operators of Relational Algebra Database Systems

  20. The End. Database Systems

More Related