Que6

Q. 8)    Consider the following Entities and Relationships                [30 Marks]
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.
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.
•    Find total amount collected by hotel on date 08/01/2013
•    Count number of menus of billno 301.
•    Display menu details having   price between 100 and 500. 
•    Display total number of bills collected from each table on 01/12/2013.

Ans
drop table bill;
drop table menu;
drop table  bill_menu;

create table bill(
bno int primary key,
day1 date,
tableno int,
total int
);
create table menu(
dno int primary key,
ddesc varchar(10),
price int check(price>0)
);
create table bill_menu(
bno int references bill(bno),
dno int references menu(dno),
qnt int ,
primary key(bno,dno)
);
insert into bill values(300,'1-dec-2013',23,500);
insert into bill values(301,'8-jan-2013',24,100);
insert into bill values(302,'13-jeb-1013',26,300);
insert into bill values(303,'31-dec-2013',28,600);

insert into  menu values(11,'manturian',20);
insert into  menu values(12,'roti',20);
insert into  menu values(13,'panier',20);
insert into  menu values(14,'rice',20);
insert into  menu values(15,'plane-rice',20);

insert into bill_menu values(300,12,4);
insert into bill_menu values(300,15,2);
insert into bill_menu values(300,11,2);
insert into bill_menu values(301,11,1);
insert into bill_menu values(302,14,1);

select *from bill;
select *from menu;
select *from bill_menu;

************************************************
Q. 9)    Consider the following Entities and Relationships                [30 Marks]
Musician (mno, mname, addr, phno)
Album (title, copy_right_date, format)
Relation between Musicians and Album is One to Many.
Constraint: Primary key.
Create a Database in 3NF & write queries for following.
•    Display all albums composed by ‘A R Rehman’.
•    Display musician details who have composed Audio album.
•    Find all musicians who have composed maximum albums.
•    Display musician wise album details.
•    Display Musian details from 'Pune'

Ans.
create table musician(
mno int primary key,
mname varchar(20),
addr varchar(10),
phone int
);
create table album(
mnno int references musician(mno),
title varchar(30),
crdate date,
format varchar(10)
);
insert into musician values(1,'Shreya Ghoshal','mumbai',1245666);
insert into musician values(2,'Lata Mangeshkar','mumbai',1245666);
insert into musician values(3,'Asha Bhosle','mumbai',1245666);
insert into musician values(4,'A R Rehman','mumbai',1245666);

insert into album values(4,'jay ho','01-jan-2013','Audio');
insert into album values(2,'jana-gana','01-jan-2013','Audio');
insert into album values(2,'parichy','01-jan-2013','Audio');
insert into album values(3,'Ijaazat','01-jan-2013','Audio');
insert into album values(4,'vande matram','01-jan-2013','Audio');
insert into album values(4,'shivaji','01-jan-2013','Audio');
insert into album values(1,'Paheli','01-jan-2005','Audio');
select *from album;
select *from musician;

1]Display all albums composed by ‘A R Rehman’.
//select title from album where mnno in(select mnno from musician where mname='A R Rehman');
2]    Display musician details who have composed Audio album.
//select *from musician where musician.mnno=album.mno and album.format='Audio';
select *from musician where mno in(select mno from album where format='Audio');
3]    Find all musicians who have composed maximum albums.
//select mname from musician where mno in(select mnno,(mnno) from album group by mnno);
4]Display musician wise album details.
//
5]Display Musian details from 'Pune'
//select *from musician where addr='mumbai';