Answer to Question #157802 in Databases | SQL | Oracle | MS Access for dixon

Question

Pizza(pizza)

Customers (cname,area)

Restaurants (rname,area)

Contains (pizza,ingredient)

Sells(rname,pizza,price)

Likes(cname,pizza)

Primary keys are bolded

Foreign keys are:

• Contains.pizza is a foreign key that refers to Pizzas.pizza.

• Sells.rname is a foreign key that refers to Restaurants.rname.

• Sells.pizza is a foreign key that refers to Pizzas.pizza.

• Likes.cname is a foreign key that refers to Customers.cname.

• Likes.pizza is a foreign key that refers to Pizzas.pizza.

Answer in Relational Algebra Format

Find all customer-pizza pairs (C, P) where the pizza P is sold by some restaurant

that is located in the same area as that of the customer C. Include customers

whose associated set of pizzas is empty.

Answer 1

a) π_pizza (σ_{pizza (cname='Alice' and cname<>'Bob')} Likes )

b) π_canme,rname (σ_cname,rname (Customers ⋈ _{customers.area = restaurants.area} Restaurants ⋈ _{restaurants.rname = sells.rname} Sells ⋈ _{sells.pizza = likes.pizza} Likes )

c) π_{Likes.cname,Pizza.pizza} ( σ _{Likes.cname,Pizza.pizza(Likes.pizza<>Pizza.pizza)} (Pizza x Likes) )

d) π_{L1.cname,L2.cname} ( σ_{L1.cname,L2.cname(L1.cname<>L2.cname and L1.pizza<>L2.pizza)} (Likes L1 x Likes L2) )

e) π_{L1.cname,L2.cname} ( σ_{L1.cname,L2.cname(L1.cname<>L2.cname and L1.pizza=L2.pizza)} (Likes L1 x Likes L2) )

f)  π_price ( _rname γ _MAX(price) (Sells) ) -- γ is grouping symbol

g)  π_cname,pizza (σ_cname,pizza (Likes ⟖ _{Likes.cname = Customers.cname} Customers ⋈ _{Customers.area = Restaurants.area} Restaurants ⋈ _{restaurants.rname = sells.rname} Sells) )