数据库课程设计 书刊租借信息管理系统.doc

数据库系统原理课程设计数据库系统原理课程设计 课题名称 课题名称 书刊租借信息管理系统书刊租借信息管理系统 姓姓 名 名 班班 级 级 计算机计算机 11021102 学学 号 号 311109010225311109010225 指导老师 指导老师 2013 年 9 月 10 日 1 目录目录 一 系统需求分析一 系统需求分析 1 1 1 1 背景 1 1 2 系统主要功能 1 1 3 数据流程图 DFD 2 二 概念结构设计二 概念结构设计 3 3 2 1 书刊信息 E R 图 3 2 2 会员信息 E R 图 3 2 3 管理员信息 E R 图 3 2 4 书刊租借信息管理系统总 E R 图 4 三 逻辑结构设计三 逻辑结构设计 6 6 3 1 关系模式 6 四 表结构定义四 表结构定义 6 6 五 数据实施和维护五 数据实施和维护 8 8 5 1 创建数据库 8 5 2 数据初始化 11 5 3 单表查询 15 5 4 数据查询 更新及触发器 存储过程的创建 17 六 设计小结六 设计小结 2626 七 参考文献七 参考文献 2727 2 一 一 系统需求分析系统需求分析 1 11 1 背景背景 书刊租借工作繁琐 借阅频繁 包含大量的信息数据 面对着如此庞大的 信息量 传统的人工方式管理会导致书刊租借管理上的混乱 人力与物力过多 浪费 因此 就需要一个合理 有效 规范和实用的书刊租借信息管理系统来 实现对这些数据的有效管理 提高书刊租借管理工作效率 作到信息的规范管 理 科学统计和快速查询 更方便于顾客的租借 1 21 2 系统主要功能系统主要功能 1 会员基本信息的输入 包括会员证号 姓名 性别 年龄 电话 地 址 已借书数等 2 会员基本信息的查询 修改 包括会员证号 姓名 性别 年龄 电 话 地址 已借书数等 3 书刊库存信息的输入 包括书刊编号 书刊名称 作者 出版社 出 版日期 类别 年份 库存量 借出量 4 书刊库存信息的查询 修改 包括书刊编号 书刊名称 作者 出版 社 出版日期 类别 年份 库存量 借出量 5 借书信息的输入 包括会员证号 书刊编号 借书日期 借书天数 租借费用 6 借书信息的查询 修改 包括会员证号 书刊编号 借书日期 借书 天数 租借费用 7 还书信息的输入 包括会员证号 书刊编号 还书日期 8 还书信息的查询和修改 包括还书会员证号 会员姓名 书刊编号 书刊名称 借书日期 还书日期等 9 超期还书罚款输入 还书超出期限包括超出期限还书的会员证号 管 理员编号 书刊编号 借书日期 超期罚款 10 超期还书罚款查询 删除 包括会员证号 会员姓名 书刊编号 书 刊名称 超期罚款等 1 31 3 数据流程图 数据流程图 DFDDFD 3 管理员登记 会员信息管理 书 刊 登 记 会员登记 书刊信息管理 管理员信息管理 数据库 数据库 数据库 数 据 库 数 据 库 借阅信息管理 基本信息录入 基本信息录入 书刊信息返回 借阅信息录入 超期罚款 会员信息返回 基本信息录入 归还信息录入 数据库 二 二 概念结构设计概念结构设计 2 12 1 书刊信息书刊信息 E RE R 图 图 书刊信息 书刊编号 书刊名称 出版日期 库存量 借出量 出版社 类别作者 4 2 22 2 会员信息会员信息 E RE R 图 图 会员信息 会员证号 已借书数 年龄 最大借书数 账户余额 性别 地址姓名 电话 2 32 3 管理员信息管理员信息 E RE R 图 图 管理员 管理员编号 姓名 电话 地址 会员信息 租借 书刊信息 M N 书刊编号 会员证号 租借费用 借书天数 借书日期 shijian 归还时间 shijian 5 2 42 4 书刊租借信息管理系统总书刊租借信息管理系统总 E RE R 图 图 会员信息 会员证号 已借书数 年龄 最大借书数 账户余额 性别 地址姓名 电话 书刊信息 书刊编号 书刊名称 出版日期 库存量 借出量 出版社 类别 作者 租借 租 借 费 用 借 书 天 数 借 书 日 期 shij ian 书 刊 编 号 会 员 证 号 管理员 管理员编号 姓名 电话 地址 管理 罚款 M N 1 N M N 归 还 日 期 6 三 逻辑结构设计三 逻辑结构设计 3 13 1 关系模式 关系模式 通过 E R 模型到关系模型的转化 可以得到如下关系模式 会员信息 会员证号 姓名 性别 年龄 电话 地址 最大借书数 已借书 数 账户余额 管理员 管理员编号 姓名 地址 电话 书刊信息 书刊编号 书刊名称 作者 出版社 出版日期 类别 库存量 借出量 书刊借阅 会员证号 书刊编号 借书日期 借书天数 租借费用 书刊归还 会员证号 书刊编号 还书日期 罚款 会员证号 管理员编号 借书日期 超期罚款 四 四 表结构定义表结构定义 userinf 管理员信息表 列名数据类型可否为空说明 useridchar 7 NOT NULL 主键 管理员编号 usernamechar 20 NOT NULL 姓名 useradresschar 30 NULL 地址 usertelchar 12 NOT NULL 电话 borrow record 书刊借阅表 列名数据类型可否为空说明 memberidchar 7 NOT NULL 会员证号 bookidchar 7 NOT NULL 书刊编号 borrowdateDateTimeNOT NULL 借书日期 borrownumintNOT NULL 借书天数 bpricefloatNOT NULL 租借费用 7 system books 书刊信息表 列名数据类型可否为空说明 bookidchar 7 NOT NULL 书刊编号 booknamevarchar 40 NOT NULL 书刊名称 bookstylechar 10 NOT NULL 类别 bookpubdateDatetimeNULL 出版日期 bookpubchar 50 NULL 出版社 bookauthorchar 20 NOT NULL 作者 stockcountintNOT NULL 库存量 borrowcchar 2 NOT NULL 借出量 memberinf 会员信息表 列名数据类型可否为空说明 membernamechar 20 NOT NULL 会员姓名 memberidchar 7 NOT NULL 会员证号 membertelchar 12 NOT NULL 联系电话 memberadressvarchar 30 NOT NULL 地址 membersexchar 7 NULL 性别 memberageintNULL 年龄 maxnumintNOT NULL 最大借书数 bbcountintNOT NULL 已借书数 balancefloatNOT NULL 账户余额 return record 书刊归还表 列名数据类型可否为空说明 memberidchar 7 NOT NULL 会员证号 bookidchar 7 NOT NULL 书刊编号 returndateDateTimeNOT NULL 还书日期 8 member fee 罚款记录表 列名数据类型可否为空说明 memberidchar 7 NOT NULL 会员证号 bookidchar 7 NOT NULL 书刊编号 borrowdateDateTimeNOT NULL 借书日期 useridchar 7 NULL 管理员编号 bookfeefloatNOT NULL 罚款金额 五 五 数据实施和维护数据实施和维护 5 15 1 数据库建立 数据库建立 5 1 15 1 1 创建数据库创建数据库 CREATE DATABASE bookstoresystem ON NAME bookstoresystem FILENAME d bookstoresystem mdf SIZE 10 MAXSIZE 50 FILEGROWTH 5 LOG ON NAME bookstore FILENAME d bookstoresystem ldf SIZE 5MB MAXSIZE 25MB FILEGROWTH 5MB GO 5 1 25 1 2 创建基本表创建基本表 1 管理员信息表 create table userinf useridchar 7 NOT NULL primary key username char 20 NOT NULL useradress char 30 NULL usertelchar 12 NOT NULL 9 2 创建书刊信息表 create table system books bookid char 7 NOT NULL primary key bookname varchar 40 NOT NULL bookstyle char 10 NOT NULL bookpubdateDatetime NULL bookpubchar 50 NULL bookauthor char 20 NOT NULL stockcount int NOT NULL borrowcchar 2 NOT NULL 3 创建会员信息表 create table memberinf memberid char 7 NOT NULL primary key membername char 20 NOT NULL membertel char 12 NOT NULL memberadress varchar 30 NOT NULL membersex char 7 NULL memberage int NULL maxnum int NOT NULL bbcount intNOT NULL 10 balancefloat NOT NULL 4 创建书刊借阅表 create table borrow record bookid char 7 NOT NULL primary key memberid char 7 NOT NULL borrowdate datetime NOT NULL borrownumintNOT NULL bprice float NOT NULL foreign key bookid references system books bookid foreign key memberid references memberinf memberid 5 创建书刊归还表 create table return record bookid char 7 primary key memberid char 7 returndate datetime foreign key bookid references system books bookid foreign key memberid references memberinf memberid 6 创建罚款单表 create table member fee 11 memberid char 7 NOT NULL bookid char 7 NOT NULL primary key borrowdate DateTimeNOT NULL userid char 7 NULL bookfeefloat NOT NULL 5 25 2数据初始化 数据初始化 1 将管理员信息加入userinf insert into userinf values 2013003 李华 商丘夏邑骆集乡 6223736 insert into userinf values 2013006 言丽丽 商丘夏邑康复路 6222766 2 将已有的书刊加入system books insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 2013013 计算机组成原理 工程技术类 2001 01 03 清华大 学出版社 王爱英 8 3 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 2013086 数据库原理 工程技术类 2007 07 02 高等教育出 版社 萨师煊 10 4 insert into system books 12 bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 2013016 计算机组成原理 工程技术类 2001 01 03 清华大 学出版社 王爱英 8 3 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 2013081 C程序设计 工程技术类 2002 04 02 清华大学出 版社 谭浩强 12 6 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 2013034 数据结构 C语言版 工程技术类 2002 06 28 清华大学出版社 吴伟民 严蔚敏 6 2 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 5455515 中华历史年 人文艺术类 2005 04 03 北京大学出 版社 吴强 6 1 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 1546568 微观经济学 政治经济类 2000 10 03 北京大学出 版社 李小刚 12 2 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 0151153 古代埃及 社会科学类 2001 02 02 北京大学出版 社 赵文华 4 1 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 1514514 日本文化 人文艺术类 2002 04 02 北京大学出版 社 吴小鹏 7 0 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 5658 影视文学 图片艺术类 1999 02 28 北京大学出版社 苏庆东 5 2 insert into system books bookid bookname bookstyle bookpubdate bookpub bookauthor stoc kcount borrowc values 5658020 探索宇宙奥秘 自然科学类 1999 02 28 北京大学 出版社 苏庆东 10 3 13 3 将已有的会员加入memberinf insert into memberinf membername memberid membertel memberadress membersex memberag e maxnum bbcount balance values 陈远鹏 07 6213048 商丘夏邑 男 21 5 2 32 insert into memberinf membername memberid membertel memberadress membersex memberag e maxnum bbcount balance values 李德 17 6234048 郑州 男 31 7 3 62 insert into memberinf membername memberid membertel memberadress membersex memberag e maxnum bbcount balance values 赵铭静 02 6223078 商丘 女 18 4 4 30 insert into memberinf membername memberid membertel memberadress membersex memberag e maxnum bbcount balance values 李丽 37 6213565 开封 女 26 8 3 80 insert into memberinf membername memberid membertel memberadress membersex memberag e maxnum bbcount balance values 蒋伟 13 6233348 周口 男 24 6 2 18 insert into memberinf membername memberid membertel memberadress membersex memberag e maxnum bbcount balance values 赵鑫 16 6218888 焦作 女 25 6 2 60 14 4 添加借阅信息到borrow record表 insert into borrow record memberid bookid borrowdate borrownum bprice values 17 2013013 2013 07 20 30 5 insert into borrow record memberid bookid borrowdate borrownum bprice values 02 2013081 2013 08 03 30 3 insert into borrow record memberid bookid borrowdate borrownum bprice values 13 1546568 2013 09 02 30 4 insert into borrow record memberid bookid borrowdate borrownum bprice values 16 2013034 2013 08 16 30 7 insert into borrow record memberid bookid borrowdate borrownum bprice values 17 0151153 2013 07 28 30 2 insert into borrow record memberid bookid borrowdate borrownum bprice values 37 2013086 2013 09 02 30 5 5添加归还信息到return record表 15 5 35 3单表查询 单表查询 5 3 15 3 1简单查询简单查询 1 表userinf中查询 select from userinf 2 表ststem books中查询 select from system books 3 表memberinf中查询 select from memberinf 16 4表borrow record和return record中查询 select from borrow record select from return record 5 查询已借数量大于等于3会员的所有信息 select from memberinf where bbcount 3 17 6 查询借书会员的已借书天数 select borrow record memberid 会员证号 membername 会员姓名 bookname 书刊名称 borrowdate 借书日期 datediff day convert smalldatetime borrowdate getdate 已借天 数 from borrow record memberinf system books where borrow record bookid system books bookid and borrow record memberid memberinf memberid 7 查询未被借出的书本 select from system books where bookid not in select bookid from borrow record 5 45 4数据查询 更新及触发器 存储过程的创建 数据查询 更新及触发器 存储过程的创建 18 5 4 15 4 1创建触发器创建触发器 1 在return record表中建立一个插入触发器 returnbook 功能 当还书操作 时 要在还书记录中添加一条记录 同时删除相应书本的借阅记录 SQL 语言 create trigger returnbook on return record after insert as delete from borrow record where borrow record bookid select borrow record bookid from borrow record inserted where borrow record bookid inserted bookid 2 在return record表中再建立一个插入触发器 returnbook2 功能 当还书 操作时 同时对相应表system books memberinf做相应的改动 system books的属性 库存量加1 借出量减1 memberinf的属性 已借书数减 1 SQL 语言 create trigger returnbook2 on return record after insert as update system books set stockcount stockcount 1 borrowc borrowc 1 where system books bookid in select system books bookid from system books inserted where system books bookid inserted bookid and system books borrowc 0 update memberinf set bbcount bbcount 1 where memberinf memberid in select memberinf memberid from memberinf inserted where memberinf memberid inserted memberid and memberinf bbcount 0 假设归还书刊编号为 2013086 代码 insert into return record values 2013086 37 2013 9 6 19 执行前system books表 执行后system books表 执行前memberinf表 20 执行后memberinf表 3 在borrow record表中建立一个插入触发器 borrowbook 当借书操作时 要 改变memberinf表的部分属性 已借书数加 1 账户余额减去相应的租借费用 SQL 语言 create trigger borrowbook on borrow record after insert as update memberinf set bbcount bbcount 1 balance balance select borrow record bprice from borrow record inserted where inserted bookid borrow record bookid where memberinf memberid in select memberinf memberid from memberinf inserted where memberinf memberid inserted memberid 假设借书刊编号为 5658 代码 insert into borrow record values 5658 02 2013 09 01 30 5 21 执行代码前memberinf表 执行代码后memberinf表 4 在borrow record表中建立一个插入触发器 borrowbook2 当借书操作时 要 改变system books表的部分属性 库存量减 1 借出量加 1 SQL 语言 create trigger borrowbook2 on borrow record after insert as update system books set stockcount stockcount 1 borrowc borrowc 1 22 where system books bookid in select system books bookid from system books inserted where system books bookid inserted bookid 成功创建截图 5 4 25 4 2创建存储过程 创建存储过程 1 创建一个按照书刊类型查找该类型书刊的所有信息的存储过程 SQL 语言 create procedure bstyle 类别char 10 as select from system books where bookstyle 类别 执行存储过程查询工程技术类书刊信息 23 2 创建一个当会员属性已借书数等于最大借书数时 提示不能进行借书的存储 过程 SQL 语言 create procedure memer 会员证号char 7 会员姓名char 20 as if select bbcount from memberinf where memberid 会员证号 select maxnum from memberinf where memberid 会员证号 print 会员姓名 已借书数等于最大借书数 不能再借书 else print 会员姓名 可以继续借书 执行存储过程分别查询赵铭静 与陈远鹏是否可以借书 memberinf表对比图 3 创建一个存储过程根据会员证号 查询出会员借书信息 24 SQL 语句 create procedure selectinf 会员证号char 7 as select memberinf memberid 会员证号 membername 会员姓名 bbcount 已 借书数 balance 账户余额 system books bookid 书刊编号 bookname 书刊名称 borrowdate 借书日期 borrownum 借书天数 bprice 租借 费用 from borrow record system books memberinf where borrow record bookid system books bookid and borrow record memberid memberinf memberid and memberinf memberid 会员证号 查找会员证号为02 37 13借书信息 exec selectinf 02 exec selectinf 37 exec selectinf 13 参与对照表borrow record表 25 所创建的触发器 存储过程列表图 5 4 35 4 3超期处理 超期处理 1 对已借书的会员进行查询借书是否超期 为了方便借书天数全为30 说明 当前的日期为2013年9月13日计算 SQL 语言 select memberinf memberid 会员证号 memberinf membername 会员姓名 system books bookid 书刊编号 bookname 书刊名称 borrowdate 借书日期 datediff day convert smalldatetime borrowdate getdate 30 超 出天数 from borrow record memberinf system books where borrow record bookid system books bookid and borrow record memberid memberinf memberid and datediff day convert smalldatetime borrowdate getdate 30 运行结果图 26 对照表borrow record表的截图 2 对超过天数的读者进行罚款 将信息插入到memb