Relational Algebra contd.

As mentioned before, Codd defined an algebra to address the manipulative aspect of the relational model. He proposed eight basic operations: Selection, Projection, Product, Union, Difference, Intersection, Divide, ( see the Algebra I lecture notes) and Join. This section discusses the Join operation.

Join

• Combines tuples from two or more relations.
• Allows relations to be linked by a common attribute.
• General form:
  relation_name1 JOIN (condition) relation_name2

  Your text presents an example on page 225. The STUDENT and ENROLLMENT relations are joined by SID and StudentNumber. Since these attributes have the same domain then they may be considered common attributes.

  STUDENT JOIN (SID = StudentNumber) ENROLLMENT

The JOIN operation essentially involves a three step process. The steps are:

1. A Cartesian product of the relations. See Fig 8.16 to see the result of this step for the example above.
2. A Selection of the relation resulting from the Cartesian product with the specified condition applied.
3. A Projection of the relation resulting from the Selection to eliminate duplicate attributes. See Fig 8.19b to see the result of this step for the example above.
   Note: This is an optional step. If it is done then the join is referred to as a Natural join, otherwise it is referred to as an Equi-join.

Although we have used the equality comparison operator for our example, any comparison operator may be used for a join. We will only consider joins that use the equality operator in this class.

Outer Join

For completeness, we will also discuss an operation known as the Outer Join. Codd added this operation to the relational model at about the same time the notion of null values was added. It may be considered as an extension of the Join operation discussed above.

The Outer Join differs from the Join in that tuples in one relation having no counterpart in the other appear in the resulting relation with nulls in the other attribute positions. That is, such tuples are retained instead of being discarded. Consider the example presented above for the Join operation. The tuple from the STUDENT relation for SID 271 was discarded since SID 271 has no counterpart in the ENROLLMENT relation. For the Outer Join this tuple would be retained and attributes StudentNumber, ClassName, and PositionNumber would be set to null.

Three varieties of Outer Join have been proposed, Left-, Right-, and Full-. The prefix Left- merely indicates that only tuples from the relation named on the left of the join expression (with no counterpart) will be retained while the prefix Right- indicates that tuples from the relation named on the right will be retained. The prefix Full- indicates that tuples from the right and left will be retained. On page 227 of the text, Left- and Right- are briefly discussed and an example of Left-Outer Join is presented in Fig 8-19c.
Note: Notice that this Figure is incomplete, in that the tuple for SID 158 is missing. ( corrected in the 8^th edition)

Notes:

One of the important benefits of basing the relational model on set theoretic ideas is that we may make use of several set theory laws when evaluating relational algebra expressions. In some cases these laws have profound implications for how products based on the relational model may be implemented (we will discuss relational DBMS and optimization at some later point).

Recall the laws of commutativity and associativity. The Union, Product, Intersection, and Join operators are commutative but Difference is not. That is, given relations A and B, we may say:

This means that for these operations the same relation will result whether A or B is placed first.

Also, the Union, Product, Intersection, and Join operators are associative but Difference is not. That is, given relations A, B and C, we may say:

This, in addition to the commutative law, means that the ordering of the reations in the expression has no bearing on the result (i.e. the same relation will result).

Readings: Chapter 8, pg 225-227