Relational Operations:

Operations from mathematical set theory:

1.    UNION

2.    INTERSECTION

3.    DIFFERENCE

4.    PRODUCT

Operations from that apply specifically to the relational model

1.    SELECT

2.    PROJECT

3.    JOIN

4.    DIVIDE
 

Further Descriptions of These Operations.

UNION

Data from two relations are combined.  The tables are combined vertically, that is the data from the second table is appended
to the data from the first table.  Any row that exists in both relations appears only once in the Union.  The Union operation
requires that each table, or relation, is union compatible, that is they both must have exactly the same number and domain of columns.  (Union compatibility is also required for the Intersection and Difference operations.)

INTERSECTION

Data that are common to two relations are placed into another relation.  The result of an intersection operation is the relation consisting of all rows that are in both of the relations.

DIFFERENCE

The operation identifies rows that are in the first relation and not in the second relation.  The Difference operation is also called the Subtraction operation.

PRODUCT

The Product operation can be thought of as a building block that is used to effect a Join operation.  (It is the same operation in mathematics which creates the Cartesian product of two sets.)  The Product operation is created by concatenating, or adjoining, the attributes of two relations and attaching to each row of Relation A, each of the rows of Relation B.  Needless to say, the result of a Product operation can be quite hugh!

SELECT

This operation creates another relation from an existing relation by selecting only rows from the original relation that conform to a specified condition.

PROJECT

Project eliminates unwanted columns.  That is, the resultant relation contains only the columns asked for by the user, and is often referred to as a projection of the original relation.

JOIN

The Join operation connects data across relations.  Several versions, or types, of joins are extant.  These include the natural join, the theta join, and the outer join.  The natural join is the most important.

THE TYPES OF JOINS DESCRIBED:

NATURAL JOIN

The process is essentially a table lookup.  For each row in Relation A having attribute Aa, you look up the rows in Relation B where their attributes Aa have the same value as attribute Aa in A.  The result would have duplicate, or redundant,  columns for Aa.  One of these columns for Aa is then removed.  A more specific way to view the natural join is:  Take the product of A and B.  Eliminate all rows from the product except those on which the values of the column Aa in A are equal, respectively, to the values of the column Aa in B.  Lastly, project out one copy of the Aa column.  This description is for the situation where the two relations are joined having only one column in common.  It is possible to have more than one common column in both relations.

THETA JOIN

The Theta join involves the use of comparison operators including:  =, not =, <, >, <=, >=.

OUTER JOIN

It is possible that, in a natural join, not all rows of one table appear in the resulting relation.  In a sense, some information is lost, even though that information is still in the original table.  The outer join allows this information to appear in the join's result.  For example, consider the one-to-many relationship between a customer entity and a sales representative entity.  It is assumed here that each sales rep can have many customers, but any given customer is assigned to one, and only one, sales rep.  A natural join on these two tables on the salesrep_ID column would not list any sales rep who is not currently assigned to any customer.  The outer join could include those particular sales representatives.  So, the outer join expands the natural join by making sure that every record from both relations is listed in the join relation at least once.

DIVIDE

Divide results in a relation by selecting the rows in one relation, A, that match every row in another relation, B.  The Divide operation is the reverse of the product operation.  In practice, the divide operation solves queries that include "every" or "all" as part of the condition.  An example would be:  List salespeople who have sold every product.  Also, List customers who have had shipments delivered by every driver.
 
End of File. DWL.