Chapter 6 The Relational Algebra and Calculus Copyright

Chapter Outline n Relational Algebra n n n Relational Calculus 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 Example Database Application (COMPANY) Overview of the QBE language (appendix D) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 2

Relational Algebra Overview n n n Relational algebra is the basic set of operations for the relational model These operations enable a user to specify basic retrieval requests (or queries) The result of an operation is a new relation, which may have been formed from one or more input relations Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 3

Relational Algebra Overview (continued) n The algebra operations thus produce new relations n n These can be further manipulated using operations of the same algebra A sequence of relational algebra operations forms a relational algebra expression n The result of a relational algebra expression is also a relation that represents the result of a database query (or retrieval request) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 4

The COMPANY Database Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 5

The following query results refer to this database state Copyright © 2007 Ramez Elmasri

Unary Relational Operations: SELECT n The SELECT operation (denoted by (sigma)) is used to select a subset of the tuples from a relation based on a selection condition. n n Keeps only those tuples that satisfy the qualifying condition Examples: n Select the EMPLOYEEs whose department # is 4: DNO = 4 (EMPLOYEE) n Select the EMPLOYEEs whose salary > $30, 000: SALARY > 30, 000 (EMPLOYEE) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 7

SELECT Operation Properties n The SELECT operation <selection condition>(R) produces a relation S that has the same schema (same attributes) as R n SELECT is commutative: <condition 1>( < condition 2> (R)) = <condition 2> ( < condition 1> (R)) n Because of commutativity, a sequence of SELECT operations may be applied in any order: <cond 1>( <cond 2> ( <cond 3> (R)) = <cond 2> ( <cond 3> ( <cond 1> ( R))) n A cascade of SELECT operations may be replaced by a single selection with a conjunction of all the conditions: <cond 1>( < cond 2> ( <cond 3>(R)) = <cond 1> AND < cond 2> AND < cond 3>(R))) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 8

Unary Relational Operations: PROJECT n n n PROJECT Operation is denoted by (pi) This operation keeps certain columns (attributes) from a relation and discards the other columns. Example: To list each employee’s first and last name and salary, the following is used: LNAME, FNAME, SALARY(EMPLOYEE) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 9

PROJECT (cont. ) n The general form of the project operation is: <attribute list>(R) n n <attribute list> is the desired list of attributes from relation R. The project operation removes any duplicate tuples n This is because the result of the project operation is still a relation, that is, a set of tuples Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 10

PROJECT Operation Properties n The number of tuples in the result of projection <list>(R) is always less or equal to the number of tuples in R n n If the list of attributes includes a key of R, then the number of tuples in the result of PROJECT is equal to the number of tuples in R PROJECT is not commutative n <list 1> ( <list 2> (R) ) = <list 1> (R) as long as <list 2> contains the attributes in <list 1> Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 11

Examples of applying SELECT and PROJECT operations Copyright © 2007 Ramez Elmasri and Shamkant

Single expression versus sequence of relational operations (Example) n n Task: To retrieve the first name, last name, and salary of all employees who work in department number 5. Solution 1: A single relational algebra expression: n n FNAME, LNAME, SALARY( DNO=5(EMPLOYEE)) Solution 2: a sequence of operations, giving a name to each intermediate relation: n DEP 5_EMPS DNO=5(EMPLOYEE) n RESULT FNAME, LNAME, SALARY (DEP 5_EMPS) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 13

Example of applying multiple operations and RENAME LNAME, FNAME, SALARY( Dno=5 (EMPLOYEE)) Temp = Dno=5 (EMPLOYEE) R (First_name, Last_name, Salary) = LNAME, FNAME, SALARY(Temp) * Notice the implied renaming Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 14

Important Note n n The Select and Project operations produce relations. Dno ( SSN = 123456789 (EMPLOYEE)) results in {(5)}. n n n The schema of the relation is (Dno). The result relation has only one tuple. It is still a relation, not a number. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 15

Unary Relational Operations: RENAME n n The RENAME operator is denoted by (rho) In some cases, we may want to rename the attributes of a relation or the relation name or both n Useful when a query requires multiple operations n Necessary in some cases (see JOIN operation later) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 16

Example of applying multiple operations and RENAME Temp = Dno=5 (EMPLOYEE) R = (First_name, Last_name Salary) ( LNAME, FNAME, SALARY(Temp)) * Notice the explicit renaming Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 17

Relational Algebra Operations from Set Theory n n Type Compatibility of operands is required for the binary set operation UNION , (also for INTERSECTION , and SET DIFFERENCE –, see next slides) R 1(A 1, A 2, . . . , An) and R 2(B 1, B 2, . . . , Bn) are type compatible if: n n n they have the same number of attributes, and the domains of corresponding attributes are type compatible (i. e. dom(Ai)=dom(Bi) for i=1, 2, . . . , n). The resulting relation for R 1 R 2 (also for R 1 R 2, or R 1– R 2) has the same attribute names as the first operand relation R 1 (by convention) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 18

Example Uses of UNION n n To retrieve the SSNs of all employees who either work in department 5 (RESULT 1 below) or directly supervise an employee who works in department 5 (RESULT 2 below) We can use the UNION operation as follows: DEP 5_EMPS DNO=5 (EMPLOYEE) RESULT 1 SSN(DEP 5_EMPS) RESULT 2(SSN) SUPERSSN(DEP 5_EMPS) RESULT 1 RESULT 2 n The union operation produces the tuples that are in either RESULT 1 or RESULT 2 or both Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 19

Union (two type compatible relations) Intersection Examples Copyright © 2007 Ramez Elmasri and Shamkant

Troubles with Attribute Names n n n We define the domain Date (mm/dd/yyyy). R 1 (Birth_date, SSN), where Birth_date is of domain Date R 2 (Death_date, SSN), where Death_date is also of domain Date. R 3 = R 1 R 2 will have the attribute list (Birth_date, SSN), which sounds weird. R 3 is best named as R 3(Bor. D_date, SSN) = R 1 R 2 Or more formally, R 3 = (Bor. D_date, SSN) (R 1 R 2) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 21

Exercises n n n Give the SSNs of those managers have dependent(s). Give the SSNs non-manager employees Give the SSNs of employees who are both supervisors and supervisees Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 22

Properties of Set Operations n Notice that both union and intersection are commutative; that is n n n R S = S R, and R S = S R Both union and intersection are associative operations; that is n R (S T) = (R S) T n (R S) T = R (S T) The minus operation is not commutative; that is, in general n R – S ≠ S – R Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 23

Relational Algebra Operations from Set Theory: CARTESIAN PRODUCT n CARTESIAN (or CROSS) PRODUCT Operation n This operation is used to combine tuples from two relations in a combinatorial fashion. Denoted by R(A 1, A 2, . . . , An) x S(B 1, B 2, . . . , Bm) Result is a relation Q with degree n + m attributes: n n Q(A 1, A 2, . . . , An, B 1, B 2, . . . , Bm), in that order. The resulting relation state has one tuple for each combination of tuples—one from R and one from S. Hence, if R has n. R tuples (denoted as |R| = n. R ), and S has n. S tuples, then R x S will have n. R * n. S tuples. The two operands do NOT have to be "type compatible” Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 24

CARTESIAN PRODUCT (cont. ) n Example: To find the dependents’ names of all female employees n FEMALE_EMPS SEX=’F’(EMPLOYEE) n EMPNAMES FNAME, LNAME, SSN (FEMALE_EMPS) n EMP_DEPENDENTS EMPNAMES x DEPENDENT n ACTUAL_DEPS SSN=ESSN(EMP_DEPENDENTS) n RESULT FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPS) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 25

Example of applying CARTESIAN PRODUCT Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

More Exercises n n List the names of those departments that have at least one female employee whose salary $100, 000. List the Names and SSNs of the managers of the above departments. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 27

JOIN Operator ( ) n n The sequence of CARTESIAN PRODECT followed by SELECT is used quite commonly to identify and select related tuples from two relations A special operation, called JOIN combines this sequence into a single operation The general form of a join operation on two relations R(A 1, A 2, . . . , An) and S(B 1, B 2, . . . , Bm) is: R <join condition>S where R and S can be any relations that result from general relational algebra expressions. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 28

Exercise n Use the JOIN operator to find the dependents’ names of all female employees n FEMALE_EMPS SEX=’F’(EMPLOYEE) n EMPNAMES FNAME, LNAME, SSN (FEMALE_EMPS) n EMP_DEPENDENTS EMPNAMES x DEPENDENT n ACTUAL_DEPS SSN=ESSN(EMP_DEPENDENTS) n RESULT FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPS) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 29

Exercises n List the names of department managers. Use Cartesian Product and Select n

Binary Relational Operations: EQUIJOIN n n n EQUIJOIN Operation The most common use of join involves join conditions with equality comparisons only Such a join, where the only comparison operator used is =, is called an EQUIJOIN. n n In the result of an EQUIJOIN we always have one or more pairs of attributes (whose names need not be identical) that have identical values in every tuple. The JOIN seen in the previous example was an EQUIJOIN. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 31

NATURAL JOIN Operations n Another variation of JOIN called NATURAL JOIN — denoted by * — was created to get rid of the second (superfluous) attribute in an EQUIJOIN condition. n n n because one of each pair of attributes with identical values is superfluous The standard definition of natural join requires that the two join attributes, or each pair of corresponding join attributes, have the same name in both relations If this is not the case, a renaming operation is applied first. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 32

Exercise n Use the Natural JOIN operator to find the dependents’ names of all female employees n FEMALE_EMPS SEX=’F’(EMPLOYEE) n EMPNAMES FNAME, LNAME, SSN (FEMALE_EMPS) n RESULT FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPS) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 33

Results of Natural JOINs Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 34

NATURAL JOIN: Example 2 n Because of the same attribute name Dnumber, Natural Join can be applied directly to DEPARTMENT and DEP_LOCATION n DEPT_LOCS DEPARTMENT * DEPT_LOCATIONS Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 35

NATURAL JOIN (contd. ) n n Notice that all pairs of identical attribute names are compared Example: Q R(A, B, C, D) * S(C, D, E) n The implicit join condition includes each pair of attributes with the same name, “AND”ed together: n n R. C=S. C AND R. D=S. D Result keeps only one attribute of each such pair: n Q(A, B, C, D, E) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 36