Ready Project |BCA Project | MCA Project | BBA Project | MBA Project |INDIA'S BIGGEST COLLEGE PROJECT STORE

9) Consider the following entities and their relationship.
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’.
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)


 create table Patient(p_no number primary key, p_name varchar2(20),p_addr varchar2(20));

create table Doctor(d_no number primary key, d_name varchar2(20), d_addr varchar2(20), city varchar2(20));

create table P_D(p_no number references Patient(p_no),d_no number references Doctor(d_no),disease varchar2(20),no_of_visits number,primary key(p_no,d_no,disease));

insert into Patient values(1,'raj','pune');

insert into Patient values(2,'rajesh','pune');

insert into Patient values(3,'mukesh','mumbai');

insert into Doctor values(101,'yogesh','pune','pune');

insert into Doctor values(102,'dipali','pune','pune');

insert into Doctor values(103,'amol','varsova','mumbai');



insert into P_D values(1,101,'swineflue',7);

insert into P_D values(1,101,'dehydrtion ',7);

insert into P_D values(1,102,'dehydrtion ',3);

insert into P_D values(2,102,'diabetes ',7);

insert into P_D values(3,103,'diabetes ',2);

insert into P_D values(1,101,'diabetes',4);

insert into P_D values(1,101,'asthma',4);

insert into P_D values(2,101,'asthma',4);

insert into P_D values(2,101,'diabetes',5);

insert into P_D values(3,101,'diabetes',6);



Q1)

create or replace procedure p1(s_name in varchar2)

  as

  cursor c1 is select p.* from Patient p,Doctor d,P_D pd

  where p.p_no=pd.p_no and d.d_no=pd.d_no

  and pd.disease='diabetes' and d.d_name=s_name and pd.no_of_visits>3;


  begin

  for abc in c1

  loop

  dbms_output.put_line('patient no:='||abc.p_no||'patient name:='   ||abc.p_name||   'patient address:='||abc.p_addr);

  end loop;

  end;


declare

begin

p1('yogesh');

end;


Q2) 

create or replace trigger trg

    before insert or update on P_D

     for each row

     begin

     if:new.no_of_visits<0 then

     raise_application_error(-2010,'no_of_visits less than zero');

     end if;

     end;

Search

Project Categories

Recent Posts

Sign In