DBMS-Slip8

Slip8

Consider the following Entities and Relationships                                     
Bill (billno, day, tableno, total)                                             
Menu (dish_no, dish_desc, price)
Relation between Bill and Menu is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Solution:-
Create a Database in 3NF & write queries for following.
•Display receipt which includes bill_no with Dish description, price, quantity and total amount of each menu.
SQL>Select sum(bill.total),menu.ddesc,menu.price,bill_menu.qnt from bill,menu,bill_menu  where bill.bno=bill_menu.bno and menu.dno= bill_menu.dno group by menu.ddesc, menu.price, bill_menu.qnt;

•Find total amount collected by hotel on date 08/01/2013
SQL>Select sum(total) from bill where day1=’8-jan-2013’;

•Count number of menus of billno 301.
SQL>Select count(ddesc) from bill,menu,bill_menu where bill.bno=bill_menu.bno and  menu.dno= bill_menu.dno and bill.bno=301;

•Display menu details having   price between 100 and 500.
SQL>Select ddesc,price from menu where  price between 100 and  500;

•Displaytotal number of bills collected from each table on 01/12/2013.
SQL>Select sum(total),tableno from bill where day1=’1-dec-2013’ group by tableno;