ra-sql2

# ra-sql2 - More SQL Extended Relational Algebra Outerjoins...

This preview shows pages 1–13. Sign up to view the full content.

1 More SQL Extended Relational Algebra Outerjoins, Grouping/Aggregation Insert/Delete/Update

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
2 The Extended Algebra δ  = eliminate duplicates from bags. τ  = sort tuples. γ  = grouping and aggregation. Outerjoin   : avoids “ dangling tuples ” = tuples  that do not join with anything.
3 Duplicate Elimination R1 :=  δ (R2). R1 consists of one copy of each tuple  that appears in R2 one or more times.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
4 Example : Duplicate Elimination R =  ( A B ) 1 2 3 4 1 2 δ (R) = A B 1 2 3 4
5 Sorting R1 :=  τ L  (R2). L   is a list of some of the attributes of R2. R1 is the list of tuples of R2 sorted first on the  value of the first attribute on  L , then on the  second attribute of  L , and so on. Break ties arbitrarily. τ  is the only operator whose result is neither  a set nor a bag.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
6 Example : Sorting R =  ( A B ) 1 2 3 4 5 2 τ B  (R) = [(5,2), (1,2), (3,4)]
7 Aggregation Operators Aggregation operators are not operators  of relational algebra. Rather, they apply to entire columns of  a table and produce a single result. The most important examples: SUM,  AVG, COUNT, MIN, and MAX.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
8 Example : Aggregation R =  ( A B ) 1 3 3 4 3 2 SUM(A) = 7 COUNT(A) = 3 MAX(B) = 4 AVG(B) = 3
9 Grouping Operator R1 :=  γ L  (R2).   L   is a list of elements  that are either: 1. Individual ( grouping  ) attributes. 2. AGG( A  ), where AGG is one of the  aggregation operators and  A   is an  attribute. An arrow and a new attribute name renames  the component.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
10 Applying  γ L (R) Group  R  according to all the grouping  attributes on list  L . That is: form one group for each distinct list  of values for those attributes in  R . Within each group, compute AGG( A  ) for  each aggregation on list  L . Result has one tuple for each group: 1. The grouping attributes and 2.  Their group’s aggregations.
11 Example : Grouping/Aggregation R =  ( A B C ) 1 2 3 4 5 6 1 2 5 γ A , B ,AVG(C)->X  (R) = ?? First, group  by  A  and  : A B C 1 2 3 1 2 5 4 5 6 Then, average  C   within groups: A B X 1 2 4 4 5 6

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
12 Outerjoin Suppose we join  R   C  S . A tuple of  R   that has no tuple of   with  which it joins is said to be  dangling .
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern