Introduction to Databases with Stanford University : Relational Algebra Exercises

• Introduction to Databases by Standford University http://db-class.org
• Additional materials: http://openclassroom.stanford.edu/MainFolder/CoursePage.php?course=IntroToDatabases
• Database data Pizza_data

1. Find all pizzas eaten by at least one female over the age of 20.

\project_{pizza} ( 
 \select_{gender='female' and age > 20} Person 
  \join Eats) 

2. Find the names of all females who eat at least one pizza served by Straw Hat. (Note: The pizza need not be eaten at Straw Hat.)

\project_{name} ( 
  \select_{gender='female'} ( 
      ( \select_{pizzeria='Straw Hat'} Serves 
        \join Eats 
       ) \join Person 
   )
)

3. Find all pizzerias that serve at least one pizza for less than $10 that either Amy or Fay (or both) eat.

\project_{pizzeria} ( 
   \select_{name='Amy' or name='Fay'} ( 
      \select_{price < 10} Serves 
      \join Eats 
   ) 
)

4. Find all pizzerias that serve at least one pizza for less than $10 that both Amy and Fay eat.

\project_{pizzeria} ( 
  \project_{pizzeria,pizza,price} ( 
     \select_{name='Fay'} ( 
        \select_{price < 10} Serves 
        \join Eats 
      )
  ) \join 
    \project_{pizzeria,pizza,price} ( 
        \select_{name='Amy'} ( 
           \select_{price < 10} Serves 
           \join Eats 
        )
     ) 
)

5. Find the names of all people who eat at least one pizza served by Dominos but who do not frequent Dominos.

\project_{name} ( 
    \select_{pizzeria='Dominos'} Serves \
    \join Eats 
) 
\diff 
\project_{name} ( 
    \select_{pizzeria = 'Dominos'} Frequents 
)

6. Find all pizzas that are eaten only by people younger than 24, or that cost less than $10 everywhere they're served.

( 
\project_{pizza} ( 
   \select_{age < 24} (Person \join Eats) 
) 
\diff 
\project_{pizza} ( 
   \select_{age >= 24} (Person \join Eats) 
) 
) 
\union 
( 
\project_{pizza} ( 
   \select_{price < 10} Serves 
) 
\diff 
\project_{pizza} ( 
   \select_{price >= 10} Serves 
) 
)

7. Find the age of the oldest person (or people) who eat mushroom pizza.

\project_{age} (( 
    \select_{pizza = 'mushroom'} Eats 
    ) \join Person 
    ) 
\diff 
\project_{age1} ( 
\select_{age2 > age1} ( 
\rename_{age1} ( 
\project_{age} (( 
    \select_{pizza = 'mushroom'} Eats 
    ) \join Person 
    ) 
    ) 
\cross 
\rename_{age2} ( 
\project_{age} (( 
    \select_{pizza = 'mushroom'} Eats 
    ) \join Person 
    ) 
    ) 
))

8. Find all pizzerias that serve only pizzas eaten by people over 30.

\project_{pizzeria} ( 
    Serves 
) 
\diff 
\project_{pizzeria} ( 
( 
   \project_{pizza} ( 
       Serves 
   ) 
\diff 
\project_{pizza} ( 
   \select_{age > 30} Person 
   \join Eats 
   )) \join Serves 
)

9. Find all pizzerias that serve every pizza eaten by people over 30.

\project_{pizzeria} ( 
    Serves 
) 
\diff 
\project_{pizzeria} ( 
\project_{pizzeria,pizza} (( 
   \select_{age > 30} Person 
   \join Eats 
) \cross Serves 
) 
\diff 
\project_{pizzeria,pizza} ( 
   Serves 
) 
)