SQL- LIBRARY MANAGEMENT SYSTEM



LIBRARY MANAGEMENT SYSTEM

 

>>>>>>>>>>>>>>>>>>>>CREATE TABLE<<<<<<<<<<<<<<<<<<<<

 

create table book_rec(book_name char(15) primary key,author char(15),total_copies number(5),available_copies number(5));

 

create table membership_rec(mem_id number(5) primary key,mem_name char(15),no_of_books_taken number(2));

 

create table book_details(book_id number(5) primary key,book_name char(15) references book_rec(book_name),mem_id number(5));

 

create table circulation_rec(book_id number(5) references book_details(book_id),mem_id number(5) references membership_rec(mem_id),issue_date date,return_date date);

 

 

>>>>>>>>>>>>>>>>>>>>ADD A NEW BOOK<<<<<<<<<<<<<<<<<<<<

 

declare

 

bknam char(15);

auth char(15);

tot number(5);

id number(5);

no number(5);

i number(3);

 

begin

 

bknam:='&bknam';

auth:='&auth';

tot:=&tot;

 

insert into book_rec values(bknam,auth,tot,tot);

 

 

for i in 1..tot

loop

select MAX(book_id) into no from book_details;

 

if no is not null then

id:=no+1;

else

id:=1;

end if;

 

insert into book_details values(id,bknam,null);

 

end loop;

 

end;

/

 

 

Enter value for bknam: DCD

old   9: bknam:='&bknam';

new   9: bknam:='DCD';

Enter value for auth: Mano

old  10: auth:='&auth';

new  10: auth:='Mano';

Enter value for tot: 3

old  11: tot:=&tot;

new  11: tot:=3;

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for bknam: OS

old   9: bknam:='&bknam';

new   9: bknam:='OS';

Enter value for auth: Silber

old  10: auth:='&auth';

new  10: auth:='Silber';

Enter value for tot: 2

old  11: tot:=&tot;

new  11: tot:=2;

 

PL/SQL procedure successfully completed.

 

SQL> select * from book_rec;

 

BOOK_NAME                        AUTHOR        TOTAL_COPIES         AVAILABLE_COPIES

--------------------------------------------------------------------------------------------------------------

DCD                                       Mano                    3                                  3

OS                                          Silber                    2                                  2

 

SQL> select * from book_details;

 

  BOOK_ID     BOOK_NAME                        MEM_ID

--------------------------------------------------------------

        1               DCD

        2               DCD

        3               DCD

        4               OS

        5               OS

 

 

 

 

>>>>>>>>>>>>>>>>>>>>CREATE A NEW MEMBER<<<<<<<<<<<<<<<<<<<<

 

 

 

 

declare

 

nam char(15);

id number(5);

no number(5);

i number(3);

 

begin

 

nam:='&nam';

 

 

select MAX(mem_id) into no from membership_rec;

 

if no is not null then

id:=no+1;

else

id:=1;

end if;

 

insert into membership_rec values(id,nam,null);

 

dbms_output.put_line('Mr/Mrs/Miss. '||nam||', your membership id is '||id);

 

end;

/

 

 

Enter value for nam: Tintumon

old   7: nam:='&nam';

new   7: nam:='Tintumon';

Mr/Mrs/Miss. Tintumon       , your membership id is 1

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for nam: Kuttoos

old   7: nam:='&nam';

new   7: nam:='Kuttoos';

Mr/Mrs/Miss. Kuttoos        , your membership id is 2

 

PL/SQL procedure successfully completed.

 

SQL> select * from membership_rec;

 

   MEM_ID      MEM_NAME              NO_OF_BOOKS_TAKEN

--------------------------------------------------------------------------------------

        1             Tintumon

        2             Kuttoos

 

 

 

>>>>>>>>>>>>>>>>>>>>ISSUE A BOOK<<<<<<<<<<<<<<<<<<<<

 

 

 

 

declare

 

bknam char(15);

mid number(5);

bid number(5);

dat date;

nam char(15);

n number(5);

 

begin

 

bknam:='&bknam';

select MIN(book_id) into bid from book_details where book_name=bknam and mem_id is null;

mid:=&mid;

 

select mem_name into nam from membership_rec where mem_id=mid;

 

update book_details set mem_id=mid where book_id=bid;

select no_of_books_taken into n from membership_rec where mem_id=mid;

 

if n is null then

n:=0;

end if;

update membership_rec set no_of_books_taken=n+1 where mem_id=mid;

update book_rec set available_copies=available_copies-1 where book_name=bknam;

select ADD_months(sysdate,1) into dat from dual;

 

insert into circulation_rec values(bid,mid,sysdate,dat);

 

end;

/

 

Enter value for bknam: DCD

old   8: bknam:='&bknam';

new   8: bknam:='DCD';

Enter value for mid: 2

old  10: mid:=&mid;

new  10: mid:=2;

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for bknam: OS

old   8: bknam:='&bknam';

new   8: bknam:='OS';

Enter value for mid: 1

old  10: mid:=&mid;

new  10: mid:=1;

 

PL/SQL procedure successfully completed.

 

SQL>/

Enter value for bknam: DCD

old   9: bknam:='&bknam';

new   9: bknam:='DCD';

Enter value for mid: 1

old  11: mid:=&mid;

new  11: mid:=1;

 

PL/SQL procedure successfully completed.

 

SQL> select * from book_details;

 

  BOOK_ID     BOOK_NAME          MEM_ID

------------------------------------------------------------

        1             DCD                            2

        2             DCD                            1

        3             DCD

        4             OS                               1

        5             OS

 

SQL> select * from book_rec;

 

BOOK_NAME                        AUTHOR        TOTAL_COPIES         AVAILABLE_COPIES

-------------------------------------------------------------------------------------------------------------

DCD                                          Mano                3                                  1

OS                                             Silber                 2                                  1

 

SQL> select * from membership_rec;

 

   MEM_ID      MEM_NAME                          NO_OF_BOOKS_TAKEN

------------------------------------------------------------------------------------

        1             Tintumon                                              2

        2             Kuttoos                                                1

 

SQL> select * from circulation_rec;

 

  BOOK_ID    MEM_ID           ISSUE_DAT    RETURN_DA

------------------------------------------------------------------------------

        1              2                      08-JAN-10       08-FEB-10

        4              1                      08-JAN-10       08-FEB-10

        2              1                      08-JAN-10       08-FEB-10

 

 

>>>>>>>>>>>>>>>>>>>>RETURN A BOOK<<<<<<<<<<<<<<<<<<<<

 

declare

bknam char(15);

id number(5);

bid number(5);

tim number(7,2);

dat date;

 

begin

 

bknam:='&bknam';

id:=&id;

 

select book_id into bid from book_details where book_name=bknam and mem_id=id;

 

update book_details set mem_id=null where book_id=bid;

update membership_rec set no_of_books_taken=no_of_books_taken-1 where mem_id=id;

update book_rec set available_copies=available_copies+1 where book_name=bknam;

 

select return_date into dat from circulation_rec where book_id=bid and mem_id=id;

select months_between(sysdate,dat) into tim from dual;

 

if tim>0 then

dbms_output.put_line('You have to pay fine');

end if;

 

end;

/

 

Enter value for bknam: DCD

old   8: bknam:='&bknam';

new   8: bknam:='DCD';

Enter value for id: 1

old   9: id:=&id;

new   9: id:=1;

 

PL/SQL procedure successfully completed.

 

SQL> select * from book_rec;

 

BOOK_NAME                        AUTHOR        TOTAL_COPIES         AVAILABLE_COPIES

-------------------------------------------------------------------------------------------------------------

DCD                                           Mano               3                                  2

OS                                              Silber               2                                  1

 

SQL> select * from membership_rec;

 

   MEM_ID      MEM_NAME              NO_OF_BOOKS_TAKEN

-----------------------------------------------------------------------------------

        1             Tintumon                            1

        2             Kuttoos                              1

 

SQL> select * from book_details;

 

 

 

BOOK_ID       BOOK_NAME                        MEM_ID

--------------------------------------------------------------

        1             DCD                                         2

        2             DCD

        3             DCD

        4             OS                                            1

        5             OS

 

SQL> select * from circulation_rec;

 

  BOOK_ID     MEM_ID         ISSUE_DAT    RETURN_DA

------------------------------------------------------------------------------

        1              2                      08-JAN-10       08-FEB-10

        4              1                      08-JAN-10       08-FEB-10

        2              1                      08-JAN-10       08-FEB-10







Post a Comment

0 Comments