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