Chapter 6 Continued The Relational Algebra and Calculus
Chapter 6 (Continued) The Relational Algebra and Calculus Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Generalized Projection Allow functions to be applied to the attributes retrieved n Renaming is required for the results. n Example Report(Ssn, Net_salary, Bonus, Tax) n = Ssn, Salary-Deduction, 2000*years_service, 0. 25*salary (EMPLOYEE) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 2
Additional Relational Operations: Aggregate Functions and Grouping n n 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: n COUNT, SUM, AVERAGE, MAXIMUM, and MINIMUM. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 3
Aggregate Function Operation ℱ n n Pronounced “script F” ℱMAX Salary (EMPLOYEE) retrieves the maximum salary value from the EMPLOYEE relation n n The result is a single attribute, single tuple relation. To make this more explicit, one can use R 1 (max_salary) = ℱMAX Salary (EMPLOYEE) ℱMIN Salary (EMPLOYEE) and ℱSUM Salary (EMPLOYEE) are similar. ℱCOUNT SSN, AVERAGE Salary (EMPLOYEE) computes the count (number) of employees and their average salary n n Note: count just counts the number of rows, without removing duplicates This time the result is a 2 -attribute, single tuple relation. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 4
Exercises n Find the name(s) of the employees with the highest salary in the company. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 5
Exercise n Count the number of employees who earns more than twice as much as the average salary. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 6
Using Grouping with Aggregation n n Grouping can be combined with Aggregate Functions A variation of aggregate operation ℱ allows this: Grouping attribute placed to left of symbol Aggregate functions to right of symbol n n n Example: For each department, retrieve the DNO, COUNT SSN, and AVERAGE SALARY DNO n ℱCOUNT SSN, AVERAGE Salary (EMPLOYEE) Above operation groups employees by DNO (department number) and computes the count of employees and average salary per department Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 7
Grouping by Dno Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 8
Examples of applying aggregate functions and grouping Renaming by auto-renaming Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 9
Exercise n List the SSN of employees who involve in exactly 3 projects. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 10
Exercises n n List the department names that have more than 10 employees with salary $100, 000 List the names of departments whose best-paid employees earn more than $200, 000. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 11
Recursive Closure Operations n n Another type of operation that, in general, cannot be specified in the basic original relational algebra is recursive closure. Example: all SUPERVISEES of an EMPLOYEE e at all levels — that is, n n n all EMPLOYEE e’ directly supervised by e; all employees e’’ directly supervised by each employee e’; all employees e’’’ directly supervised by each employee e’’; and so on. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 12
Exercises n n e = “James Borg”. Get e’’’ Termination Condition ? Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 13
Additional Relational Operations (cont. ) n Although it is possible to retrieve employees at each level and then take their union, we cannot, in general, specify a query such as “retrieve the supervisees of ‘James Borg’ at all levels” without utilizing a looping mechanism. n The SQL 3 standard includes syntax for recursive closure. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 14
The OUTER JOIN Operation n In NATURAL JOIN and EQUIJOIN, tuples without a matching (or related) tuple are eliminated from the join result n n n Tuples with null in the join attributes are also eliminated This amounts to loss of information. OUTER joins can be used when we want to keep all the tuples in R, or all those in S, or all those in both relations in the result of the join, regardless of whether or not they have matching tuples in the other relation. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 15
Additional Relational Operations (cont. ) n n n The left outer join operation keeps every tuple in the first or left relation R in R S; if no matching tuple is found in S, then the attributes of S in the join result are filled or “padded” with null values. A similar operation, right outer join, keeps every tuple in the second or right relation S in the result of R S. A third operation, full outer join, denoted by keeps all tuples in both the left and the right relations when no matching tuples are found, padding them with null values as needed. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 16
An Example of Left Outer Join n T = EMPLOYEE n RESULT = Fname, Minit, Lname, Dname (T) ssn=mgr_ssn Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe DEPARTMENT 17
Exercises n Use outer joins to count the number of employees who are not working on any project. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 18
Exercises n Use outer joins to list SSNs of employees who do not supervise anyone. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 19
OUTER UNION Operations n n The outer union operation takes the union of two relations R(X, Y) and S(X, Z) that are partially compatible, meaning that only some of their attributes, say X, are type compatible. The attributes that are type compatible are represented only once in the result, and those attributes that are not type compatible from either relation are also kept in the result relation T(X, Y, Z). Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 20
Example of OUTER UNIONs n n Consider two relations STUDENT (Name, SSN, Dept, Advisor) INSTRUCTOR (Name, SSN, Dept, Rank). Their OUTER UNION will have the following attributes: STU_OR_INS n n n (Name, SSN, Dept, Advisor, Rank) Tuples from the two relations are matched based on the shared attributes — Name, SSN, Department. If a student is also an instructor, both Advisor and Rank will have a value; otherwise, one of these two attributes will be null. A person being both a student of CS and an instructor of MATH will have two entries in STU_OR_INS. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 21
Chapter Summary n Relational Algebra n n n Relational Calculus n n n Unary Relational Operations Relational Algebra Operations From Set Theory Binary Relational Operations Additional Relational Operations Examples of Queries in Relational Algebra Tuple Relational Calculus Domain Relational Calculus Overview of the QBE language (appendix C) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 22
- Slides: 22