图书馆管理系统-程序设计报告0906840442赵军旺-0906840419黄岳

程序设计报告数据表ER图：LevelLevelBookNumBorrowdateReturndatemanageManagerManagerIDPasswordNameLevelPresentPositionPlevelPnameBorrowedReaderNameSexBirthdayUserIDPasswordDeadlineLevelhasReaderKindMaxSumaddOrderOrderIDOrderdateorderStaffIDBookNameBookPressofStockstockIDorderIDenterStaffIDofBookIndexNumIsbnBookIDNameAuthorPressPriceBookstatePriceAuthorIsbnrealNumenterstockclassBookClassClassIDClassNameMemoBorrowIDFine_ticketEmailPhoneKindNamePhoneEmailSexBirthdayFineKindFineKindFineKindDescribeNum关系模式：Book(BookId,Index,ISBN,BookName,Press,Author,ClassID,Memo,State)Bookclass(classid,Bookclass,classID)Borrowed(BorrowedId,ReaderId,BookId,Borrow_Date,DeadLine,Return_date,Fine_ticket)FineKind(FineKind,Describe,Num)\Reader(ReaderId,Password,ReaderLevel,Name,Sex,Email,Phone,Birthday,Expiry_Time)ReaderKind(ReaderLevel,Kind_Name,Max_borrow_num)Manager(ManagerId,Password,Name,Level,Phone,Email,,Birthday,Sex)ManagerKind(ManagerLevel,Level_Name)Order(OrderId,BookName,Press,Order_num,OrderDate,OrderStaffId)Stock(StockId,OrderId,StockStaffId,Author,ISBN,Price,RealNum,StockDate)表的物理设计Book（图书表）属性名数据类型是否主键是否允许为空缺省值说明BookIdVarchar(50)YESNOTNULL书号IndexVarchar(50)NOTNULL索书号ISBNVarchar(15)NULL条形码BookNameVarchar(50)NOTNULL书名AuthorVarchar(30)NOTNULL作者PressVarchar(30)NOTNULL出版社ClassIDVarchar(20)NULL类别号MemoVarchar(30)NULL备注StateIntegerNOTNULL状态 BookClass（图书分类表）属性名数据类型是否主键是否允许为空缺省值说明ClassidIntegerNOTNULL号码BookcalssNvarchar(50)NOTNULL类别名classIDVarchar(20)YESNOTNULL类别号Borrowed（借阅表）属性名数据类型是否主键是否允许为空缺省值说明BorrowIdVarchar(50)YESNOTNULL借阅编号ReaderIdVarchar(50)NOTNULL用户号BookIdVarchar(50)NOTNULL书号Borrow_DateDATENOTNULL借书日期DeadLineDATENOTNULL期限Return_DateDATENULL还书日期Fine_ticketfloatNULL罚款FineKind（罚款设置表）属性名数据类型是否主键是否允许为空缺省值说明FineKindIntegerYESNOTNULL罚款类别DescribeVarchar(20)NOTNULL描述NumFloatNOTNULL赔偿比例Manager（管理员表）属性名数据类型是否主键是否允许为空缺省值说明ManagerIdVarchar(20)YESNOTNULL管理员号PasswordVarchar(20)NOTNULL密码NameVarcahr(20)NULL姓名LevelIntegerNOTNULL级别PhoneVarchar(20)NULL电话EmailVarchar(30)NULL邮件BirthdayDATENULL生日SexVarchar(10)NULL性别ManagerKind（管理员类别表）属性名数据类型是否主键是否允许为空缺省值说明ManagerLevelIntegerYESNOTNULL级别Level_NameVarchar(20)NOTNULL级别名称Order（订单表）属性名数据类型是否主键是否允许为空缺省值说明OrderIdVarchar(30)YESNOTNULL订单号BookNameVarchar(30)NOTNULL书名PressVarcahr(20)NOTNULL出版社Order_numIntegerNOTNULL订货数量OrderDateDATENOTNULL订货日期OrderStaffIdVarchar(50)NOTNULL采购员Reader（用户表）属性名数据类型是否主键是否允许为空缺省值说明ReaderIdVarchar(50)YESNOTNULL用户号PasswordVarchar(20)NOTNULL密码NameVarcahr(20)NULL姓名ReaderLevelIntegerNOTNULL级别PhoneVarchar(20)NULL电话EmailVarchar(30)NULL邮件BirthdayDATENULL生日SexVarchar(10)NULL性别ReaderKind（用户类别表）属性名数据类型是否主键是否允许为空缺省值说明ReaderLevelIntegerYESNOTNULL用户级别Kind_NameVarchar(10)NOTNULL名称Max_borrow_numIntegerNOTNULL最大借阅量Stock（图书入库表）属性名数据类型是否主键是否允许为空缺省值说明StockIdVarchar(30)YESNOTNULL入库号OrderIdVarchar(30)NOTNULL订单号ISBNVarcahr(15)NOTNULL条形码PriceFloatNOTNULL价格RealNumIntegerNOTNULL实际数量StockDateDATENOTNULL入库日期AuthorVarchar(30)NOTNULL作者StockStaffIdVarchar(30)NOTNULL入库员工号视图，索引，数据库权限： 存储过程：增加员工：ALTERPROCEDURE[dbo].[Proc_addAdm]@managerIdvarchar(20),@passwordvarchar(20)='888888',@Namevarchar(20)=null,@Levelint,@Phonevarchar(20)=null,@Emailvarchar(30)=null,@Birthdaydate=null,@sexvarchar(10)=null,@ableintoutputASBEGIN ifexists(select*fromManagerwheresManagerId=@managerId) set@able=0 else begin insertintoManagervalues(@managerId,@password,@Name,@Level ,@Phone,@Email,@Birthday,@sex) set@able=1 endENDif@@error<>0or@@rowcount=0 rollbacktransaction;

增加用户：ALTERPROCEDURE[dbo].[Proc_addReader]@readeridvarchar(50),@spasswordvarchar(20)='888888',@readerlevelint=2,@Namevarchar(20)=null,@sexvarchar(10)=null,@Emailvarchar(30)=null,@Phonevarchar(20)=null,@Birthdaydate=null,@lend_ableint=1,@ableintoutputASBEGIN ifexists(select*fromReaderwheresReaderId=@readerid) set@able=0 else begin insertintoReadervalues(@readerid,@spassword,@readerlevel,@Name,@sex,@Email,@Phone,@Birthday,@lend_able,null) set@able=1 endENDif@@error<>0or@@rowcount=0 rollbacktransaction;借阅书籍：ALTERPROCEDURE[dbo].[Proc_Borrow]@ReaderIdvarchar(50),@BookIdvarchar(50),@stateintoutputASBEGIN if(selectbalancefromReaderwheresReaderId=@ReaderId)<0 set@state=0 --有欠款，不可借 else begin declare@BorrowIdvarchar(50) set@BorrowId=@ReaderId+'-'+@BookId+cast(GETDATE()asvarchar(10)) insertinto[dbo].Borrowedvalues(@BorrowId,@ReaderId,@BookId,GETDATE(),GETDATE()+30,null,0) updateBooksetiState=0wheresBookId=@BookId set@state=1 endENDif@@error<>0or@@rowcount=0 rollbacktransaction;用户是否能借阅：ALTERPROCEDURE[dbo].[Proc_Canborrow]@userIdvarchar(30),@can_borrowintoutputASBEGIN declare@tmp_num1int --已借书目 declare@tmp_num2int --可借书目 declare@tmpint select@tmp_num1=count(*)fromborrowedwheresReaderId=@userIdandreturn_date=null select@tmp_num2=Max_borrow_numfromReaderKindwhereiReaderLevel=(selectireaderLevelfromReaderwheresReaderId=@userId) select@tmp=Lend_ablefromReaderwheresReaderId=@userId if@tmp_num1<=@tmp_num2and@tmp=1 set@can_borrow=1 else set@can_borrow=0ENDif@@error<>0or@@rowcount=0 rollbacktransaction;检查书目状态：ALTERPROCEDURE[dbo].[Proc_Checkbook]@bookIdvarchar(50),@bookStateint,@pagesint=nullASBEGIN if@bookState=2 --缺页 begin updateBorrowedsetFine_ticket=Fine_ticket+ (selectfnumfromFineKindwhereiFineKind=2)*@pages wheresBookId= (selecttop(1)sBookIdfromBorrowed wheresBookId=@bookIdorderbyReturn_Datedesc) updateBooksetiState=1wheresBookId=@bookId end elseif@bookState=4 --遗失 begin updateBorrowedsetFine_ticket=Fine_ticket+ (selectfnumfromFineKindwhereiFineKind=4)*(selectfPricefromBookwheresBookId=@bookId) wheresBookId= (selecttop(1)sBookIdfromBorrowed wheresBookId=@bookIdorderbyReturn_Datedesc) updateBooksetiState=4wheresBookId=@bookId endEND if@@error<>0or@@rowcount=0 rollbacktransaction;图书入库：ALTERPROCEDURE[dbo].[Proc_EnterStock]@orderIdvarchar(30),@stockstaffIdvarchar(30),@Authorvarchar(30)=null,@Isbnvarchar(15)=null,@Pricefloat=0,@RealNumintASBEGIN declare@StockIdvarchar(30) set@StockId=@orderId+'日期'+cast(GETDATE()asvarchar(10)) insertintoStockvalues(@StockId,@orderId,@Isbn,@price,@RealNum,GETDATE(),@Author,@stockstaffId,1) END if@@error<>0or@@rowcount=0 rollbacktransaction;登录：ALTERPROCEDURE[dbo].[Proc_LogIn]@idvarchar(50),@passwordvarchar(50),@posintoutput,@namevarchar(50)outputAS--BEGINIF@pos=0 BEGIN ifexists(select*fromReaderwheresReaderId=@idandsPassword=@passwordandLend_able=1) select@name=NamefromReaderwheresReaderId=@idandsPassword=@password else set@pos=-1 END ELSE BEGIN ifexists(select*fromManagerwheresManagerId=@idandsPassword=@password) select@pos=iLevel,@name=NamefromManagerwheresManagerId=@idandsPassword=@password /*设置pos的值，当显示管理员界面时根据级别不同而设置管理员可进行的操作*/ else set@pos=-1 END--END else set@pos=-1 END--END if@@error<>0or@@rowcount=0 rollbacktransaction;丢失书目：ALTERPROCEDURE[dbo].[Proc_lose]@bookIdvarchar(50),@readerIdvarchar(50)ASBEGIN updateBorrowedsetFine_ticket=(selectfnumfromFineKindwhereiFineKind=3)* (selectfpricefromBookwheresBookId=@bookId) wheresBookId= (selecttop(1)sBookIdfromBorrowed wheresBookId=@bookIdorderbyDeadLinedesc) updateBooksetiState=4wheresBookId=@bookId END if@@error<>0or@@rowcount=0 rollbacktransaction;下订单：ALTERPROCEDURE[dbo].[Proc_MakeOrder]@booknamevarchar(30),@pressvarchar(20)=null,@order_numint,@orderstaffIdvarchar(20),@stateint=nulloutputASBEGIN declare@orderidvarchar(30) set@orderid=@bookname+'日期'+cast(GETDATE()asvarchar(10)) ifexists(select*from[order]wheresOrderId=@orderid) set@state=0 else begin insertinto [dbo].[Order]values(@orderid,@bookname,@press,@order_num,GETDATE(),@orderstaffId) set@state=1 endEND if@@error<>0or@@rowcount=0 rollbacktransaction;修改密码：ALTERPROCEDURE[dbo].[Proc_Password_change]@old_pasvarchar(20),@new_pasvarchar(20),@userIdvarchar(30),@user_kindint,@stateintoutputASBEGIN IF@user_kind=1 --manager begin ifExists(select*fromManagerwheresManagerId=@userIdandsPassword=@old_pas) begin updateManagersetsPassword=@new_paswheresManagerId=@userId set@state=1 end else set@state=0 end ELSEif@user_kind=2 --reader begin ifExists(select*fromReaderwheresReaderId=@userIdandsPassword=@old_pas) begin updateReadersetsPassword=@new_paswheresReaderId=@userId set@state=1 end else set@state=0 endENDif@@error<>0or@@rowcount=0 rollbacktransaction; 充值：ALTERPROCEDURE[dbo].[Proc_paydebt]@sReaderIdvarchar(30),@moneyfloatASBEGIN declare@tmpfloat select @tmp=balancefromReaderwheresReaderId=@sReaderId if@tmpisnull updateReadersetbalance=@moneywheresReaderId=@sReaderId else updateReadersetbalance=balance+@moneywheresReaderId=@sReaderIdENDif@@error<>0or@@rowcount=0 rollbacktransaction;超期：ALTERPROCEDURE[dbo].[Proc_readerborrow]@sReaderIdvarchar(30)ASBEGIN select*fromBorrowed,BookwheresReaderId=@sReaderId andBook.sBookId=Borrowed.sBookId andReturn_DateisnullENDif@@error<>0or@@rowcount=0 rollbacktransaction;还书：ALTERPROCEDURE[dbo].[Proc_Return]@bookIdvarchar(50)ASBEGIN declare@balancefloat ifGETDATE()>(selectDeadLinefromBorrowedwheresBookId=@bookIdandReturn_Date=null) begin declare@tmpfloat declare@deadlinedate select@deadline=deadlinefromBorrowed wheresBookId=@bookIdandReturn_Date=null--该书的应还日期 select@tmp=fNumfromFineKindwhereiFineKind=1 set@tmp=@tmp*CAST((GETDATE()-@deadline)asint) updateBorrowedsetFine_ticket=@tmpwheresBookId=@bookId updateReadersetbalance=balance-@tmp wheresReaderId= (selecttop(1)sReaderIdfromBorrowed wheresBookId=@bookIdorderbyDeadLinedesc) end updateBorrowedsetReturn_date=GETDATE()wheresBookId=@bookId --设置还书日期 updateBooksetiState=1wheresBookId=@bookIdENDif@@error<>0or@@rowcount=0 rollbacktransaction;搜索书籍：ALTERPROCEDURE[dbo].[Proc_Select]@stringvarchar(200),@strvarchar(20),@ableint=0ASBEGIN declare@preint declare@lasint declare@tepstrvarchar(20) declare@sqlvarchar(500) set@sql='select sbookIdas图书编号, sBookNameas图书名称, sAuthoras作者, sISBNasISBN, sPressas出版社, sIndexas索引号, fpriceas价格, istateas是否可借, smemoas备注fromBookwheresindexlike''%'+@str+'%''' set@pre=0 set@las=1 while(@las<>0) begin set@pre=@pre+1 set@las=CharIndex('',@string,@pre) if(@las<>0) begin set@tepstr=LTRIM(substring(@string,@pre,@las-@pre)) if(@tepstr<>'') set@sql=@sql+'and(sBookNamelike''%'+@tepstr+'%''orsAuthorlike''%'+@tepstr+'%''orsmemolike''%'+@tepstr+'%''orsPresslike''%'+@tepstr+'%'')'; set@pre=@las end end set@tepstr=substring(@string,@pre,len(@string)+1-@pre) set@sql=@sql+'and(sBookNamelike''%'+@tepstr+'%''orsAuthorlike''%'+@tepstr+'%''orsmemolike''%'+@tepstr+'%''orsPresslike''%'+@tepstr+'%'')'; if@able=1set@sql=@sql+'andistate=1' exec(@sql)ENDif@@error<>0or@@rowcount=0 rollbacktransaction;搜索借阅数为前n的书ALTERPROCEDURE[dbo].[Proc_selectbylendtimes]@numintASBEGIN selecttop(@num)*,COUNT(*)lendtimesfrom(selectsBookName,sAuthor,sIndex,sISBN,sPress,sClassIdfromBook,BorrowedwhereBook.sBookId=Borrowed.sBookId)view1groupbysIndex,sBookName,sAuthor,sISBN,sPress,sClassIdorderbylendtimesdescENDif@@error<>0or@@rowcount=0 rollbacktransaction;搜索借阅次数排名在m到n之间的书ALTERPROCEDURE[dbo].[Proc_selectbylendtimes2]@num1int,@num2intASBEGIN select*from(selecttop(@num2-@num1+1)*from(selecttop(@num2)*,COUNT(*)lendtimesfrom(selectsBookName,sAuthor,sIndex,sISBN,sPress,sClassIdfromBook,BorrowedwhereBook.sBookId=Borrowed.sBookId)view1groupbysIndex,sBookName,sAuthor,sISBN,sPress,sClassIdorderbylendtimesdesc)aorderbylendtimes)borderbylendtimesdescENDif@@error<>0or@@rowcount=0 rollbacktransaction;图书上架：ALTERPROCEDURE[dbo].[Proc_Shelve]@Isbnvarchar(15),@sBookNamevarchar(50),@spressvarchar(30),@Authorvarchar(30),@classIdvarchar(20),@sMemovarchar(50),@stateint=1,@stockIdvarchar(30),@fpricefloatASBEGIN declare@iint declare@numint declare@bookIdvarchar(50) declare@Indexvarchar(50) set@i=0 set@Index=@classId+'.'+@Isbn --设置索书号 select@num=RealNumfromStockwhereStockId=@stockId begin while@i<@num begin set@bookId=@Index+'.'+cast(@iasvarchar(5))--设置图书号 insertintoBookvalues(@bookId,@Index,@Isbn,@sBookName,@spress,@Author,@classId, @sMemo,@state,@fprice) set@i=@i+1 end update[dbo].Stocksetstockstate=0whereStockId=@stockId endEND if@@erro