2022年图书管理系统数据库设计——SQL.pdf

图书管理系统数据库设计SQL 数据库课程设计课设名称 : 图书管理系统数据库设计与实现年级: 专业: 网络工程班级: 姓名: 学号: 成绩: 指导教师 : 颜颖提交报告时间 : 2015 年 1 月 14 日数据需求图书馆管理信息系统需要完成功能主要有: 1、 读者基本信息的输入,包括借书证编号、读者姓名、读者性别登记日期。2.读者基本信息的查询、修改,包括读者借书证编号、读者姓名、性别等。3.书籍类别标准的制定、类别信息的输入,包括类别编号、类别名称。4.书籍类别信息的查询、修改,包括类别编号、类别名称。5.书籍库存信息的输入,包括书籍编号、书籍名称、书籍类别编号、作者、出版社、出版日期、登记日期,价格 ,就是否可借。6.书籍库存信息的查询,修改,包括书籍编号、书籍名称、书籍类别编号、姓名、出版社、出版日期、登记日期、价格、就是否可借等。7.借书信息的输入,包括读者借书证号、书籍编号、借书日期,应还时间。8.借书信息的查询、修改,包括借书证编号、读者编号、读者姓名、书籍编号、书籍名称、借书日期等。9.还书信息的输入,包括借书证编号、书籍编号、还书日期。10.还书信息的查询与修改,包括还书读者借书证编号、读者姓名、书籍编号、书籍名称、借书日期、还书日期等。11.超期还书罚款输入,还书超出期限包括超出期限还书的读者借阅证号,书籍编号 ,罚款金额。12、超期还书罚款查询,删除,包括读者借书证编号、读者姓名、书籍编号、书籍名称,应还时间 ,罚款金额 ,借阅时间 ,超期时间等精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 1 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 事物需求(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 图精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 2 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 还书信息实体归还借阅者信息实体罚款借阅罚款信息实体书籍实体借阅记录信息实体书籍名称书籍类别编号作者出版社借阅证号书籍编号借阅时间借阅证号书籍编号归还时间姓名借阅证号罚款金额书籍名称借阅时间是否可借价格学生实体教师实体学号姓名工号借阅数借阅数专业系部属于系部编号系部名称IsaIsa借阅证号职位书籍类别分类书籍类别编号类别名称书籍编号出版日期登记日期读者性别登记日期应还时间书籍编号应还时间超期时间读者类别编号数据字典表 -1book_sytle 书籍类别信息表表中列名数据类型可否为空说明bookstyleno Varchar(20) not null( 主键 ) 书籍类别编号bookstyle Varchar(30) not null 种类名称表 -2system_readers借阅者信息表格表中列名数据类型可否为空说明readerid Varchar(9) not null( 主键 ) 借阅证号readername Varchar(9) not null 读者姓名readersex Varchar(2) not null 读者性别精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 3 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL readertype Varchar(10) Not null 读者类别regdate datetime null 登记日期表 2-3system_books 书籍信息表表中列名数据类型可否为空说明bookid Varchar(20) Not null( 主键 ) 书籍编号price Varchar(6) Not null 价格bookname Varchar(30) Not null 书籍名称Bookstyleno Varchar(20) Not null 书籍类别编号bookauthor Varchar(30) Not null 作者isborrowed Varchar(1) Not null 就是否可借bookpub Varchar(30) Null 出版社bookpubdate Datetime Null 出版日期bookindate Datetime Null 登记日期表 2-4borrow_record 借阅记录信息表表中列名数据类型可否为空说明readerid Varchar(9) Not null( 外主键 ) 借阅证号bookid Varchar(20) Not null( 外主键 ) 书籍编号borrowdate datetime Not null 借阅时间shouldreturn datetime ? 应还时间表 2-5return_record 还书记录信息表表中列名数据类型可否为空说明readerid Varchar(9) Not null( 外主键 ) 借阅证号bookid Varchar(20) Not null( 外主键 ) 书籍编号returndate datetime Not null 读者还书时间表 2-6reader_fee 罚款记录信息表readerid Varchar(9) Not null( 外主键 ) 借阅证号readername Varchar(9) Not null 读者姓名bookid Varchar(20) Not null( 外主键 ) 书籍编号bookname Varchar(30) Not null 书籍名称bookfee Varchar(10) Not Null 罚款金额borrowdate datetime Not Null 借阅时间shouldreturn datetime null 应还时间exceeddate datetime null 超期时间表 2-7 system_student 学生实体信息表表中列名数据类型可否为空说明studentno Varchar(20) not null( 主键 ) 学号major Varchar(30) not null 专业borrownum Int not null 借阅数readertype Varchar(10) Not null 读者类别phone Varchar(20) null 电话精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 4 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 表 2-8 system_teacher 教师实体信息表表中列名数据类型可否为空说明teachertno Varchar(20) not null( 主键 ) 工号Profession Varchar(20) null 职位borrownum Int not null 借阅数readertype Varchar(20) Not null 读者类别phone Varchar(20) null 电话表 2-9 System_department 系部信息表表中列名数据类型可否为空说明departmentno Varchar(20) not null( 主键 ) 系部编号deparmentname Varchar(20) not null 系部名称表 2-10system_resdertype 读者类别表表中列名数据类型可否为空说明readertypeno Varchar(20) Not null( 主键 ) 读者类别编号Readertype Varchar(20) Not null 读者类别名称建表语句 : 1、书本类别表建立createtablebook_style (bookstylenovarchar( 20 )primarykey , bookstylevarchar( 30 )notnull ) 2、书籍表建立createtablesystem_books (bookidvarchar( 20 )primarykey , booknamevarchar( 20 )Notnull,bookstylenovarchar( 30 )Notnull, pricevarchar( 6)notnull, bookauthorvarchar( 30 )notnull, isborrowedvarchar( 1)notnull, bookpubvarchar( 30 ), bookpubdatedatetime, bookindatedatetime, foreignkey ( bookstyleno)referencesbook_style( bookstyleno), ) 3、借阅者表建立createtablesystem_readers(readeridvarchar( 9)primarykey , readernamevarchar( 9)notnull, readersexvarchar( 2)notnull, readertypevarchar( 10 )notnull, 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 5 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL regdatedatetime ) 4、 借书记录表建立createtableborrow_record (bookidvarchar( 20 )primarykey , readeridvarchar( 9)notnull, borrowdatedatetimenotnull, shouldreturndatetime, foreignkey ( bookid)referencessystem_books( bookid), foreignkey ( readerid)referencessystem_readers( readerid), )5、还书记录表建立createtablereturn_record (bookidvarchar( 20 )primarykey , readeridvarchar( 9)notnull, returndatedatetimenotnull, foreignkey ( bookid)referencessystem_books( bookid), foreignkey ( readerid)referencessystem_readers( readerid) ) 6、 罚款单表建立 */ create table reader_fee ( readerid varchar(9)not null, readernamevarchar(9)not null , bookid varchar(20) primary key, bookname varchar(30) Not null,bookfee varchar(10)not null, borrowdate datetime not null, shouldreturn datetime, exceeddatevarchar(5) , foreign key (bookid ) references system_books(bookid ), foreign key (readerid) references system_readers(readerid) ) 7、学生表建立createtablesystem_student ( studentnovarchar( 20 )primarykey , majorvarchar ( 30 )notnull, borrownumintnotnull, readertypevarchar( 20 ), phonevarchar( 20 ) ) 8、 读者类别表建立createtablesystem_readertype ( 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 6 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL readertypenovarchar( 20 )primarykey , readertypevarchar ( 20 )notnull ) 9、系别表建立createtablesystem_department ( departmentnovarchar( 20 )primarykey , departmentnamevarchar ( 20 )notnull ) 10.教师表建立createtablesystem_teacher ( readeridvarchar( 9)notnull, teachernovarchar( 20 )primarykey , borrownumintnotnull, professionvarchar( 20 ), readertypevarchar( 20 )notnull, phonevarchar( 20 ), foreignkey ( readerid)referencessystem_readers( readerid) ) 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 7 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 8 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 数据初始化及表更新、查询1、向 Book_style 表中插入数据insertintobook_style( bookstyleno, bookstyle) values( 1, 人文艺术类 )insertintobook_style( bookstyleno, bookstyle) values( 2, 自然科学类 ) insertintobook_style( bookstyleno, bookstyle) values( 3, 社会科学类 ) insertintobook_style( bookstyleno, bookstyle) values( 4, 图片艺术类 ) insertintobook_style( bookstyleno, bookstyle) values( 5, 政治经济类 ) insertintobook_style( bookstyleno, bookstyle) values( 6, 工程技术类 ) insertintobook_style( bookstyleno, bookstyle) values( 7, 语言技能类 ) 表单查询 :select * from book_style 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 9 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 2、向 system_books 表中插入数据 : insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( , 计算机组成原理 , 6, 王爱英 , 清华大学出版社 , 2001-01-03, 2003-11-15, 35 、5 , 3, 10); insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( 00456456, 数据库原理 , 6, 萨师煊 , 高等教育出版社 , 2007-07-02, 2007-09-15, 40, 4, 10); insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( 12215121, C 程序设计 , 6, 谭浩强 , 清华大学出版社 , 2002-04-02, 2004-03-14, 60, 5, 8); insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( 9787308020558, 计算机体系结构 , 6, 石教英 , 浙江大学出版社 , 2004-10-03, 2006-11-15, 60, 5, 8); insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( 45456141414, 数据结构 (C语言版 ), 6, 吴伟民 , 严蔚敏 , 清华大学出版社 , 2002-06-28, 2004-01-21, 40, 5, 10); insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( 545551523, 中华历史年 , 1, 吴强 , 北京大学出版社 , 2005-04-03, 2006-05-15, 56, 0, 10); insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( 151451424, 日本文化 , 1, 吴小鹏 , 北京大学出版社 , 2002-04-02, 2004-03-14, 35, 0, 10); 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 10 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( 151546564, 微观经济学 , 5, 李小刚 , 北京大学出版社 , 2000-10-03, 2001-11-15, 35, 0, 10); insertintosystem_books( bookid, bookname,bookstyleno, bookauthor, bookpub, bookpubdate,bookindate,price, borrowednum, totalnum) values( 565833422, 影视文学 , 4, 苏庆东 , 北京大学出版社 , 1999-02-28, 2000-01-21, 35, 0, 10); insertintosystem_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、向读者表中插入数据: insertintosystem_readers( readerid, readername, readersex, readertype, regdate) values( X05620207, 陈飞 , 男 , 1, 2005-9-23 14:23:56) insertintosystem_readers( readerid, readername, readersex, readertype, regdate) values( X05620206, 张三 , 男 , 1, 2005-09-30 13:24:54、623) insertintosystem_readers( readerid, readername, readersex, readertype, regdate) values( X05620204, 赵静 , 女 , 1, 2005-09-27 11:24:54、123) insertintosystem_readers( readerid, readername, readersex, readertype, regdate) values( X05620202, 潘小虹 , 女 , 1, 2005-09-30 13:24:54、473) insertintosystem_readers( readerid, readername, readersex, readertype, regdate) values( 008415, 蒋伟 , 男 , 2, 2004-04-30 09:24:54、478) insertintosystem_readers( readerid, readername, readersex, readertype, regdate) values( 001456, 李风 , 女 , 2, 2004-04-30 09:24:54、478) 表单查询 :select * from system_readers 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 11 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 4、( insert,update ,set) 向借书记录表中加入数据: insertintoborrow_record( bookid, readerid, borrowdate, shouldreturn) values( 545551523, X05620207, 2007-09-27 11:24:54、123, 2007-10-27 11:24:54、123) updatesystem_books setisborrowed=0 wherebookid=545551523 and isborrowed=1insertintoborrow_record( bookid, readerid, borrowdate, shouldreturn) values( 151546564, X05620204, 2014-09-03 10:24:54、123, 2014-10-03 10:24:54、123) updatesystem_books setisborrowed=0wherebookid=151546564 and isborrowed=1insertintoborrow_record( bookid, readerid, borrowdate, shouldreturn) values( 151451424, 001456, 2014-09-03 10:24:54、123, 2014-12-03 10:24:54、123) updatesystem_books setisborrowed=0、wherebookid=151451424 and isborrowed=15.向学生表中加入数据insertintosystem_student( readerid,studentno, readertype, major, borrownum, phone) values( X05620207, 1125111001, 1, 计算机 , 30, 1234567890) insertintosystem_student( readerid, studentno, readertype, major, borrownum, phone) values( X05620206, 1125111002, 1, 计算机 , 30, 1234567890) insertintosystem_student( readerid, studentno, readertype, major, borrownum, phone) values( X05620202, 1122111001, 1, 哲学与社会 , 30, 1234567890) insertintosystem_student( readerid, studentno, readertype, major, borrownum, phone) values( X05620204, 112011100, 1, 国际经济与贸易 , 30, 1234567890) 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 12 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL (DELETE) 删除学生操作 : deletefromsystem_studentwherereaderid= X05620204 系部信息表 : 6、向教师表中加入数据: insertintosystem_teacher( readerid,teacherno, readertype,profession, borrownum, phone) values( 001456, 12022301, 2, 数据库教师 , 40, 1234567890) insertintosystem_teacher( readerid,teacherno, readertype, profession, borrownum, phone) values( 008415, 12022302, 2, C 语言教师 , 40, 1234567890) 7、 (DISTINCT, 多表查询 )查询所有书所对应的类别: 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 13 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 8、向罚款表中添加数据操作(超期 1 天罚款 0、3 元): 【INSERT,多表查询 ,DATEDIFF(),GETDA TE(),CONVERT(),ADD】insertintoreader_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_record wheresystem_readers、readerid=borrow_record、readeridandsystem_books、bookid=borrow_record、bookid andDatediff( day , convert( smalldatetime, borrowdate), getdate()=30 9、创建索引 :【INDEX 】createindex keyindex on borrow_record (bookid,readerid) 删除索引 : DROP INDEX keyindex on borrow_record 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 14 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 10.应用distinct查询表select distinct readeridfrom borrow_record11、应用 COUNT 统计表单数据 : select COUNT(readerid)from borrow_recordgroup by readerid 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 15 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 12、应用 count 统计某学生节约的书籍总数select COUNT(readerid)from borrow_recordwhere readerid =X0562020113、多表查询 (查询有借书的学生的学号,姓名 ,读者类型 ,可借数 ,专业以及所借书籍的编号) select system_readers、readerid,studentno,readername,system_readers、 readertype,borrownum ,major from system_readers ,system_studentwhere system_readers、readerid=system_student、readerid14、 使用 GROUP BY , HAVING 子句 (查询有借过书的同学的学号与剩余可借数) select distinct studentno , borrownum from system_studentgroup by studentno ,borrownum精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 16 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL having borrownum 0 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 19 页，共 20 页 - - - - - - - - - - 图书管理系统数据库设计SQL 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 20 页，共 20 页 - - - - - - - - - -