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

《数据库》课程设计课设名称：图书管理系统数据库设计与实现年级：专业：网络工程班级：姓名：学号：成绩：指导教师：颜颖提交报告时间： 2015 年 1 月 14 日数据需求图书馆管理信息系统需要完成功能主要有：1. 读者基本信息的输入，包括借书证编号、读者姓名、读者性别登记日期。

2．读者基本信息的查询、修改，包括读者借书证编号、读者姓名、性别等。

3．书籍类别标准的制定、类别信息的输入，包括类别编号、类别名称。

4．书籍类别信息的查询、修改，包括类别编号、类别名称。

5．书籍库存信息的输入，包括书籍编号、书籍名称、书籍类别编号、作者、出版社、出版日期、登记日期，价格，是否可借。

6．书籍库存信息的查询，修改，包括书籍编号、书籍名称、书籍类别编号、姓名、出版社、出版日期、登记日期、价格、是否可借等。

7．借书信息的输入，包括读者借书证号、书籍编号、借书日期，应还时间。

8．借书信息的查询、修改，包括借书证编号、读者编号、读者姓名、书籍编号、书籍名称、借书日期等。

9．还书信息的输入，包括借书证编号、书籍编号、还书日期。

10．还书信息的查询和修改，包括还书读者借书证编号、读者姓名、书籍编号、书籍名称、借书日期、还书日期等。

11．超期还书罚款输入，还书超出期限包括超出期限还书的读者借阅证号，书籍编号，罚款金额。

12.超期还书罚款查询，删除，包括读者借书证编号、读者姓名、书籍编号、书籍名称，应还时间，罚款金额，借阅时间，超期时间等事物需求(1)在读者信息管理部分,要求:a.可以查询读者信息。

b.可以对读者信息进行添加及删除的操作。

(2 )在书籍信息管理部分,要求:a.可以浏览书籍信息,要求:b.可以对书籍信息进行维护,包括添加及删除的操作。

(3)在借阅信息管理部分,要求:。

a.可以浏览借阅信息。

b.可以对借阅信息进行维护操作。

(4)在归还信息管理部分，要求:a.可以浏览归还信息b.对归还信息可修改维护操作(5)在管理者信息管理部分,要求:a.显示当前数据库中管理者情况。

b.对管理者信息维护操作。

(6)在罚款信息管理部分,要求:a.可以浏览罚款信息b.对罚款信息可以更新(7)在书籍类别管理部分，要求：A.可以浏览书籍类别信息B.对书籍类别信息可以更新(8)在系部信息管理部分，要求：B.可以系部信息B.对系部信息可以进行增删改操作关系模式(一)书籍类别（书籍类别编号，类别名称）(二)借阅者信息实体（借阅证号，姓名，性别，登记时期，读者类别）(三)学生实体（读者类别，学号，借阅数，专业，电话）(四)教师实体（读者类别，职位，工号，借阅数，电话）(五)书籍（书籍编号，书籍名称，书籍类别编号，作者，出版社，出版日期，价格，登记日期，是否可借）(六)借阅（借阅证号，书籍编号，借阅时间时间，应还时间）(七)还书（借阅证号，书籍编号，还书时间）(八)罚款（借阅证号，姓名，书籍名称，书籍编号，借阅时间，应还时间，还书时间，罚款金额）(九)系部（系部名称，系部编号）(十)读者类别表（读者类别编号，读者类别名称）E/R图总的信息实体E-R图数据字典表２-1book_sytle 书籍类别信息表表２-2system_readers借阅者信息表格表2-3system_books书籍信息表表2-4borrow_record 借阅记录信息表表2-6reader_fee 罚款记录信息表表2-7 system_student 学生实体信息表表2-8 system_teacher 教师实体信息表表2-9 System_department 系部信息表建表语句：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,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,readername 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_books(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_readers(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)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(readertypeno 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,profession 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('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','语言技能类') 表单查询：select * from book_style2.向system_books 表中插入数据：insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,price,borrowednum,totalnum)values('002','计算机组成原理','6','王爱英','清华大学出版社','2001-01-03','2003-11-15','','3','10');insert into system_books(bookid,bookname,bookstyleno,bookauthor,bookpub,bookpubdate,bookindate,price,borrowednum,totalnum)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)values('','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('20558','计算机体系结构','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('414','数据结构（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)values('3','中华历史年','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('4','日本文化','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('4','微观经济学','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('2','影视文学','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('0','探索宇宙奥秘','2','苏庆东','北京大学出版社','1999-02-28','2000-01-21','35','0','10');表单查询：select * from system_books3.向读者表中插入数据：insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('X05620207','陈飞','男','1','2005-9-23 14:23:56')insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('X05620206','张三','男','1','2005-09-30 13:24:')insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('X05620204','赵静','女','1','2005-09-27 11:24:')insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('X05620202','潘小虹','女','1','2005-09-30 13:24:')insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('008415','蒋伟','男','2','2004-04-30 09:24:')insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('001456','李风','女','2','2004-04-30 09:24:')表单查询：select * from system_readers4.（insert，update ,set）向借书记录表中加入数据：insert into borrow_record(bookid,readerid,borrowdate,shouldreturn) values('3','X05620207','2007-09-27 11:24:','2007-10-27 11:24:') update system_booksset isborrowed=0where bookid='3' and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values('4','X05620204','2014-09-03 10:24:','2014-10-03 10:24:') update system_booksset isborrowed=0where bookid='4' and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate,shouldreturn) values('4','001456','2014-09-03 10:24:','2014-12-03 10:24:')update system_booksset isborrowed=0.where bookid='4' and isborrowed='1'5．向学生表中加入数据insert into system_student(readerid,studentno,readertype,major,borrownum,phone)values('X05620207','01','1','计算机','30','90')insert into system_student(readerid,studentno,readertype,major,borrownum,phone)values('X05620206','02','1','计算机','30','90')insert into system_student(readerid,studentno,readertype,major,borrownum,phone)values('X05620202','01','1','哲学与社会','30','90')insert into system_student(readerid,studentno,readertype,major,borrownum,phone)values('X05620204','0','1','国际经济与贸易','30','90')(DELETE)删除学生操作：delete from system_studentwhere readerid='X05620204'系部信息表：6.向教师表中加入数据：insert into system_teacher(readerid,teacherno,readertype, profession,borrownum,phone)values('001456','','2','数据库教师','40','90')insert into system_teacher(readerid,teacherno,readertype,profession,borrownum,phone)values('008415','','2','C语言教师','40','90')7. (DISTINCT,多表查询)查询所有书所对应的类别：8.向罚款表中添加数据操作(超期1天罚款元)：【INSERT,多表查询，DATEDIFF(),GETDATE(),CONVERT(),ADD】insert intoreader_fee(readerid,readername,bookid,bookname,bookfee,borrowdate,shoul dreturn,returndate)select读者借书证编号,readername读者姓名,书籍编号,bookname书名,*(Datediff(day,convert(smalldatetime,borrowdate),getdate())-30)超过时间天数,borrowdate借书时间,shouldreturn应还时间,returndate还书时间from borrow_record,system_readers,system_books,return_record where=and=and Datediff(day,convert(smalldatetime,borrowdate),getdate())>=309.创建索引：【INDEX】create index keyindex on borrow_record(bookid,readerid)删除索引：DROP INDEX keyindex on borrow_record10．应用distinct查询表select distinct readeridfrom borrow_record11.应用COUNT统计表单数据:select COUNT(readerid)from borrow_recordgroup by readerid12.应用count统计某学生节约的书籍总数select COUNT(readerid)from borrow_recordwhere readerid='X05620201'13.多表查询（查询有借书的学生的学号，姓名，读者类型，可借数，专业以及所借书籍的编号）select,studentno,readername,,borrownum,majorfrom system_readers,system_studentwhere=14. 使用GROUP BY , HA VING子句（查询有借过书的同学的学号和剩余可借数）select distinct studentno , borrownumfrom system_studentgroup by studentno,borrownumhaving borrownum<3015.使用ORDER BY子句（查询书籍，以价格从低到高排序）select*from system_booksorder by price16.嵌套查询，引入IN的用法：select*from system_bookswhere bookpub in('北京大学出版社','清华大学出版社')查询出版社为“清华大学出版社”的书籍信息：select*from system_bookswhere bookid in(select bookid from system_bookswhere bookpub='清华大学出版社')17.创建视图（查询已借书籍的编号、名称以及借阅者信息）create view bookprod asselect,bookname,,readernamefrom system_books,system_readers,borrow_recordwhere=and borrow_record.readerid=system_readers.readerid18.使用SELECT语句，从视图表和基本表中查询并显示数据select*from bookprod19.查询谁借了什么书：select readername读者姓名,bookname书籍名称from borrow_record,system_books,system_readers where=and=截图：若指定查询姓名为“刘群”借的书在此基础上添加：and readername='刘群'如图：20.添加还书记录操作：1．首先还书要在还书纪录中添加一条还书纪录2．其次删除相应书本的借阅纪录3．最后在书库中标记该本书为1，表示归还了可供其他读者借阅【delete，update，set】insert into return_record(bookid,readerid,returndate)select bookid,readerid,getdate()from borrow_recordwhere bookid=’1’deletefrom borrow_recordwhere bookid=’1’update system_booksset isborrowed=1where bookid=’1’21.查询超期情况：select distinct读者借书证编号,readername读者姓名,书籍编号,bookname书名,*(Datediff(day,convert(smalldatetime,shouldreturn),getdate()))罚款金额,borrowdate借书时间,shouldreturn应还时间,Datediff(day,convert(smalldatetime,shouldreturn),getdate())超期时间from borrow_record,system_readers,system_books,return_recordwhere=and=and Datediff(day,convert(smalldatetime,shouldreturn),getdate())>0。