Chapter 5 Algebraic and Logical Query Languages Prof

Chapter 5 Algebraic and Logical Query Languages Prof. Yin-Fu Huang CSIE, NYUST

5. 1 Relational Operations on Bags or multisets (See Fig. 5. 1) 5. 1. 1 Why Bags? n 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 n R in which tuple t appears n times S in which tuple t appears m times Database Systems Yin-Fu Huang

5. 1 Relational Operations on Bags Database Systems Yin-Fu Huang

5. 1 Relational Operations on Bags Database Systems Yin-Fu Huang

5. 1 Relational Operations on Bags R∪S: n+m times R∩S: min(n, m) times R - S: max(0, n-m) times n. Example R AB S AB 12 12 34 34 12 56 R∩S: A B 12 34 R - S: A B 12 12 R∪S: A B 12 12 34 34 34 56 Database Systems Yin-Fu Huang

5. 1 Relational Operations on Bags 5. 1. 3 Projection of Bags n πA, B(R) (See Fig. 5. 1 and Fig. 5. 2) 5. 1. 4 Selection of Bags n Example R ABC 125 3 4 6 σC≧ 6(R) ⇒ 127 ABC 346 127 Database Systems Yin-Fu Huang

5. 1 Relational Operations on Bags Database Systems Yin-Fu Huang

5. 1 Relational Operations on Bags Database Systems Yin-Fu Huang

5. 1 Relational Operations on Bags 5. 1. 5 Product of Bags n Example (See Fig. 5. 3) 5. 1. 6 Joins of Bags n Example R∞S A B C 123 R∞R. B<S. BS A R. B S. B C 1 2 4 5 Database Systems Yin-Fu Huang

5. 1 Relational Operations on Bags Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra 5. 2. 1 Duplicate Elimination n δ(R) 5. 2. 2 Aggregation Operators n SUM, AVG, MIN, MAX, COUNT n Example R AB SUM(B)=10 12 AVG(A)=1. 5 34 MIN(A)=1 12 MAX(B)=4 12 COUNT(A)=4 Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra 5. 2. 3 Grouping n Example (See Fig. 5. 4) 5. 2. 4 The Grouping Operator n γL(R) n 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 Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra n 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. n Example γ star. Name, MIN(year)→min. Year, COUNT(title)→ct. Title (Stars. IN) (See Fig. 5. 5) Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra 5. 2. 5 Extending the Projection Operator n πL(R) n 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 n Example πA, B+C→X(R) πB-A→X, C-B→Y(R) Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra 5. 2. 6 The Sorting Operator n τL(R) n Example τC, B(R) 5. 2. 7 Outerjoins n R⊙S n 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. n Example (See Fig. 5. 6) Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra n Left outerjoin: R⊙LS n Right outerjoin: R⊙RS n Example R⊙A >V. CS (See Fig. 5. 7) Database Systems Yin-Fu Huang

5. 2 Extended Operators of Relational Algebra Database Systems Yin-Fu Huang

The End. Database Systems Yin-Fu Huang