《数据库》课程设计课设名称: 图书管理系统数据库设计与实现年级:专业: 网络工程班级:姓名:学号:成绩:指导教师: 颜颖提交报告时间: 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 书籍类别信息表表2-4borrow_record 借阅记录信息表表2-5return_record 还书记录信息表表2-6reader_fee 罚款记录信息表表2-7 system_student 学生实体信息表表2-8 system_teacher 教师实体信息表表2-10system_resdertype读者类别表建表语句: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('','计算机组成原理','6','王爱英','清华大学出版社','2001-01-03','2003-11-15','35、5','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('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('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('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)values('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('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('151546564','微观经济学','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','影视文学','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','探索宇宙奥秘','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:54、623') insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('X05620204','赵静','女','1','2005-09-27 11:24:54、123') insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('X05620202','潘小虹','女','1','2005-09-30 13:24:54、473') insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('008415','蒋伟','男','2','2004-04-30 09:24:54、478')insert intosystem_readers(readerid,readername,readersex,readertype,regdate) values('001456','李风','女','2','2004-04-30 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=0where 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='151546564' 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='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)values('X05620206','1125111002','1','计算机','30','1234567890')insert into system_student(readerid,studentno,readertype,major,borrownum,phone)values('X05620202','1122111001','1','哲学与社会','30','1234567890') insert into system_student(readerid,studentno,readertype,major,borrownum,phone)values('X05620204','112011100','1','国际经济与贸易','30','1234567890')(DELETE)删除学生操作:delete from system_studentwhere readerid='X05620204'系部信息表:6、向教师表中加入数据:insert 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('008415','12022302','2','C语言教师','40','1234567890')7、(DISTINCT,多表查询)查询所有书所对应的类别:8、向罚款表中添加数据操作(超期1天罚款0、3元):【INSERT,多表查询,DA TEDIFF(),GETDA TE(),CONVERT(),ADD】insert intoreader_fee(readerid,readername,bookid,bookname,bookfee,borrowdate,shoul dreturn,returndate)select 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_recordwhere system_readers、readerid=borrow_record、readeridand 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_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 system_readers、readerid,studentno,readername,system_readers、readertype,borrownum,major from system_readers,system_studentwhere system_readers、readerid=system_student、readerid14、使用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 system_books、bookid,bookname,system_readers、readerid,readernamefrom system_books,system_readers,borrow_recordwhere system_books、bookid=borrow_record、bookid and borrow_record、readerid=system_readers、readerid18、使用SELECT语句,从视图表与基本表中查询并显示数据select*from bookprod19、查询谁借了什么书:select readername读者姓名,bookname书籍名称from borrow_record,system_books,system_readerswhere system_readers、readerid=borrow_record、readeridand system_books、bookid=borrow_record、bookid截图:若指定查询姓名为“刘群”借的书在此基础上添加: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=’111111111’deletefrom borrow_recordwhere bookid=’111111111’update system_booksset isborrowed=1where bookid=’111111111’21、查询超期情况:select distinct system_readers、readerid读者借书证编号,readername读者姓名,system_books、bookid书籍编号,bookname书名,0、3*(Datediff(day,convert(smalldatetime,shouldreturn),getdate()))罚款金额,borrowdate借书时间,shouldreturn应还时间,Datediff(day,convert(smalldatetime,shouldreturn),getdate())超期时间from borrow_record,system_readers,system_books,return_recordwhere system_readers、readerid=borrow_record、readeridand system_books、bookid=borrow_record、bookidand Datediff(day,convert(smalldatetime,shouldreturn),getdate())>0。