Consider the following Entities and Relationships
Questions
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;