Database Reference
In-Depth Information
Binary Operations
These operations are based on the set theory classic operations. We first
introduce the basic binary operations, namely, union, difference, and Carte-
sian product, and then discuss the most used binary operation, the join, and
its variants inner and outer join.
The
union
operation, denoted
R
1
∪ R
2
, takes two relations with the same
schema and returns the tuples that are in
R
1
,in
R
2
, or in both, removing
duplicates. If the schemas are compatible, but the attribute names differ, the
attributes must be renamed before applying the operation.
The union can be used to express queries like “Identifier of employees from
the UK, or who are reported by an employee from the UK,” which reads:
UKEmps
← σ
Country=
'
UK
'
(
Employees
)
Result1
← π
EmployeeID
(
UKEmp
)
Result2
← ρ
ReportsTo
→
EmployeeID
(
π
ReportsTo
(
UKEmps
))
Result
←
Result1
∪
Result2.
Relation
UKEmps
contains the employees from the UK.
Result1
contains
the projection of the former over
EmployeeID
,and
Result2
contains the
EmployeeID
of the employees reported by an employee from the UK. The
union of
Result1
and
Result2
yields the desired result.
The
difference
operation, denoted
R
1
\ R
2
, takes two relations with the
same schema and returns the tuples that are in
R
1
but not in
R
2
.Asinthe
case of the union, if the schemas are compatible, but the attribute names
differ, the attributes must be renamed before applying the operation.
We use the difference to express queries like “Identifier of employees who
are not reported by an employee from the UK,” which is written as follows:
Result
← π
EmployeeID
(
Employees
)
\
Result2.
The first term of the difference contains the identifiers of all employees. From
this set, we subtract the set composed of the identifiers of all employees
reported by an employee from the UK, already computed in
Result2
.
The
Cartesian product
, denoted
R
1
×R
2
, takes two relations and returns
a new one, whose schema is composed of all the attributes in
R
1
and
R
2
(renamed if necessary) and whose instance is obtained concatenating each
pair of tuples from
R
1
and
R
2
. Thus, the number of tuples in the result is
the product of the cardinalities of both relations.
Although by itself the Cartesian product is usually meaningless, it is very
useful when combined with a selection. For example, suppose we want to
retrieve the name of the products supplied by suppliers from Brazil. To answer
this query, we use the Cartesian product to combine data from the tables
Products
and
Suppliers
. For the sake of clarity, we only keep the attributes
we need:
ProductID
,
ProductName
,and
SupplierID
from table
Products
,and
SupplierID
and
Country
from table
Suppliers
. Attribute
SupplierID
in one of
Search WWH ::
Custom Search