Slip 2 - C) Consider the following Item_Supplier database : Item (itemno, itemname ) Supplier (supplier_No , supplier_name, address, city ) Relationship between Item and Supplier is many-to-many with descriptive attribute rate and quantity Constraints: itemno ,supplier_No primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write function to print the total number of suppliers of a particular item 2) Write a trigger which will fire before insert or update on rate and quantity less than or equal to zero. (Raise user defined exception and give appropriate message)

Solution:

SQL>create table item(ino int primary key,iname varchar(11));

SQL>insert into item values(1,'soap');

SQL>create table supplier(sno int primary key,sname varchar(11),address varchar(11),city varchar(11));

SQL>insert into supplier  values(101,'ram',’loni’,’pune’);

SQL>create table i_s(ino int,sno int,rate int,quantity int);

SQL>insert into i_s values(1,101,250,3);

1)Write function to print the total number of suppliers of a particular item

 set serveroutput on

create or replace function f1(abc in varchar) return number as xyz number;

begin

select sum(supplier.sno) into xyz from item,supplier,i_s where item.ino=i_s.ino and supplier.sno=i_s.sno and iname='soap';

if sql %found then

return (xyz);

else

return null;

end if;

end f1;

/

 begin

dbms_output.put_line (‘item’||f1(‘soap’));

end;

/

2)Write a trigger which will fire before insert or update on rate and quantity 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 i_s

  2  for each row

  3  begin   

  4  if(:new.quantity<=0)then

  5  raise_application_error(-20001,’quantity >0');

  6  end if;

  7  end;

  8  /