Database Concepts - Data Warehouse Systems: Design and Implementation - page 31

the relations must be renamed, since a relation cannot have two attributes
with the same name:

Temp1 ← π ProductID,ProductName,SupplierID ( Products )

Temp2 ← ρ SupplierID → SupID ( π SupplierID,Country ( Suppliers ))

Temp3 ← Temp1 × Temp2.

The Cartesian product combines each product with all the suppliers, as given
in the table below.

ProductID ProductName SupplierID SupID Country

1 Chai 1 1 UK

2 Chang 1 1 UK

... ... ... ... ...

17 Alice Mutton 7 2 USA

18 Carnarvon Tigers 7 2 USA

We are only interested in the rows that relate a product to its supplier (e.g.,
the first two rows). The other ones are not useful (e.g., the last row combines
a product supplied by supplier 7 with the country of supplier 2). We then
filter the meaningless tuples, select the ones corresponding to suppliers from
Brazil, and project the column we want, that is, ProductName :

Temp4 ← σ SupplierID=SupID ( Temp3 )

Result ← π ProductName ( σ Country= ' Brazil ' ( Temp4 ))

The join operation, denoted R 1 φ R 2 ,where φ is a condition over the
attributes in R 1 and R 2 , takes two relations and returns a new one, whose
schema consists in all attributes of R 1 and R 2 (renamed if necessary) and
whose instance is obtained concatenating each pair of tuples from R 1 and
R 2 that satisfy condition φ . The operation is basically a combination of a
Cartesian product and a selection.
Using the join operation, the query “Name of the products supplied by
suppliers from Brazil” will read:

Temp1 ← ρ SupplierID → SupID ( Suppliers )

Result ← π ProductName ( σ Country= ' Brazil ' ( Product SupplierID=SupID Temp1 )) .

Note that the join combines the Cartesian product in Temp3 and the selection
in Temp4 in a single operation, making the expression much more concise.
There are a number of variants of the join operation. An equijoin is a join
R 1 φ R 2 such that condition φ states the equality between all the attributes
with the same name in R 1 and R 2 . If we project the result of an equijoin over
all the columns in R 1 ∪ R 2 (i.e., all the attributes in R 1 and R 2 , without
duplicates), we have the natural join , denoted R 1 ∗ R 2 .