4. The Rename Operation

The rename operation solves the problems that occurs with naming when performing the cartesian productof a relation with itself. Suppose we want to find the names of all the customers who live on the same streetand in the same city as Smith. We can get the street and city of Smith by writing

â…¡street.ccity(Σcname="Smith"(customer))

To find other customers with the same information, we need to reference the customer relation again:

(a)

(b)

Figure 3.5: The union and set-difference operations.

ΣP (customer × (â…¡street,ccity (Σcname="Smith"(customer))))

where P is a selection predicate requiring street and ccity values to be equal.

Problem: how do we distinguish between the two street values appearing in the Cartesian product, as both come from a customer relation?

Solution: use the rename operator, denoted by the Greek letter rho (Σ). We write

Σx(r)

to get the relation r under the name of x.

If we use this to rename one of the two customer relations we are using, the ambiguities will disappear.

â…¡customer.cname(Σcust2.street=customer.street∧cust2.ccity=customer.ccity

(customer ×(â…¡street,ccity(Σcname="Smith"(Σcust2(customer))))))

5. The Union Operation

The union operation is denoted [ as in set theory. It returns the union (set union) of two compatible
relations. For a union operation r∪s to be legal, we require that

• r and s must have the same number of attributes.
• The domains of the corresponding attributes must be the same.

To nd all customers of the SFU branch, we must nd everyone who has a loan or an account or both at the
branch. We need both borrow and deposit relations for this:

â…¡cname(Σbname="SFU"(borrow)) [Σcname(Σbname="SFU"(deposit))

As in all set operations, duplicates are eliminated, giving the relation of Figure 3.5(a).

6. The Set Difierence Operation

Set difference is denoted by the minus sign (ô€€€). It nds tuples that are in one relation, but not in another.Thus r ô€€€ s results in a relation containing tuples that are in r but not in s. To find customers of the SFU branch who have an account there but no loan, we write

â…¡cname(Σbname="SFU"(deposit)) â…¡cname(Σbname="SFU"(borrow))

The result is shown in Figure 3.5(b).
We can do more with this operation. Suppose we want to nd the largest account balance in the bank.Strategy:

• Find a relation r containing the balances not the largest.
• Compute the set difference of r and the deposit relation.

Figure 3.6: Find the largest account balance in the bank.

To s and r, we write

â…¡deposit.balance (Σdeposit.balance﹤d.balance (deposit×Σd(deposit)))

This resulting relation contains all balances except the largest one. (See Figure 3.6(a)). Now we can finish our query by taking the set difference:

â…¡balance(deposit)â…¡deposit.balance (Σdeposit.balance<d.balance (deposit×Σd(deposit)))

Figure 3.6(b) shows the result.