Sql Library Management System

[cc lang="sql"]
CREATE TABLE Book_Mst (“ISBN” VARCHAR(10) primary key,

“BOOK_TITLE” VARCHAR(50) ,

“PUBLICATION_YEAR” int,

“LANGUAGE” VARCHAR(50),

“CATEGORY_TYPE” int,

“BINDING_TYPE” int,

“DESCRIPTION” VARCHAR(1000),

“NO_OF_COPIES_ACTUAL” int,

“NO_OF_COPIES_CURRENT” int)

select * from book_mst

insert into Book_Mst values(‘BK0004',’CONTROLSYSTEMS’,08,’ENGLISH’,1,2,’This book covers the course material for Powersystems’,10,9)

create table Binding_MSt(“Binding_id” int primary key,”Binding_Name” Varchar(50))

select * from Binding_Mst

insert into Binding_Mst values

create table Category_Master(“Category_id” int primary key,”Category_Name” Varchar(50))

select * from Category_Master

insert into Category_Master values(2,’LONG LOAN’)

create table Borrower_details(Borrower_ID int ,Book_ID varchar(10) ,Borrowed_From_Date datetime , primary key (Borrower_ID,Book_ID,Borrowed_From_Date),Borrowed_To_Date datetime,Actual_Return_date datetime,ISsued_by int)

select * from Borrower_details

insert into Borrower_details values(10005,’BK0002',’09/14/2009',’12/14/2009',’11/09/2009',50002)

create table staff_mst(User_id INT primary key,User_Name varchar(100),Designation varchar(50))

select * from staff_mst

insert into staff_mst values(50002,’SyamMohan’,’Librarian’)

create table Student_Details (Student_id int primary key,
Student_Name varchar(50),

Fathers_Name Varchar(50),
Sex varchar(6),
DOB datetime,
Borrower_ID int unique,
Registration_School varchar(100),

Course varchar(50),

Address varchar(300),
Phone_No bigint unique
)
select * from Student_Details

insert into Student_Details values(30002,’Charles’,’James’,’Male’,’01/04/1986',10002,’St.Anns’,’Msc’,’Patamata’,9701506000)

create table Vedios_Mst(

Vedio_ID varchar(50) primary key,

Title varchar(100),

Author varchar(100),

Co_Author varchar(100),

Genus varchar(30),

Duration_Minutes int,

Production_date datetime,

Publisher_name varchar(100)

)

insert into Vedios_Mst values(‘VD1000002',’Managerial Skills’,’SanthoshPandian’,’Anuketh’,’Genus1',’63',’12/22/2000',’Prakash Publishers’)

select * from vedios_mst

NAME                    : GetBooksbyBorrowerID

DESCRIPTION             : Retries the list of books borrowed by  a specific borrower through his borrowerID

DEVELOPER               :
CREATION DATE           :

Create proc GetBooksbyBorrowerID @Borrower_id int
as
begin

select A.BORROWER_ID ,a.ISBN, b.book_Title,b.language, convert(varchar,a.borrowed_from_date,103)”Borrowed On(dd/mm/yyyy)” from borrower_details a, book_mst b
where a.borrower_id=@Borrower_id
and a.ISBN = b.ISBN
end
go
exec SP_Task1 10001

NAME                    : GetbooksCountPerAcedamicYear

DESCRIPTION             : Retries the total number of books(count) borrowed by  a specific borrower through his borrowerID in a given Academic Year in (YY-YY) format

DEVELOPER               :
CREATION DATE           :

Create proc GetbooksCountPerAcedamicYear @Borrower_id int,@period varchar(50)
as

begin

declare @from_year varchar(10)

declare @to_year varchar(10)

declare @from_date varchar(20)

declare @to_date varchar(20)

set @from_year = ’20'+substring(@period,1,2)

set @to_year = ’20'+substring(@period,4,2)

set @from_date = ’01/09/’+@from_year

set @to_date = ’31/08'+@to_year

select count(*)AS “Total No.OfBooks Borrowed” from borrower_details where borrower_id=@Borrower_id and convert(varchar,borrowed_from_date,103) between  @from_date and @to_date
end
go

exec SP_Task2 10005,’09-10'

NAME                    : GetBooksByCategory

DESCRIPTION             : Retries the list of books(count) that are under a specified category.

DEVELOPER               :
CREATION DATE           :

alter proc GetBooksByCategory @Category_Name varchar(100)
as

begin

declare @Vcategory_id int

set @Vcategory_id =(select category_id from category_master where category_name = rtrim(ltrim(@Category_Name)))

select a.Category_id,a.Category_Name,sum(b.no_of_copies_actual)”Total No.Of Copies”

from Category_Master a,book_mst b where a.Category_id=b.category_type and a.Category_id = @VCategory_id group by a.Category_id,a.Category_Name

end
go
exec SP_Task3 ‘short loan’
NAME                    : GetBooksByTitle

DESCRIPTION             : Retries the list of books(count) that have same Title.

DEVELOPER               :
CREATION DATE           :

alter proc GetBooksByTitle @BookTitle varchar(100)
as

begin

select book_title”Book titile”,ISBN,publication_year”Year Of Publication”,(select “Binding_Name” from binding_mst where “Binding_id” =a.Binding_type)”Type Of Binding”,Language

from book_mst a where book_title =rtrim(ltrim(@BookTitle))

end
go
exec SP_Task4 ‘ POWERSYSTEMS’

NAME                    : GetbooksbyISBN

DESCRIPTION             : Retries list of Borrowers that borrowed a particular book based on the ISBN

DEVELOPER               :
CREATION DATE           :

alter proc GetbooksbyISBN @ISBN varchar(100)
as

begin

select b.borrower_id”Borrower ID”,convert(varchar,b.borrowed_from_date,103)”Borrowed Date(dd/mm/yyyy)”,convert(varchar,b.Actual_return_date,103)”Returened Date(dd/mm/yyyy)” from book_mst a,borrower_details b where a.ISBN=b.ISBN and a.ISBN= @ISBN

end
go
exec SP_Task5 ‘BK0001'

NAME                    : GetVedioByAuthor

DESCRIPTION             : Retries list of Vedios that are cretaed by a specified Author or Co-Author

DEVELOPER               :

CREATION DATE           :

create proc GetVedioByAuthor @AuthorName varchar(100)

as
begin

select * from vedios_mst where (author=rtrim(ltrim(@AuthorName)) or co_Author=rtrim(ltrim(@AuthorName)) )

end
go

exec SP_Task6 ‘Nehawahi’
NAME                    : CreateBorrower

DESCRIPTION             : This Sp will create a new borrower by assigning him a book and then decrease the no.of_copies_available count in for that particular book

DEVELOPER               :

CREATION DATE           :

alter proc CreateBorrower  @Book_ID varchar(10) ,

@ISsued_by int

as
begin

declare @Borrower_ID int

declare @Borrowed_From_Date datetime

declare     @Borrowed_To_Date datetime

declare     @Actual_Return_date datetime

set @Borrower_ID=(select max(Borrower_id) from borrower_details)

set @Borrower_ID=@Borrower_ID+1

set @Borrowed_From_Date=getdate()

set @Borrowed_To_Date= @Borrowed_From_Date

set @Actual_Return_date = null

insert into Borrower_details values(@Borrower_ID,@Book_ID,@Borrowed_From_Date,@Borrowed_To_Date,@Actual_Return_date,@ISsued_by)

update book_mst set no_of_copies_current = no_of_copies_current-1 where ISBN=@Book_ID
end
go

exec CreateBorrower ‘BK0001',50002

NAME                    : DeleteBorrower

DESCRIPTION             : This Sp will will delete an exising borrower from the library database.

DEVELOPER               :
CREATION DATE           :

Create proc DeleteBorrower  @Borrower_ID int
as
begin

delete from borrower_details where borrower_id=@Borrower_ID
end
go

exec DeleteBorrower 10001

[/cc]