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

《数据库综合作业(图书管理系统)(共17页).docx》由会员分享，可在线阅读，更多相关《数据库综合作业(图书管理系统)(共17页).docx（17页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上图书馆数据管理系统一、图书馆数据管理系统的描述1、概念模型（E-R图）2、关系模式(1)书籍类别（种类编号、种类名称）(2)读者（借书证编号，读者姓名、读者性别、读者种类、登记时间）(3)书籍（书籍编号、书籍名称、书籍类别、书籍作者、出版社名称、出版日期、登记日期）(4)借阅（借书证编号、书籍编号、读者借书日期）(5)还书（借书证编号、书籍编号、读者还书时间）(6)罚款（借书证编号、读者姓名、书籍编号、读者借书时间、读者还书日期）二、图书馆数据库的建立-(1)书本类别表create table book_style(bookstyleno varchar(30) pr

2、imary 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, -登记日期is

3、borrowed 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)借书记录表c

4、reate 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, -书籍编

5、号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 k

6、ey, -书籍编号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,booksty

7、le)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)va

8、lues(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,book

9、author,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,bo

10、okstyleno,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,b

11、ookname,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_re

12、aders表中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,regda

13、te)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,readern

14、ame,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)-添加已借书读者的记录

15、导入borrow_record表中，同时将在已借出的标记0insert into borrow_record(bookid,readerid,borrowdate)values(,Q,2018-01-18 12:20)update system_booksset isborrowed = 0where bookid=and isborrowed=1insert into borrow_record(bookid,readerid,borrowdate)values(,Q,2018-01-19 13:15)update system_booksset isborrowed = 0where bo

16、okid=and isborrowed=1insert into borrow_record(bookid,readerid,borrowdate)values(,Q,2018-01-20 13:33)update system_booksset isborrowed = 0where bookid=and isborrowed=1insert into borrow_record(bookid,readerid,borrowdate)values(,Q,2018-01-21 12:01)update system_booksset isborrowed = 0where bookid=and

17、 isborrowed=1insert into borrow_record(bookid,readerid,borrowdate)values(,Q,2018-01-22 15:23)update system_booksset isborrowed = 0where bookid=and isborrowed=1insert into borrow_record(bookid,readerid,borrowdate)values(,2018-01-23 18:50)update system_booksset isborrowed = 0where bookid=and isborrowe

18、d=1insert 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.bookstyl

19、enogo -（2）查询所有穿越小说类的书 select distinct bookname 书籍名称,bookstyle 书籍类别 from book_style,system_books where book_style.bookstyleno = system_books.bookstyleno and system_books.bookstyleno=2go -（3）查询中国海关出版社出版的书 select distinct bookname 书籍名称,bookpub from book_style,system_books where bookpub=中国海关出版社go -(4)查询

20、白三借了什么书 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,re

21、adersex,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）查询当前借了三体却

22、没有借盛夏晚晴天的读者，输出其读者借书证编号，并按编号降序排列 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=b

23、orrow_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.re

24、aderid 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

25、_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)s

26、elect 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=

27、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_re

28、cord 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=

29、 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_read

30、ers 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语句的理解与运用，插入、删除、修改、查询以及表与表之间的联系，主键与外键的定义，约束项的定义等等，都让我养成了更加出色的逻辑分析能力。在完成设计的过程中，也通过网络查询了一些资料以增添自己贫瘠的创造能力，学以致用并不断完善，最终完成了这份数据库综合实践作业。专心-专注-专业