Set Operations

Set Operations
The Selection and Projection operations extract information from only one relation.
There are obviously cases where we would like to combine information from several
relations. In the remainder of this section, we examine the binary operations of the relational
algebra, starting with the set operations of Union, Set difference, Intersection, and
Cartesian product.
Union
R ¾ S The union of two relations R and S defines a relation that contains all the
tuples of R, or S, or both R and S, duplicate tuples being eliminated. R and S
must be union-compatible.
If R and S have I and J tuples, respectively, their union is obtained by concatenating them
into one relation with a maximum of (I + J) tuples. Union is possible only if the schemas
of the two relations match, that is, if they have the same number of attributes with each
pair of corresponding attributes having the same domain. In other words, the relations
must be union-compatible. Note that attributes names are not used in defining unioncompatibility.
In some cases, the Projection operation may be used to make two relations
union-compatible.
Example 4.3 Union operation
List all cities where there is either a branch office or a property for rent.
Πcity(Branch) ∪ Πcity(PropertyForRent)
To produce union-compatible relations, we first use the Projection operation to project the
Branch and PropertyForRent relations over the attribute city, eliminating duplicates where
necessary. We then use the Union operation to combine these new relations to produce the
result shown in Figure 4.4.
Set difference
R - S The Set difference operation defines a relation consisting of the tuples that are

in relation R, but not in S. R and S must be union-compatible.
Example 4.4 Set difference operation
List all cities where there is a branch office but no properties for rent.
Πcity(Branch) − Πcity(PropertyForRent)
As in the previous example, we produce union-compatible relations by projecting the
Branch and PropertyForRent relations over the attribute city. We then use the Set difference
operation to combine these new relations to produce the result shown in Figure 4.5.
Intersection
R ∩ S The Intersection operation defines a relation consisting of the set of all tuples

that are in both R and S. R and S must be union-compatible.
Example 4.5 Intersection operation
List all cities where there is both a branch office and at least one property for rent.
Πcity(Branch) ∩ Πcity(PropertyForRent)
As in the previous example, we produce union-compatible relations by projecting the
Branch and PropertyForRent relations over the attribute city. We then use the Intersection
operation to combine these new relations to produce the result shown in Figure 4.6.
Note that we can express the Intersection operation in terms of the Set difference operation:
R ∩ S = R − (R − S)
Cartesian product
R × S The Cartesian product operation defines a relation that is the concatenation of

every tuple of relation R with every tuple of relation S.
The Cartesian product operation multiplies two relations to define another relation consisting
of all possible pairs of tuples from the two relations. Therefore, if one relation has
I tuples and N attributes and the other has J tuples and M attributes, the Cartesian product
relation will contain (I * J) tuples with (N + M) attributes. It is possible that the two relations
may have attributes with the same name. In this case, the attribute names are prefixed

with the relation name to maintain the uniqueness of attribute names within a relation.
Example 4.6 Cartesian product operation
List the names and comments of all clients who have viewed a property for rent.
The names of clients are held in the Client relation and the details of viewings are held in
the Viewing relation. To obtain the list of clients and the comments on properties they have
viewed, we need to combine these two relations:
(ƒ®clientNo, fName, lName(Client)) ~ (ƒ®clientNo, propertyNo, comment(Viewing))
This result of this operation is shown in Figure 4.7. In its present form, this relation contains
more information than we require. For example, the first tuple of this relation contains
different clientNo values. To obtain the required list, we need to carry out a Selection
operation on this relation to extract those tuples where Client.clientNo = Viewing.clientNo. The
complete operation is thus:
ƒÐClient.clientNo = Viewing.clientNo((ƒ®clientNo, fName, lName(Client)) ~ (ƒ®clientNo, propertyNo, comment(Viewing)))

The result of this operation is shown in Figure 4.8.
Decomposing complex operations
The relational algebra operations can be of arbitrary complexity. We can decompose such
operations into a series of smaller relational algebra operations and give a name to the
results of intermediate expressions. We use the assignment operation, denoted by ←, to
name the results of a relational algebra operation. This works in a similar manner to the
assignment operation in a programming language: in this case, the right-hand side of the
operation is assigned to the left-hand side. For instance, in the previous example we could
rewrite the operation as follows:
TempViewing(clientNo, propertyNo, comment) ←ΠclientNo, propertyNo, comment(Viewing)
TempClient(clientNo, fName, lName) ←ΠclientNo, fName, lName(Client)
Comment(clientNo, fName, lName, vclientNo, propertyNo, comment) ←
TempClient × TempViewing
Result ← sclientNo = vclientNo(Comment)
Alternatively, we can use the Rename operation ρ (rho), which gives a name to the result
of a relational algebra operation. Rename allows an optional name for each of the attributes

of the new relation to be specified.
rS(E) or The Rename operation provides a new name S for the expression

rS(a1, a2, . . . , an)(E) E, and optionally names the attributes as a1, a2, . . . , an.