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.
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.
