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
Using the INVENTORY relation:
results in the following relation:
ProdCode ProdDesc Price ------------------------------- 213543 Battery 1.50 202544 Bulb 1.30
Projection
Using the INVENTORY relation:
results in the following relation:
ProdDesc Price ------------------------------- Battery 1.50 Drill 35.00 Bulb 1.30
Product
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:
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
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:
results in the following relation:
ProdCode -------- 213543 113452 202544 202545 110400
Difference
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:
results in the following relation:
ProdCode -------- 113452 202545
Intersection
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:
results in the following relation:
ProdCode -------- 213543 202544
Notes:
e.g.
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