The database contains the following relations: Person ( ID, name, age, gender) /

Question

The database contains the following relations:

Person ( ID, name, age, gender) // ID is a key

(e.g., (1, Gary, 30, M), (2, Mary, 21, F)…)

Shop_list (ID, item) // (ID, item) is the key

(e.g., (1, 1), (1, 2), (2, 2), (2,3)…)

Sell (supermarket, item, price) // (supermarket, item) is the key

(e.g., (Walmart, 1, 3.0), (Walmart, 2, 2.50), (Costco, 3, 50.0), (Costco, 1, 2.50)…)

Write relational algebra expressions to answer the following queries, using Project operation to project the result tuples onto only the column(s)/attribute(s) requested. Assume all relational operators take their (default) set semantics.

1) Find all the items on the shop_list of at least one person over the age of 18.

2) Find the names of all males who have at least one item in their shop_list sold at Walmart.

*Note that all the relational operators take their SET semantics by DEFAULT.*

Explanation / Answer

1) SELECT Shop_list.item FROM Shop_item LEFT JOIN Person ON Shop_item.id = Person.id AND Person.id > 18;

2) SELECT Person.name FROM Person
LEFT JOIN Shop_list ON Shop_item.id = Person.id AND Person.gender = "M"
LEFT JOIN Sell ON Shop_item.id = Sell.id AND Sell.supermarket = "Walmart";

**Comment for any further queries.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.