LIBRARY MANAGEMENT SYSTEM



CREATION

SQL> create table lib ( bookname varchar(15),author varchar(15), publicationvarchar(10), noofcopies number);

Table created.

SQL> create table student ( rollno number,  name varchar(20), no_card number  );

Table created.

SQL> create table books ( bookno number, bookname varchar(15),
available varchar(10),subscribed_to number );

Table created.

SQL> create table subscription ( bookno number, rollno number, do_sub date,
do_return date,  fineamount number,status varchar(10) );

Table created.SQL> desc lib;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BOOKNAME                                           VARCHAR2(15)
 AUTHOR                                             VARCHAR2(15)
 PUBLICATION                                        VARCHAR2(10)
 NOOFCOPIES                                         NUMBER

SQL> desc student;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLLNO                                             NUMBER
 NAME                                               VARCHAR2(20)
 NO_CARD                                            NUMBER

SQL> desc books;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BOOKNO                                             NUMBER
 BOOKNAME                                           VARCHAR2(15)
 AVAILABLE                                          VARCHAR2(10)
 SUBSCRIBED_TO                                      NUMBER

SQL> desc subscription;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BOOKNO                                             NUMBER
 ROLLNO                                             NUMBER
 DO_SUB                                             DATE
 DO_RETURN                                          DATE
 FINEAMOUNT                                         NUMBER
 STATUS                                             VARCHAR2(10)

INSERTION

SQL> begin
  2      insert into student values(&rollno,'&name',2);
  3      end;
  4      /
Enter value for rollno: 5058
Enter value for name: pavi
old   2:     insert into student values(&rollno,'&name',2);
new   2:     insert into student values(5058,'pavi',2);
PL/SQL procedure successfully completed.

SQL> /
Enter value for rollno: 5100
Enter value for name: vidhya
old   2:     insert into student values(&rollno,'&name',2);
new   2:     insert into student values(5100,'vidhya',2);

PL/SQL procedure successfully completed.

SQL> /
Enter value for rollno: 5097
Enter value for name: anitha
old   2:     insert into student values(&rollno,'&name',2);
new   2:     insert into student values(5097,'anitha',2);

PL/SQL procedure successfully completed.

SQL> /
Enter value for rollno: 5066
Enter value for name: hari
old   2:     insert into student values(&rollno,'&name',2);
new   2:     insert into student values(5066,'hari',2);

PL/SQL procedure successfully completed.
SQL> /
Enter value for rollno: 5056
Enter value for name: suresh
old   2:     insert into student values(&rollno,'&name',2);
new   2:     insert into student values(5056,'suresh',2);

PL/SQL procedure successfully completed.

SQL> ed ins;

bookno number;
bname varchar(15);
noc number;
noc1 number;
author varchar(15);
publication varchar(10);
nobooks number;
lib_rec lib%rowtype;
begin
bookno:=&bookno;
bname:='&bookname';
author:='&author';
publication:='&publication';
noc:=&noofcopies;
noc1:=noc;
select count(*) into nobooks from lib where bookname=bname;
if nobooks=0 then
insert into lib values(bname,author,publication,noc);
else
update lib set noofcopies=noofcopies+noc where bookname=bname;
end if;
while noc!=0
loop
insert into books values(bookno,bname,'yes',0);
noc:=noc-1;
bookno:=bookno+1;
end loop;
end;


SQL> @ ins;
 30  /
Enter value for bookno: 23
old  11: bookno:=&bookno;
new  11: bookno:=23;
Enter value for bookname: cn
old  12: bname:='&bookname';
new  12: bname:='cn';
Enter value for author: forozan
old  13: author:='&author';
new  13: author:='forozan';
Enter value for publication: tata
old  14: publication:='&publication';
new  14: publication:='tata';
Enter value for noofcopies: 4
old  15: noc:=&noofcopies;
new  15: noc:=4;

PL/SQL procedure successfully completed.

SQL> /
Enter value for bookno: 28
old  11: bookno:=&bookno;
new  11: bookno:=28;
Enter value for bookname: ooad
old  12: bname:='&bookname';
new  12: bname:='ooad';
Enter value for author: scilber
old  13: author:='&author';
new  13: author:='scilber';
Enter value for publication: tata
old  14: publication:='&publication';
new  14: publication:='tata';
Enter value for noofcopies: 4
old  15: noc:=&noofcopies;
new  15: noc:=4;

PL/SQL procedure successfully completed.

SQL> /
Enter value for bookno: 32
old  11: bookno:=&bookno;
new  11: bookno:=32;
Enter value for bookname: dbms
old  12: bname:='&bookname';
new  12: bname:='dbms';
Enter value for author: elmasri
old  13: author:='&author';
new  13: author:='elmasri';
Enter value for publication: tata
old  14: publication:='&publication';
new  14: publication:='tata';
Enter value for noofcopies: 4
old  15: noc:=&noofcopies;
new  15: noc:=4;

PL/SQL procedure successfully completed.

SQL> /
Enter value for bookno: 37
old  11: bookno:=&bookno;
new  11: bookno:=37;
Enter value for bookname: evs
old  12: bname:='&bookname';
new  12: bname:='evs';
Enter value for author: ravi
old  13: author:='&author';
new  13: author:='ravi';
Enter value for publication: scitech
old  14: publication:='&publication';
new  14: publication:='scitech';
Enter value for noofcopies: 1
old  15: noc:=&noofcopies;
new  15: noc:=1;

PL/SQL procedure successfully completed.

DIDPLAY

SQL> select * from lib;

BOOKNAME        AUTHOR          PUBLICATION       NOOFCOPIES
--------------- --------------- ---------- -------------------------------------------------
       cn                         forozan                 tata                              4
      ooad                     scilber                   tata                              4
      dbms                    elmasri                  tata                               4
      evs                        ravi                       scitech                        1

SQL> select * from books;

    BOOKNO   BOOKNAME        AVAILABLE    SUBSCRIBED_TO
---------- --------------- ---------- -------------------------------------------------
        23                     cn                          yes                    0
        24                     cn                          yes                    0
        25                     cn                          yes                    0
        26                     cn                          yes                    0
        28                   ooad                        yes                    0
        29                   ooad                        yes                    0
        30                   ooad                           yes                   0
        31                   ooad                            yes                  0
        32                   dbms                           yes                  0
        33                   dbms                           yes                  0
        34                   dbms                           yes                  0

    BOOKNO     BOOKNAME       AVAILABLE      SUBSCRIBED_TO
---------- --------------- ---------- ------------------------------------------------------
        35                dbms                          yes                     0
        37                  evs                           yes                     0

13 rows selected.

SQL> select * from student;

    ROLLNO       NAME                    NO_CARD
---------- -------------------- -----------------------------
      5058                pavi                          2
      5100                vidhya                      2
      5097                anitha                       2
      5066                 hari                          2
      5056                suresh                       2

PROCEDURE TO TAKE A BOOK

SQL> ed take;

create or replace procedure sub(bname char,roll_no number)as
lib_rec lib%rowtype;
book_rec books%rowtype;
stud_rec student%rowtype;
sub_rec subscription%rowtype;
book_no number;
no_of_books number;
begin
select * into stud_rec from student where rollno=roll_no;
if stud_rec.no_card=0 then
dbms_output.put_line('***************');
dbms_output.put_line('no card available');
dbms_output.put_line('***************');
else
select count(*) into no_of_books from books where bookname=bname and available='yes';
if no_of_books=0 then
dbms_output.put_line('***************');
dbms_output.put_line('book not available');
dbms_output.put_line('***************');
else
select min(bookno) into book_no from books where bookname=bname and available='yes';
insert into subscription values(book_no,roll_no,sysdate,sysdate+7,0,'ntreturned');
update student set no_card=no_card-1 where rollno=roll_no;
update books set available='no' where bookno=book_no;
update books set subscribed_to=roll_no where bookno=book_no;
end if;
end if;
exception
when no_data_found then
dbms_output.put_line('***************');
dbms_output.put_line('u are not a user');
dbms_output.put_line('***************');
end;

SQL> @ take;
 35  /

Procedure created.

SQL> exec sub('evs',5058);

PL/SQL procedure successfully completed.

SQL> select * from books;

    BOOKNO         BOOKNAME        AVAILABLE          SUBSCRIBED_TO
---------- --------------- ---------- ------------------------------------------------------------
        23                           cn                       yes                           0
        24                           cn                       yes                           0
        25                           cn                       yes                           0
        26                           cn                       yes                           0
        28                         ooad                     yes                           0
        29                         ooad                     yes                           0
        30                         ooad                     yes                           0
        31                         ooad                     yes                           0
        32                         dbms                    yes                           0
        33                         dbms                    yes                           0
        34                         dbms                    yes                           0

    BOOKNO          BOOKNAME        AVAILABLE    SUBSCRIBED_TO
---------- --------------- ---------- ------------------------------------------------------
        35                           dbms                   yes                            0
        37                            evs                       no                     5058

13 rows selected.


SQL> select * from subscription;
 BOOKNO     ROLLNO      DO_SUB    DO_RETURN     FINEAMOUNT     STATUS
---------- ---------- --------- --------- ---------- -------------------------------------------------------
        37               5058        21-SEP-09     28-SEP-09               0                       ntreturned

SQL> select * from student;

    ROLLNO    NAME                    NO_CARD
---------- -------------------- ------------------------
      5058          pavi                           1
      5100          vidhya                        2
      5097          anitha                         2
      5066          hari                            2
      5056          suresh                        2

SQL> exec sub('evs',5100);
***************
book not available
***************

PL/SQL procedure successfully completed.

SQL> exec sub('ooad',5058);

PL/SQL procedure successfully completed.

SQL> select * from books;

    BOOKNO         BOOKNAME        AVAILABLE      SUBSCRIBED_TO
---------- --------------- ---------- -----------------------------------------------------
        23                           cn                       yes                           0
        24                           cn                       yes                           0
        25                           cn                       yes                           0
        26                           cn                       yes                           0
        28                         ooad                     no                           5058
        29                         ooad                     yes                           0
        30                         ooad                     yes                           0
        31                         ooad                     yes                           0
        32                         dbms                    yes                           0
        33                         dbms                    yes                           0
        34                         dbms                    yes                           0
        35                         dbms                   yes                            0
        37                         evs                       no                      5058

SQL> select * from subscription;

BOOKNO     ROLLNO     DO_SUB       DO_RETURN     FINEAMOUNT     STATUS
---------- ---------- --------- --------- ---------- --------------------------------------------------------
        37             5058           21-SEP-09        28-SEP-09            0                      ntreturned
        28             5058           21-SEP-09        28-SEP-09            0                      ntreturned

SQL> select * from student;
 
  ROLLNO    NAME                    NO_CARD
---------- -------------------- ------------------------
      5058          pavi                           0
      5100          vidhya                       2
      5097          anitha                         2
      5066          hari                            2
      5056          suresh                        2


SQL> exec sub('dbms',5058);
***************
no card available
***************

PL/SQL procedure successfully completed.

Procedure created.

PROCEDURE FOR RETURNING A BOOK

SQL> ed ret;

create or replace procedure ret(bno number)as
lib_rec lib%rowtype;
book_rec books%rowtype;
stud_rec student%rowtype;
sub_rec subscription%rowtype;
book_no number;
no_of_books number;
fine number;
begin
select * into book_rec from books where bookno=bno;
if book_rec.available='yes' then
dbms_output.put_line('$$$$$$$$$$$$');
dbms_output.put_line('book available');
dbms_output.put_line('$$$$$$$$$$$$');
else
update subscription set do_return=sysdate where bookno=bno and status='ntreturned';
select do_return-do_sub into fine from subscription where bookno=bno and status='ntreturned';
if fine>7 then
update subscription set fineamount=fine where bookno=bno and status='ntreturned';
dbms_output.put_line('$$$$$$$$$$$$');
dbms_output.put_line('u have to pay afine of||rs||fine||');
dbms_output.put_line('$$$$$$$$$$$$');
end if;
update subscription set status='returned' where bookno=bno;
update student set no_card=no_card+1 where rollno=book_rec.subscribed_to;
update books set available='yes' where bookno=bno;
update books set subscribed_to=0 where bookno=bno;
end if;
exception
when no_data_found then
dbms_output.put_line('$$$$$$$$$$$$');
dbms_output.put_line('book doesnot belong to library');
dbms_output.put_line('$$$$$$$$$$$$');
end;
SQL> @ ret;
304  /
Procedure created.

SQL> exec ret(28);

PL/SQL procedure successfully completed.

SQL> select * from subscription;

    BOOKNO     ROLLNO   DO_SUB    DO_RETURN    FINEAMOUNT     STATUS
---------- ---------- --------- --------- ---------- -----------------------------------------------------
        37                 5058        21-SEP-09    28-SEP-09            0                       returned
        28                  5058        21-SEP-09    21-SEP-09           0                       returned

SQL> select * from books;

  
 BOOKNO             BOOKNAME       AVAILABLE     SUBSCRIBED_TO
---------- --------------- ---------- --------------------------------------------------------
        23                           cn                       yes                           0
        24                           cn                       yes                           0
        25                           cn                       yes                           0
        26                           cn                       yes                           0
        28                         ooad                     yes                           0
        29                         ooad                     yes                           0
        30                         ooad                     yes                           0
        31                         ooad                     yes                           0
        32                         dbms                    yes                           0
        33                         dbms                    yes                           0
        34                         dbms                    yes                           0

  BOOKNO              BOOKNAME    AVAILABLE      SUBSCRIBED_TO
---------- --------------- ---------- ----------------------------------------------------------
           35                         dbms                   yes                            0
           37                         evs                       no                      5058
13 rows selected.


SQL> select * from student;

  ROLLNO    NAME                    NO_CARD
---------- -------------------- ------------------------
      5058          pavi                           1
      5100          vidhya                       2
      5097          anitha                         2
      5066          hari                            2
      5056          suresh                        2

SQL> exec ret(345);
$$$$$$$$$$$$
book doesnot belong to library
$$$$$$$$$$$$

Previous
Next Post »

Still not found what you are looking for? Try again here.