数据库综合作业(图书管理系统)(共17页).docx

精选优质文档-倾情为你奉上图书馆数据管理系统一、图书馆数据管理系统的描述1、概念模型（E-R图）2、关系模式(1)书籍类别（种类编号、种类名称）(2)读者（借书证编号，读者姓名、读者性别、读者种类、登记时间）(3)书籍（书籍编号、书籍名称、书籍类别、书籍作者、出版社名称、出版日期、登记日期）(4)借阅（借书证编号、书籍编号、读者借书日期）(5)还书（借书证编号、书籍编号、读者还书时间）(6)罚款（借书证编号、读者姓名、书籍编号、读者借书时间、读者还书日期）二、图书馆数据库的建立-(1)书本类别表create table book_style(bookstyleno varchar(30) primary key,-种类编号bookstyle varchar(30) ) -种类名称go-(2)书库表create table system_books(bookid varchar(20) primary key, -书籍编号bookname varchar(30) not null, -书籍名称bookstyleno varchar(30) not null, -书籍种类bookauthor varchar(30), -书籍作者bookpub varchar(30), -出版社bookpubdate datetime, -出版日期bookindate datetime, -登记日期isborrowed varchar(2), -是否借出foreign key(bookstyleno)references book_style(bookstyleno)go-(3)借书证表create table system_readers (readerid varchar(9) primary key, -读者借书证号readername varchar(9) not null, -读者姓名readersex varchar(2) not null, -读者性别readertype varchar(10), -读者种类regdate datetime) -登记日期go-(4)借书记录表create table borrow_record(bookid varchar(20) primary key, -书籍编号readerid varchar(9), -读者借书证编号borrowdate datetime, -读者借书时间foreign key(bookid)references system_books(bookid),foreign key(readerid)references system_readers(readerid)go-(5)还书记录表create table return_record(bookid varchar(20)primary key, -书籍编号readerid varchar(9), -读者借书证编号returndate datetime, -读者还书时间foreign key(bookid)references system_books(bookid),foreign key(readerid)references system_readers(readerid)go-(6)罚款单表create table reader_fee(readerid varchar(9) not null, -读者借书证号readername varchar(9)not null, -读者姓名bookid varchar(20) primary key, -书籍编号bookname varchar(30) not null, -书籍名称bookfee varchar(30), -罚款金额borrowdate datetime, -借书时间foreign key(bookid)references system_books(bookid),foreign key(readerid)references system_readers(readerid)go数据库的表的截图:三、图书馆数据库数据的添加use library-书籍类别导入book_style表中insert into book_style(bookstyleno,bookstyle)values('1','恐怖小说')insert into book_style(bookstyleno,bookstyle)values('2','穿越小说')insert into book_style(bookstyleno,bookstyle)values('3','恐怖小说')insert into book_style(bookstyleno,bookstyle)values('4','都市小说')insert into book_style(bookstyleno,bookstyle)values('5','科幻小说')insert into book_style(bookstyleno,bookstyle)values('6','仙侠小说')insert into book_style(bookstyleno,bookstyle)values('7','言情小说')-已有书籍导入system_books表中insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('','飘邈之旅','1','萧乾','向往','2015-09-01','2018-03-25','1')insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('','明朝那些事儿','2','多一半','新星出版社','2015-05-09','2018-05-23','1')insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('','鬼吹灯','3','天下霸唱','安徽文艺出版社','2014-09-18','2018-05-27','1')insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('','盛夏晚晴天','4','顾漫','中国海关出版社','2015-09-01','2018-05-28','1')insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('','三体','5','刘慈欣','凤凰出版社','2015-10-11','2018-05-29','1')insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('','凡人修仙传','6','三十','凡人出版社','2013-04-21','2018-05-30','1')insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,isborrowed)values('','霸道总裁爱上我','7','桐华','民族出版社','2012-06-20','2018-05-31','1')-将已有图书证的读者加入system_readers表中insert into system_readers(readerid,readername,readersex,readertype,regdate)values('Q','白一','男','学生','2018-01-18 12:20')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('Q','白二','男','学生','2018-01-19 13:15')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('Q','白三','男','学生','2018-01-20 13:33')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('Q','白四','男','学生','2018-01-21 12:01')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('Q','白五','男','学生','2018-01-22 15:23')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('','黑五','男','教师','2018-01-23 18:50')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('','黒六','男','教师','2018-01-24 18:25')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('GL001','空一','女','管理','2018-01-01 16:20')-添加已借书读者的记录导入borrow_record表中，同时将在已借出的标记0insert into borrow_record(bookid,readerid,borrowdate)values('','Q','2018-01-18 12:20')update system_booksset isborrowed = 0where bookid=''and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate)values('','Q','2018-01-19 13:15')update system_booksset isborrowed = 0where bookid=''and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate)values('','Q','2018-01-20 13:33')update system_booksset isborrowed = 0where bookid=''and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate)values('','Q','2018-01-21 12:01')update system_booksset isborrowed = 0where bookid=''and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate)values('','Q','2018-01-22 15:23')update system_booksset isborrowed = 0where bookid=''and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate)values('','','2018-01-23 18:50')update system_booksset isborrowed = 0where bookid=''and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate)values('','','2018-01-24 18:25')update system_booksset isborrowed = 0where bookid=''and isborrowed='1'四、图书馆数据库的查询 -(1)查询所有书对应的类型 select distinct bookname 书籍名称,bookstyle 书籍类别 from book_style,system_books where book_style.bookstyleno = system_books.bookstylenogo -（2）查询所有穿越小说类的书 select distinct bookname 书籍名称,bookstyle 书籍类别 from book_style,system_books where book_style.bookstyleno = system_books.bookstyleno and system_books.bookstyleno='2'go -（3）查询中国海关出版社出版的书 select distinct bookname 书籍名称,bookpub from book_style,system_books where bookpub='中国海关出版社go' -(4)查询白三借了什么书 select readername 读者姓名,bookname 书籍名称 from borrow_record,system_books,system_readers where system_readers.readerid = borrow_record.readerid and system_books.bookid = borrow_record.bookid and readername='白三'Go -（5）查询同名同姓读者，同时统计同名同姓人数（先插入一个同名同姓读者） insert into system_readers(readerid,readername,readersex,readertype,regdate) values('Q','白五','男','学生','2018-01-22 15:26') select readername 读者姓名,count(readername)as'同名人数' from system_readers group by readername having count(*)>1go -(6)查询书名包括“盛”关键词的图书，输出书籍编号、书籍名称、作者 select bookstyleno,bookname,bookauthor from system_books where bookname like '%盛%'go -（7）查询当前借了三体却没有借盛夏晚晴天的读者，输出其读者借书证编号，并按编号降序排列 select system_books.bookid 书籍编号,bookname 书籍名称,bookauthor 作者,readerid 读者借书证编号 from system_books,borrow_record where system_books.bookid=borrow_record.bookid and bookname='三体' and readerid not in (select readerid from system_books,borrow_record where system_books.bookid=borrow_record.bookid and bookname='盛夏晚晴天')go -(8)创建一个视图account,显示所有学生的借书信息（只显示姓名和书名） create view account as select system_readers.readerid,system_books.bookname from borrow_record,system_books,system_readers where borrow_record.bookid = system_books.bookid and borrow_record.readerid = system_readers.readerid and system_readers.readertype='学生' go select * from account-（9）查询已借书的读者借书是否超期（30天）select system_readers.readerid 读者借书证编号,readername 读者姓名,system_books.bookid 书籍编号,bookname 书名,borrowdate 借书时间,datediff(day,convert(smalldatetime,borrowdate),getdate()-30 超过时间from borrow_record,system_readers, system_bookswhere system_readers.readerid=borrow_record.readeridand system_books.bookid=borrow_record.bookidand datediff(day,convert(smalldatetime,borrowdate),getdate()>=30go-（10）对超过天数的读者进行罚款，一天以0.3结算，添加进reader_fee表里，再进行select查询insert into reader_fee(readerid,readername,bookid,bookname,bookfee,borrowdate)select system_readers.readerid 读者借书证编号,readername 读者姓名,system_books.bookid 书籍编号,bookname 书名,borrowdate 借书时间,0.3*(datediff(day,convert(smalldatetime,borrowdate),getdate()-30) 超过时间from borrow_record,system_readers, system_bookswhere system_readers.readerid=borrow_record.readeridand system_books.bookid=borrow_record.bookidand datediff(day,convert(smalldatetime,borrowdate),getdate()>=30goselect readerid 书读者借书证编号,readername 读者姓名,bookid 书籍编号,bookfee 超期罚款from reader_fee五、图书馆数据库的更新 -（一）还书操作 - 1、添加一条还书记录 insert into return_record(bookid,readerid,returndate) select bookid,readerid,getdate() from borrow_record where bookid='' go select bookid 书籍编号,readerid 读者借书证编号,returndate 归还时间 from return_record -2、删除相应书籍的借书记录 delete from borrow_record where bookid='' go select bookid 书籍编号,readerid 读者借书证编号,borrowdate 归还时间 from borrow_record -3在书库中标记该书籍为1，表示已归还，其他读者可借阅 update system_books set isborrowed=1 where bookid='' go select bookid 书籍编号,bookname 书籍名称,isborrowed 是否借出 from system_books where bookid='' -（二）借书证 -1、申请借书证 insert into system_readers(readerid,readername,readersex,readertype,regdate) values('Q','白六','女','学生',getdate() go select * from system_readers where readerid='Q' -2、注销借书证(借书和归还记录都要删除) delete from system_readers where readerid='Q' go delete from return_record where readerid='Q' Go -确认是否删除 select * from system_readers where readerid='Q'六、总结与分析通过这次数据库的综合作业设计，经过独立的思考以及运用，增强了对数据库应用方面的理解。当然与此同时，也发现了自己的许多不足，对于知识点掌握的不够纯熟，思考以及运用得时候都略嫌生疏，还是需要多加练习，积累一些属于自己的经验。在数据库设计过程中，队数据库设计理念以及思想上有了更深层次的认识，从需求分析到概念设计，E-R模型的设计（由于WPS的模板功能的限制：只能使用60个约束条件，导致数据库的E-R模型中的读者信息实体无法展示出其各个属性，致使E-R模型图有些瑕疵，敬请见谅），关系模式的建立，弄清了不少之前有些模糊的概念。而在数据库的查询与更新中，更加深了对于SQL语句的理解与运用，插入、删除、修改、查询以及表与表之间的联系，主键与外键的定义，约束项的定义等等，都让我养成了更加出色的逻辑分析能力。在完成设计的过程中，也通过网络查询了一些资料以增添自己贫瘠的创造能力，学以致用并不断完善，最终完成了这份数据库综合实践作业。专心-专注-专业