Blood and Blood_Donar

Q.1)     Consider the following entities and their relationship.                             

Donor (donor_no, donor_name, city)

            Blood_donation_detail (bd_no, blood_group, qty, date_of_collection)

Relationship between Donor and Blood_donation_detail is one-to-many.

Constraints:   primary key, foreign key,

                        donor_name should not be null,

                        blood_group can be A+, A-, B+, B-, AB+, AB-, O+, O-

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

Create table donor (donor_no int primary key, donor_name varchar2(20) NOT NULL, city varchar2(20));

Insert into donor values(1,’ABC’,’pune’);

Insert into donor values (2,’XYZ’,’pune’);

Insert into donor values (3,’PQR’,’pune’);   

Create table Blood_donation_detail (bd_no int primary key, blood_group varchar2(10) check(blood_group in('A+','A-','B+','o+')), qty int, date_of_collection date,donor_no int references donor(donor_no));

  

Insert into Blood_donation_detail values (101,’A+’,2,’01-july-16’,1);

Insert into Blood_donation_detail values (102,’A+’,2,’01-july-16’,2);

Insert into Blood_donation_detail values (103,’B+’,2,’01-july-16’,3);

1)Write a function which will count total amount of blood collected for a given blood group

on given date.

 Create or replace function fun(b varchar2,d date)

 Return number

    As

    M number;

    begin

    SELECT count(qty) into M  FROM donor, Blood_donation_detail

      Where  blood_group=b and date_of_collection=d;

   Return M;

    End;

   /

                       

 Declare

  A number;

    Begin

    A:=fun('A+','01-july-16');

    Dbms_output.put_line(A);

    End;

    /

2)Write a cursor which will display donor wise blood donation details. (Use parameterized cursor)

declare

 cursor c1 is

select doner.dname,bdgroup,qty,date_of_collection

from  doner, Blood_donation_detail

where doner.dno= Blood_donation_detail.dno

group by dname,bdgroup,qty,date_of_collection;

drec c1%rowtype;

begin

open c1;

loop

fetch c1 into drec;

exit when c1%notfound;

dbms_output.put_line(drec.dname||' '||drec.bdgroup||' '||drec.qty||' '||drec.date_of_collection);

end loop;

close c1;

end;

/

3)Write a procedure which will display blood group wise total amount of quantity of blood available.

set serveroutput on;

create or replace procrdure p3(n in varchar)

as

cursor c1 is select qty,blood_group

 from blood_donation order by bloodgroup;

c c1%rowtype;

begin

open c1;

dbms_output.put_line(‘blood group’||’ ‘|| qty);

loop

fetch c1 into c;

exit when c1%notfound;

dbms_output.put_line(c.blood_group||’ ‘||c.qty);

end loop;

close c1;

end;

/

Procedure created.

SQL>begin

P3(‘blood_group’);

end;

/

4)Write a trigger that restricts insertion of blood_donation_details having quantity greater than 300ml. (Raise user defined exception and give appropriate message)

set serveroutput on;

create or replace trigger tbd before insert or update on blood_donation

for each row

begin

if(:new.qty>300)then

raise_application_erroer(-20001,’blood must be less than 300ml’);

end if;

end;

/