5.1 Relational Operations on Bags

2. 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

3. 5.1 Relational Operations on Bags Database Systems

4. 5.1 Relational Operations on Bags Database Systems

5. 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

6. 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

7. 5.1 Relational Operations on Bags Database Systems

8. 5.1 Relational Operations on Bags Database Systems

9. 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

10. 5.1 Relational Operations on Bags Database Systems

11. 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

12. 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

13. 5.2 Extended Operators of Relational Algebra Database Systems

14. 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

15. 5.2 Extended Operators of Relational Algebra Database Systems

16. 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

17. 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

18. 5.2 Extended Operators of Relational Algebra Database Systems

19. 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

20. 5.2 Extended Operators of Relational Algebra Database Systems

21. The End. Database Systems