Slide 6- 1

CARTESIAN (or cross) Product Operation

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(A1, A2, . . ., An) x S(B1, B2, . . ., Bm) is a relation Q with degree n + m attributes Q(A1, A2, . . ., An, B1, B2, . . ., Bm), in that order.

The two operands do NOT have to be "type compatible” Example:

FEMALE_EMPS SEX=’F’ (EMPLOYEE)

EMPNAMES FNAME, LNAME, SSN (FEMALE_EMPS) EMP_DEPENDENTS EMPNAMES X DEPENDENT

Slide 6- 2

CARTESIAN Product Example

Slide 6- 3

CARTESIAN Product Example (Cont.)

EMP_DEPENDENTS EMPNAMES X DEPENDENT

Slide 6- 4

CARTESIAN Product Example (Cont.)

ACTUAL_DEPENDENTS SSN=ESSN (EMP_DEPENDENTS)

RESULT FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPENDENTS)

Slide 6- 5

JOIN Operation

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(A1, A2, . . ., An) and S(B1, B2, . . ., Bm) is:

R <join condition>

S

Slide 6- 6

JOIN Operation Example

Example: Suppose that we want to retrieve the name of the manager of each department. 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- 7

JOIN Operation (Cont.)

A general join condition is of the form: <condition> AND <condition> AND . . . AND <condition> 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- 8

JOIN Operation (Cont.)

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. 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 EQUIJOIN. NATURAL JOIN Operation

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.

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- 9

NATURAL JOIN Operation Example

(a) PROJ_DEPT PROJECT* (DNAME, DNUM,MGRSSN, MGRSTARTDSATE ) (DEPARTMENT))

(b) DEPT_LOCS DEPARTMENT * DEPT_LOCATIONS

Slide 6- 10

Complete Set of Relational Operations

The set of operations including select , project , 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 = <join condition> (R X S)

Slide 6- 11

Division Operation Examples of Division AB

sno pno s1 p1 s1 p2 s1 p3 s1 p4 s2 p1 s2 p2 s3 p2 s4 p2 s4 p4

pno p2

pnop2p4

pnop1p2p4

snos1s2s3s4

snos1s4

snos1

A

B1B2

B3

AB1 AB2 AB3

Suited for queries that include the phrase “for all”

Slide 6- 12

Division Example

Retrieve the names (first and last) of employees who work on all the projects that ‘John Smith’ works on.

First, retrieve the list of project numbers that ‘John Smith’ works on in the intermediate relation SMITH_PNOS:

SMITH_PNOS PNO(WORKS_ONESSN=SSN (FNAME=’John’ AND LNAME=’Smith’ (EMPLOYEE) ))

Slide 6- 13

Division Example (Cont.)

Next, create a relation that includes tuples <PNO, ESSN> in the intermediate relation SSN_PNOS:

SSN_PNOS ESSN,PNO(WORKS_ON)

Slide 6- 14

Division Example (Cont.)

Finally, apply the DIVISION operation to the two relations, which gives the desired employees’ social security numbers:

SSNS(SSN) SSN_PNOS ÷ SMITH_PNOS

RESULT FNAME, LNAME (SSNS * EMPLOYEE)

Slide 6- 15

Recap of Relational Algebra Operations