Transcript Chapter 6

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- 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  SSN=ESSN (EMP_DEPENDENTS)
RESULT  FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPENDENTS)
Slide 6- 4
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- 5
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- 6
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- 7
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- 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


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- 10
Division Operation
Examples of Division AB

Suited for queries that include the phrase “for all”
sno
s1
s1
s1
s1
s2
s2
s3
s4
s4
pno
p1
p2
p3
p4
p1
p2
p2
p2
p4
A
pno
p2
B1
sno
s1
s2
s3
s4
A B1
pno
p2
p4
B2
sno
s1
s4
AB2
pno
p1
p2
p4
B3
sno
s1
AB3
Slide 6- 11
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- 12
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- 13
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- 14
Recap of Relational Algebra Operations
Slide 6- 15