图书管理系统数据库设计——SQL

1、数据库课程设计课设名称： 图书管理系统数据库设计与实现 年 级： 专 业： 网络工程 班 级： 姓 名： 学 号： 成 绩： 指导教师： 颜 颖 提交报告时间： 2015 年 1 月 14 日数据需求图书馆管理信息系统需要完成功能主要有：1. 读者基本信息的输入，包括借书证编号、读者姓名、读者性别登记日期。2读者基本信息的查询、修改，包括读者借书证编号、读者姓名、性别等。3书籍类别标准的制定、类别信息的输入，包括类别编号、类别名称。4书籍类别信息的查询、修改，包括类别编号、类别名称。5书籍库存信息的输入，包括书籍编号、书籍名称、书籍类别编号、作者、出版社、出版日期、登记日期，价格，是否可借。6

2、书籍库存信息的查询，修改，包括书籍编号、书籍名称、书籍类别编号、姓名、出版社、出版日期、登记日期、价格、是否可借等。7借书信息的输入，包括读者借书证号、书籍编号、借书日期，应还时间。8借书信息的查询、修改，包括借书证编号、读者编号、读者姓名、书籍编号、书籍名称、借书日期等。9还书信息的输入，包括借书证编号、书籍编号、还书日期。 10还书信息的查询和修改，包括还书读者借书证编号、读者姓名、书籍编号、书籍名称、借书日期、还书日期等。11超期还书罚款输入，还书超出期限包括超出期限还书的读者借阅证号，书籍编号，罚款金额。12.超期还书罚款查询，删除，包括读者借书证编号、读者姓名、书籍编号、书籍名称，应

3、还时间，罚款金额，借阅时间，超期时间等事物需求(1)在读者信息管理部分,要求:a.可以查询读者信息。b.可以对读者信息进行添加及删除的操作。(2 )在书籍信息管理部分,要求:a.可以浏览书籍信息,要求:b.可以对书籍信息进行维护,包括添加及删除的操作。(3)在借阅信息管理部分,要求:。a.可以浏览借阅信息。b.可以对借阅信息进行维护操作。(4)在归还信息管理部分，要求:a.可以浏览归还信息b.对归还信息可修改维护操作(5)在管理者信息管理部分,要求:a.显示当前数据库中管理者情况。b.对管理者信息维护操作。(6)在罚款信息管理部分,要求:a.可以浏览罚款信息b.对罚款信息可以更新(7) 在书籍

4、类别管理部分，要求：A. 可以浏览书籍类别信息B.对书籍类别信息可以更新(8) 在系部信息管理部分，要求：B. 可以系部信息B.对系部信息可以进行增删改操作关系模式(一) 书籍类别（书籍类别编号，类别名称）(二) 借阅者信息实体（借阅证号，姓名，性别，登记时期，读者类别）(三) 学生实体（读者类别，学号，借阅数，专业，电话）(四) 教师实体（读者类别，职位，工号，借阅数，电话）(五) 书籍（书籍编号，书籍名称，书籍类别编号，作者，出版社，出版日期，价格，登记日期，是否可借）(六) 借阅（借阅证号，书籍编号，借阅时间时间，应还时间）(七) 还书（借阅证号，书籍编号，还书时间）(八) 罚款（借阅证

5、号，姓名，书籍名称，书籍编号，借阅时间，应还时间，还书时间，罚款金额）(九) 系部（系部名称，系部编号）(十) 读者类别表（读者类别编号，读者类别名称）E/R图总的信息实体E-R图数据字典表-1 book_sytle 书籍类别信息表表中列名数据类型可否为空说明bookstylenoVarchar(20)not null(主键)书籍类别编号bookstyleVarchar(30)not null种类名称表-2system_readers借阅者信息表格表中列名数据类型可否为空说明readeridVarchar(9)not null(主键)借阅证号readernameVarchar(9)not nu

6、ll读者姓名readersexVarchar(2)not null读者性别readertypeVarchar(10)Not null读者类别regdatedatetimenull登记日期表2-3system_books书籍信息表表中列名数据类型可否为空说明bookidVarchar(20)Not null(主键)书籍编号priceVarchar(6)Not null价格booknameVarchar(30)Not null书籍名称BookstylenoVarchar(20)Not null书籍类别编号bookauthorVarchar(30)Not null作者isborrowedVarcha

7、r(1)Not null是否可借bookpubVarchar(30)Null出版社bookpubdateDatetimeNull出版日期bookindateDatetimeNull登记日期表2-4borrow_record 借阅记录信息表表中列名数据类型可否为空说明readeridVarchar(9)Not null(外主键)借阅证号bookidVarchar(20)Not null(外主键)书籍编号borrowdatedatetimeNot null借阅时间shouldreturndatetime?应还时间表2-5return_record 还书记录信息表表中列名数据类型可否为空说明read

8、eridVarchar(9)Not null(外主键)借阅证号 bookidVarchar(20)Not null(外主键)书籍编号returndatedatetimeNot null读者还书时间表2-6reader_fee 罚款记录信息表readeridVarchar（9）Not null（外主键）借阅证号readernameVarchar(9)Not null读者姓名bookidVarchar(20)Not null(外主键)书籍编号booknameVarchar(30)Not null书籍名称bookfeeVarchar(10)Not Null罚款金额borrowdatedatetime

9、Not Null借阅时间shouldreturndatetime null应还时间exceeddatedatetime null超期时间表2-7 system_student 学生实体信息表表中列名数据类型可否为空说明studentnoVarchar(20)not null(主键)学号majorVarchar(30)not null专业borrownumIntnot null借阅数readertypeVarchar(10)Not null读者类别phoneVarchar(20)null电话表2-8 system_teacher 教师实体信息表表中列名数据类型可否为空说明teachertnoVa

10、rchar（20）not null(主键)工号ProfessionVarchar（20）null职位borrownumIntnot null借阅数readertypeVarchar（20）Not null读者类别phoneVarchar（20）null电话表2-9 System_department 系部信息表表中列名数据类型可否为空说明departmentnoVarchar(20)not null(主键)系部编号deparmentnameVarchar（20）not null系部名称表2-10system_resdertype读者类别表表中列名数据类型可否为空说明readertypenoVa

11、rchar(20)Not null(主键)读者类别编号ReadertypeVarchar(20)Not null读者类别名称建表语句：1.书本类别表建立create table book_style( bookstyleno varchar(20) primary key, bookstyle varchar(30) not null)2.书籍表建立create table system_books( bookid varchar(20) primary key, bookname varchar(20) Not null, bookstyleno varchar(30) Not null,

12、price varchar(6) not null, bookauthor varchar(30) not null, isborrowed varchar(1) not null, bookpub varchar(30) , bookpubdate datetime, bookindate datetime , foreign key (bookstyleno) references book_style (bookstyleno),)3.借阅者表建立create table system_readers ( readerid varchar(9) primary key, readerna

13、me varchar(9) not null , readersex varchar(2) not null, readertype varchar(10) not null, regdate datetime)4. 借书记录表建立create table borrow_record( bookid varchar(20) primary key, readerid varchar(9) not null, borrowdate datetime not null ,shouldreturn datetime, foreign key (bookid) references system_bo

14、oks(bookid), foreign key (readerid) references system_readers(readerid),)5.还书记录表建立create table return_record( bookid varchar(20) primary key, readerid varchar(9)not null, returndate datetime not null, foreign key (bookid) references system_books(bookid), foreign key (readerid) references system_read

15、ers(readerid)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(10) not null, borrowdate datetime not null, shouldreturn datetime, exceeddate varchar(5) , foreign key (bookid)

16、 references system_books(bookid), foreign key (readerid) references system_readers(readerid) )7.学生表建立create table system_student(studentno varchar(20) primary key,major varchar (30) not null,borrownum int not null,readertype varchar(20) ,phone varchar(20)8. 读者类别表建立create table system_readertype(read

17、ertypeno varchar(20) primary key,readertype varchar (20) not null）9.系别表建立 create table system_department(departmentno varchar(20) primary key,departmentname varchar (20) not null)10教师表建立create table system_teacher(readerid varchar(9)not null,teacherno varchar(20) primary key,borrownum int not null,p

18、rofession varchar(20),readertype varchar(20) not null,phone varchar(20),foreign key (readerid) references system_readers (readerid)数据初始化及表更新、查询1.向Book_style表中插入数据insert into book_style(bookstyleno,bookstyle)values('1','人文艺术类') insert into book_style(bookstyleno,bookstyle)values('

19、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,bo

20、okstyle)values('6','工程技术类')insert into book_style(bookstyleno,bookstyle)values('7','语言技能类')表单查询：select * from book_style2.向system_books 表中插入数据：insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum

21、 )values('00125415152','计算机组成原理','6','王爱英','清华大学出版社','2001-01-03','2003-11-15','35.5','3','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnu

22、m )values('00456456','数据库原理','6','萨师煊','高等教育出版社','2007-07-02','2007-09-15','40','4','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )val

23、ues('12215121','C程序设计','6','谭浩强','清华大学出版社','2002-04-02','2004-03-14','60','5','8');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values(

24、9;9787308020558','计算机体系结构','6','石教英','浙江大学出版社','2004-10-03','2006-11-15','60','5','8');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values(

25、9;45456141414','数据结构（C语言版）','6','吴伟民，严蔚敏','清华大学出版社','2002-06-28','2004-01-21','40','5','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )valu

26、es('545551523','中华历史年','1','吴强','北京大学出版社','2005-04-03','2006-05-15','56','0','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values(

27、9;151451424','日本文化','1','吴小鹏','北京大学出版社','2002-04-02','2004-03-14','35','0','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values('1515

28、46564','微观经济学','5','李小刚','北京大学出版社','2000-10-03','2001-11-15','35','0','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values('565833422

29、','影视文学','4','苏庆东','北京大学出版社','1999-02-28','2000-01-21','35','0','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values('565800020',

30、'探索宇宙奥秘','2','苏庆东','北京大学出版社','1999-02-28','2000-01-21','35','0','10');表单查询：select * from system_books3.向读者表中插入数据：insert into system_readers(readerid,readername,readersex,readertype,regdate)values('X05620207','陈飞'

31、,'男','1','2005-9-23 14:23:56')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('X05620206','张三','男','1','2005-09-30 13:24:54.623')insert into system_readers(readerid,readername,readersex,readertype,regd

32、ate)values('X05620204','赵静','女','1','2005-09-27 11:24:54.123')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('X05620202','潘小虹','女','1','2005-09-30 13:24:54.473')insert into system_read

33、ers(readerid,readername,readersex,readertype,regdate)values('008415','蒋伟','男','2','2004-04-30 09:24:54.478')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('001456','李风','女','2','2004-04-30

34、 09:24:54.478')表单查询：select * from system_readers4.（insert，update ,set）向借书记录表中加入数据：insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values('545551523','X05620207','2007-09-27 11:24:54.123','2007-10-27 11:24:54.123')update system_booksset isborrowed

35、=0 where bookid='545551523' and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values('151546564','X05620204','2014-09-03 10:24:54.123','2014-10-03 10:24:54.123')update system_booksset isborrowed=0where bookid='1515

36、46564' and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values('151451424','001456','2014-09-03 10:24:54.123','2014-12-03 10:24:54.123')update system_booksset isborrowed=0.where bookid='151451424' and isborrowed=&

37、#39;1'5向学生表中加入数据insert into system_student (readerid , studentno,readertype ,major ,borrownum ,phone )values('X05620207','1125111001','1','计算机','30','1234567890')insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )

38、values('X05620206','1125111002','1','计算机','30','1234567890')insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )values('X05620202','1122111001','1','哲学与社会','30','1234567890&#

39、39;)insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )values('X05620204','112011100','1','国际经济与贸易','30','1234567890')(DELETE)删除学生操作：delete from system_student where readerid= 'X05620204' 系部信息表：6.向教师表中加入数据：ins

40、ert into system_teacher(readerid , teacherno,readertype , profession ,borrownum ,phone )values('001456','12022301','2','数据库教师','40','1234567890')insert into system_teacher(readerid , teacherno,readertype ,profession ,borrownum ,phone )values('00841

41、5','12022302','2','C语言教师','40','1234567890')7. (DISTINCT,多表查询)查询所有书所对应的类别：8.向罚款表中添加数据操作(超期1天罚款0.3元)：【INSERT,多表查询，DATEDIFF(),GETDATE(),CONVERT(),ADD】insert into reader_fee(readerid,readername,bookid,bookname,bookfee,borrowdate,shouldreturn ,returndate )sele

42、ct system_readers.readerid 读者借书证编号 ,readername 读者姓名,system_books.bookid 书籍编号,bookname 书名,0.3*(Datediff(day,convert(smalldatetime,borrowdate),getdate()-30) 超过时间天数,borrowdate 借书时间, shouldreturn 应还时间 , returndate 还书时间 from borrow_record ,system_readers ,system_books ,return_record where system_readers.

43、readerid=borrow_record.readerid and system_books.bookid=borrow_record.bookidand Datediff(day,convert(smalldatetime,borrowdate),getdate()>=309.创建索引：【INDEX】create index keyindex on borrow_record (bookid,readerid)删除索引：DROP INDEX keyindex on borrow_record 10应用distinct查询表select distinct readerid from

44、borrow_record 11.应用COUNT统计表单数据: select COUNT (readerid) from borrow_record group by readerid12.应用count统计某学生节约的书籍总数select COUNT (readerid) from borrow_record where readerid ='X05620201'13.多表查询（查询有借书的学生的学号，姓名，读者类型，可借数，专业以及所借书籍的编号）select system_readers.readerid,studentno,readername,system_reade

45、rs.readertype,borrownum,majorfrom system_readers ,system_student where system_readers.readerid=system_student.readerid 14. 使用GROUP BY , HAVING子句（查询有借过书的同学的学号和剩余可借数）select distinct studentno , borrownumfrom system_student group by studentno ,borrownum having borrownum <3015. 使用ORDER BY子句（查询书籍，以价格从低到高排序）select *from system_books order by price 16.嵌套查询，引入IN的用法：select * from system_books where bookpub in ('北京大学出版社','清华大学出版社')查询出版社为“清华大学出版社”的书