音响店DVD零售出租管理系统.doc

数据库原理及应用课程设计设计报告题 目：音响店DVD零售出租管理系统学 号： 学生姓名: 指导教师： 提交时间： 2015-01-05 目录第1章需求分析11.1需求调查11.2 系统功能分析1第2章概念结构设计22.1局部E-R图：22.2 全局E-R图4第3章逻辑结构设计53.1E-R图向关系模式转换53.2建立关系模式53.3 关系模式逻辑结构定义6第4章数据库物理结构设计94.1存取方法设计94.2存储结构设计94.3物理设计9第5章数据库完整性设计125.1 主键125.2参照完整性设计135.3Check约束135.4触发器设计13第6章 数据库视图设计15第7章 数据库存储过程设计16总结16参考文献：17数据库原理及应用课程设计报告第1章需求分析1.1需求调查通过对各个用户的调查，该DVD零售租赁管理系统有如下需求：1) 系统管理：实现系统管理人员对系统的管理，包括添加删除用户，更改密码， 数据备份，数据还原，数据清除等功能。2) 数据管理：实现管理人员对系统的管理，包括DVD的入库、借还、零售等功能。3) 基本信息：实现显示客户和DVD的基本信息（包括客户信息及其租赁或购买的DVD信息，DVD的类型、库存等基本信息）。4) 查询：查询客户租赁或购买状况，某段时间内各DVD的零售、借还数量，已租赁但未还的DVD信息及客户信息，现有的DVD的库存情况等。5) 帮助系统：帮助系统为用户指明方向。图1 系统功能图1.2 系统功能分析1) 会员管理：用于增加会员，删除会员，会员基本信息的修改，会员缴费、退押金、退租金等。2) 业务操作：实现DVD的出租、返还、销售、退售、退租、添租功能。3) DVD管理：新添光盘入库，原有光盘进货，DVD光盘信息修改，光盘注销。4) 查询：已出租、销售DVD查询，即将缺货的DVD查询，某一DVD的出租、销售情况查询，会员租赁或购买的情况查询。5) 系统设置：操作员及其权限的设置，影片类型、会员类型的设置，非会员、会员的影片收费标准。6) 账务管理：添加、修改、删除账户，账户收支，所有资金及其明细等。第2章概念结构设计 2.1局部E-R图：图2 用户信息图图3 会员卡信息图图4 租赁信息图图5 零售信息图图6 DVD归还图7 DVD信息图8 DVD退购图9 DVD入库2.2 全局E-R图第3章逻辑结构设计3.1E-R图向关系模式转换逻辑结构是独立于任何一种数据模型的，在实际应用中，一般所用的数据库环境已经给定（如SQL Server或Oracel或MySql)。由于目前使用的数据库基本上都是关系数据库，因此首先需要将E-R图转换为关系模型，然后根据具体DBMS的特点和限制转换为特定的DBMS支持下的数据模型，最后进行优化系模式转换3.2建立关系模式DVD信息（DVD编号，DVD名称，导演，主演，类型，年份，进货商，现货，库存，租赁价格，出售价格，被租次数）用户信息（用户编号，用户类型，会员卡号，姓名，性别，联系方式，现住址，当前租赁，当前购买，历史租赁，历史购买）会员卡信息（会员卡号，用户编号，会员类型，状态，卡内余额，累计出租，登记日期）DVD租赁（租赁单号，DVD编号，DVD名称，租赁用户编号，应付押金，租赁日期，到期时间）DVD归还（归还单号，DVD编号，DVD名称，归还用户编号，归还日期，逾期天数，应付金额，损坏情况，应退押金）DVD零售（零售单号，DVD编号，DVD名称，购买用户编号，出售日期，出售数量，应付金额）DVD退购（退购单号， DVD编号，DVD名称，退购用户编号，退购日期，退购数量，有无损坏，应退金额）DVD入库（DVD编号，DVD名称，入库日期，入库数量）3.3 关系模式逻辑结构定义1用户详细信息字段名数据类型长度约束用户编号varchar12主键用户类型varchar10会员、非会员会员卡号varchar20可为空姓名varchar10不为空性别char2男或女联系方式varchar20不为空现住址varchar100不为空当前租赁int可为空当前购买int可为空历史租赁int可为空历史购买int可为空2会员卡详细信息字段名数据类型长度约束会员卡号varchar20主键用户编号varchar12外键姓名varchar10不为空会员类型varchar20普通、白金会员等状态varchar10激活、未激活卡内余额float可为空累计出租int可为空登记日期date不为空3DVD详细信息字段名数据类型长度约束DVD编号varchar10主键DVD名称varchar20不为空导演varchar10不为空主演varchar20不为空类型varchar10可为空年份varchar5不为空进货商varchar20不为空现货int不小于0库存int不小于0租赁价格float不为空出售价格float不为空被租次数int可为空4DVD入库信息字段名数据类型长度约束DVD编号varchar10主键、外键DVD名称varchar20不为空入库日期date不为空入库数量int不为空5 DVD租赁信息字段名数据类型长度约束租赁单号varchar12主键DVD编号varchar10外键DVD名称varchar20不为空租赁用户编号varchar12外键应付押金float不为空租赁日期date不为空到期时间date不为空6DVD归还信息字段名数据类型长度约束归还单号varchar12主键DVD编号varchar10外键DVD名称varchar20不为空归还用户编号varchar12外键归还日期date不为空逾期天数int4可为空应付金额float不为空损坏状况char2有、无应退押金float不为空7DVD零售信息字段名数据类型长度约束零售单号varchar12主键DVD编号varchar10外键DVD名称varchar20不为空购买用户编号varchar12外键出售日期date不为空出售数量int不为空应付金额float不为空8DVD退购信息字段名数据类型长度约束退购单号varchar12主键DVD编号varchar10外键DVD名称varchar20不为空退购用户编号varchar12外键退购日期date不为空退购数量int不为空有无损坏char2有、无应退金额float不为空第4章数据库物理结构设计4.1 存取方法设计数据库系统是多用户共享的系统，对同一个关系要建立多条存储路径才能满足多用户的多种应用要求。对于DVD零售出租管理系统来说，为了提高某些属性(如：DVD的现货、库存、出租、零售记录等)的查询速度，可以选择聚簇存取的方法，即把这些属性上具有相同值的元组集中放在连续的物理块上。这样在查询某一类DVD或同一主演、导演出租零售数量时就会大大提高查询速度。因此，该系统中选择聚簇存取方法。4.2存储结构设计DVD零售出租管理系统是一个复杂的计算机网络信息系统，采用基于客户端/服务器（C/S）应用体系结构来建设DVD零售出租管理系统。数据库管理系统采用Microsoft 公司推出的SQL Server 2000 或以上版本，并用SQL进行数据库的建立和数据库中数据的维护和查询。4.3物理设计实现该设计的环境为Windows XP Professional + MS SQL Server 2008 或以上版本。1、 建立数据库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)2、 建立用户信息表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、 建立会员卡信息表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 用户信息表(用户编号)4、 建立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）5、 建立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 用户信息表(用户编号)6、 建立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 用户信息表(用户编号)7、 建立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 用户信息表(用户编号)8、 建立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 用户信息表(用户编号)9、 建立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编号)第5章数据库完整性设计5.1 主键表名主键DVD信息表(DVD编号)用户信息表(用户编号)会员卡信息表(会员卡号)DVD租赁信息表(租赁编号)DVD归还信息表(归还编号)DVD零售信息表(零售编号)DVD退购信息表(退购编号)DVD入库信息表(DVD编号)5.2 参照完整性设计表名外键参照DVD租赁表(DVD编号，租赁用户编号)DVD信息表.DVD编号用户信息表.用户编号DVD归还表(DVD编号，归还用户编号)DVD信息表.DVD编号用户信息表.用户编号DVD零售表(DVD编号，购买用户编号)DVD信息表.DVD编号用户信息表.用户编号DVD退购表(DVD编号，退购用户编号)VD信息表.DVD编号用户信息表.用户编号DVD入库表(DVD编号)DVD信息表.DVD编号5.3Check约束1、会员卡信息表中将会员类型、状态进行check约束：check(会员类型 in(普通会员白金会员黄金会员钻石会员)check(状态 in(激活未激活)2、用户信息表中用户类型、性别进行check约束：check (用户类型in (会员非会员) ，check (性别in (男女)3、 DVD归还表中将损坏状况进行check约束：check (损坏状况in (有无)4、DVD退购表中将有无损坏进行check约束：check (有无损坏in (有无)5、DVD信息表中对现货、库存进行check约束：check(现货=0 )，check(库存=0 )5.4触发器设计1.实现出租时自动修改DVD现货及客户的当前租赁数量create trigger _rent on DVD租赁信息表-租赁触发器for insertasbeginupdate DVD信息表 set 现货=现货-1from DVD信息表,insertedwhere DVD信息表.DVD编号=inserted.DVD编号update 用户信息表 set 当前租赁=当前租赁+1from 用户信息表,insertedwhere 用户信息表.用户编号=inserted.租赁用户编号end2.实现归还时自动修改DVD现货及客户的当前租赁数量和历史租赁数量create trigger _return on DVD归还信息表-归还触发器for insertasbeginupdate DVD信息表 set 现货=现货+1from DVD信息表,insertedwhere DVD信息表.DVD编号=inserted.DVD编号update 用户信息表 set 当前租赁=当前租赁-1from 用户信息表,insertedwhere 用户信息表.用户编号=inserted.归还用户编号update 用户信息表 set 历史租赁=历史租赁+1from 用户信息表,insertedwhere 用户信息表.用户编号=inserted.归还用户编号update DVD归还信息表 set DVD编号=DVD租赁信息表.DVD编号,DVD名称=DVD租赁信息表.DVD名称,归还用户编号=DVD租赁信息表.租赁用户编号from DVD租赁信息表,DVD归还信息表,insertedwhere DVD租赁信息表.租赁单号=inserted.归还单号end3.实现入库时自动修改DVD库存create trigger _buy on DVD入库信息表-入库触发器for insertasupdate DVD信息表 set 库存=库存+inserted.入库数量from DVD信息表,insertedwhere DVD信息表.DVD编号=inserted.DVD编号4.实现零售时自动修改DVD库存及用户当前购买和历史购买create trigger _sale on DVD零售信息表-零售触发器for insertasbeginupdate 用户信息表 set 历史购买=当前购买from 用户信息表,insertedwhere 用户信息表.用户编号=inserted.购买用户编号update 用户信息表 set 当前购买=当前购买+inserted.出售数量from 用户信息表,insertedwhere 用户信息表.用户编号=inserted.购买用户编号update DVD信息表 set 库存=库存-inserted.出售数量from DVD信息表,insertedwhere DVD信息表.DVD编号=inserted.DVD编号end 5.实现退购时自动修改DVD库存及用户当前购买create trigger _off on DVD退购信息表-零售触发器for insertasbeginupdate 用户信息表 set 当前购买=当前购买-inserted.退购数量from 用户信息表,insertedwhere 用户信息表.用户编号=inserted.退购用户编号update DVD信息表 set 库存=库存+inserted.退购数量from DVD信息表,insertedwhere DVD信息表.DVD编号=inserted.DVD编号update DVD退购信息表 set DVD编号=DVD零售信息表.DVD编号,DVD名称=DVD零售信息表.DVD名称,退购用户编号=DVD零售信息表.购买用户编号from DVD零售信息表,DVD退购信息表,insertedwhere DVD零售信息表.零售单号=inserted.退购单号end第6章 数据库视图设计1.创建视图查询DVD的现货、库存create view numberasselect DVD编号,DVD名称,现货,库存from DVD信息表2.创建视图查询用户的租赁，购买状况create view user_numberasselect 当前购买,历史购买,当前租赁,历史租赁from 用户信息表3.创建视图查看会员情况create view vipasselect 会员卡信息表.会员卡号,会员卡信息表.姓名,性别,会员类型,状态,联系方式,现住址,卡内余额,登记日期from 会员卡信息表,用户信息表where 会员卡信息表.会员卡号=用户信息表.会员卡号4.创建视图查看影片是否缺货create view out_of_stockasselect DVD名称,导演,主演,类型from DVD信息表where DVD信息表.库存=0第7章 数据库存储过程设计创建存储过程统计某段时间内各DVD的销售、借还数量create procedure tongji begintime date,endtime date,type char(6) asdeclare leixing char(6)set leixing=typebeginif leixing=零售select begintime as 起始日期,endtime as 截至日期,DVD编号,SUM(出售数量) as 销售量from DVD零售信息表where 出售日期=begintime and 出售日期=begintime and 租赁日期=begintime an