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

5) Consider the following entities and their relationship.
Drug(d_no, d_name, company, price)
Medical _store(m_no, m_name, m_city, ph_no)
Relationship between Drug and Medical_Store is many-to-many with descriptive attribute quantity.
Constraints: primary key, foreign key,
m_name and d_name should not be null,
m_city can be pune or pimpri.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a package, which consists of one procedure and one function. Pass drug number as a parameter to procedure and display details of that drug. Pass city as a parameter to a function and return total number of medical_store in given city.
Write a trigger that restricts insertion and updation of drug having price less than zero. (Raise user defined exception and give appropriate message)


 create table Drug(d_no number primary key, d_name varchar2(20) not null, company varchar2(20), price number);

create table Med_s(m_no number primary key, m_name varchar2(20), m_city varchar2(20), ph_no number);

create table D_M(d_no number references Drug(d_no),m_no number references Med_s(m_no),quantity number);



insert into Drug values(1001,'dicoliy_gel ','cipla',600);

insert into Drug values(1002,'fit_gel ','cipla',789);

insert into Drug values(1003,'crocine ','mosco',700);

insert into Med_s values(1,'raj_medical','pune',9096188754);

insert into Med_s values(2,'dipali_medical','pune',9096188755);

insert into Med_s values(3,'laxmi_medical','mumbai',9096188752);



insert into D_M values(1001,1,5);

insert into D_M values(1001,2,4);

insert into D_M values(1002,3,8);  
 
insert into D_M values(1003,1,8);

insert into D_M values(1003,2,9);

insert into D_M values(1003,3,10);

insert into D_M values(1001,3,5);

insert into D_M values(1002,1,6);

insert into D_M values(1002,2,7);

Q1)

create or replace package pack1

as

procedure p1(dno in number);

function f1(c Med_s.m_city%type )

return number;

end pack1;


*******pack body************

create or replace package body pack1

as

procedure p1(dno in number)

as

a Drug%rowtype;

begin 

select Drug.* into a from Drug 

where d_no=dno;

dbms_output.put_line(' .');

dbms_output.put_line(' .... OUTPUT .....');

dbms_output.put_line(' .');

dbms_output.put_line('dno     name     company     price');

dbms_output.put_line(a.d_no||'     '||a.d_name||'     '||a.company||'     '||a.price);

end p1;

function f1(c Med_s.m_city%type)

return number

as cnt number;

begin

select count(*) into cnt from Med_s where m_city=c;

return cnt;

end f1;

end pack1;

***********************************************************

SQL> set serveroutput on;

SQL> declare

  2  n number;

  3  name Med_s.m_city%type ;

  4  i number;

  5  begin

  6  n:=&n;

  7  pack1.p1(n);

  8  name:= '&name';

  9  i:=pack1.f1(name);

 10  dbms_output.put_line(' .');

 11  dbms_output.put_line(' .');

 12  dbms_output.put_line(' .');

 13  dbms_output.put_line('count of '||name||' is '||i);

 14  end;

Q2)

create or replace trigger t14

before insert or update on Drug

for each row

begin

if(:new.price<0) then

raise_application_error(-2013,'drug having price less than zero.');

end if;

end;

Search

Project Categories

Recent Posts

Sign In