Database Reference
In-Depth Information
Difference
The difference operation is performed by the SUBTRACT command in relational algebra.
64
EXAMPLE 26
List the number and name of those customers that have orders but that are not represented by sales rep 65.
This process is virtually identical to the one you encountered in the union and intersection examples, but
in this case, you subtract one of the tables from the other instead of taking their union or intersection. This
process is accomplished in relational algebra using the following command:
JOIN Orders, Customer
WHERE Orders.CustomerNum=Customer.CustomerNum
GIVING Temp1
PROJECT Temp1 OVER CustomerNum, CustomerName
GIVING Temp2
SELECT Customer WHERE RepNum=
'
65
'
GIVING Temp3
PROJECT Temp3 OVER CustomerNum, CustomerName
GIVING Temp4
SUBTRACT Temp4 FROM Temp2 GIVING Answer
The next two sections present the final two important but infrequently used commands in relational
algebra: product and division.
Product
The product of two tables (mathematically called the Cartesian product) is the table obtained by concatenat-
ing every row in the first table with every row in the second table. Thus, the product of the Orders table and
the Part table, which are both shown in Figure 2-43, appears in the figure as the table labeled
“
Product of
Orders and Part.
”
Orders
Part
OrderNum
OrderDate
PartNum
Description
21608
10/20/2013
DR93
Gas Range
21610
10/20/2013
DW11
Washer
21613
10/21/2013
Product of Orders and Part
OrderNum
OrderDate
PartNum
Description
21608
10/20/2013
DR93
Gas Range
21610
10/20/2013
DR93
Gas Range
21613
10/21/2013
DR93
Gas Range
21608
10/20/2013
DW11
Washer
21610
10/20/2013
DW11
Washer
21613
10/21/2013
DW11
Washer
FIGURE 2-43
Product of two tables
Every row in the Orders table is matched with every row in the Part table. If the Orders table has m rows
and the Part table has n rows, there would be m times n rows in the product. If, as is typically the case, the
tables have many rows, the number of rows in the product can be so great that it is not practical to form the
product. Usually, you want only those combinations that satisfy certain restrictions; thus, you would almost
always use the join operation instead of the product operation.
