RDBMS Slip No 2

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)

Answer : 

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:

CREATE TABLE Item11(
Ino int primary key ,
Iname varchar(25));
INSERT INTO Item11 VALUES (101,'Keyboard');
INSERT INTO Item11 VALUES (102,'Mouse');
INSERT INTO Item11 VALUES (103,'Sound');
INSERT INTO Item11 VALUES (104,'Printer');

CREATE TABLE Sup11(
Sno int primary key,
Sname varchar(25),
Address varchar(25),
City varchar(30) );

INSERT INTO Sup11 VALUES (201,'Ashish Lokhande','Hadapsar','Pune');
INSERT INTO Sup11 VALUES (202,'Patil','Indapur','Indapur');
INSERT INTO Sup11 VALUES (203,'Bhatia','Sarswatinagar','Indapur');
INSERT INTO Sup11 VALUES (204,'Baburao','Anandnagar','Baramati');

CREATE TABLE It_Sup11(
isno integer primary key,
Ino1 int references item11(Ino),
Sno1 int references Sup11(Sno),
Rate int ,
quantity int);

INSERT INTO It_Sup11 VALUES (301,101,201,500,10);
INSERT INTO It_Sup11 VALUES (302,102,202,100,5);
INSERT INTO It_Sup11 VALUES (303,103,203,3300,15);
INSERT INTO It_Sup11 VALUES (304,104,204,5300,15);
INSERT INTO It_Sup11 VALUES (305,101,201,510,17);
INSERT INTO It_Sup11 VALUES (306,104,202,1200,8);

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

create or replace function tosup return number
is
total_count number;
begin
select count(*) into total_count from Item11,Sup11,It_Sup11 where Item11.Ino=It_Sup11.Ino1 and Sup11.Sno=It_Sup11.Sno1 and Iname='Keyboard';
return total_count;
end;

call function-----------
1)By using select keyword
select tosup from dual

2)By using calling program
declare
total_count2 number;
begin
total_count2:=tosup;
dbms_output.put_line('Total Number of suppler to provide KEYBOARD Is'||total_count2);
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)

create or replace trigger Rate
before insert or update of Rate on It_Sup11
for each row
begin
if:new.Rate<=0then
raise_application_error(-20001,'insert valid rate');
end if;
end;