图书管理系统数据库设计——SQL(12页).doc

-第 1 页图书管理系统数据库设计SQL-第 1 页数据库课程设计课设名称：课设名称：图书管理系统数据库设计与实现图书管理系统数据库设计与实现年年级：级：专专业：业：网络工程网络工程班班级：级：姓姓名：名：学学号：号：成成绩：绩：指导教师：指导教师：颜颜颖颖提交报告时间：提交报告时间：20152015年年1 1月月1414日日数据需求数据需求图书馆管理信息系统需要完成功能主要有：1.读者基本信息的输入，包括借书证编号、读者姓名、读者性别登记日期。2读者基本信息的查询、修改，包括读者借书证编号、读者姓名、性别等。3书籍类别标准的制定、类别信息的输入，包括类别编号、类别名称。4书籍类别信息的查询、修改，包括类别编号、类别名称。5书籍库存信息的输入，包括书籍编号、书籍名称、书籍类别编号、作者、出版社、出版日期、登记日期，价格，是否可借。6书籍库存信息的查询，修改，包括书籍编号、书籍名称、书籍类别编号、姓名、出版社、出版日期、登记日期、价格、是否可借等。7借书信息的输入，包括读者借书证号、书籍编号、借书日期，应还时间。8借书信息的查询、修改，包括借书证编号、读者编号、读者姓名、书籍编号、书籍名称、借书日期等。9还书信息的输入，包括借书证编号、书籍编号、还书日期。10还书信息的查询和修改，包括还书读者借书证编号、读者姓名、书籍编号、书籍名称、借书日期、还书日期等。11超期还书罚款输入，还书超出期限包括超出期限还书的读者借阅证号，书籍编号，罚款金额。12.超期还书罚款查询，删除，包括读者借书证编号、读者姓名、书籍编号、书籍名称，应还时间，罚款金额，借阅时间，超期时间等-第 2 页事物需求事物需求(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 图-第 3 页数据字典数据字典表-1book_sytle 书籍类别信息表表中列名数据类型可否为空说明bookstylenoVarchar(20)not null(主键)书籍类别编号bookstyleVarchar(30)not null种类名称表-2system_readers 借阅者信息表格表中列名数据类型可否为空说明readeridVarchar(9)not null(主键)借阅证号readernameVarchar(9)not null读者姓名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作者isborrowedVarchar(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 还书记录信息表表中列名数据类型可否为空说明readeridVarchar(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书籍名称-第 4 页bookfeeVarchar(10)Not Null罚款金额borrowdatedatetimeNot Null借阅时间shouldreturndatetimenull应还时间exceeddatedatetimenull超期时间表 2-7system_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(主键)工号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 读者类别表表中列名数据类型可否为空说明readertypenoVarchar(20)Not null(主键)读者类别编号ReadertypeVarchar(20)Not null读者类别名称建表语句：建表语句：1.书本类别表建立create table book_stylebookstyleno varchar(20)primary key,bookstyle varchar(30)not null2.书籍表建立create table system_booksbookid 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,-第 5 页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 datetime4.借书记录表建立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_studentstudentno varchar(20)primary key,major varchar(30)not null,borrownum int not null,readertype varchar(20),phone varchar(20)8.读者类别表建立-第 6 页create table system_readertypereadertypeno varchar(20)primary key,readertype varchar(20)not null9.系别表建立create table system_departmentdepartmentno varchar(20)primary key,departmentname varchar(20)not null10教师表建立create table system_teacherreaderid 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(00125415152,计算机组成原理,6,王爱英,清华大学出版社,2001-01-03,2003-11-15,35.5,3,10);insert intosystem_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,-第 7 页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*fromsystem_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)-第 8 页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-2711:24:54.123)update system_booksset isborrowed=0wherebookid=545551523andisborrowed=1insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values(151546564,X05620204,2014-09-03 10:24:54.123,2014-10-0310:24:54.123)update system_booksset isborrowed=0wherebookid=151546564andisborrowed=1insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values(151451424,001456,2014-09-03 10:24:54.123,2014-12-0310:24:54.123)update system_booksset isborrowed=0.wherebookid=151451424andisborrowed=15向学生表中加入数据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)删除学生操作：-第 9 页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,多表查询，DATEDIFF(),GETDATE(),CONVERT(),ADD】insert intoreader_fee(readerid,readername,bookid,bookname,bookfee,borrowdate,shouldreturn,returndate)selectsystem_readers.readerid 读者借书证编号,readername 读者姓名,system_books.bookid 书籍编号,bookname 书名,0.3*(Datediff(day,convert(smalldatetime,borrowdate),getdate()-30)超过时间天数,borrowdate 借书时间,shouldreturn 应还时间,returndate 还书时间fromborrow_record,system_readers,system_books,return_recordwheresystem_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=X0562020113.多表查询（查询有借书的学生的学号，姓名，读者类型，可借数，专业以及所借书籍的编号）select system_readers.readerid,studentno,readername,system_readers.readertype,borrownum,majorfrom system_readers,system_studentwhere system_readers.readerid=system_student.readerid-第 10 页14.使用 GROUPBY,HAVING 子句（查询有借过书的同学的学号和剩余可借数）select distinct studentno,borrownumfrom system_studentgroup by studentno,borrownumhaving borrownum 0