Slip 30 - C) Consider the following entities and their relationships. Library(Lno, Lname, Location, Librarian, no_of_books) Book(Bid, Bname, Author_Name, Price, publication) Relation between Library and Book is one to many. Constraint: Primary key, Price should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a procedure to display names of book written by “Mr. Patil” and are from “DPU Library”. 2) Write a trigger which will fire before insert or update on book having price less than or equal to zero. (Raise user defined exception and give appropriate message)

Solution:

SQL> create table library(lno int primary key,lname varchar(10),location varchar(10),librarian varchar(10),no_of_books varchar(10));

SQL> create table book(bid int primary key,bname varchar(10),author_name varchar(10),price varchar(10),publication varchar(10),lno int);

SQL> insert into library values(1,'AMJ','pune','ram','100');

SQL> insert into book values(1,'SWAMI','SM','5000','AM','1');

1) Write a procedure to display names of book written by “Mr. Patil” and are from “DPU Library”.

SQL>  set serveroutput on

SQL> create or replace procedure p1(n in varchar)as cursor c1 is select lname,author_name from library,book where library.lno=book.lno and author_name='RM' and lname='DPU';

  2  c c1 %rowtype;

  3     begin

  4     open c1;

  5     dbms_output.put_line('lname'||''||'author_name');

  6     loop

  7     fetch c1 into c;

  8      exit when c1%notfound;

  9     if(c.lname=n) then

 10     dbms_output.put_line(c.lname||''||c.author_name);

 11     end if;

 12     end loop;

 13     close c1;

 14     end;

 15  /

Procedure created.

SQL> begin

  2  p1('DPU');

  3  end;

  4  /

lnameauthor_name

DPURM

2) Write a trigger which will fire before insert or update on book having price less than or equal to zero. (Raise user defined exception and give appropriate message)

SQL> Set serveroutput on

SQL> Create or replace trigger t5 before insert or update on book

  2  For each row

  3  Begin

  4  If(:new.price<=0) then

  5  Raise_application_error(-20001,'price>0');

  6  End if;

  7  End;

  8  /

Trigger created.

   /