Question 3 (Total 13 Points):

Answer questions 3.1 and 3.2 based on the following schema (data types are not needed).

Product(pid, pname, type, manufacturer, price)

Buys(cid, pid,quantity)

Customer(cid, cname, age, gender)

(3.1) (8 Points) For parts 3.1.1), 3.1.2), and 3.1.3), write SQL statements to return the required information.

3.1.1) (2 Points) Find names and types of all products that are manufactured by Sears.

3.1.2) (3 Points) Find names of all customers who have purchased a product that is manufactured by Acme and have a gender of Coyote

3.1.3) (3 Points) Find type and price of all products that have been purchased by a customer who is older than 42 years old and quantity > 10.

3.2) (5 Points) Answer parts 3.2.1) and 3.2.2) based on the following SQL query,

SELECT P.manufacturer

FROM Product P, Buys B

WHERE P.pid=B.pid and P.pname=Stuffed Puppy Dog and B.quantity < 11

3.2.1) (2 Points) Write an English sentence to explain the output of the query.

3.2.2) (3 Points) Write the relational algebra expression that is equivalent to the given SQL query