Relational Algebra

The relational algebra defines an algebra for the relational model. That is, it addresses the manipulative aspect of the model (the allowable operations on relations). The following operations are the basic operations defined in the algebra:

• Selection
• Projection
• Product
• Union
• Difference
• Intersection
• Join

Note: Codd also proposed a Divide operation but it will not be discussed in this class.

An important point to recognize about these operations is that they all result in relations. That is, the notion of closure is inherent to the relational model. The important benefit of closure is that operations may be "nested" in that the output of one operation may be the input to another.

Note: Codd noted that relational products need not implement the relational algebra but he stressed that the data manipulational language of any product deemed relational must support the operations defined in the algebra. A data manipulation language is said to be relationally complete if it is at least as powerful as the algebra.

Following is the syntax of these relational algebra operations using the syntactic notation summarized on pg. 209. The syntax is illustrated with examples using tables at the bottom of the page. Note that the selection and join operations allow for a condition. This is just a boolean expression (see below for a review).

Selection

• Produces a relation that is a subset of the tuples of the subject relation.
• Yields a horizontal subset of a relation
• General form:
  relation_name WHERE condition

  Using the INVENTORY relation:

  INVENTORY WHERE Price < 2.00

  results in the following relation:

       ProdCode  ProdDesc        Price
       -------------------------------
         213543  Battery         1.50
         202544  Bulb            1.30
  

Projection

• Produces a relation where the attributes constitute a subset of the attributes of the subject relation.
• Yields a vertical subset of a relation
• General form:
  relation_name [attribute list]

  Using the INVENTORY relation:

  INVENTORY [ProdDesc, Price]

  results in the following relation:

       ProdDesc        Price
       -------------------------------
       Battery         1.50
       Drill          35.00
       Bulb            1.30
  

Product

• Produces a relation that is the Cartesian product of the subject relations.
• General form:
  relation_name1 X relation_name2

  Note: This general form contains two relations, but it may be easily extended to include an arbitrary number of relations.

  Using the ITEM and LOCATION relations:

  ITEM X LOCATION

  results in the following relation:

       ItemCode  Price  Store  Aisle  Shelf
       ------------------------------------
         A13-43   5.50    23     W      5
         A13-43   5.50    24     K      9
         A13-43   5.50    25     Z      6
         B02-44  21.30    23     W      5
         B02-44  21.30    24     K      9
         B02-44  21.30    25     Z      6
  

Union

• Produces a relation that contains the union (in a set theoretic sense) of the tuples from the subject relations.
• Subject relations must be union compatible (see below).
• General form:
  relation_name1 + relation_name2

  Note: This general form contains two relations, but it may be easily extended to include an arbitrary number of relations.

  Using the INVENTORY-ST and INVENTORY-WS relations:

  INVENTORY-ST + INVENTORY-WS

  results in the following relation:

       ProdCode  
       --------
         213543
         113452
         202544
         202545
         110400
  

Difference

• Produces a relation that contains the difference (in a set theoretic sense) of the tuples from the subject relations.
• Subject relations must be union compatible (see below).
• General form:
  relation_name1 - relation_name2

  Note: This general form contains two relations, but it may be easily extended to include an arbitrary number of relations.

  Using the INVENTORY-ST and INVENTORY-WS relations:

  INVENTORY-ST - INVENTORY-WS

  results in the following relation:

       ProdCode  
       --------
         113452
         202545
  

Intersection

• Produces a relation that contains the intersection (in a set theoretic sense) of the tuples from the subject relations.
• Subject relations must be union compatible (see below).
• General form:
  relation_name1 INTERSECT relation_name2

  Note: This general form contains two relations, but it may be easily extended to include an arbitrary number of relations.

  Using the INVENTORY-ST and INVENTORY-WS relations:

  INVENTORY-ST INTERSECT INVENTORY-WS

  results in the following relation:

       ProdCode  
       --------
         213543
         202544
  

Notes:

1. A boolean expression is a logical expression that evaluates to TRUE or FALSE. The usual comparison operators (i.e. <, >, =, etc.) and logical operators (i.e. AND, OR, NOT) may be used with parentheses added where necessary.

   e.g.

   1. salary < 50000
   2. (salary > 50000) AND (age < 30)

2. The term union compatible means that the subject relations must have the same number of attributes and, the attributes in corresponding column positions, must have the same domain. Note that if our subject tables are not union compatible we may (in some cases) be able to make them so by first doing a projection operation.

Readings: Chapter 8, pg 221-225

Relation Name: INVENTORY

     ProdCode  ProdDesc        Price
     -------------------------------
       213543  Battery         1.50
       113452  Drill          35.00
       202544  Bulb            1.30


Relation Name: ITEM

     ItemCode  Price
     ---------------
       A13-43   5.50
       B02-44  21.30


Relation Name: LOCATION

     Store  Aisle  Shelf
     -------------------
       23     W      5
       24     K      9
       25     Z      6


Relation Name: INVENTORY-ST

     ProdCode  
     --------
       213543
       113452
       202544
       202545


Relation Name: INVENTORY-WS

     ProdCode  
     --------
       213543
       110400
       202544