372
Relations, Functions, and Matrices
join Results2 and Results1 over Name giving Results3
Results3
Name
address
city
State petName
petType
Breed
Smith, Mary 1121 Ridge Rd. Rockville IL
Twinkles
Cat
Siamese
project Results3 over PetName giving FinalResults
finalResults
petName
Twinkles
This query could also be performed by first doing the join operation of Example
24 followed by the restrict and project operations, but the join table would be much
larger.
example 25
relational algebra is a theoretical relational database language in which the restrict, project, and join operations can be combined. The relational algebra equivalent of the sequence of operations we did to find the names of cats whose owners
live in Illinois would be the statement
project (join(restrict PetOwner where PetType = “Cat”) and
(restrict Person where State = “IL”) over Name)
over PetName giving Final_Results.
(2)
sQL is an international standard relational database language; the preceding
query would appear as the following SQL statement, where the lines are numbered
only for discussion purposes:
1. seLeCt PetName
2. FrOM PetOwner, Person
3. Where PetOwner. Name = Person. Name
4. aND PetType = “Cat”
5. aND State = “IL”;
(3)
SQL’s seLeCt statement can actually perform relational algebra restricts, projects, and joins, as shown here. Lines 4 and 5 represent the two restrict operations.
Line 2 represents the Cartesian product between the two relations and line 3 identifies the common attribute. Therefore lines 2 and 3 together represent the join.
Line 1 represents the project operation. AND, OR, and NOT connectives are also
available.
Instead of using the relational algebra approach, in which the restrict, project,
and join operations are used to process a query, we can use the relational calculus
approach. In relational calculus, instead of specifying the operations to be done