数据库图书管理系统课程设计.doc

课程设计报告课程设计题目：图书借阅管理系统地设计与实现专 业：信息工程班 级：1122301姓 名：胡慧斌学 号: 201820230105指导教师:罗亚宾2018年1月 4日一实验题目图书借阅管理系统数据库地设计与实现二.实验时间.地点 软件楼601,2018年1月56号三. 实验目地通过本次课程设计让学生能够综合运用所学地关系数据库原理知识解决并能设计一个实际问题,进一步掌握数据库原理地相关理论和数据库地设计实现过程,进一步提高学生地分析问题和解决问题地能力以及学生地动手能力.b5E2RGbCAP四. 实验要求1对各个系统进行系统功能需求分析2. 数据库设计分析阶段,进行详细地数据库需求分析,进行概念数据库地设计,画出数据库地E-R图局部和整体E-R图）p1EanqFDPw3. 设计出详细地逻辑数据库结构,将各个实体和联系转化为相应地二维表即关系模式,指定各个关系地主关键字和外部关键字,并对各个关系地约束加以限定DXDiTa9E3d4. 通过企业管理器或是查询分析器实现各个二维关系建议最好用SQL代码实现）,要求建立相关地索引5. 根据系统功能需求设计相应地查询视图6. 要求根据系统功能需求建立存储过程7. 根据功能需求建立相应地触发器以保证数据地一致性8. 通过建立用户和权限分配实现数据库一定地安全性,考虑数据库地备份与恢复此内容选作）五. 实验思路1系统需求分析：A. 系统目标：本系统将实现用计算机管理图书借阅地功能.具体完成图书信息管理、读者信息管理、及图书借阅管理等功能.RTCrpUDGiTB. 系统地功能需求：本系统用户分为读者用户和管理员用户两类,包括对图书信息、读者信息、借阅信息地录入、修改、查询、删除等.读者用户只具有查询书籍和自己信息地权限.5PCzVD7HxAC. 系统功能描述：系统开发地主要目地是实现图书馆信息管理地系统化、规范化和自动化.图书馆信息管理系统主要提供对图书信息地管理及读者信息地管理.需要完成地主要功能有：jLBHrnAILg1、 有关读者信息地输入、修改和查询；2、 图书信息地输入、修改和查询；3、 借阅信息地维护读者借书证号readernamevarcharnot null读者姓名readersexvarcharnot null读者性别readertypevarcharnot null读者种类regdatedatetimenull登记日期booktotalintnull可借书数borrowednumintnull已借书数overduenoreturnnumintnull逾期未还书数表中列名数据类型可否为空说明bookidVarcharNot null(主键书籍编号booknameVarcharNot null书籍名称表3-2system_book书籍信息表bookstyleVarcharNot null书籍类别bookauthorVarcharNot null书籍作者bookpubVarcharNull出版社名称bookpubdateDatetimeNull出版日期bookindateDatetimeNull登记日期isborrowedBitNot Null是否被借出表3-3borrow_record 借阅记录信息表表中列名数据类型可否为空说明readeridVarcharNot null(外主键读者借阅证编号bookidVarcharNot null(外主键书籍编号borrowdateDatetimeNot null读者借书时间表3-4return_record 借阅记录信息表表中列名数据类型可否为空说明readernameVarcharNot null(外主键读者借阅证编号readeridVarcharNot null(外主键书籍编号returndatedatetimeNot null读者还书时间表3-5reader_fee 罚款记录信息表表中列名数据类型可否为空说明readeridvarcharNot null读者借书证编号readernamevarcharNot null读者姓名bookidvarcharNot null(外主键书籍编号booknamevarcharNot null书籍名称bookfeeSmallmoneyNot Null罚款金额borrowdatedatetimeNot Null借阅时间4.关系模式(1读者书籍借阅还书罚款log on-指明事务日志文件( name = library, filename = d:librarysystem.ldf,-指定存储地路径名和文件名LDAYtRyKfE size = 5mb, maxsize = 25mb, filegrowth = 5mb 建立图书信息表create table system_books (bookid varchar(20primary key , -书籍编号为主键bookname varchar(30 Not null, -图书名称bookstyle varchar(30 Not null,-图书类型bookauthor varchar(30, -图书作者bookpub varchar(30 , -出版社bookpubdate datetime, -出版时间bookindate datetime , -收录时间isborrowed bit, -是否被借建立读者信息表create table system_readers (readerid varchar(9primary key,-借书证号为主键readername varchar(10 not null,-读者姓名readersex char(2not null,-性别readertype varchar(20not null,-读者类型regdate datetime,-登记时间booktotal int,-可借书数borrowednum int,-已借书数overduenoreturnnum int-过期末还书数建立借书记录表create table borrow_record( bookid varchar(20 primary key,-书籍编号 readerid varchar(9,-借书证号 borrowdate datetime,-借书时间 foreign key (bookid references system_books(bookid,-定义书籍编号为借书表地外键Zzz6ZB2Ltk foreign key (readerid references system_readers(readerid,-定义借书证号为借书表地外键dvzfvkwMI1建立还书记录表create table return_record( bookid varchar(20 primary key,-书籍编号为主键 readerid varchar(9,-借书证号 returndate datetime,-归还时间 foreign key (bookid references system_books(bookid,-定义书籍编号外键rqyn14ZNXI foreign key (readerid references system_readers(readerid-定义借书证号为外键EmxvxOtOco 建立罚款单表create table reader_fee( readerid varchar(9not null,-借书证号 readername varchar(9not null ,-读者姓名 bookid varchar(20 primary key,-借书证号 bookname varchar(30 Not null, -书籍名称 bookfee smallmoney,-罚款金额 borrowdate datetime,-借阅时间 foreign key (bookid references system_books(bookid,- 定义书籍编号外键SixE2yXPq5 foreign key (readerid references system_readers(readerid- 定义借书证号为外键6ewMyirQFL索引地创建create index borrow_record_index_readerid on borrow_record(readerid为借阅表在借书证号上建索引kavU42VRUscreate index reader_fee_index_readerid on reader_fee (readerid为罚款表在借书证号上建索引y6v3ALoS89create index return_record_index_readerid on return_record(readerid- 为归还表在借书证号上建索引M2ub6vSTnPcreate index system_books_index_bookname on system_books(bookname为图书信息表在书籍编号上建索引0YujCfmUCwcreate index system_books_index_bookauthor on system_books(bookauthor-为图书信息表在作者上建索引eUts8ZQVRd创建视图create view b1_system_booksas select bookid,bookstyle,bookpub,booknamefrom system_bookscreate view r1_system_readersas select readerid,readername,readertypefrom system_readerscreate view b2_borrow_recordas select bookid, readeridfrom borrow_record建立借书记录存贮过程create procedure sp_borrowbook bookid char(20, readerid char(9 asinsert into borrow_record (bookid,readerid,borrowdatesQsAEJkW5Tvalues(bookid,readerid,getdate(go-建立还书记录存贮过程create procedure sp_returnbook bookid char(20,readerid char(9GMsIasNXkAasdelete from retun_recordwhere bookid=bookid and readerid=readeridgo-建立罚款单存贮过程create procedure sp_ readerfee readerid char(9, borrowdate datetimeTIrRGchYzgasupdate reader_feeset bookfee=0.1*(day(getdate(-day(borrowdatewhere reader=readeridand borrowdate=borrowdate-建立借书记录表增加触发器create trigger tr_addlend on borrow_recordfor insertasupdate system_booksset isborrowed =1where bookid in (select bookid from insertedupdate readerset borrowednum = borrowednum +1where readerid in (select readerid from inserted-建立还书记录删除触发器create trigger tr_dellend on return_recordfor deleteasupdate system_booksset isborrowed =0where bookid in (select bookid from deletedupdate system_readersset borrowednum = borrowednum -1where readerid in (select readerid from deleted-建立删除图书触发器create trigger tr_dellendbook on system_booksfor deleteasdelete reader_feewhere bookid in (select bookid from deleteddelete return _recordwhere bookid in (select bookid from deleted-信息地录入insert into system_books(bookid ,bookname,bookstyle,bookauthor,bookpub,bookpubdate, bookindate, isborrowed 7EqZcWLZNXvalues(00125415152,计算机组成原理,王爱英, 工科类,清华大学出版社,2001-01-03,2003-11-15,1lzq7IGf02Einsert into system_readers(readerid,readername,readersex,readertype,regdate,booktotal,borrowednum,overduenoreturnnumzvpgeqJ1hkvalues(201820180105,张三,男,学生,2018-1-6,8,2,0NrpoJac3v1insert into borrow_record(bookid,readerid,borrowdate1nowfTG4KIvalues (00125415152,201820230105,2018-12-18insert into return_record (bookid,readerid,returndatefjnFLDa5Zovalues (00125415152,201820230105,2018-1-18-信息地修改update system_booksset bookname=王英where bookid=00125415152update system_readers set readersex=女where