《数据库原理》-音像租借管理系统

《数据库原理》课程设计报告
设计题目：音像租借管理系统专业：信息管理与信息系统
计算机与数据科学学院
2019 年1月5 日
目录
第1章概述 (3)
1.1选题的背景与意义 (3)
1.2相关技术分析 (3)
第2章系统功能设计 (4)
2.1系统总体结构设计图 (4)
2.2系统功能模块 (4)
2.2.1 模块一：会员管理 (4)
2.2.2 模块二：业务操作 (4)
2.2.3模块三：DVD管理 (4)
2.2.4模块四：查询 (4)
2.2.5模块五：系统设置 (5)
2.2.6模块六：账务管理 (5)
第3章数据库设计 (6)
3.1需求分析 (6)
3.1.1总体数据流图： (6)
3.1.2顶层数据流图： (6)
3.1.3用户借还流图： (6)
3.1.4涉及数据项（数据字典） (7)
3.1.5数据结构（数据字典） (7)
3.2概念结构设计 (8)
3.3逻辑结构设计 (10)
3.3.1 E-R图向关系模式转换 (10)
3.3.2范式的判定 (10)
3.3.3子模式的设计 (10)
3.4物理结构设计 (11)
3.4.1存取方法设计 (11)
3.4.2存储结构设计 (11)
3.4.3索引的建立 (11)
3.5数据库实施 (12)
3.5.1创建存储过程统计某段时间内各DVD的销售、借还数量 (17)
3.5.2触发器 (18)
3.6数据库运行与维护 (19)
第4章结束语 (20)
参考文献 (21)
第1章概述
1.1 选题的背景与意义
随着人民的生活水平不断提高，而对文化生活的需求也不断提高。

图书、音像行业得到了迅猛的发展，消费持续增长。

就音像系统而言，它具有种类繁多，难于管理等特点，而且，音像管理人员在租借音像制品时，要经历登记、查找、统计等枯燥的手工工作，这种操作即烦琐又不精确，许多记录不便长期保留，这就迫切需要标准的，高效率的管理方式引导音像租借行业的发展。

因此，为了满足人民日益增长的文化需求，为了适应音像行业的迅猛发展，提高工作效率，便于管理者统一管理，我们要开发一套音像租借系统来满足音像租借者和学生的需要。

1.2相关技术分析
SQL语言是结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；SQL 语句用于取回和更新数据库中的数据。

SQL 可与数据库程序协同工作，比如MS Access、DB2、Informix、
MS SQL Server、Oracle、Sybase 以及其他数据库系统。

存在着很多不同版本的SQL 语言，但是为了与ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的关键词（比如SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。

SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
SQL Server用于操作数据库的编程语言，简称T-SQL。

T-SQL与PL/SQL不同，并没有固定的程序结构。

第2章系统功能设计
2.1系统总体结构设计图
图2-1 系统功能图
2.2 系统功能模块
2.2.1 模块一：会员管理
用于增加会员，删除会员，会员基本信息的修改，会员缴费、退押金、退租金等。

2.2.2 模块二：业务操作
实现DVD的出租、返还、销售、退售、退租、添租功能。

2.2.3模块三：DVD管理
新添光盘入库，原有光盘进货，DVD光盘信息修改，光盘注销。

2.2.4模块四：查询
已出租、销售DVD查询，即将缺货的DVD查询，某一DVD的出租、销售情况查询，会员租赁或购买的情况查询。

2.2.5模块五：系统设置
操作员及其权限的设置，影片类型、会员类型的设置，非会员、会员的影片收费标准。

2.2.6模块六：账务管理
添加、修改、删除账户，账户收支，所有资金及其明细等。

第3章数据库设计3.1 需求分析
3.1.1总体数据流图：
图3-1总体数据流图3.1.2顶层数据流图：
图3-2顶层数据流图
3.1.3用户借还流图：
图3-3用户借还流图
3.1.4涉及数据项（数据字典）
表3-1涉及数据项
3.1.5数据结构（数据字典）
表3-2数据结构
数据结构编号数据结构名数据结构含义数据结构组成DV-1 用户信息用户的信息DVD-1到
DVD-11 DV-2 DVD租赁DVD租赁的信息DVD-12到
DVD-17 DV-3 DVD归还归还DVD的信息DVD-13到
DVD-23 3.2 概念结构设计
局部E-R图
图3-4用户信息图
图3-5会员卡信息图
图3-6租赁信息图
图3-7零售信息图
图3-8 DVD归还信息图
图3-9 DVD信息图
图3-10 DVD退购信息图
图3-11 DVD入库信息图
合并各分Ｅ－Ｒ图,消除属性冲突、命名冲突、结构冲突等三类冲突，得到初
步E-R图，再消除不必要冗余，得到的基本E-R图如下所示：
图3-12 总体E-R图
3.3 逻辑结构设计
3.3.1 E-R图向关系模式转换
逻辑结构是独立于任何一种数据模型的，在实际应用中，一般所用的数据库环境已经给定（如SQL Server或Oracel或MySql)。

由于目前使用的数据库基本上都是关系数据库，因此首先需要将E-R图转换为关系模型，然后根据具体DBMS 的特点和限制转换为特定的DBMS支持下的数据模型，最后进行优化系模式转换
3.3.2范式的判定
第一范式：如果关系模式R所有的属性均为原子属性，即每个属性都是不可再分的，则称R属于第一范式；
第二范式：如果关系模式R属于1NF，且每个非主属性都完全依赖于R的主码，则称R属于第二范式；
第三范式：如果关系模式R属于2NF，且每个非主属性都不传递函数依赖于R 的主码，则称R属于第三范式。

根据范式的定义得所有的关系模式都是满足第三范式。

3.3.3子模式的设计
1.创建视图查询DVD的现货、库存
create view number
as
select DVD编号,DVD名称,现货,库存
from DVD信息表
2.创建视图查询用户的租赁，购买状况
create view user_number
as
select当前购买,历史购买,当前租赁,历史租赁
from用户信息表
3.创建视图查看会员情况
create view vip
as
select会员卡信息表.会员卡号,会员卡信息表.姓名,性别,会员类型,状态,联系方式,现住址,卡内余额,登记日期
from会员卡信息表,用户信息表
where会员卡信息表.会员卡号=用户信息表.会员卡号
4.创建视图查看影片是否缺货
create view out_of_stock
as
select DVD名称,导演,主演,类型
from DVD信息表
where DVD信息表.库存=0
3.4 物理结构设计
3.4.1存取方法设计
数据库系统是多用户共享的系统，对同一个关系要建立多条存储路径才能满足多用户的多种应用要求。

对于DVD零售出租管理系统来说，为了提高某些属性(如：DVD的现货、库存、出租、零售记录等)的查询速度，可以选择聚簇存取的方法，即把这些属性上具有相同值的元组集中放在连续的物理块上。

这样在查询某一类DVD或同一主演、导演出租零售数量时就会大大提高查询速度。

因此，该系统中选择聚簇存取方法。

3.4.2存储结构设计
DVD零售出租管理系统是一个复杂的计算机网络信息系统，采用基于客户端/服务器（C/S）应用体系结构来建设DVD零售出租管理系统。

数据库管理系统采用Microsoft 公司推出的SQL Server 2000 或以上版本，并用SQL进行数据库的建立和数据库中数据的维护和查询。

3.4.3索引的建立
CREATE CLUSTERED TNDEX QAQ ON DVD入库信息表（DVD名称）；
CREATE CLUSTERED TNDEX QAQ ON DVD信息表（DVD名称）；
CREATE CLUSTERED TNDEX QAQ ON 用户信息表（姓名）；
CREATE CLUSTERED TNDEX QAQ ON 会员卡信息表（姓名）；
CREATE CLUSTERED TNDEX QAQ ON DVD退购信息表（DVD名称）；
CREATE CLUSTERED TNDEX QAQ ON DVD零售信息表（DVD名称）；
CREATE CLUSTERED TNDEX QAQ ON DVD租赁信息表（DVD名称）；
CREATE CLUSTERED TNDEX QAQ ON DVD归还信息表（DVD名称）；
3.5 数据库实施
建立数据库
create database音响店DVD零售出租管理系统数据库
on
(name='音响店DVD零售出租管理系统数据文件',
filename='E:\SJKKCSJ\音响店DVD零售出租管理系统数据文件.mdf',
size=15MB,
maxsize= 50MB,
filegrowth=5%
)
log on
( name='音响店DVD零售出租管理系统日志文件',
filename='E:\SJKKCSJ\音响店DVD零售出租管理系统日志文件.ldf',
size= 2MB,
maxsize= 5MB,
filegrowth= 1MB
)
建立用户信息表
create table用户信息表
(
用户编号varchar(12)not null primary key,
用户类型varchar(10)not null check(用户类型in('会员''非会员')), 会员卡号varchar(20),
姓名varchar(10)not null,
性别char(2)not null check(性别in('男''女')),
联系方式varchar(20)not null,
现住址varchar(100)not null,
当前租赁int,
当前购买int,
历史租赁int,
历史购买int
)
图3-13 同户信息表截图
建立会员卡信息表
create table会员卡信息表
(
会员卡号varchar(20)not null primary key,
用户编号varchar(12)not null,
姓名varchar(10)not null,
会员类型varchar(20)not null check(会员类型in('普通会员''白金会员' '黄金会员''钻石会员')),
状态varchar(10)not null check(状态in('激活''未激活')),
卡内余额float,
累计租赁int,
登记日期date not null,
foreign key(用户编号)references用户信息表(用户编号)
)
图3-14 会员卡信息表截图
建立DVD信息表
create table DVD信息表
(
DVD编号varchar(10)primary key,
DVD名称varchar(20)not null,
导演varchar(10)not null,
主演varchar(20)not null,
类型varchar(10),
年份varchar(5)not null,
进货商varchar(20)not null,
现货int check(现货>=0 ),
库存int check(库存>=0 ),
租赁价格float not null,
出售价格float not null,
被租次数int
）
图3-15 DVD信息表截图
建立DVD租赁信息表
create table DVD租赁信息表
(
租赁单号varchar(12)not null primary key,
DVD编号varchar(10)not null,
DVD名称varchar(20)not null,
租赁用户编号varchar(12)not null,
应付押金float not null,
租赁日期date not null,
到期时间date not null,
foreign key(DVD编号)references DVD信息表(DVD编号),
foreign key(租赁用户编号)references用户信息表(用户编号) )
图3-16 DVD租赁信息表截图
建立DVD归还信息表
create table DVD归还信息表
(
归还单号varchar(12)not null primary key,
DVD编号varchar(10)not null,
DVD名称varchar(20)not null,
归还用户编号varchar(12)not null,
归还日期date not null,
逾期天数int,
应付金额float not null,
损坏状况char(2)not null check(损坏状况in('有''无')), 应退押金float not null,
foreign key(DVD编号)references DVD信息表(DVD编号),
foreign key(归还用户编号)references用户信息表(用户编号) )
图3-17 DVD归还信息表截图
建立DVD零售信息表
create table DVD零售信息表
(
零售单号varchar(12)not null primary key,
DVD编号varchar(10)not null,
DVD名称varchar(20)not null,
购买用户编号varchar(12)not null,
出售日期date not null,
出售数量int,
应付金额float not null,
foreign key(DVD编号)references DVD信息表(DVD编号),
foreign key(购买用户编号)references用户信息表(用户编号)
)
图3-18 DVD零售信息表截图
建立DVD退购信息表
create table DVD退购信息表
(
退购单号varchar(12)not null primary key,
DVD编号varchar(10)not null,
DVD名称varchar(20)not null,
退购用户编号varchar(12)not null,
退购日期date not null,
退购数量int not null,
有无损坏char(2)not null check(有无损坏in('有''无')), 应退金额float not null,
foreign key(DVD编号)references DVD信息表(DVD编号),
foreign key(退购用户编号)references用户信息表(用户编号) )
图3-19 DVD退购信息表截图
建立DVD入库信息表
create table DVD入库信息表
(
DVD编号varchar(10)not null primary key,
DVD名称varchar(20)not null,
入库日期date not null,
入库数量int not null,
foreign key(DVD编号)references DVD信息表(DVD编号)
)
图3-20 DVD入库信息表截图
3.5.1创建存储过程统计某段时间内各DVD的销售、借还数量
create procedure tongji@begintime date,@endtime date,@type char(6)
as
declare@leixing char(6)
set@leixing=@type
begin
if@leixing='零售'
select@begintime as起始日期,@endtime as截至日期,DVD编号,SUM(出售数量)as销售量
from DVD零售信息表
where出售日期>=@begintime and出售日期<=@endtime
group by DVD编号
else if@leixing='出租'
select@begintime as起始日期,@endtime as截至日期,DVD编号,count(DVD编号)as 出租量
from DVD租赁信息表
where租赁日期>=@begintime and租赁日期<=@endtime
group by DVD编号
else if@leixing='归还'
select@begintime as起始日期,@endtime as截至日期,DVD编号,count(DVD编号)as 归还量
from DVD归还信息表
where归还日期>=@begintime and归还日期<=@endtime
group by DVD编号
else print'请输入正确的查询类型'
end
3.5.2触发器
1.实现出租时自动修改DVD现货及客户的当前租赁数量
create trigger_rent on DVD租赁信息表--租赁触发器
for insert
as
begin
update DVD信息表set现货=现货-1
from DVD信息表,inserted
where DVD信息表.DVD编号=inserted.DVD编号
update用户信息表set当前租赁=当前租赁+1
from用户信息表,inserted
where用户信息表.用户编号=inserted.租赁用户编号
end
2.实现归还时自动修改DVD现货及客户的当前租赁数量和历史租赁数量create trigger_return on DVD归还信息表--归还触发器
for insert
as
begin
update DVD信息表set现货=现货+1
from DVD信息表,inserted
where DVD信息表.DVD编号=inserted.DVD编号
update用户信息表set当前租赁=当前租赁-1
from用户信息表,inserted
where用户信息表.用户编号=inserted.归还用户编号
update用户信息表set历史租赁=历史租赁+1
from用户信息表,inserted
where用户信息表.用户编号=inserted.归还用户编号
update DVD归还信息表set DVD编号=DVD租赁信息表.DVD编号,DVD名称=DVD租赁信息表.DVD名称,
归还用户编号=DVD租赁信息表.租赁用户编号
from DVD租赁信息表,DVD归还信息表,inserted
where DVD租赁信息表.租赁单号=inserted.归还单号
end
3.实现入库时自动修改DVD库存
create trigger_buy on DVD入库信息表--入库触发器
for insert
as
update DVD信息表set库存=库存+inserted.入库数量
from DVD信息表,inserted
where DVD信息表.DVD编号=inserted.DVD编号
4.实现零售时自动修改DVD库存及用户当前购买和历史购买
create trigger_sale on DVD零售信息表--零售触发器
for insert
as
begin
update用户信息表set历史购买=当前购买
from用户信息表,inserted
where用户信息表.用户编号=inserted.购买用户编号
update用户信息表set当前购买=当前购买+inserted.出售数量
from用户信息表,inserted
where用户信息表.用户编号=inserted.购买用户编号
update DVD信息表set库存=库存-inserted.出售数量
from DVD信息表,inserted
where DVD信息表.DVD编号=inserted.DVD编号
end
3.6 数据库运行与维护
backup database音响店DVD零售出租管理系统数据库to disk='C:\Program
Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\音响店DVD 零售出租管理系统数据库.bak'
restore database音响店DVD零售出租管理系统数据库from disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\音响店DVD 零售出租管理系统数据库.bak'
with replace
第4章结束语
在做本次课程设计的过程中，我发现有很多已经学过的东西没有理解到位，不能灵活运用于实际，不能很好的用来解决问题，这需要自己不断的大量的实践，通过不断的自学，不断地发现问题，思考问题，进而解决问题。

而在这个过程中，我深刻理解所学知识，同时也学到不少很实用的东西。

我做的是音像租借管理系统，在做需求分析的时候，我发现有很多方面需要考虑，而且在后面建立表，建立视图等时候，发现前面思考的还是不够全面，后面还需要不断的改进，这需要耐心、细心，有的时候，会有感到抓狂。

还有值得肯定的一点是，课程设计使我们的自学习能力加强了，当我们遇到一些不会的问题时，首先是自己思考，然后大家很多时候都积极的探讨，有时会借鉴其他同学会而自己不会的地方将其变为自己脑袋里的知识。

这些都让我们意识到，只要我们努力，只要我们付出，就会得到收获和回报。

《数据库原理》课程设计报告- 参考文献
参考文献
[1]王珊,萨师煊 .数据库系统概论[M]. 高等教育出版社,2005
[2]郑阿奇等. SQLserver实用教程（第3版）[M] . 电子工业出版社,2009
[3]赵乃真等. 信息系统设计与应用. 清华大学出版社,2005
21。