Question

Consider Retail Store database which store the details of different items available in the store and the sales of these items to different customers: Schema: Item(ItemNo, ItemName, Category, UnitPrice) Sales(SalesNo, ITemNo, SalesDate, CustomerNo, Qty) Customer(CustomerNo, CustomerName, City, Income, MobileNo) Write the following queries in Relational Algebra:

a) List the No. and Name of items in ‘ABC’ category.

b) Count the No. of categories from which the items were bought by the customer ‘Mohan Kumar’

c) List the customers who has bought all the items of ‘ABC’ category

Answer 1

a)Select itemno, itemname from item where category='ABC' ;

b)select count(Category) from Item i , customer c, sales s where C.customerNO=S.customerno and S.itemno=i.itemno where c.customername='Mohan Kumar';

c)select c.customername from Item i , customer c, sales s where C.customerNO=S.customerno and S.itemno=i.itemno where i.category='ABC';