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 ) )