VCD管理系统数据库课程设计解读.doc

Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateVCD管理系统数据库课程设计解读VCD管理系统数据库课程设计解读信息工程学院数据库原理与应用课程设计论文题 目：音响店VCD零售出租管理系统学 号：1212210225专业班级：计12本2姓 名：刘 康指导老师：尚佩妮完成日期：2014-12-28-目 录1引 言12 需求分析阶段22.1 引言22.2 需求分析阶段的目标与任务22.2.1 处理对象22.2.2 处理功能及要求32.2.3 安全性与完整性要求32.3 需求分析阶段成果32.3.1 体会与收获42.3.2 音像店VCD管理系统数据流图42.3.4 音像店VCD管理系统数据字典43 概念设计阶段73.1 引言73.2 任务与目标73.3 阶段结果74 逻辑结构设计94.1 逻辑设计的任务和目标94.2 数据组织94.2.1 将E-R图转换为关系模式94.2.2 模式优化104.2.3 数据库模式定义104.3 数据处理115 物理结构设计125.1物理设计阶段的目标与任务125.2数据存储方面126 数据库实施阶段136.1建立数据库、数据表、视图、索引136.1.1 建立数据库136.1.2 建立数据表136.1.3 建立视图156.1.4 建立索引166.1.5 建立触发器166.2数据入库186.3创建各个功能的存储过程197系统调试和测试198实习心得20附录1 存储过程定义21附录2 数据查看和存储过程功能的验证27附录3 所有的SQL运行语句31摘 要随着社会经济的发展，人们对生活质量的要求越来越高，对生活中的娱乐追求也是越来越高，传统的娱乐方式已经发生了变化。作为VCD销售商来说，更多的用户需要对VCD进行租赁。在租赁的过程中需求量大，操作复杂却流程完全相同，销售/租赁商频繁的做着相同的工作，既浪费时间又不能提高效率，这要求编写一个方便使用的VCD出租系统以其解决上诉问题。实现对商品的浏览，租赁和出售，并对销售的情况作出相关的统计，并对各类的VCD分类影片进行一个统计。利用该系统体现了管理者的思想，实现计算机的统一管理。通过使用计算机实现传统的VCD销售商的繁杂的手工操作计算环节，可大大提高工作效率。在互联网发展和服务效率至上的今天，各种重复的操作都可以交给计算机实现，既能学习先进的管理经验，有可以提高工作效率。所以对于VCD这个传统的行业来说，确实需要编写一个系统去代替人力完成工作。本文从VCD出租/销售系统的开发为背景，全文分为目录、需求分析、概要设计、逻辑设计、物理设计，以及系统的实现等全过程。在程序设计与调试上采用了自上而下，逐步细化，逐步完善的原则。采用结构化的功能模块设计系统功能，可读性好，易于扩充。基本功能全面，系统可读性好，易于维护、更新，安全性好。关键词：VCD销售；VCD出租；VCD管理1引 言随着互联网时代的到来，我们的生活方式发生了巨大的变化。利用计算机能够帮助我们解决各种问题。各种的应用软件层出不穷。人们对计算机智能化的要求越来越高，许多传统的手工管理逐渐被计算机系统所代替。人们的生活质量的要求越来越高，对生活中的娱乐方式也更加多样化，已经不再是很长的时间看一部很经典的电影。在过去的VCD出租和销售中都是依靠的商家的自己的手工的筛选和销售。对大量的客服而言的商家是既费时又费力，对VCD的管理也不能做到相对的统一，有明确的分类。计算机的出现，提供了一个很好的解决的方案，能够通过相关的管理软件对其进行统一的管理、销售、租赁等等商业活动。开发VCD零售/出租管理系统帮助商家提高效率，降低经营成本，优化资源配置，从而实现商品的最大利益化。2 需求分析阶段2.1 引言进行系统设计，首先要对系统的现状进行分析。根据系统的目标、需求和功能，制定和选择一个较好的系统方案，从而达到一个合理的优化系统。如今又许多的VCD销售商，最初购买的人数较少，手工管理还能应付，但是随着VCD需求人数的增多，传统的手工方式已远远不能满足商家的需求，就需要新的销售/出租系统去做。需求分析是在于要弄清用户对开发的数据库应用系统的确切要求。数据库设计的第一步是明确数据库的目的和如何使用，也就是说需要从数据库中得到哪些信息。明确目的之后，就可以确定您需要保存哪些主题的信息（表），以及每个主题需要保存哪些信息（表中字段）。在构造系统时，首先从需求出发构造数据库表，然后再由数据库表结合需求划分系统功能模块。这样，就把一个大的系统分解成了几个小系统。这里把系统的层次划分为了四个部分：用户信息管理，VCD信息管理，VCD销售信息管理，VCD出租信息管理。能够实现以下功能： 新的用户加入，用户信息及时更新； 对于新入库VCD，能够及时更新VCD信息，便于用户查询； 能够记录用户的销售和出租信息； 用户、商家均能查询相应的信息，方便交流；2.2 需求分析阶段的目标与任务2.2.1 处理对象用户信息：用户编号，用户性别，用户电话号码，用户身份证号码VCD信息：VCD编号，VCD类型号，VCD片名，导演，主演，上市时间，库存数量，格式VCD类型：VCD类型号，类型名VCD出租：出租编号，VCD编号，出租数量，出租时间，归还时间，押金，状态，用户编号VCD销售：VCD编号，用户编号，销售时间，销售数量2.2.2 处理功能及要求1能够存储一定数量的VCD信息,并方便有效的进行相应的VCD数据操作和管理，这主要包括：1) VCD信息的录入、删除及修改。2) VCD信息的多关键字检索查询。3) VCD的出租、销售管理。能够对一定数量的用户进行相应的信息存储与管理，这其中包括：1) 用户信息的登记、删除及修改。 3) 用户资料的统计与查询。3能够提供一定的安全机制，提供数据信息授权访问，防止随意删改、查询。4对查询、统计的结果能够列表显示。2.2.3 安全性与完整性要求1） 安全性要求 系统安全性要求体现在数据库安全性、信息安全性和系统平台的安全性等方面。安全性先通过视图机制，不同的用户只能访问系统授权的视图，这样可提供系统数据一定程度上的安全性，再通过分配权限、设置权限级别来区别对待不同操作者对数据库的操作来提高数据库的安全性；系统平台的安全性体现在操作系统的安全性、计算机系统的安全性和网络体系的安全性等方面。2） 完整性要求系统完整性要求系统中数据的正确性以及相容性。可通过建立主、外键，使用check约束，或者通过使用触发器和级联更新。2.3 需求分析阶段成果2.3.1 体会与收获系统需求分析主要是通过自己对于VCD销售与出租行业的了解，去咨询一些VCD销售商，以及通过互联网进行一些调查，了解VCD销售商在管理中的一些问题。由于没有经验和时间的不足，所以在做的过程中有许多问题，而又正值放假，所以只能自己通过互联网去查询，并通过数据流图一步一步了解其中过程，最终完成了需求分析任务。 2.3.2 音像店VCD管理系统数据流图顶层数据流图：图2.1 顶层数据流图第二层数据流图：图2.2 第二层数据流图2.3.4 音像店VCD管理系统数据字典a)数据项表1.1 数据项列表数据项编号数据项名数据项含义与其它数据项的关系存储结构别名DI-1UserID唯一标识一个用户char(9)用户标号DI-2UserSex用户的性别char(2)性别DI-3UserTel用户的电话号码char(11)电话DI-4UserNum用户身份证号码char(18)身份证号码DI-5VidVCD的编号char(10)VCD编号DI-6VtypeVCD类型号char(20)VCD类型号DI-7VnameVCD片名Char(30)VCD片名DI-8Vdirector该VCD的导演char(20)导演DI-9Vact该VCD的主演char (20)主演DI-10VdateVCD的上市时间Date上市时间DI-11Vamount该VCD当前的总数int库存数量DI-12Vform该VCD的播放格式Char(5)格式DI-13TnameVCD属于哪种类型Char(20)类型名DI-14Hid出租的VCD编号Char(5)出租编号DI-15Hamount出租该VCD的总数char(5)出租数量DI-16Htime出租该VCD的时间Date出租时间DI-17Hre归还该VCD的时间Date归还时间DI-18Hmoney出租VCD的抵押金Money押金DI-19Hstate该VCD是否可以出租Char(1)状态DI-20Stime该VCD的销售时间Date销售时间DI-21Samount该VCD的销售总数Char(5)销售总数b)数据结构：表1.2 数据结构列表数据结构编号数据结构名数据结构含义组成DS-1User用户信息UserID, UserSex, UserTel, UserNum DS-2VCDVCD信息Vid, Vtype, Vname, Vdirector, Vact, Vdata, Vamount, Vform DS-3TypeVCD类型Vtype, TnameDS-4HireVCD出租Vid, Hid, Hamount, Htime,Hre, Hmoney, HstateDS-5SaleVCD销售UserID, Vid, Stime, Smount3 概念设计阶段3.1 引言系统开发的总体目标是实现VCD销售商在VCD销售/出租方面的自动化，减轻工作人员的工作量，方便工作人员对它的操作，提高管理的质量和水平，做到高效、智能化管理，从而达到提高VCD销售/出租管理效率的目的。主要任务是对用户信息、VCD信息、VCD出租信息、VCD销售信息的基本信息的操作及外理。概念设计阶段主要是将需求分析阶段得到的用户需求抽象为信息结构(概念模型)的过程,它是整个数据库设计的关键。3.2 任务与目标（1）选择第二层数据流为切入点，通常选择实际系统中的子系统； （2）设计生成初步分E-R图，通过合并方法，做到各实体、属性、联系统一； （4）生成全局E-R图，通过消除冲突等方面。在本VCD销售/出租管理系统中，从第2层数据流程图下手。分析数据流图和数据字典，知道整个系统功能围绕“用户”和“VCD”的处理。根据实体与属性间的两条准则：生成各个分E-R图：从而合并成总E-R图：。3.3 阶段结果（1）a.VCD信息与VCD类型之间的关系：图3.1 分E-R图b.用户与VCD信息出租之间的关系：图3.2 分E-R图c.用户与VCD信息销售之间的关系：图3.3 分E-R图（2）基本E-R图图3,.4 总E-R图E-R图的各实体属性如下所示：用户：User(UserID, UserSex, UserTel, UserNum )VCD信息：VCD(Vid, Vtype, Vname, Vdirector, Vact, Vdata, Vamount, Vform)VCD类型：Type(Vtype, Tname)VCD出租：Hire(Vid, UserID,Hid, Hamount, Htime,Hre, Hmoney, Hstate)VCD销售：Sale(UserID, Vid, Stime, Smount);4 逻辑结构设计4.1 逻辑设计的任务和目标以上的概念设计阶段是独立于任何一种数据模型的，但是逻辑设计阶段就与选用的DBMS产品发生关系了，系统逻辑设计的任务就是将概念设计阶段设计好的基本E-R图转换为选用DBMS产品所支持的数据模型相符合的逻辑结构。具体内容包括数据组织（将E-R图转换成关系模型、模型优化、数据库模式定义、用户子模式设计）、数据处理（画出系统功能模块图）两大任务。4.2 数据组织4.2.1 将E-R图转换为关系模式实体型转换为关系模式。实体的属性就是关系的属性，实体的码就是关系的码。对于实体间的联系则有以下不同的情况：一个m：n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。一个1：n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。 一个1：1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。三个或三个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合具体的基本E-R图向关系模型的转化如下：用户：User(UserID, UserSex, UserTel, UserNum )VCD信息：VCD(Vid, Vtype, Vname, Vdirector, Vact, Vdata, Vamount, Vform)VCD类型：Type(Vtype, Tname)VCD出租：Hire(Vid , UserID, Hid, Hamount, Htime,Hre, Hmoney, Hstate)VCD销售：Sale(UserID, Vid, Stime, Smount);4.2.2 模式优化关系模式User,VCD,Type,Hire,Sale 既不存在非主属性对主属性的部分函数依赖，也不存在传递函数依赖，已经达到了3NF。4.2.3 数据库模式定义列名数据类型可否为空说明UserIDCharnot null用户编号UserSexChar用户性别UserTelChar用户电话号码UserNumCharnot null用户的身份证号列名数据类型可否为空说明VidCharnot nullVCD编号VtypeCharnot nullVCD类型号VnameCharnot nullVCD片名VdirectorChar导演VactChar主演VdataChar上市时间VamountDatenot null库存数量VformCharnot null播放格式列名数据类型可否为空说明VtypeCharnot nullVCD类型号TnameCharnot nullVCD类型名列名数据类型可否为空说明VidCharnot nullVCD编号UserIDCharnot null用户编号HidCharnot null出租编号HamountCharnot null出租数量HtimeChar出租时间HreChar归还时间HmoneyDate押金HstateCharnot null出租状态列名数据类型可否为空说明UserIDCharnot null用户编号VidCharnot nullVCD编号StimeChar销售时间SamountCharnot null销售数量4.3 数据处理系统功能模块图:图4.1 系统功能模块5 物理结构设计5.1物理设计阶段的目标与任务 数据库的物理设计就是为逻辑数据模型选取一个最合适应用要求的物理结构的过程，在这个阶段中要完成两大任务：（1）确定数据库的物理结构，在关系数据库中主要是存取方法和存储结构；（2）对物理结构进行评价，评价的重点是时间和空间效率。5.2数据存储方面为数据库中各基本表建立的索引如下：1. 由于基本表User，VCD的主码UserID，Vid经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，在两个属性上建立唯一性索引；2. 由于基本表VCD的属性Vname，Type的属性Tname经常在查询条件中出现在两个属性上建立聚簇索引；6 数据库实施阶段6.1建立数据库、数据表、视图、索引6.1.1 建立数据库Create database ManagerVCD;6.1.2 建立数据表（1）用户信息表：Create table User(UserID char(10) primary key,UserSex char(2) not null,UserTel char(11) not null,UserNum char(18) not null,);（2）VCD信息表Create table VCD(Vid char(10) primary key,Vname char(30) not null,Vtype char(20) not null,Vdirector char(20),Vact char(20),Vdate date,Vamount int not null,Vform char(5) not null,)（3）VCD类型表Create table Type(Vtype char(20) primary key;Tname char(20) not null,)（4）VCD出租表Create table Hire(Hid char(5) primary key,Vid char(10) not null,Hamount char(5),Htime date,Hre date,Hmoney int,Hstate char(2) not null,UserID char(9) not null,)（5）VCD销售表Create table Sale(UserID char(9) primary key,Vid char(10) primary key,Samount char(5) not null,Stime date,)外键约束通过MySQL语句进行添加（附录2）。6.1.3 建立视图（1）建立用户视图：Create view user_viewasSelect * from User（2）建立用户查询VCD视图;Create view user_QueryASselect VCD.Vid , Type.Tname , VCD.Vname , VCD.Vamountfrom VCD,Typewhere VCD.Vtype = Type.Vtype;（3）管理员查询用户视图(a)：Create View Manager_HAsSelect VCD.Vid , Hire.HamountFrom VCD , Hire Where VCD.Vid = Hire.Vid（4）管理员查询用户视图(b)：Create View Manager_SAsSelect VCD.Vid , Sale.SamountFrom VCD , SaleWhere VCD.Vid = Sale.Vid6.1.4 建立索引Create unique index user_id on User(UserID)Create unique index vcd_id on VCD(Vid)Create cluster index VCD_index on VCD(Vname)Create cluster index Type_index on Type(Tname)6.1.5 建立触发器（1）当删除Hire表中的一条出租记录时，触发VCD表，更新相应记录delimiter $create trigger t_vcd_hire_deleteafter delete on hirefor each rowbeginupdate vcdset vamount = vamount - old.hamountwhere vcd.Vid = old.vid;end $delimiter ;（2）当在Hire表中增加一条出租记录时，触发VCD，更新相应记录delimiter $create trigger t_vcd_hire_insertbefore insert on hirefor each rowbeginupdate vcdset vamount = vamount - new.hamountwhere vcd.Vid = new.vid;end $delimiter ;（3）当更新Hire表中的一条出租记录，触发VCD表，更新相应的记录delimiter $create trigger t_vcd_hire_updateafter update on hirefor each rowbeginupdate vcdset vamount = vamount - new.hamountwhere vcd.Vid = new.vid;end $delimiter ;（4）当删除Sale表中的一条销售记录时，触发VCD表，更新相应的记录delimiter $create trigger t_vcd_sale_deleteafter delete on salefor each rowbeginupdate vcdset vamount = vamount - old.samountwhere vcd.Vid = old.vid;end $delimiter ;（5）当在Sale表中增加一条销售记录时，触发VCD表，更新相应的记录delimiter $create trigger t_vcd_sale_insertafter insert on salefor each rowbeginupdate vcdset vamount = vamount - new.samountwhere vcd.Vid = new.vid;end $delimiter ;(6)当Sale表更新一条销售记录时，触发VCD表，更新相应的记录delimiter $create trigger t_vcd_sale_updateafter update on salefor each rowbeginupdate vcdset vamount = vamount - new.samountwhere vcd.Vid = new.vid;end $delimiter ;6.2数据入库系统包括VCD用户信息管理、VCD出租信息管理、VCD销售信息管理、VCD信息管理等几个功能模块，共有5张基本表,直接用MySQL建立这5张基本表,并将数据导入到相应的基本表中。6.3创建各个功能的存储过程系统共创建了12个存储过程，具体列表如下：表3.1 创建的存储过程列表编号存储过程名称定义作用P-1VCD_insert详见附录1-3在VCD中插入一个元组P-2User_insert详见附录1-1在User中插入一个元组P-3Type_insert详见附录1-2在Type中插入一个元组P-4User_delete详见附录1-4在User中删除一个元组P-5VCD_delete详见附录1-5从VCD中删除一个元组P-6Type_delete详见附录1-6从Type中删除一个元组P-7Query_User详见附录1-7用户在User中查询本人信息P-8Query_User_All详见附录1-8管理员在User中查询全部读者P-9Query_User_S详见附录1-9用户查询自己的VCD购买情况P-10Query_User_H详见附录1-10用户查询自己的VCD购买情况P-11Hire_insert详见附录1-11在Hire表中增加一条出租元组P-12Sale_insert详见附录1-12在Sale表中增加一条销售元组7系统调试和测试对该图书管理系统进行测试，验证每个功能是否符合要求，具体的测试如下：（1）通过视图查看各个基本表和视图中的数据（见附录2）（2）检测各个存储过程的功能：（见附录2）8实习心得1. 这次课程设计使我对数据库知识应用有了进一步认识，通过自己寻找各个资料，在纸上开始做需求分析，画数据流图，以及数据库的概念、逻辑、物理结构设计，让后边编写代码和编写文档比以往更方便。做纸上的东西却很麻烦，要做VCD系统，还需要去了解商家如何去操作的，以及如何做才能更方便的满足商家和用户的需求。2. 通过这次课程设计我学到了许多东西，包括课堂上的，当然还有课后学到的，都是通过去图书馆阅读和网上查阅。因为这次数据库设计使用的是MySQL语言，和我们平时学到的SQL server有些区别，所以在设计过程中就会有趣多问题，比如某个语法不一样，导致总是出现语法错误。就需要不多的去查阅网上的资料。3. 但是，我进一步掌握了相关的MySQL语句方面的知识。知道我们课堂上的知识太少了，几乎知识讲了方法和基本的语法。所以导致我总是认为建表就是查询、增加、删除、更新，再做课程设计之前都不知道视图具体是用来干嘛的，触发器、存储过程、索引几乎就是空白。现在了解了建立这些的用处了，以及使用主键、外键约束。通过这些保证数据库的完整性，并且更能方便不同权限的用户的操作。为用户提供方便。4 .总之，在整个课程设计的过程中，由于时间问题以及自身经验不足，所以这次做出来的东西存在许多问题。但是，不可否认，我学到了更多的东西，包括系统的分析方式，知道前期的需求分析的重要性，这样才能让后边的建表，建立触发器之类的顺利进行。附录1 存储过程定义1. User_insert的定义：delimiter $create procedure User_insert (in id char(9),in sex char(2),in Tel char(11),in num char(18) )begin insert into user( userid,usersex,usertel,usernum ) values (id,sex,tel,num);end $delimiter ;2. Type_insert的定义：delimiter $create procedure Type_insert (in type char(20),in name char(20) )begin insert into Type( Vtype,Tname ) values (type,name);end $delimiter ;3. VCD_insert的定义：delimiter $create procedure VCD_insert (in id char(10),in type char(20),in name char(30),in director char(20)in act char(20),in date date,in amount int,in form char(5)begin insert into VCD( Vid,Vtype,Vname,Vdirector,Vact,Vdate,Vamount,Vform ) values (id,type,name,director,act,date,amount,form);end $delimiter ;4.User_delete的定义:delimiter $create procedure User_delete (in id char(9)begindeletefrom userwhere user.userid = id;end $delimiter ;5.VCD_delete的定义:delimiter $create procedure User_delete (in id char(10)begindeletefrom VCDwhere VCD.vid = id;enddelimiter ;6.Type_delete的定义:delimiter $create procedure Type_delete (in type char(20)begindeletefrom Typewhere Type.Vtype = type;enddelimiter ;7. Query_User的定义delimiter $create procedure Query_User( in id char(9) )beginselect *from Userwhere id = user.userid;end $delimiter ;8. Query_User_All的定义：delimiter $create procedure Query_User_All( )beginselect *from User;end $delimiter ;9. Query_User_S的定义：delimiter $create procedure Query_User_S (in id char(9)beginselect user.userid,vcd.Vname,sale.Samountfrom user,sale,vcdwhere id = user.userid and id = sale.UserIDand vcd.Vid = sale.Vid;end $delimiter ;10.Query_User_H的定义：delimiter $create procedure Query_User_H(in id char(9)beginselect user.userid,vcd.Vname,Hire.Hamountfrom user,Hire,vcdwhere id = user.userid and id = Hire.UserIDand vcd.Vid = Hire.Vid;end $delimiter ;11. Hire_insert的定义：delimiter $create procedure Hire_insert (in v_id char(10),in h_id char(5),in amount char(5),in time date,in re date,in money int,in state char(2),in u_id char(9)begin insert into user( Hid,Vid,Hamount,Htime,Hre,Hmoney,Hstate,UserID ) values (v_id,h_id,amount,time,re,money,state,u_id);end $delimiter ;12.Sale_insert的定义：delimiter $create procedure Sale_insert (in v_id char(10),in u_id char(9),in time date ,in amount char(5) )begin insert into user( Vid,UserID,Stime,Samount ) values (v_id,u_id,time,amount);end $delimiter ;附录2 数据查看和存储过程功能的验证1） 查看VCD表中的信息：2） 查看Type(VCD类型)表中的信息：3） 查看Hire(VCD出租)表中的信息：4） 查看User(用户信息)表中的信息：5） 查看Sale(VCD销