Employee and Project Table have Many To Many Relationship. Consider the Following Entities and Their Relationship. Employee(eno,ename,city,deptname) Project(pno,pname,status) Relationship Between Employee and Project is Many to Many with descriptive attribute no_of_days employee worked on that project. Constraints: Primary Key Status to be C-Complete, P-Progressive, I-Incomplete

SQL> create table employee

  2  (eno number primary key,

  3  ename varchar(20),

  4  city varchar(20),

  5  deptname varchar(25));


Table created.


SQL> create table project

  2  (pno number primary key,

  3  pname varchar(20),

  4  status varchar(2) check(status in('C','P','I')));


Table created.


SQL> select * from employee;


       ENO ENAME                CITY                 DEPTNAME

---------- -------------------- -------------------- -------------------------

         1 Raj                  Pune                 Computer

         2 Ramesh               Mumbai               Computer

         3 Ganesh               Delhi                HR

         4 John                 Chennai              Production

         5 Gagan                Mumbai               Sales

         6 Hitesh               Delhi                Sales


6 rows selected.


SQL> select * from project;


       PNO PNAME                ST

---------- -------------------- --

       101 Disaster Management  P

       102 Corruption           I

       103 Railway              P

       104 Library Management   C

       105 Price Control        I


SQL> select * from employee_project;


       ENO        PNO NO_OF_DAYS

---------- ---------- ----------

         1        101         26

         1        102         10

         2        102         30

         3        103         20

         4        104         38

         4        105         20

         5        105         19

         6        102         18


8 rows selected.


a) Create or Replace PL/SQL Function To Return Number of Incomplete Projects of Given Employee.


SQL> set serveroutput on;

SQL> create or replace function f10

  2  (empname in varchar2)

  3  return number

  4  as

  5  v_count number;

  6  begin

  7  select count(pname) into v_count from project,employee,employee_project

  8  where employee.eno=employee_project.eno and

  9  project.pno=employee_project.pno

 10  and status='I'

 11  and ename=empname;

 12  if SQL%Found then

 13  return v_count;

 14  else

 15  return 0;

 16  end if;

 17  end f10;

 18  /


Function created.


***CALL PROGRAM***

SQL> declare

  2  a varchar(20);

  3  r number;

  4  begin

  5  a:=&a;

  6  r:=f10(a);

  7  if (r<=0) then

  8  dbms_output.put_line(a ||' '|| 'Has No Incomplete Project.');

  9  else

 10  dbms_output.put_line(a ||' '|| 'has' ||' '|| r ||' '|| 'Incomplete Project.');

 11  end if;

 12  end;

 13  /

Enter value for a: 'Raj'

old   5: a:=&a;

new   5: a:='Raj';

Raj has 2 Incomplete Project.


PL/SQL procedure successfully completed.


Enter value for a: 'Ganesh'

old   5: a:=&a;

new   5: a:='Ganesh';

Ganesh Has No Incomplete Project.


PL/SQL procedure successfully completed.


b)Create or Replace Trigger Beofre Update on Status of Project such That Status of Project once Complete Can Not Be Changed. Display Appropriate Message.


SQL> create or replace trigger t10

  2  before

  3  update

  4  on project

  5  for each row

  6  begin

  7  if :old.status='C' then

  8  raise_application_error(-20008,'Project is Completed Status Cannot Be Changed');

  9  end if;

 10  end;

 11  /


Trigger created.


SQL> update project

  2  set status='I'

  3  where pno=104;

update project

*

ERROR at line 1:

ORA-20008: Project is Completed Status Cannot Be Changed

ORA-06512: at "SYSTEM.T10", line 3

ORA-04088: error during execution of trigger 'SYSTEM.T10'


SQL> update project

  2  set status='C'

  3  where pno=105;


1 row updated.


SQL> select * from project;


       PNO PNAME                ST

---------- -------------------- --

       101 Disaster Management  P

       102 Corruption           I

       103 Railway              P

       104 Library Management   C

       105 Price Control        C


SQL> update project

  2  set status='I'

  3  where pno=105;

update project

*

ERROR at line 1:

ORA-20008: Project is Completed Status Cannot Be Changed

ORA-06512: at "SYSTEM.T10", line 3

ORA-04088: error during execution of trigger 'SYSTEM.T10'