Slip 7
Consider
the following entities and their relationships.
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.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
SQL>create
table bill(bill_no int primary key,day varchar(10),table_no int,total int);
SQL>create
table menu(dish_no int primary key,dish_description varchar(10),price int);
SQL>create
table bm(bill_no int,dish_no int,Quantity int);
SQL>insert
into bill values( 1,'monday','101','240');
SQL>insert
into menu values(1,'paneer','200');
SQL>insert
into bm values(1,1,5);
1)Write a procedure to display menu
details having price between 200 to 500 which were order on ‘Saturday’ .
SQL>
set serveroutput on;
SQL>
create or replace procedure p1(a in varchar) as cursor c1 is select
menu.dish_no,menu.price,bil
l.day
from bill,menu,bm where bill.bill_no=bm.bill_no and menu.dish_no=bm.dish_no and
price between
200
and 500 and day='saturday';
2 c
c1 %rowtype;
3
begin
4
open c1;
5
dbms_output.put_line('dish_no'||''||'price'||''||'day');
6
loop
7
fetch c1 into c;
8
exit when c1 %notfound;
9
if(c.day=a)then
10 dbms_output.put_line(c.dish_no||''||c.price||''||c.day);
11 end
if;
12 end
loop;
13
close c1;
14
end;
15 /
Procedure
created.
SQL>
begin
2
p1('saturday');
3 end;
4 /
dish_nopriceday
3400saturday
2)Write a trigger which will fire
before insert or update on Menu having price less than or equal to zero. (Raise
user defined exception and give appropriate message)
SQL>
set serveroutput on;
SQL>
create or replace trigger t1 before insert or update on menu
2 for
each row
3
begin
4
if(:new.price<=0)then
5
raise_application_error(-20001,'price>0');
6 end
if;
7 end;
8 /
Trigger
created
SQL> insert into menu values(6,'rice',0);
insert into menu values(6,'rice',0)
*
ERROR
at line 1:
ORA-20001:
price>0
ORA-06512:
at "SCOTT.T1", line 3
ORA-04088:
error during execution of trigger 'SCOTT.T1'
