Description
Note: Write the following queries in relational algebra. Note that some of these queries
may not be expressible in standard relational algebra taught in the lectures. For such
queries, explain why they cannot be expressed.
Part 1 Consider a database:
Customer (sid, name, career)
Commodity (cid, title)
Price (sid, cid, HKD)
Details:
• Sid of Price is a foreign key referring to sid of Customer.
• Cid of Price is a foreign key referring to cid of Commodity.
1. (5 marks) Find the name of customers who have bought the commodity with cid
“c1”.
2. (5 marks) Find the sid of customers who have bought the commodity entitled “milk”.
3. (5 marks) Find the sid of customers who have bought two hamburgers.
4. (10 marks) Find the sid of customers who have bought both the commodity entitled
“milk” and the commodity entitled “bread”.
5. (10 marks) Find the sid of customers who have bought the commodity entitled “milk”
or the commodity entitled “hamburger”.
6. (10 marks) Find the sid and names of customers who did not buy the commodity
entitled “milk”.
Part 2 Consider a database:
Creature (name, category, status)
FoodChain (predator, food, percentage)
Details:
• The values of status in Creature can be:
Carnivores,
Herbivores,
Omnivorous,
Saprophagous.
• Predator of FoodChain is a foreign key referring to name of Creature.
• Food of FoodChain is a foreign key referring to name of Creature.
1. (5 marks) Find the predator(s) whose foods are all known in Creature.( For a predator
if the percentages of its foods sum up to 100%, we say that its foods are all known.)
2. (10 marks) List the name of each creature which eats any of the predators of the
creature “frog”.
3. (10 marks) List the name of each creature which eats all the predators of the creature
“bird”.
4. (15 marks) Find food(s) which are eaten by exactly three predators.
5. (15 marks) List the name of each predator which eats all creatures except itself.
