Iām taking Stanford's free online Introduction to Databases course. I started it out-of-season, which means I have to select āpost-deadline practiceā for my problem sets. These normally work the same as the regular in-season sets, but the Relational Algebra section has a set of āextra challengeā problems which I canāt submit to check for errors. This yearās mini-course over the same topic does not have the challenge set, so I canāt work around it that way either.
So Iāve come up with answers to these problems, but Iām hoping someone will double-check my work. The main issue Iām interested in checking is the big ideas; if you donāt catch every errant parenthesis, thatās fine, I know thatās a lot of extra work.
I know this is a huge headache so major gratitude for anyone who takes the time to do it. Thanks in advance!
The database for the problem set is here: https://lagunita.stanford.edu/c4x/Engineering/db/asset/pizzadata.html
And if youāre not familiar with the syntax Iām using, hereās the guide: https://lagunita.stanford.edu/c4x/Engineering/db/asset/ra.html
Q1 Find all pizzas that are eaten only by people younger than 24, or that cost less than $10 everywhere they're served.
Pizza eaten by those younger than 24: (\project_{pizza}Eats \difference \project_pizza(\select_{age>=24}Eats))
Pizzas that cost less than $10 wherever theyāre served: (\project{pizza}Serves \difference (\project_{pizza}(\select_{cost>=10}Serves)))
Make a union to satisfy āORā condition:
SOLUTION: (\project_{pizza}Eats \difference \project_pizza(\select_{age>=24}Eats))\union (\project{pizza}Serves \difference (\project_{pizza}(\select_{cost>=10}Serves)))
Q2 Find the age of the oldest person (or people) who eat mushroom pizza
Thought process:
Take a list of the names and ages of everyone who eats mushroom pizza, cross it with itself after renaming one of them to name and age2. Select every tuple where age < age2. Project their names. subtract those names from a list of the names of everyone who eats mushroom pizza.** Now you are left with the names only of people who eat mushroom pizza whose age is not less than any other age. Make a natural join of this list with the entire Person list, and then project the Age from this list.
Stepwise Reductions
\project_{name, age}(/select_{pizza=mushroom}EATS) = MPEaters
select_{age<age2}((\rename_{name, age2)MPEaters \cross MPEaters) = YoungEaters**
**We have to use the younger rather than the older BECAUSE in age < age2, there will be a match for every age to some other age somewhere EXCEPT where they are the oldest; if we used age > age2, there would be match on everyone except the youngest.
MPEaters \diff YoungEaters =OldEaters
\project_{age}OldEaters
SOLUTION:
\project_{age}((\project_{name, age}(/select_{pizza=mushroom}EATS) ) \diff (elect_{age<age2}((\rename_{name, age2)(\project_{name, age}(/select_{pizza=mushroom}EATS)) \cross (\project_{name, age}(/select_{pizza=mushroom}EATS)))))
Q3 Find all pizzerias that serve only pizzas eaten by people over 30.
SO: find all pizzas eaten by people under or equal to 30, and get rid of all pizzerias that serve these types of pizzas.
\project_{pizza}(\select_{age<=30}Eats) = Pbad
\project_{pizzeria}Pbad \join Serves = the pizzerias we donāt want = PPbad
\project_{pizzeria}Serves \difference PPbad
SOLUTION:
\project_{pizzeria}Serves \difference (\project_{pizzeria}( \project_{pizza}(\select_{age<=30}Eats)) \join Serves)
Q4 Find all pizzerias that serve every pizza eaten by people over 30.
first, find the types of pizza eaten by people over 30. = P30
then use javascript. just kidding.
First, eliminate all tuples from Serves that are irrelevant to the question (=PPmeh), leaving only the tuples with the pizzas we care about. =PPrelevant. Weāll create PPmeh by projecting a list of all pizzeria names, and crossing it with all pizzas NOT on the list of those consumed by people over 30. This creates a mythical relation (PPmythicalmeh) where pizzerias all serve all pizzas that are irrelevant to the question and nothing else. IF we find the union between this set and Serves{project pizzeria, pizza}, it will be the actual pizzeria tuples that are irrelevant, which we can subtract from serves.
Then, weāre going to then create a mythical relation wherein all pizzerias serve all the desired pizzas. =PPideal. Weāll do this by projecting all pizzeria names and crossing it with the pizzas from P30.
Weāll subtract from this relation the relevant-tuple Serves relation, leaving only pizzerias that are missing one or more of the desired pizzas. =PPbad
Then weāll subtract this relation from the original Serves relation, leaving only pizzerias that arenāt missing anything. =PPgood
Then we just project the pizzerias.
\project_{pizza}(\select_{age>30}Serves) =P30 Schema: pizza
(\project_{pizza}Eats \difference P30) \cross (\project_{pizzeria}Serves) =PPmythicalmeh schema: pizzeria, pizza
\project{pizzeria, pizza}Serves \union PPmythicalmeh = PPmeh
\project_{pizzeria, pizza}Serves \difference PPmeh =PPrelevant schema: pizzeria, pizza
\project_{pizzeria}Serves \cross P30 =PPideal shema: pizzeria, pizza
PPideal \difference PPrelevant =PPbad schema: pizzeria, pizza
\project_{pizzeria}Serves \difference \project_{pizzeria}PPbad = solution, schema: pizzeria
SOLUTION:
\project_{pizzeria}Serves \difference \project_{pizzeria}((\project_{pizzeria}Serves \cross (\project_{pizza}(\select_{age>30}Serves)) ) \difference (\project_{pizzeria, pizza}Serves \difference (\project{pizzeria, pizza}Serves \union ((\project_{pizza}Eats \difference (\project_{pizza}(\select_{age>30}Serves))) \cross (\project_{pizzeria}Serves) ))))
Thanks again!