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:

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

 

Projection

 

Product

 

Union

 

Difference

 

Intersection

 

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