RDBMS-Slip9

Consider the following entities and their relationship.                             
Patient (p_no, p_name, p_addr)
Doctor (d_no, d_name, d_addr, city)
Relationship between Patient and Doctor is many-to-many with descriptive attribute disease and no_of_visits.
 
Constraints:primary key, foreign key,primary key for third table(p_no, d_no, disease),
p_name and d_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1)Write a procedure which will display patient detail who has visited more than 3 times to the given doctor for ‘Diabetes’.
SQL> set serveroutput on
SQL> create or replace procedure p2(n in varchar) as cursor c1 is select pname,paddr,disease from patient,doctor, patientdoctor where patient.pno=patientdoctor.pno and doctor.dno=patientdoctor.dno and noofvisits>3;
  2  c c1%rowtype;
  3  begin
  4  open c1;
  5  dbms_output.put_line('pname'||' '||'paddr');
  6  loop
  7  fetch c1 into c;
  8  exit when c1%notfound;
  9  if(c.disease =n) then
 10  dbms_output.put_line(c.pname||' '||c.paddr);
 11  end if;
 12  end loop;
 13  close c1;
 14  end;
 15  /


Procedure created.
SQL> begin
  2  p2('diabetes');
  3  end;
  4  /


pname paddr
vishal daund
PL/SQL procedure successfully completed.

2)Write a trigger which will restrict insertion or updation of doctor_patient details having no_of_visits less than zero. (Raise user defined exception and give appropriate message)
SQL> set serveroutput on
SQL> create or replace trigger t1 before insert or update on patientdoctor
  2  for each row
  3  begin
  4  if(:new.noofvisits<=0)then
  5  raise_application_error(-20001,'number of visits should be greater than zero.');
  6  end if;
  7  end;
  8  /

Trigger created.

SQL> insert into patientdoctor values(106,6,'cancer',0);
insert into patientdoctor values(106,6,'cancer',0)
            *

ERROR at line 1:
ORA-20001: number of visits should be greater than zero.
ORA-06512: at "SCOTT.T1", line 3
ORA-04088: error during execution of trigger 'SCOTT.T1'