CARTESIAN or cross Product Operation n n Defines

CARTESIAN (or cross) Product Operation n n Defines a relation Q that is the concatenation of every tuple of relation R with every tuple of relation S. This operation is used to combine tuples from two relations. The result of R(A 1, A 2, . . . , An) x S(B 1, B 2, . . . , Bm) is a relation Q with degree n + m attributes Q(A 1, A 2, . . . , An, B 1, B 2, . . . , Bm), in that order. The two operands do NOT have to be "type compatible” Example: FEMALE_EMPS ¬ s SEX=’F’ (EMPLOYEE) EMPNAMES ¬ p FNAME, LNAME, SSN (FEMALE_EMPS) EMP_DEPENDENTS ¬ EMPNAMES X DEPENDENT Slide 6 - 1

CARTESIAN Product Example Slide 6 - 2

CARTESIAN Product Example (Cont. ) EMP_DEPENDENTS ¬ EMPNAMES X DEPENDENT Slide 6 - 3

CARTESIAN Product Example (Cont. ) ACTUAL_DEPENDENTS ¬ s SSN=ESSN (EMP_DEPENDENTS) RESULT ¬ p FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPENDENTS) Slide 6 - 4

JOIN Operation n The sequence of cartesian product followed by select is used quite commonly to identify and select related tuples from two relations, a special operation, called JOIN. It is denoted by a This operation is very important for any relational database with more than a single relation, because it allows us to process relationships among relations. 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 Slide 6 - 5

JOIN Operation Example: Suppose that we want to retrieve the name of the manager of each department. n n To get the manager’s name, we need to combine each DEPARTMENT tuple with the EMPLOYEE tuple whose SSN value matches the MGRSSN value in the department tuple. We do this by using the join operation: DEPT_MGR ¬ DEPARTMENT MGRSSN=SSN EMPLOYEE Slide 6 - 6

JOIN Operation (Cont. ) n A general join condition is of the form: <condition> AND. . . AND <condition> n n n where each condition is of the form Ai Bj, Ai is an attribute of R, Bj is an attribute of S, Ai and Bj have the same domain, and (theta) is one of the comparison operators {<, , >, , =, }. A JOIN operation with such a general join condition is called a THETA JOIN. Tuples whose join attributes are null do not appear in the result. Slide 6 - 7

JOIN Operation (Cont. ) n n EQUIJOIN Operation n The most common use of join involves join conditions with equality comparisons only. n Such a join, where the only comparison operator used is =, is called an EQUIJOIN. 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. n The JOIN seen in the previous example was EQUIJOIN. NATURAL JOIN Operation n Because one of each pair of attributes with identical values is redundant, a new operation called NATURAL JOIN —denoted by *—was created to get rid of the second (redundant) attribute in an EQUIJOIN condition. n 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. Slide 6 - 8

NATURAL JOIN Operation Example (a) PROJ_DEPT ¬ PROJECT* (r (DNAME, DNUM, MGRSSN, MGRSTARTDSATE ) (DEPARTMENT)) (b) DEPT_LOCS ¬ DEPARTMENT * DEPT_LOCATIONS Slide 6 - 9

Complete Set of Relational Operations n n The set of operations including select s, project p , union È, set difference - , and cartesian product X is called a complete set because any other relational algebra expression can be expressed by a combination of these five operations. For example: R Ç S = (R È S ) – ((R - S) È (S - R)) R <join condition> S = s <join condition> (R X S) Slide 6 - 10

Division Operation Examples of Division A B n Suited for queries that include the phrase “for all” B 1 B 2 B 3 A A B 1 A B 2 A B 3 Slide 6 - 11

Division Example Retrieve the names (first and last) of employees who work on all the projects that ‘John Smith’ works on. n First, retrieve the list of project numbers that ‘John Smith’ works on in the intermediate relation SMITH_PNOS: SMITH_PNOS ¬ p. PNO(WORKS_ONESSN=SSN (s. FNAME=’John’ AND LNAME=’Smith’ (EMPLOYEE) )) Slide 6 - 12

Division Example (Cont. ) n Next, create a relation that includes tuples <PNO, ESSN> in the intermediate relation SSN_PNOS: SSN_PNOS ¬ p. ESSN, PNO(WORKS_ON) Slide 6 - 13

Division Example (Cont. ) n Finally, apply the DIVISION operation to the two relations, which gives the desired employees’ social security numbers: SSNS(SSN) ¬ SSN_PNOS ÷ SMITH_PNOS RESULT ¬ p FNAME, LNAME (SSNS * EMPLOYEE) Slide 6 - 14

Recap of Relational Algebra Operations Slide 6 - 15