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

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

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

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

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

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

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

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

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

9、atetimenull应还时间exceeddatedatetimenull超期时间表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教师实体信息表表中列名数据类型可否为空说明teachertnoVarchar (20)not null(主键)工号Profes

10、sionVarchar (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 读者类别表表中列名数据类型可否为空说明readertypenoVarchar(20)Not null(主键)读者

11、类别编号ReadertypeVarchar(20)Not null读者类别名称建表语句:1.书本类别表建立create tablebook_stylevarchar (20) primary key , varchar ( 30) not nullsystem_books(bookstyleno bookstyle)2.书籍表建立create tablebookid varchar ( 20 ) primary keybookname varchar ( 20 ) Not null,bookstyleno varchar ( 30 ) Not null,price varchar ( 6) n

12、ot null, bookauthorvarchar( 30 ) not null,isborrowedvarchar( 1 ) not null,bookpub varchar ( 30 )bookpubdate datetimebookindate datetimeforeign key ( bookstyleno ) references book_style ( bookstyleno ), )3.借阅者表建立create tablesystem_readers( readerid varchar ( 9) primary key readername varchar ( 9) not

13、 null readersex varchar ( 2) not null, readertype varchar ( 10 ) not null, regdate datetime)4 . 借书记录表建立create table borrowrecordreaderidvarchar ( 9)notnull,borrowdatedatetimenotnull ,shouldreturndatetime ,foreignkey ( bookid )referencessystem_books ( bookid ),foreignkey ( readerid)referencessystem_r

14、eaders( readerid( bookid varchar ( 20 ) primary key),)5 .还书记录表建立create table returnrecord( bookid varchar ( 20 ) primary key , readerid varchar ( 9)not null,returndatedatetimenot null,foreignkey ( bookid) referencessystem_books( bookid ),foreignkey ( readerid) referencessystem_readers( readerid)6 .

15、罚款单表建立*/ create table readerfee( 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 syste

16、m_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 tablesystem_readertype(keykey n

17、ullreadertypenovarchar ( 20 ) primaryreadertype varchar ( 20 ) not null )9 .系别表建立create tablesystem_department(departmentnovarchar ( 20 ) primarydepartmentname varchar ( 20 ) not )10教师表建立create tablesystem_teacherreaderidvarchar ( 9)notnull,teachernovarchar ( 20 )borrownumint notnull,professionvarch

18、ar(20 ),readertypevarchar(20 )phone varchar ( 20 ), foreignkey ( readerid )primary key ,not null,referencessystem_readers( readerid()数据初始化及表更新、查询1 .向 Book_style 表中插入数据, bookstyle) values( '1, bookstyle) values( '2人文艺术类' )自然科学类' )insertinto book_style( bookstylenoinsertinto book_style

19、( bookstylenoinsertintobook_style( bookstyleno, bookstyle) values( '3', ' 社会科学类')insertintobook_style( bookstyleno, bookstyle) values( '4', ' 图片艺术类')insertintobook_style( bookstyleno, bookstyle) values( '5', ' 政治经济类')insertintobook_style( bookstyleno,

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

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

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

23、, 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 ,

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

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

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

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

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

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

30、#39; , '10' );insert into system_books ( bookid , bookname , bookstyleno , bookauthor , bookpub , bookpubdate , bookindate price , borrowednum , totalnum )values (, ' 探索宇宙奥秘' , '2' , ' 苏庆东 ' , ' 北京大学出版社' , '1999-02-28', '2000-01-21', '35

31、9; , '0' , '10' );表单查询： select * from system_books3. 向读者表中插入数据：insert into system_readers( readerid , readername , readersex, readertype , regdate)values ( 'X05620207', ' 陈飞 ' , ' 男' , '1' , '2005-9-23 14:23:56')insert into system_readers( read

32、erid , readername , readersexvalues ( 'X05620206', ' 张三 ' , ' 男' , '1' , '2005-09-30 13:24:', readertype , regdate )insert into system_readers( readerid , readername , readersexvalues ( 'X05620204', ' 赵静 ' , ' 女' , '1' , '20

33、05-09-27 11:24:', readertype , regdate )insert into system_readers( readerid , readername , readersexvalues ( 'X05620202', ' 潘小虹' , ' 女' , '1' , '2005-09-30 13:24:', readertype , regdate )insert into system_readers( readerid , readername , readersexvalues

34、( '008415', ' 蒋伟 ' , ' 男' , '2' , '2004-04-30 09:24:'), readertype , regdate)insert into system_readers( readerid , readername , readersexvalues ( '001456', ' 李风 ' , ' 女' , '2' , '2004-04-30 09:24:')表单查询： select * from s

35、ystem_readers4. ( insert ， update ,set )向借书记录表中加入数据：, readertype , regdate)insert into borrow_record( bookid , readerid , borrowdate ,values (, 'X05620207', '2007-09-27 11:24:', '2007-10-27 11:24:'update system_books set isborrowed=0where bookid = and isborrowed='1'sh

36、ouldreturn)insert into borrow_record( bookid , readerid , borrowdate ,values (, 'X05620204', '2014-09-03 10:24:', '2014-10-03 10:24:'update system_booksset isborrowed=0where bookid = and isborrowed='1'shouldreturn)insert into borrow_record( bookid , readerid , borrowd

37、ate ,values (, '001456', '2014-09-03 10:24:', '2014-12-03 10:24:'shouldreturn)update system_booksset isborrowed=0.where bookid = and isborrowed='1'5向学生表中加入数据insert into system_student ( readerid ,studentno , readertype , major , borrownum , phone )values ( 'X05620

38、207', '1' , ' 计算机 ' , '30' ,)insert into system_student( readerid , studentno , readertype , major , borrownum , phone )values ( 'X05620206', '1' , ' 计算机 ' , '30' ,)insert into system_student( readerid , studentno , readertype , major , bor

39、rownum , phone )values ( 'X05620202', '1' , ' 哲学与社会 ' , '30' ,)insert into system_student( readerid , studentno , readertype , major , borrownum , phone )values ( 'X05620204', '1' , ' 国际经济与贸易 ' , '30' ,)(DELETE) 删除学生操作：delete from syste

40、m_studentwhere readerid = 'X05620204'系部信息表：6 . 向教师表中加入数据：insert into system_teacher ( readerid , teacherno , readertype , profession , borrownum , phone )values ( '001456', '2' , ' 数据库教师' , '40' ,)insert into system_teacher ( readerid ,teacherno , readertype ,

41、 profession , borrownum , phone )values ( '008415', '2' , 'C 语言教师 ' , '40' ,)7 . (DISTINCT, 多表查询)查询所有书所对应的类别：8 .向罚款表中添加数据操作(超期1 天罚款元 ) ：【 INSERT, 多表查询， DATEDIFF(),GETDATE(),CONVERT(),ADD 】insert intoreader_fee ( readerid , readername , bookid , bookname , bookfee , bo

42、rrowdate , shouldret urn , returndate )select 读者借书证编号, readername 读者姓名 ,书籍编号 , bookname 书名 ,*( Datediff ( day , convert ( smalldatetime , borrowdate ), getdate ()-30 ) 超过时间天数borrowdate 借书时间 , shouldreturn 应还时间 , returndate 还书时间fromborrow_record, system_readers , system_books , return_recordwhere=and

43、 =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）fr

44、om 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 , HAVING 子句（查询有借过书的同学的学号和剩余可借数）select distinct studentno , borrownumfrom system_studentgroup b