Chapter08ARelationalAlgebra (1).pptx

# That is let y be the set of attributes of r that are

• 65

This preview shows pages 45–54. Sign up to view the full content.

that is, let Y be the set of attributes of R that are not attributes of S. The result of DIVISION is a relation T(Y) that includes a tuple t if tuples t R appear in R with t R [Y] = t, and with t R [X] = t s for every tuple t s in S. For a tuple t to appear in the result T of the DIVISION, the values in t must appear in R in combination with every tuple in S. Relational Algebra and Calculus 45

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

Example of DIVISION Relational Algebra and Calculus 46
Operations of Relational Algebra Relational Algebra and Calculus 47

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

Operations of Relational Algebra Relational Algebra and Calculus 48
Query Tree Notation Query Tree An internal data structure to represent a query Standard technique for estimating the work involved in executing the query, the generation of intermediate results, and the optimization of execution Nodes stand for operations like selection, projection, join, renaming, division, …. Leaf nodes represent base relations A tree gives a good visual feel of the complexity of the query and the operations involved Algebraic Query Optimization consists of rewriting the query or modifying the query tree into an equivalent tree. Relational Algebra and Calculus 49

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

Example of a Query Tree Relational Algebra and Calculus 50
Additional Relational Operations: Aggregate Functions and Grouping A type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database. Examples of such functions include retrieving the average or total salary of all employees or the total number of employee tuples. These functions are used in simple statistical queries that summarize information from the database tuples. Common functions applied to collections of numeric values include SUM, AVERAGE, MAXIMUM, and MINIMUM. The COUNT function is used for counting tuples or values. Relational Algebra and Calculus 51

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

Aggregate Function Operation Use of the Aggregate Functional operation MAX Salary (EMPLOYEE) retrieves the maximum salary value from the EMPLOYEE relation MIN Salary (EMPLOYEE) retrieves the minimum Salary value from the EMPLOYEE relation SUM Salary (EMPLOYEE) retrieves the sum of the Salary from the EMPLOYEE relation COUNT SSN, AVERAGE Salary (EMPLOYEE) computes the count (number) of employees and their average salary Note: count just counts the number of rows, without removing duplicates Relational Algebra and Calculus 52
Using Grouping With Aggregation The previous examples all summarized one or more attributes for a set of tuples Maximum Salary or Count (number of) Ssn Grouping can be combined with Aggregate Functions Example: For each department, retrieve the DNO, COUNT SSN, and AVERAGE SALARY A variation of aggregate operation allows this: Grouping attribute placed to left of symbol Aggregate functions to right of symbol DNO COUNT SSN, AVERAGE Salary (EMPLOYEE)

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

This is the end of the preview. Sign up to access the rest of the document.
• Fall '09
• SUNANHAN

{[ 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