Set Operations on Relations

Set Operations on Relations

The three most common operations on sets are union, intersection, and difference. We assume the reader is familiar with these  operations, which are described as follows on arbitrary sets R and S:

●  R U S, the union of R and S, is the set of elements that are in R or S or both. An element appears only once in the union even if it is present in both R and S.

●  R  S, the intersection of R and S. is the set of elements that are in both R and S.

●  R - S, the difference of R and S, is the set of elements that are in R but not in S. Note that R - S is different from S - R; the latter is the set of elements that are in S but not in R.

When we apply these operations to relations, we need to put some conditions on R and S.

1. R and S must have schemas with identical sets of attributes, and the types (domains) for each attribute must be the same in R and S.

2. Before we compute the set-theoretic union, intersection, or difference of sets of tuples, the columns of R and S must be ordered so that the order of attributes is the same for both relations.

Occasionally we would like to take the union, intersection, or difference of relations that have the same number of attributes, with corresponding domains, but that use different names for their attributes. If so, we may use the renaming operator to be discussed in "Renaming" to change the schema of one or both relations and give them the same set of attributes.

Two relations

Example (a): Assume we have the two relations R and S, instances of the relation MovieStar of "Relational Algebra". Current instances of R and S are shown in Figure (a). Then the union R U S is



Note that the two tuples for Carrie Fisher from the two relations appear only once in the result.

  The intersection R S is



Now, only the Carrie Fisher tuple appears, because only it is in both relations.

   The difference R - S is



That is, the Fisher and Hamill tuples appear in R and thus are candidates for R - S. On the other hand, the Fisher tuple also appears in S and so is not in R - S.

Projection

The projection operator is used to create from a relation R a new relation that has only some of R's columns. The value of expression is a relation that has only the columns for attributes A1, A2,. . . , An of R. The schema for the resulting value is the set of attributes {A1, A2,. . . , An}, which we conventionally show in the order listed.

The relation Movie

Example (b): Take a look at the relation Movie with the relation schema explained in "Relational Algebra". An instance of this relation is shown in Figure (b). We can project this relation onto the first three attributes with the expression



The resulting relation is


As another instance, we can project onto the attribute inColor with the expression

.

The result is the single-column relation


Note that there is only one tuple in the resulting relation, since all three tuples of Figure (b) have the same value in their component for attribute inColor, and in the relational algebra of sets, duplicate tuples are always removed.


Tags