1 / 23

Relational Algebra

National University of Computer and Emerging Sciences . Relational Algebra. Lecture # 7 July 30 ,2012. Chapter Outline. Relational Algebra Unary Relational Operations Relational Algebra Operations From Set Theory Binary Relational Operations Additional Relational Operations

duyen
Download Presentation

Relational Algebra

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. National University of Computer and Emerging Sciences Relational Algebra Lecture # 7 July 30,2012

  2. Chapter Outline • Relational Algebra • Unary Relational Operations • Relational Algebra Operations From Set Theory • Binary Relational Operations • Additional Relational Operations • Examples of Queries in Relational Algebra

  3. DIVISION (Binary Operation) • The division operation is applied to two relations • R(Z)  S(X), where X  Z. • Let Y = Z – X • We have Z = X  Y and Y is a set of attributes of R that are not the attributes of S. • The result of DIVISION is a relation T(Y) • For a tuple t to appear in the result T of the DIVISION, the values in t must appear in R in combination with everytuple in S.

  4. Example of DIVISION • Retrieve all employees who work on all the project that John Smith works on • Smith   fname=‘John’ and lname=‘Smith’ (Employee) • Smith_Pnos   Pno (Works_onessn=ssn Smith) • Ssn_Pnos   Essn,Pno (Works_on) • SSNS(ssn) Ssn_PnosSmith_Pnos

  5. Recap of Relational Algebra Operations

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

  7. Example of Query Tree

  8. 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: • retrieve the average or total salary of all employees • Retrieve total number of employee tuples. • Common functions applied to collections of numeric values include • SUM, AVERAGE, MAXIMUM, and MINIMUM. • COUNT function is used for counting tuples or values.

  9. 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 number of employees and their average salary • Note: count just counts the number of rows, without removing duplicates

  10. Using Grouping with Aggregation • Grouping can be combined with Aggregate Functions • Example: For each department, retrieve the DNO, COUNT SSN, and AVERAGE SALARY • A variation of aggregate operation ℱ • DNO ℱCOUNT SSN, AVERAGE Salary (EMPLOYEE) • This operation groups employees by DNO and computes the count of employees and average salary per department

  11. Examples of applying aggregate functions and grouping

  12. Illustrating aggregate functions and grouping

  13. Recursive Closure Operation • Recursive Closure Operations cannot be specified in general using Relational Algebra • Example of a recursive operation is to retrieve all SUPERVISEES of an EMPLOYEE e at all levels — that is, • 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. • 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. • The SQL3 standard includes syntax for recursive closure.

  14. Recursive Closure Operation

  15. Outer Join Operation • In INNER JOIN, tuples without a matchingtuple are eliminated from the join result • Tuples with null are also eliminated • This amounts to loss of information. • OUTER joins operations are 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.

  16. Outer Join Operation • Left outer join: 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 with null values. • Right outer join: keeps every tuple in the second or right relation S in the result of R S. • Full outer join: keeps all tuplesin both the left and the right relations. It is denoted by

  17. Left Outer Join • List the employees name and the department name that they manage. If they don’t manage one, then indicate this with a null value. • Temp  (Employee Ssn=Mgr_Ssn Department) • Result  Fname, Minit, Lname, Dname(Temp)

  18. OUTER UNION Operations • The outer union operation take the union of tuples in two relations R(X, Y) and S(X, Z) that are partially compatible, • That is, only some of their attributes, say X, are type compatible. • The attributes that are type compatible are represented only once in the result, and • The attributes that are not type compatible from either relation are also kept in the result relation T(X, Y, Z).

  19. Outer Join Example • An outer union can be applied to two relations whose schemas are STUDENT(Name, SSN, Department, Advisor) and INSTRUCTOR(Name, SSN, Department, Rank). • Tuples from the two relations are matched based on having the same combination of values of 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. • The result relation STUDENT_OR_INSTRUCTOR will have the following attributes: STUDENT_OR_INSTRUCTOR (Name, SSN, Department, Advisor, Rank)

  20. Examples of Queries in Relational Algebra • Q1: Retrieve the name and address of all employees who work for the ‘Research’ department. RESEARCH_DEPT DNAME=’Research’ (DEPARTMENT) RESEARCH_EMPS  (RESEARCH_DEPT DNUMBER= DNOEMPLOYEEEMPLOYEE) RESULT FNAME, LNAME, ADDRESS (RESEARCH_EMPS)

  21. Examples of Queries in Relational Algebra • Q6: Retrieve the names of employees who have no dependents. ALL_EMPS SSN(EMPLOYEE) EMPS_WITH_DEPS(SSN) ESSN(DEPENDENT) EMPS_WITHOUT_DEPS  (ALL_EMPS - EMPS_WITH_DEPS) RESULT LNAME, FNAME (EMPS_WITHOUT_DEPS * EMPLOYEE)

  22. Examples of Queries in Relational Algebra • Q5: Retrieve the names of all employees with two or more dependents. T1(SsnNo_of_dependents)  Essn ℱ COUNT Dependent_name(DEPENDENT) T2No_of_dependents >1(T1) RESULT LNAME, FNAME (T2 * EMPLOYEE)

  23. Chapter Summary • Relational Algebra • Unary Relational Operations • Relational Algebra Operations From Set Theory • Binary Relational Operations • Additional Relational Operations • Examples of Queries in Relational Algebra

More Related