lecture 9

Relational set operators
The data in relational tables are of limited value unless can be manipulated to generate useful information. This section describes the basic data manipulation capabilities of the relational model. Relational algebra defines the theoretical way of manipulating table contents using the following operators:
1. UNION
Combines all rows from tow tables, excluding duplicate rows. The tables must have the same attribute characteristics (the columns and domains must be identical) to be used in UNION.


2. INTERSECT
Yields only the rows that appear in both tables. Was True in the case of UNION, the tables must be union-compatible to yield valid results.

3. DIFFERENCE
Yields all rows in one table that are not found in the other Table; that is subtracts one table from the other, the tables must be Union-compatible to yield valid results.



4. PRODUCT
Yields all possible pairs of rows from two tables also known as the Cartesian product.



5. SELECT
Also known RESTRICT, yields values for all rows found in a table that satisfy a given condition.


6. PROJECT
Yields all values for selected attributes. In other words, PROJECT yields a vertical subset of a table.



7. JOIN
Allows information to be combined from two or more tables, there are several types of JOIN:
o Natural join links tables by selecting only the rows with common values in their common attributes .It is the result of a three- stage process:
a) PRODUCT

b) SELECT on output of step a

c) A PROJECT on the result of step b yield a single copy of each attribute, thereby eliminating duplicate columns.


-Equijoin
Links tables on the basis of an equality condition that compares specified columns of each table. The outcome of the equijoin does not eliminate duplicate columns, and the condition or criterion use to join the tables must be explicitly defined.

- Outer join
a- left outer join
yields all of the rows in the CUSTOMER table, including those that do not have a matching value in the AGENT table.

b- Right outer join
Yields all of the rows in the AGENT table, including those that do not have a matching value in the CUSTOMER table.

8. DIVID

Table 1 is divided by table 2 to produce table 3 . table 1 and 2 both contain CODE but do not share LOC. To be included in the table3 ,a value in the unshared column(LOC)must be associated (in the dividing table 2) with every value in the table 1.
The only value associated with both A and B is 5.