1. Additional operations are de ned in terms of the fundamental operations. They do not add power to the algebra, but are useful to simplify common queries.

2. The Set Intersection Operation

Set intersection is denoted by ∩, and returns a relation that contains tuples that are in both of its argument relations. It does not add any power as

r ∩ s = r - (r - s)

To nd all customers having both a loan and an account at the SFU branch, we write

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

3. The Natural Join Operation

Often we want to simplify queries on a cartesian product. For example, to nd all customers having a loan

Figure 3.7: Joining borrow and customer relations.

at the bank and the cities in which they live, we need borrow and customer relations:

â…¡borrow.cname,ccity (Σborrow.cname=customer.cname (borrow ×customer))

Our selection predicate obtains only those tuples pertaining to only one cname.

This type of operation is very common, so we have the natural join, denoted by a 1 sign. Natural join combines a cartesian product and a selection into one operation. It performs a selection forcing equality on those attributes that appear in both relation schemes. Duplicates are removed as in all relation operations. To illustrate, we can rewrite the previous query as

â…¡cname.ccity(borrow ∞ customer)

The resulting relation is shown in Figure 3.7.
We can now make a more formal de nition of natural join.

• Consider R and S to be sets of attributes.
• We denote attributes appearing in both relations by R ∩ S.
• We denote attributes in either or both relations by R ∩ S.
• Consider two relations r(R) and s(S).
• The natural join of r and s, denoted by r ∞ s is a relation on scheme R ∪ S.
• It is a projection onto R ∪S of a selection on r × s where the predicate requires r.A = s.A for each

attribute A in R ∩ S.

Formally,

r ∞ s = â…¡R∪S(Σr.A1=s.A1 ∧ r.A2=s.A2∧...∧r.An=s.An(r × s))

where R∩ S = {A1,A2,....,An}.

To find the assets and names of all branches which have depositors living in Stamford, we need customer,deposit and branch relations:

â…¡bname,assets (Σccity="Stamford" (customer ∞ deposit ∞ branch))

Note that 1 is associative.
To find all customers who have both an account and a loan at the SFU branch:

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

This is equivalent to the set intersection version we wrote earlier. We see now that there can be several ways to write a query in the relational algebra.

If two relations r(R) and s(S) have no attributes in common, then R ∩ S =Ф, and r ∞ s = r × s.