Slip 26 - C) Consider the following entities and their relationships. Project (pno, pname, start_date, budget, status) Department (dno, dname, HOD, loc) The relationship between Project and Department is Many to One. Constraint: Primary key. Project Status Constraints: C – Completed, P - Progressive, I – Incomplete Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a procedure to display the name of HOD who has completed maximum project. 2) Write a trigger which will fire before insert or update on project having budget less than or equal to zero. (Raise user defined exception and give appropriate message)

Solution:

1)Write a procedure to display the name of HOD who has completed maximum project.

SQL> set serveroutput on

SQL> create or replace procedure p1(n in varchar) as cursor c1 is  select pname,dname,HOD from Department,Project where Department.dno=Project.dno and pname=(select max(pname)from Department,Project where Department.dno=Project.dno)group by HOD ;

  2  c c1 %rowtype;

  3  begin

  4  open c1;

  5  dbms_output.put_line('pname'||''||'dname'||''||'HOD');

  6   loop

  7   fetch c1 into c;

  8  exit when c1 %notfound;

  9   if(c.pname=n)then

 10  dbms_output.put_line(c.pname ||''||c.dname||''||C.HOD);

 11   end if;

 12   end loop;

 13   close c1;

 14    end;

 15  /

2)Write a trigger which will fire before insert or update on project having budget less than or equal to zero. (Raise user defined exception and give appropriate message)

SQL>  set serveroutput on

SQL>  create or replace trigger t4 before insert or update on project

  2   for each row

  3   begin

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

  5   raise_application_error(-20001,'budget>0');

  6   end if;

  7   end;

  8   /

Trigger created.