Bill (billno, day, tableno, total) Menu (dish_no, dish_desc, price) The relationship between Bill and Menu is Many to Many with quantity as descriptive Attribute. Constraint: Primary key, price should be > 0.

 

Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints.

Bill (billno, day, tableno, total)

Menu (dish_no, dish_desc, price)

The relationship between Bill and Menu is Many to Many with quantity as descriptive Attribute.  Constraint: Primary key, price should be > 0.

Solution:-

 

SQL> create table menu(menuno int primary key,dishno int ,dish_desc varchar(20),price int check(price>0));

SQL> insert into menu values(1,1,'Veg',160);

 

SQL> create table bill(billno int primary key,day varchar(10),tableno int,total int);

SQL>insert into bill values(201,'sunday',101,1200);

 

SQL>create table billmenu(billno int, menuno int,quantity int,day1 varchar(10));

SQL> insert into billmenu values(301,7,2,'09/07/2019');

 

Consider the above tables and execute the following queries:

1. Display the tableno whose dish_desc is “Veg”

SQL> select tableno from bill,menu,billmenu where bill.billno=billmenu.billno and menu.menuno=billmenu.menuno and dish_desc='Veg';

 

 2. Display the special menu of Monday

SQL> select menu.dish_desc from bill,menu,billmenu where bill.billno=billmenu.billno and menu. menuno=billmenu. menuno and bill.day='Monday';

 

 

1. Display receipt which includes bill_no with Dish description, price, quantity and total amount of each menu.

 SQL>  select sum(bill.total) , menu.dish_desc,menu.price,billmenu.quantity,bill.total from bill,menu, billmenu where bill.billno=billmenu.billno and menu. menuno=billmenu. menuno group by       menu.dish_desc,menu.price,billmenu.quantity ;      

   

2. Find total amount collected by hotel on date 09/07/2019.

 select sum(bill.total)from bill,menu,billmenu where bill.billno=billmenu.billno and menu. menuno=billmenu. menuno and billmenu.date1='09/07/2019';

 

3. Count number of menus of billno 301.

SQL> select count(dish_desc) from bill,menu,billmenu where bill.billno=billmenu.billno and menu. menuno=billmenu. menuno and billno=301;

 

4. Display menu details having price between 100 and 500.

 SQL> select dish_desc from menu where price between 100 and 500;

 

5. Display the tableno and day whose bill amount is zero.

 SQL> select tableno,day from bill where total=0;