• list the operator symbol,
• give a three or four word intuitive, English description,
• whether the operation is unary or binary (takes one or two relations as arguments),
• give an example of how the operation is used,
• state the relationship (if any) of the degree of the result to the degree of the operands,
• state the relationship (if any) of the cardinality of the result to the cardinality of the operands,
• and state any restrictions on the use of the operator.

  student(id, name, address)
  JCUsubjects(code, lecturer)
  TAFEsubjects(code, lecturer)
  enrolledIn(id, code)

A Relational Algebra cheatsheet - Part 1

English	operation	arity	description
selection	θ(R) = T	unary	select rows that satisfy θ
union	R S = T	binary	create set of tuples from relations R and S

A Relational Algebra cheatsheet - Part 2

English	example	restrictions
selection	code=CP1500 OR code=cp3010(JCUsubjects)	none
union	JCUsubjects TAFEsubjects	R and S must be union-compatible

Solution: A Relational Algebra cheatsheet - Part 1 English operation arity description selection θ(R) = T unary select rows that satisfy θ projection A1, A2, ..., An(R) unary project named columns from R Cartesian product R X S binary glue together all possible combinations of tuples from R and S union R S = T binary everything in R plus everything in S intersection R S = T binary only tuples in both R and S difference R - S = T binary everying in R take away everything in S theta-join R θ S = T binary do Cartesian product then selection using θ equi-join R θ S = T binary do Cartesian product then selection using θ natural join R S = T binary equi-join on common attributes, but project only one copy of attribute values! A Relational Algebra cheatsheet - Part 2 English example restrictions selection code=CP1500 OR code=cp3010(JCUsubjects) none projection code(JCUsubjects) none Cartesian product JCUsubjects X TAFEsubjects none union JCUsubjects TAFEsubjects R and S must be union-compatible intersection JCUsubjects TAFEsubjects R and S must be union-compatible difference JCUsubjects - TAFEsubjects R and S must be union-compatible theta-join student id <> id enrolledIn none equi-join student id = id enrolledIn join condition can only involve AND and equality comparisons natural join student enrolledIn is a kind of equi-join A Relational Algebra cheatsheet - Part 3 English degree cardinality selection degree(R) = degree(T) card(R) >= card(T) projection degree(R) >= degree(T) card(R) >= card(T) Cartesian product degree(R) + degree(S) = degree(T) card(R) * card(S) = card(T) union degree(R) = degree(S) = degree(T) card(R) + card(S) >= card(T) >= max(card(R),card(S)) intersection degree(R) = degree(S) = degree(T) min(card(R),card(S)) >= card(T) difference degree(R) = degree(S) = degree(T) card(R) >= card(T) theta-join degree(R) + degree(S) = degree(T) card(R) * card(S) >= card(T) equi-join degree(R) + degree(S) = degree(T) card(R) * card(S) >= card(T) natural join degree(R) + degree(S) >= degree(T) card(R) * card(S) >= card(T)

  student              enrolledIn                 subject
     id  |  name          id    |   code           code  | lecturer
 ------------------     --------------------    ---------------------
    1234 |  joe          1234   |   cp1500        cp1500 |  curtis   
    4000 | hector        1234   |   cp1200        cp2001 |  dave      
    2000 |  ling         1234   |   cp2001        cp3010 |  curtis          
                         4000   |   cp3010        cp2001 |  olivier
                         4000   |   ma3000        ma3000 |  roger 

In this exercise, we would like you to figure out which relational algebra operations were used to obtain each of the following tables.

1. 
         name      
       ---------  
         joe     
        hector  
         ling  
   

   Solution: name(student)

2. 
    lecturer 
   ---------
    curtis   
    dave      
    olivier
    roger 
   

   Solution: lecturer(subject)

3. State two ways to get this table. Hint: Use an OR in the selection condition for one method.

   
     code  | lecturer
   ---------------------
    cp3010 |  curtis
    cp1500 |  curtis
   

   Solution: lecturer=curtis(subject) code=cp1500 OR code=cp3010(subject)

4. State three ways to get this table (hint: what about difference?).

   
        id  |  name      
    ------------------  
       1234 |  joe     
       4000 | hector  
   

   Solution: name=joe OR name=hector(student) id, name(student enrolledIn) student - name=ling(student)

5. 
        id  |  name  |   id    |   code     
    -------------------------------------- 
       1234 |  joe   |  1234   |   cp1500    
       1234 |  joe   |  1234   |   cp1200        
       1234 |  joe   |  1234   |   cp2001  
       1234 |  joe   |  4000   |   cp3010 
       1234 |  joe   |  4000   |   ma3000
   

   Solution: Many ways are possible. Here are two. name = joe(student enrolledIn) name = joe(student) enrolledIn

6. 
        id  |  name  |   id    |   code     
    -------------------------------------- 
       1234 |  joe   |  1234   |   cp1500    
       1234 |  joe   |  1234   |   cp1200        
       1234 |  joe   |  1234   |   cp2001  
   

   Solution: Many ways are possible. Here is one. student student.id = enrolledIn.id enrolledIn

7. 
        id  |  name  |   code     
    ---------------------------- 
       1234 |  joe   |   cp1500    
       1234 |  joe   |   cp1200        
       1234 |  joe   |   cp2001  
   

   Solution: Many ways are possible. Here is one. name=joe(student enrolledIn)

8. State two ways to get this table.

   
        id  |   code     
    ------------------- 
       1234 |  cp1500    
       1234 |  cp1200        
       1234 |  cp2001  
   

   Solution: id,code(name=joe(student enrolledIn) id=1234(enrolledIn)

9. 
        id  |  name  |   code    | lecturer
    --------------------------------------------------
       4000 | hector |   cp3010  |  curtis
       4000 | hector |   ma3000  |  roger
   

   Solution: (name=hector(student)) enrolledIn subject

10. [Hint: two joins, one selection, and one projection.

    
         name  |   lecturer
     -------------------------
         joe   |   curtis   
        hector |   curtis
    

    Solution: name, lecturer(lecturer=curtis(subject enrolledIn student))

      student(id, name) 
      enrolledIn(id, code) 
      subject(code, lecturer)

1. What are the names of students enrolled in cp3020?

   Solution: name(cp3020=code(student enrolledIn))

2. Which subjects is Hector taking?

   Solution: code(name=Hector(student enrolledIn))

3. Who teaches cp1500?

   Solution: lecturer(code=cp1500(subject))

4. Who teaches cp1500 or cp3020?

   Solution: lecturer(code=cp1500 OR code=cp3020(subject))

5. Who teaches at least two different subjects?

   Solution: For this query we have to relate subject to itself. To disambiguate the relation, we will call the subject relation R or S. lecturer(R.lecturer = S.lecturer AND R.code <> S.code(R S))

6. What are the names of students in cp1500 or cp3010?

   Solution: name(code=cp1500(student enrolledIn)) name(code=cp3010(student enrolledIn))

7. What are the names of students in both cp1500 and cp1200?

   Solution: name(code=cp1500(student enrolledIn)) name(code=cp3010(student enrolledIn))

8. What are the names of students in at least two different subjects?

   Solution: For this query we have to relate enrolledIn to itself. To disambiguate the relation, we will call the enrolledIn relation R or S. name(student (R.id = S.id AND R.code <> S.code(R S)))

9. What are the codes of all the subjects taught?

   Solution: code(subject)

10. What are the names of all the students?

    Solution: name(student)

11. What are the names of all the students in cp1500?

    Solution: name(code=cp1500(student enrolledIn))

12. What are the names of students taking a subject taught by Roger.

    Solution: name(lecturer=Roger(student enrolledIn subject))

13. What are the names of students who are taking a subject not taught by Roger?

    Solution: name(lecturer <> Roger(student enrolledIn subject))

       movie(movieName, whenMade) 
       star(starName, age) 
       studio(studioName, where) 
       produces(studioName, movieName)
       starsIn(starName, movieName)

1. When was the movie Sneakers made?

   Solution: whenMade(movieName = Sneakers(movie))

2. Who stars in Sneakers?

   Solution: starName(movieName = Sneakers(starsIn))

3. Which stars that are over 40 appear in Sneakers?

   Solution: starName(age > 40 AND movieName = Sneakers(star starsIn))

4. Which stars do not appear in Sneakers?

   Solution: starName(star) - starName(movieName = Sneakers(starsIn)))

5. Which studio produces Sneakers?

   Solution: studioName(movieName = Sneakers(produces))

6. What are the names of stars who star in movies produced by studios located in Burbank?

   Solution: starName(where = Burbank(studio produces starsIn))

1. _{movieName = Sneakers}((starsIn) movie)

   Solution: Who stars in Sneakers?

2. _movieName (_{starName = 'Robert Redford'}(star starsIn movie))

   Solution: Which movies star Robert Redford?

3. _movieName (stars (_{starName = 'Robert Redford'}(starsIn movie)))

   Solution: Which movies star Robert Redford?

4. _starName (_{movieName = Sneakers}(starsIn) _{age < 20}(stars))

   Solution: Which Sneakers stars are under 20?

5. _{studioName, starName}(_{movieName = Sneakers} (star starsIn produces studio))

   Solution: Give me the stars and the studios they work for who starred in Sneakers.

Copyright © 1998 Curtis Dyreson. All rights reserved.