数据库客户信息管理系统课程设计

数据库系统原理课程设计课题名称:客户信息管理系统姓名:刘俊岭班级：信管10-3班学号：３１1009030３19指导老师:刘小燕2013年1月６日目录TOC\ｏ"１-3"\ｈ\uHＹPＥRLIＮK\ｌ＿Toc1８4９３一、需求分析 PAGEREF＿Tｏc18４933HYPERＬIＮK＼l_Toｃ320151、系统的基本功能是: PＡGＥＲEF＿Toc32０153HYPＥRＬINK\l_Ｔoc１72２72、该系统数据库中的数据表: ＰAＧERＥF＿Toc1722７３ＨYPERLINK\l＿Ｔoｃ1４7633、数据流程图(DＦD)ﻩPAGEＲＥF_Ｔoc1４763３HYPＥRＬINK\l_Toc２19534、数据字典(ＤD) PAGEＲEF_Tｏｃ２1953４ＨYPERＬINK\ｌ＿Toc13４58二、概念结构设计ﻩPAGEREＦ_Ｔoc1３4584ＨYＰEＲＬＩＮK\l_Ｔoc１14781、定义每个实体的属性 PAGEREF＿Toc1147８4HYPERＬINK\ｌ_Ｔoｃ15３７９2、E－R实体模型图 PAGEＲEF_Toc1537９5HYPERＬINK＼l_Toc１7330三、逻辑结构设计ﻩPAGEＲEF_Toc17３306ＨＹＰERＬINK＼ｌ_Toc29８27四、物理结构设计ﻩPＡGEREF_Toｃ298277HＹPＥＲＬＩNK＼l＿Tｏc51101、对E-R图所需的实体以及联系设计需要的表 PＡGＥREF_Tｏc511０7ＨYPERLINK\l_Toc176851.1．企业员工表ﻩPAＧEREF_Toc1７6857HＹPＥRLＩNK\l＿Toc２14361.2.客户信息表ﻩPAGＥＲＥF_Toｃ2１4３６7ＨＹＰERLＩNK＼l_Toc4２04１.3.订单表 PAGEREF_Ｔoc4２0４8ＨYPERLINK\l＿Tｏc297931.4.设计数据库管理员表 PAＧＥRＥＦ_Toc2９7９38HYPERLINK＼l_Tｏc1６03８2、ＳQL语句 ＰＡＧEREF_Toｃ160388ＨYPＥＲLINＫ\ｌ_Toc２８８85五、数据库实施和维护ﻩＰAGEREF_Toc２88851１HYPERＬIＮK＼l_Ｔoｃ3３275.1、建表插入记录结果 ＰAGEREF_Toc３３２71１HYPERＬINK\ｌ_Toc121085.１．1员工基本信息表ﻩＰＡGEREF＿Toc1210811HYＰERLINK＼l＿Ｔoc30518５.1.２客户基本信息表 PAＧＥREＦ_Ｔoｃ3０51811HYPERＬINK\l_Tｏc６５2３５.1.３订单基本信息表ﻩPＡGEREF＿Ｔｏc652311HYＰERLＩＮK\l_Toｃ2９0０85．1.4数据库管理员表 ＰAGEREF＿Ｔoｃ２90081２HYPEＲＬINK＼ｌ_Toc2６5７６５.2、查询操作ﻩPＡGEREF_Toc2６5７61２HYPEＲＬINK\l_Ｔoc７30０５.２.1对员工表的查询操作ﻩＰAＧEREF＿Toc７300１２HYＰERＬINK＼l_Tｏc16189５.2.２对客户表的各种查询 PＡＧＥREF＿Toｃ１6１８912HYＰERＬINK\l_Toc１44０2５.２.3对订单表的查询操作ﻩPＡＧＥREF_Toc1440２１4HＹＰERLＩNK\l_Toc32402５.2.4对数据库管理员表的查询操作 16ＨYPERLINＫ\ｌ＿Toｃ２271０5.３、定义视图ﻩPAGＥRＥF_Toc2271０1７HYＰＥRLINK\l_Ｔoc1625３5．3．１定义员工表的视图 PAGＥREF_Toc1625317HYPＥRLINＫ\l_Ｔoc30２7１5.3.2定义客户表的视图ﻩPＡＧEREF_Tｏc302７117ＨYPＥRLINK＼l_Ｔoc２８8355．3.3定义订单表的视图ﻩPAＧＥＲEF_Toｃ２88351８HYＰＥRLINK\l_Toc17８335．3.4建立用户表的视图ﻩ７８3319ＨYＰＥＲLINK＼l_Tｏc2290０5.4、定义触发器 PAGＥRＥF_Toc２２900２0ＨYＰERLINK\l_Toc18３75.5、定义存储过程ﻩＰＡGＥRＥＦ_Toc18３7２２HYＰEＲＬIＮＫ\ｌ＿Toｃ7589５．５.1定义客户表上的存储过程 PAGＥREF_Toc75８92２HＹＰＥRLＩNK\l_Ｔoc108865.5.2定义员工表上的存储过程 PAGEREF_Tｏc108８623HYＰEＲLIＮK\l_Toc17７８3六、设计小结ﻩ８３24一、需求分析客户信息管理系统主要是公司为客户建立的档案信息,从而可以使公司很方便的对客户信息进行查找、插入、修改和删除等操作，同时通过对客户详细资料的深入分析，了解客户的爱好习性,从而从最大程度满足客户需求,提高企业竞争能力。客户的基本信息应包括:编号、客户名称、出生日期、电话、性别、地址、客户级别等。１、系统的基本功能是:（1）．新增客户:当有新客户时，向客户信息表里面添加一条新的记录。(2)．修改客户:当有客户的信息如联系方式需要修改时,可更新客户资料。(3).删除客户:当客户的信息已经失效或无用时,可以删除该客户的信息。（4).查询客户;可以根据不同的条件查询不同客户的信息。（５)．汇总反馈：可以进行总汇,反映每月、年的客户数量。2、该系统数据库中的数据表：(1）．企业员工表；（2)．客户信息表；（3）.订单表;３、数据流程图(DFD)订单信息订单信息记录客户信息客户信息查询企业员工信息信息查询结果客户管理企业员工表订单表客户信息表数据库管理员４、数据字典（DD）数据项名数据项含义数据类型长度取值范围员工编号唯一标识员工char１２数字员工姓名区分不同员工varcｈａr２5汉字或英语员工性别区别男女ｃhar2男、女员工职位判断员工级别vａrchar２0汉字或英语员工电话跟员工联系vaｒchar20数字员工住址联系员工ｖarcｈaｒ30汉字或英语客户编号唯一标识客户char1２数字客户名称区分不同客户vaｒchaｒ40汉字或英语邮政编码了解客户现居地char8数字出生日期知道客户的年龄datａtimｅ数字客户电话公司联系客户varchａr2０数字客户性别知道男女char２男、女客户地址公司联系客户ｖarchaｒ40汉字、英语订单编号唯一标识商品cｈａｒ１2数字客户编号唯一标识客户ｃｈaｒ12数字员工编号唯一标识员工chａｒ12数字订单运费运输费用ｃhaｒ8数字订单数量计算每一笔成交订单ｉｎt数字订单日期商品交易日期datetime二、概念结构设计概念结构的设计就是将需求分析得到的用户需求抽象为信息结构概念模型的过程，它是整个数据库设计的关键。描述概念模型的主要工具是E－R图,它将现实世界的信息结构统一用属性、实体以及他们之间爱你的联系来描述。根据需求分析，可看出有三个实体:员工、客户和订单。1、定义每个实体的属性企业员工:｛员工编号,员工姓名,员工性别，员工职位，员工电话,员工住址}客户:{客户编号,客户名称,邮政编码，出生日期,客户电话,客户性别,客户地址}订单:{订单编号,客户编号,员工编号,运费，订单数量,订单日期}2、E-Ｒ实体模型图(1）.企业员工实体Ｅ-Ｒ图如下:企业员工企业员工员工姓名员工编号员工职位员工性别员工电话员工住址(2）.客户实体E-Ｒ图如下：客户客户客户名称客户编号出生日期邮政编码客户电话电话客户性别客户地址（3)．订单实体E-R图如下:订单订单客户编号订单编号运费员工编号订单数量订单日期三、逻辑结构设计逻辑结构设计的任务是把概念结构设计阶段设计好的基本Ｅ-R图转换为与选用DBMＳ产品所支持的数据模型相符合的逻辑结构。（1）E-R图向关系模型的转换，实现将实体型和实体间的联系转换为关系模式。把E-R图转换为关系模型如下:员工(员工编号,员工姓名,员工性别,员工职位,联系方式，员工住址）客户（客户编号,客户姓名，邮政编码,出生日期,客户电话,客户性别，客户地址）订单（订单编号,员工编号，客户编号,订单运费，订单数量,订单日期)（备注：在以上的关系模式中,用下划线标出的是各个关系的主键,用波波线标出的是外键)四、物理结构设计１、对E-R图所需的实体以及联系设计需要的表１.1.企业员工表属性名属性含义数据类型EmｐlｏyｅeID员工编号chａrEｍployeｅＮame员工姓名vａrcｈａｒEsｅｘ员工性别chaｒtitle员工职位vaｒcharEtelｅphone员工电话varchａrEａddｒess员工住址varchar1.2.客户信息表属性名属性含义数据类型CustoｍerID客户编号ｃharＣustomeｒＮame客户名称varchａrｐoｓtｃｏde邮政编码ｃｈaｒbirthday出生日期dａtaｔimeＣtｅｌeｐhone客户电话ｖarcｈarCsex客户性别ｃharＣaｄdress客户地址varcｈar１.3．订单表属性名属性含义数据类型OrdeｒIＤ订单编号chａrCustomｅＩD客户编号chａrＥmployｅeＩD员工编号charｆreight订单运费charOrdercｏuｎt订单数量ｉｎｔOｒderdate订单日期dａteｔime1.４.设计数据库管理员表属性名属性含义数据类型UserＩD管理员编号chａrＵsｅrNａme管理员姓名ｃhａrＵsｅｒpassｗoｒd管理员密码char2、ＳQL语句-－建立员工基本信息表CＲＥATETＡＢＬEEmployeｅs( EｍｐｌｏｙeeIDcｈaｒ（１2）priｍaryｋey,ﻩEmplｏyeeNameｖａｒcｈaｒ(2５)NＯＴNULＬ,Eseｘｃｈａｒ(2)CHEＣK(EseｘＩN（＇男','女')),titlｅvarchar(20)ＮOTNULＬ，Etelephｏｎeｖaｒｃｈar(20)NOTNＵLＬ, Eadｄressvarcｈaｒ(30)ＮOTNULＬ)--插入5条员工基本信息记录ｉnsｅｒtintｏEmployeesvaluｅs(＇31１０0856２30１'，'张博'，'男','销售员'，'1３320387869',＇洛阳市学生路')ｉnｓertiｎtoＥmｐloyｅｅsvalues('311008562302','陈玉'，'女＇,'市场专员','１37３９03568４'，'郑州市农业路＇）inserｔinｔｏEmpｌoyeesｖalues(＇31100８５62303','刘鑫','男'，'销售经理',,'焦作市高新区')insｅrtintoEmployeｅsｖａlues（'3１1００８5６２304'，＇冯超','男','市场经理','1５２39102８56','焦作市丰收路')inserｔintoEmployeesｖaｌｕes（'31100８56230５'，'李红','女','采购专员＇,，'周口市迎宾路')--建立客户基本信息表CREAＴETABLEＣustｏmeｒs(CustoｍｅrIＤｃｈaｒ(12)pｒｉmａryｋey，ＣustomeｒNａｍevａrchar(40)NOTＮULL,posｔｃodeｃhaｒ（8），biｒtｈdaydatetｉmｅNOTNULＬ，Cteｌｅpｈonevaｒchar(20)NOTNULL,Cｓexchaｒ（２）CＨEＣK(ＣｓexＩN(＇男','女＇))，Caｄdressvａrchaｒ(４0)NOTNULＬ,)；－-向客户基本信息表内插入5条记录iｎｓｅrｔiｎtoCusｔomerｓvalues('3１10０９030301'，'张三'，４５4０00,'1990-6－15',,'男','焦作市高新区')insertiｎtoCuｓtｏmerｓｖalｕes（'3１100９0３0302＇,'王晓东',４5400０,'1９９0-4-15'，＇15２3913756８','男','焦作市高新区')insertinｔｏＣustomeｒsvalues('3１10０9030303＇,'李丽',45４0００，'199１-７-2３＇,，'女＇,＇焦作市高新区')insertｉｎtoCuｓｔomeｒsvaｌuｅs('311０090３0３0４',＇冯珊',4５4０00，＇19９０-12－04',＇1５２3919６8５4','女＇,'焦作市高新区')ｉnsｅrtiｎtoCusｔoｍerｓｖalｕｅs(＇３110０9０3030５＇,'顾峰',4５400０,'１９90-１-16',,＇男','焦作市高新区')－－建立订单基本信息表ＣREATEＴABＬEOrｄｅrｓ（ﻩOrdｅｒＩDcｈar(12)primarykｅｙ,ﻩＣustomerIDchar(12)NOTNULＬ，ﻩEmplｏyｅｅIＤｃhaｒ(12)NOTNＵLＬ,Freightchar(８),Ｏｒｄｅｒｃounｔinｔ,Oｒderｄateｄatetｉｍe ）-－插入5条订单基本信息记录iｎsertｉntoOｒｄersvalueｓ('Ａ001２００1','3１1０0903０301＇,'３１１００8562301＇,'５０0'，10,'２012-1０-０７＇）insertｉntoＯｒdeｒsvａｌuｅs(＇A０0１２０02','3１1009030302',＇311００8５62302','9０0'，１５,'２0１2-10-1１')ｉｎsertinｔoOｒｄｅrsvalueｓ('Ａ0012003','31１009030303'，'３１１008562303','1５００'，２5,'2012－11-03')inserｔｉｎtoOrｄersvaｌｕes('Ａ00１200４','3１100９03０304',＇31100８5623０4',＇450０'，９5,'2012-1１－11')insertｉntoOrderｓvalues('A0０１2０05','31100９０303０5','３1１0085６23０５',＇３０00',7０，'20１2-1２－20'）－-建立数据库管理员表ＣREATEＴABLEUｓers（ UｓｅrIDchar(12)ｐrｉmaｒykｅｙ，UｓerNameｃｈar（40），Ｕsｅrpａｓswoｒdchaｒ（８)NＯTNUＬＬ)--插入５条数据库管理员记录inseｒtｉｎtoUｓerｓvalueｓ('000001＇,'李华＇,'123４56')insertinｔoＵseｒsvａlueｓ（'0０0００２'，＇张涛'，'147258')inseｒtintoUsｅrsvalues（'000003','吴斌＇,'258３６９＇)iｎｓertinｔoUsersvalｕes('000004','孙福','748955')inｓertinｔoＵserｓvaluｅs（'0000０5','王铭',＇256６54')五、数据库实施和维护5．1、建表插入记录结果5．1.１员工基本信息表5．1.2客户基本信息表5.1.3订单基本信息表５.１.４数据库管理员表5.２、查询操作5．２.1对员工表的查询操作selｅct*fｒomEmplｏyees查询所有的员工信息seｌect*ｆromEｍｐｌoｙeeｓwhereｔｉtｌe＝'市场经理＇查询员工的基本信息ｓeｌect*fｒomＥｍployｅeｓwｈereｔitｌe＝＇市场专员＇5.２．2对客户表的各种查询sｅｌｅct*fromCustomerｓ查询所有的客户信息查询所有员工的住址selectCustoｍerName,CadｄresｓfromCustomeｒｓ查询所有员工的编号及其对应的名字ｓｅlｅctCustomerNaｍe，ＣuｓtomerIDｆromCuｓtomｅrｓ通过电话查询客户的信息selecｔ*ｆｒomＣｕｓｔomerswhereＣtｅlepｈｏne=＇15２3919５2３４'5．2.３对订单表的查询操作ｓeleｃt*frｏｍOｒdeｒs查询所有的订单信息通过订单时间查询订单的详细信息sｅｌecｔ*fromOrderswhｅｒeＯｒｄerdate＝'2012-１0－11'通过员工号查询所有的订单信息select＊fｒoｍＯｒｄeｒｓwhｅrｅＥｍployeeID=＇３１10０8５６２３02'通过时间查询订单信息select*fromOｒdersｗｈereOrderｄａtｅ=＇20１2-10-1１'通过运费查询订单信息sｅlｅｃｔ*frｏｍＯrderswherefrｅight>5０0查询员工编号为31１008562303的姓名、联系方式、所拥有的客户及订单信息SｅlectＥmploｙｅeＮamｅ,Ｅｔｅlｅphone，ＣｕstｏｍｅrName，OrdｅｒID,Freight,Orｄercounｔ,OrdｅrｄateFrｏmEmployｅes,Ordｅrｓ,CｕstomeｒsWhereEmployｅｅｓ.EｍｐloyｅｅID=Ordｅrｓ.ＥmploｙeeＩDandOrders.CustoｍeｒID=Ｃusｔomeｒs.ＣustｏmerＩDａndＥｍployeｅｓ．EmployeeIＤ=＇31100８５623０3'5．2.4对数据库管理员表的查询操作seｌecｔ*fｒomUsers查询数据库管理员的个数sｅleｃtcｏuｎt(*）客户数froｍUseｒs修改数据库管理员的密码5．3、定义视图5.3．１定义员工表的视图cｒeatｅvieｗEｍployeｅs＿viewassｅleｃt＊fromEｍployees命令完成后查询新建视图sｅlect＊ｆromEmｐｌｏyｅes_viｅw5.３．2定义客户表的视图cｒｅatevｉｅwCuｓtomｅｒs＿ｖiewａsseｌect＊froｍCｕｓtomeｒsseleｃt*fromCustomers_ｖｉeｗ建立男客户的视图，并显示男客户的基本信息ｃreatevｉｅwCseｘ_ｖiewasseｌecｔ*fromCｕstｏmeｒswherｅCsex='男'select*fromＣsex_view５.3.3定义订单表的视图creatｅviewOrders_vｉewasselecｔ*frｏmOｒdｅrｓｓｅlect＊froｍOrder_ｖｉew定义运费大于900的订单的视图ｃｒeateviewOrdｅrs_freiｇht_viewasseleｃt＊frｏｍOrdｅｒｓwｈerｅｆrｅiｇht>900seｌect＊ｆrｏｍＯrdｅrs＿freight＿viｅw5.3.4建立用户表的视图crｅａtevｉewUserｓ_vｉewasselect*fｒｏmUsｅrsｓelecｔ*froｍUsｅrｓ＿vieｗ5.4、定义触发器仅允许数据库管理员可以删除Cｕｓtomeｒ表中的数据creａteｔriggerCｕｓtｏmers_triggeronCusｔomｅrsfordeleteasbeginｉfuseｒ='dbo＇ｃomｍｉｔelsｅbｅginprｉnt＇仅允许dbｏ用户可以删除Cuｓtomｅrs表中的数据!！'roｌlbacｋtｒansactiｏnendＥnｄ仅允许数据库管理员可以修改Cｕsｔｏmｅrs表中的数据ｃreaｔeｔriggerCustomeｒs_triggｅr2onCｕstomersforupｄaｔeasｂeｇｉnifuser='dbo'cｏmmitｅlsebeginpriｎt'仅允许dｂo用户可以修改Customeｒｓ表中的数据!!'ｒｏllbaｃktraｎsactioｎendend在Oｒdeｒｓ上创建触发器，插入数据时要先检查Cusｔｏmｅrs表和Ｅmｐloyees表里面是否存在和Orｄerｓ表同值的订单编号、员工编号与客户编号。cｒｅaｔeｔrigｇerOrdeｒs_ｔｒiｇgerｏnOｒdersfｏrｉnseｒtasｉfeｘｉstｓ(ｓelｅｃt*froｍOrderswｈereOrderIDnotiｎ(ｓelectOｒｄeｒIDfrｏmＯｒｄers）or(EmｐlｏyeeIＤnoｔiｎ（sｅｌｅcｔEmpｌoyeeIDｆromEmployeｅｓ)andCｕstomｅrＩDnotin(ｓeｌecｔCｕｓtomerＩDfｒｏｍCｕｓtomｅrs)))beginprinｔ'Orderｓ表中不存在订单编号，或者Cusｔomers表中不存在客户编号和Empｌoyees表中不存在员工编号