数据库大作业物流管理系统附代码

/数据库设计作品题目：物流数据库管理系统组员1姓名：组员1学号：组员2姓名：组员2学号：完成时间：目录<供参考>1.数据库规划1.1任务陈述 本数据库的设计,是为了物流公司能够快捷高效的处理快递邮件,提高物流信息化水平,提高运输效率,同时增加企业管理者对公司运营情况的了解,和方便用户查询快递信息。1.2任务目标 维护〔录入、更新和删除企业员工的数据 维护〔录入、更新和删除客户的数据维护〔录入、更新和删除收件人的数据 维护〔录入、更新和删除运单的数据 维护〔录入、更新和删除快递线路的数据维护〔录入、更新和删除仓库的数据维护〔录入、更新和删除客户评价信息数据实现对货物物流信息的查询实现对快递员的查询实现对运单状态的查询实现对仓库的查询实现对收货人的查询实现对客户的查询跟踪物流信息跟踪运单状态信息报告公司运营情况报告公司员工情况报告客户情况报告仓库情况报告运单的情况1.3定义系统边界1.4主要用户视图经理：维护〔录入、更新、和删除公司的数据维护〔录入、更新、和删除员工的数据维护〔录入、更新、和删除用户的数据维护〔录入、更新、和删除仓库的数据实现对仓库的情况 实现对所有员工的检索报告所有公司运营的情况报告所有客户的情况 主管：维护〔录入、更新、和删除配送的数据维护〔录入、更新、和删除转运的数据维护〔录入、更新、和删除运单的数据维护〔录入、更新、和删除客户的数据实现对仓库检索 实现对员工的检索报告仓库的情况报告所在分公司货单的情况派送员：查看运单信息 查看配送信息查看配送信息查看收货人信息客户：查看快递配送信息 维护〔录入、更新、和删除评价信息2.需求分析2.1用户需求说明<重点论述>2.1.1数据需求快递物流公司职员分为经理、主管、派送员,经理拥有最高权限,可以对任意数据进行添加修改删除,主管可以增加运单信息和更改物流信息,查询客户资料,查看仓库情况,派送员只能查询客户资料,客户可以根据运单编号,查询运单详情.客户在提交货物时,要求填写基本信息,包括客户名称、客户电话、收货地址、等信息,并且由系统在注册用户注册完后自动为其生成一个客户编号,并且嵌入到客户注册信息内,形成完整的客户资料,同时,客户还将填写收件人信息,生成收货人信息表。系统自动生成运单,运单有唯一的编号,根据货件类型、邮寄地址,自动生成邮寄费用。每个仓库有具体负责的区域,根据收货人地址,可以确定货物转运到的仓库,若仓库已经满了,则不能转运到此仓库。当公司接受运单时,初始运单状态为"接受",若为此状态,用户还可以取消运单,则状态变为"取消"；当货物被转运至仓库时,订单状态变为"配送",之后再由配送员配送至收货人地址,由收件人接收后状态变为"完成"。当订单完成后,客户才可以给予评价。运单价格是根据货物重量计算得出,运单价格为货物重量×100.同时,公司记录统计运营情况,即收取快递的件数,营业额度等信息,并查看仓库存储货物的信息,判断仓库是否会出现爆满。员工工资计算为,当员工职位为经理时,当月薪水为当月接受运单总金额的1%,当员工职位为主管时,薪水为当月接受运单总金额的5%,,当员工职位为其配送运单数量乘以50。2.1.2事务需求2.1.2.1数据录入 1>录入公司的新员工信息2>录入公司仓库信息 3>录入新顾客信息 4>录入新运单的详细情况5>录入新转运表信息6>录入新配送表信息7>录入新收件人信息8>录入客户的评价信息2.1.2.2数据更新/删除1>更新/删除给定员工的信息2>更新删除给定仓库信息3>更新删除给定客户信息4>更新删除给定收货人信息 5>更新/删除给定运单的信息6>更新删除给定转运表信息7>更新删除给定配送表信息 8>更新/删除客户评价信息2.1.2.3数据查询<列出支持的查询,需要有一定具有统计功能的查询>1>按照职位、姓名顺序列出员工详细信息。2>利用存储过程,根据运单编号,查询运单状态以及相关信息3>根据配送表中,快速查询员工的完成的运单数4>根据客户评价中serve的评分,查询配送员工所有配送服务中的平均分5>根据客户评价中speed的评分,查询所有客户对物流速度的平均评分6>查看各个仓库所提供的配送服务的总平均评分7>列出指定日期运单总数量8>根据用户姓名,查询此人所在物流公司的运营记录以及总金额9>根据用户编号,查询此人在我物流公司的交易的详细信息10>根据收货人编号,查找其地址11>在配送表中,快速查询员工的完成的运单数12>>查询公司某一天的营业额13>查询公司某一年的营业额14>计算员工工资2.2系统需求说明2.2.1初始数据库大小公司拥有50名左右的员工,5个仓库。2.2.2数据库增长速度大约每天能进行1000左右笔运单和10个新顾客每月大概有5个左右的员工加入或者离开。离开书店半年的员工记录从数据库删除。每月大概删除2条员工记录.每年增加一个仓库。2.2.3记录查找的类型和平均数量客户查询快递运输的信息,每天大概100次企业经理查询营业部的运单数量,每天2次企业经理查询中转部的运输数量,每天2次2.2.4网络和共享访问需求整个系统必须全天24小时开放查询〔网站维护升级除外系统能够支持至少多名员工同时进行数据的增删改。需要考虑这么大数量并发访问的许可需求2.2.5性能1>客户查找运单信息的搜索时间不超过2秒2>各种搜索的响应时间少于3秒3>更新/保存记录的时间不超过1秒2.2.6安全性1>整个平台系统必须有口令密码支持2>每个不同的用户应该分配一个到特定用户视图的数据库访问权限,主要有系统管理员,数据管理员,客户可以查询指定的信息3>每位使用者只能在适合他们完成工作的需要的窗口中看到需要的数据2.2.7备份和恢复数据库在每天半夜12点进行增量备份和差异备份,在周日必须进行一次完整备份。3.逻辑设计3.1实体归档3.1.1实体定义客户 仓库 员工 收货人 评价3.1.2实体文档EntitynameDescriptionOccurrence客户提交货物公司拥有多个客户员工管理公司或者配送货物多名员工收件人收取货物的人一个运单包含一个收件人评价客户对快递服务的评价用户只能评价一次仓库储存货物的地方拥有多个仓库〔参考p126图9-23.3实体和关系的有关属性 〔应该给出实体和关系的所有属性,扩展p132图9-8的列名为：实体/关系名、属性、描述、键〔主键/侯选键、数据类型和长度、是否为空、是否多值、是否复合、是否派生、默认值、属性域约束客户信息表 kehu列名数据类型可否为空说明主/外键khnovarchar<20>NOTNULL客户编号主键khnamevarchar<20>NOTNULL客户姓名khtelvarchar<20>NOTNULL客户电话khsexvarchar<10>客户性别khaddvarchar<20>NOTNULL客户地址check<khsexin<"男","女">>员工信息表 yuangong列名数据类型可否为空说明主/外键ygnovarchar<20>NOTNULL员工编号主键ygnamevarchar<20>NOTNULL员工姓名ygposvarchar<20>NOTNULL员工职务ygsexvarchar<5>NOTNULL员工性别ygageintNOTNULL员工年龄ygtelvarchar<20>NOTNULL员工电话check<ygsexin<"男","女">>check<ygage>18andygage<65>check<ygposin<"经理","主管","快递员">>收货人信息表 shouhuoren列名数据类型可否为空说明主/外键shrnovarchar<20>NOTNULL收货人编号主键shrnamevarchar<20>NOTNULL收货人姓名shrsexvarchar<10>收货人性别shraddvarchar<20>NOTNULL收货人地址shrtelvarchar<20>NOTNULL收货人电话check<shrsexin<"男","女">>仓库 cangku列名数据类型可否为空说明主/外键cknovarchar<20>NOTNULL仓库编号主键ckaddvarchar<50>NOTNULL详细地址ckareavarchar<50>NOTNULL负责区域cktelvarchar〔20NOTNULL仓库电话ckcapintNOTNULL仓库容量ckweightintNOTNULL以存货物重量check<ckareain<"上海","北京","XX","XX","XX">>运单信息表 yundan列名数据类型可否为空说明主/外键ydnovarchar<20>NOTNULL货单编号主键ydpriceint运费总价ydstatevarchar<20>运单状态shrnovarchar<20>NOTNULL收货人编号外键khnovarchar<20>NOTNULL客户编号外键hwweightintNOTNULL货物重量sltimevarchar<20>NOTNULL受理时间check<ydstatein<"接受","派送","完成","取消">转运信息zhuanyun列名数据类型可否为空说明主/外键zynovarchar<20>NOTNULL转运编号主键ydnovarchar<20>NOTNULL运单编号外键cknovarchar<20>仓库编号外键ygnovarchar<20>NOTNULL员工编号外键qstimevarchar<20>起送时间rktimevarchar<20>入库时间配送信息 peisong列名数据类型可否为空说明主/外键psnovarchar<20>NOTNULL配送编号主键ydnovarchar<20>NOTNULL运单编号外键ygnovarchar<20>员工编号外键cktimevarchar<20>出库时间wctimevarchar<20>完成时间cknovarchar<20>NOTNULL仓库编号外键评价信息pingjia列名数据类型可否为空说明主/外键pjnovarchar<20>NOTNULL评价编号主键ydnovarchar<20>NOTNULL运单编号外键speedintNOTNULL运输速度serveintNOTNULL员工服务totalintNOTNULL评分advicevarchar〔100评价3.4ER图3.5表和外键属性存档yuangong<ygno,ygname,ygpos,ygsex,ygage,ygtel>primarykeyygnokehu<khno,khname,khtel,khsex,khadd>primarykeykhnohouhuoren<shrno,shrname,shrsex,shraddr,shrtel>primarykeyshrnoyundan<ydno,ydprice,shrno,khno,ygno>primarykeyydnoforeignkeyshrnoreferenceshouhuoren<shr>foreignkeykhnoreferenceshouhuoren<khno> cangku<ckno,ckadd,ckarea,cktel,ckcap,ckweight>primarykeycknozhuanyun<zyno,sno,yno,qstime,rktime>primarykeyzynoforeignkeysknoreferencecangku<ckno>foreignkeyygnoreferenceyuangong<ygno>peisong<psno,shrno,ygno,cktime,qstime>primarykeypsnoforeignkeyshrnoreferenceshouhuorenforeignkeyygnoreferenceyuangongpingjia<pjno,ydno,pf,pj>primarykeypjnoforeignkeyydnoreferenceyundan3.7定义参照完整性约束huowuforeignkeyydnoreferenceyundanonupdatecascadeondeletecascadezhuanyunforeignkeycknoreferencecangkuonupdatecascadeondeletenoactionforeignkeyygnoreferenceyuangongonupdatecascadeondeletenoactionpeisongforeignkeyshrnoreferenceshouhuorenonupdatecascadeondeletenoactionforeignkeyygnoreferenceyuangongonupdatecascadeondeletenoactionpingjiaforeignkeyydnoreferenceyundanonupdatecascadeondeletecascade3.8其他业务规则当运单状态为"接受"时,客户可以取消订单；当运单状态为"配送"时,订单生效不可 取消,当状态为"完成"时,客户才可以进行评价。仓库存储的货物总量不能超过其容量；若超过,则运单自动取消。4.物理设计4.1转换全局逻辑数据模型4.1.1创建基本表〔给出创建每个基本表的SQL语句,应有如下实现：表名、列名、主键/侯选键、外键、参照完整性约束,对每个列,应有数据类型和长度、默认、是否可空等信息<1>创建客户表createtablekehu<khnovarchar<20>notnullprimarykey,khnamevarchar<20>notnull,khtelvarchar<20>notnull,khsexvarchar<10>,khaddvarchar<20>notnull,check<khsexin<'男','女'>>>数据插入insertintokehuvalues<10001,'赵一',8800881,'男','上海号'>insertintokehuvalues<10002,'钱二',8800882,'男','XX号'>insertintokehuvalues<10003,'孙三',8800883,'女','北京号'>insertintokehuvalues<10004,'李四',8800884,'男','XX号'>insertintokehuvalues<10005,'周五',8800885,'男','XX号'>insertintokehuvalues<10006,'吴六',8800886,'男','北京号'>insertintokehuvalues<10007,'郑七',8800881,'女','上海号'>insertintokehuvalues<10008,'王八',8800881,'男','XX号'>insertintokehuvalues<10009,'冯九',8800881,'男','XX号'>insertintokehuvalues<10010,'陈十',8800881,'男','XX号'><2>创建员工表createtableyuangong<ygnovarchar<20>notnullprimarykey,ygnamevarchar<20>notnull,ygposvarchar<20>notnull,ygsexvarchar<20>notnull,ygageintnotnull,ygtelvarchar<20>notnull,check<ygsexin<'男','女'>>,check<ygage>18andygage<65>,check<ygposin<'经理','主管','快递员'>>,>数据插入insertintoyuangongvalues<20001,'褚一','经理','男',50,88228801>insertintoyuangongvalues<20002,'卫二','主管','男',48,88228802>insertintoyuangongvalues<20003,'蒋三','主管','男',46,88228803>insertintoyuangongvalues<20004,'沈四','主管','女',39,88228804>insertintoyuangongvalues<20005,'韩五','快递员','男',30,88228805>insertintoyuangongvalues<20006,'杨六','快递员','男',32,88228806>insertintoyuangongvalues<20007,'朱七','快递员','男',31,88228807>insertintoyuangongvalues<20008,'秦八','快递员','男',34,88228808>insertintoyuangongvalues<20009,'尤九','快递员','男',30,88228809>insertintoyuangongvalues<20010,'许十','快递员','男',29,88228810><3>创建收货人表createtableshouhuoren<shrnovarchar<20>notnullprimarykey,shrnamevarchar<20>notnull,shrsexvarchar<10>,shraddvarchar<20>notnull,shrtelvarchar<20>notnull,check<shrsexin<'男','女'>>,>数据插入insertintoshouhuorenvalues<30001,'何一','男','上海号',88330001>insertintoshouhuorenvalues<30002,'何二','男','上海号',88330002>insertintoshouhuorenvalues<30003,'何三','男','北京号',88330003>insertintoshouhuorenvalues<30004,'何四','男','北京号',88330004>insertintoshouhuorenvalues<30005,'何五','女','XX号',88330005>insertintoshouhuorenvalues<30006,'何六','男','XX号',88330006>insertintoshouhuorenvalues<30007,'何七','男','XX号',88330007>insertintoshouhuorenvalues<30008,'何八','女','XX号',88330008>insertintoshouhuorenvalues<30009,'何九','男','XX号',88330009>insertintoshouhuorenvalues<30010,'何十','男','XX号',88330010><4>创建仓库表createtablecangku<cknovarchar<20>notnullprimarykey,ckaddvarchar<20>notnull,ckareavarchar<20>notnull,cktelvarchar<20>notnull,ckcapintnotnull,ckweightintnotnulldefault0,check<ckareain<'上海','北京','XX','XX','XX'>>,>数据插入insertintocangkuvalues<1,'上海号','上海',88118800,10000,0>insertintocangkuvalues<2,'北京号','北京',88228800,10000,0>insertintocangkuvalues<3,'XX号','XX',88338800,10000,0>insertintocangkuvalues<4,'XX号','XX',88448800,10000,0>insertintocangkuvalues<5,'XX号','XX',88558800,10000,0><5>创建运单表createtableyundan<ydnovarchar<20>notnullprimarykey,ydpricevarchar<10>notnull,ydstatevarchar<20>notnulldefault‘接受’,shrnovarchar<20>notnull,khnovarchar<20>notnull,sltimevarchar<20>notnull,hwweightintnotnull,check<ydstatein<'接受','派送','完成','取消'>>,foreignkey<shrno>referencesshouhuoren<shrno>onupdatecascade,>运单数据插入insertintoyundanvalues<50001,10000,'接受',30001,10001,2014-05-08,100>insertintoyundanvalues<50002,10000,'接受',30002,10002,2014-05-08,100>insertintoyundanvalues<50003,10000,'接受',30003,10003,2014-05-09,100>insertintoyundanvalues<50004,10000,'接受',30004,10004,2014-05-09,100>insertintoyundanvalues<50005,10000,'接受',30005,10005,2014-05-10,100>insertintoyundanvalues<50006,20000,'接受',30006,10006,2014-05-10,200>insertintoyundanvalues<50007,20000,'接受',30007,10007,2014-05-11,200>insertintoyundanvalues<50008,20000,'接受',30008,10008,2014-05-11,200>insertintoyundanvalues<50009,20000,'接受',30009,10009,2014-05-12,200>insertintoyundanvalues<50010,20000,'接受',30010,10010,2014-05-12,200><6>创建转运表createtablezhuanyun<zynovarchar<20>notnullprimarykey,ydnovarchar<20>notnull,cknovarchar<20>notnull,ygnovarchar<20>notnull,qstimevarchar<20>,rktimevarchar<20>,foreignkey<ckno>referencescangku<ckno>,foreignkey<ygno>referencesyuangong<ygno>,foreignkey<ydno>referencesyundan<ydno>onupdatecascade>转运数据输入insertintozhuanyunvalues<60001,50001,1,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60002,50002,2,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60003,50003,3,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60004,50004,4,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60005,50005,5,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60006,50006,1,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60007,50007,1,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60008,50008,1,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60009,50009,1,20001,2014-05-09,2014-5-10>insertintozhuanyunvalues<60010,50010,1,20001,2014-05-09,2014-5-10><7>创建配送表createtablepeisong<psnovarchar<20>notnullprimarykey,ydnovarchar<20>notnull,ygnovarchar<20>,cktimevarchar<20>,wctimevarchar<20>,foreignkey<ygno>referencesyuangong<ygno>,foreignkey<ydno>referencesyundan<ydno>onupdatecascade>配送表数据输入insertintopeisongvalues<70001,50001,20008,2014-05-11,2014-05-12>insertintopeisongvalues<70002,50002,20008,2014-05-11,2014-05-12>insertintopeisongvalues<70003,50003,20008,2014-05-11,2014-05-12>insertintopeisongvalues<70004,50004,20009,2014-05-11,2014-05-12>insertintopeisongvalues<70005,50005,20009,2014-05-11,2014-05-12>insertintopeisongvalues<70006,50006,20009,2014-05-11,2014-05-12>insertintopeisongvalues<70007,50007,20010,2014-05-11,2014-05-12><8>创建评价表createtablepingjia<pjnovarchar<20>notnull,ydnovarchar<20>notnull,speedintnotnulldefault10,serveintnotnulldefault10,totalintnotnulldefault10,pjvarchar<100>,foreignkey<ydno>referencesyundan<ydno>,>insertintopingjiavalues<80001,50001,10,10,10,'好'>insertintopingjiavalues<80002,50002,8,10,9,'不错'>insertintopingjiavalues<80003,50003,10,6,8,'不错'>insertintopingjiavalues<80004,50004,10,8,9,'不错'>insertintopingjiavalues<80005,50005,10,10,10,'好'>insertintopingjiavalues<80006,50006,6,6,6,'有待加强'>insertintopingjiavalues<80007,50007,10,8,9,'不错'>4.1.2派生数据说明yundan表中的运费总价ydprice,根据货物重量乘以100pingjia表中的总评total,是根据speed/2+serve/24.1.3业务规则存档〔给出使用约束、触发器等来实现业务规则的情况<1>在运单表yundan上创建触发器,若price为null,则其值设为货物重量hwweight×100createtriggeryd_priceonyundanforinsertasdeclare@priceint,@hwweightint,@ydnovarchar<20>select@price=inserted.ydprice,@hwweight=inserted.hwweight,@ydno=inserted.ydnofrominsertedif<@priceisnull>beginupdateyundansetydprice=@hwweight*100whereydno=@ydnoend<2>转运表zhuanyun上创建触发器,若zhuanyun表中的cangku为null,则由仓库负责区域和收件人地址决定转运到哪个仓库createtriggerdecide_ckonzhuanyunforinsertasdeclare@zynovarchar<20>,@cknovarchar<20>,@ydnovarchar<20>,@shraddvarchar<20>select@ckno=inserted.ckno,@ydno=inserted.ydno,@zyno=inserted.zynofrominsertedif<@cknoisnull>beginselect@shradd=shr.shraddfrominserted,yundanyd,shouhuorenshrwhereinserted.ydno=yd.ydnoandyd.shrno=shr.shrnoprint@shraddif<@shraddlike'上海%'>set@ckno=1elseif<@shraddlike'北京%'>set@ckno=2elseif<@shraddlike'XX%'>set@ckno=3elseif<@shraddlike'XX%'>set@ckno=4elseset@ckno=5 updatezhuanyunsetckno=@cknowherezhuanyun.zyno=@zynoendgo<3>当运单状态为完成时,将不能修改运单createtriggeryundan_nofixonyundanforupdateasdeclare@statevarchar<20>select@state=deleted.ydstatefromdeletedif<@state='完成'>rollbacktransaction<4>当运单状态为完成时,不能在此运单上新建配送createtriggerpeisong_nofixonyundanforinsertasdeclare@statevarchar<20>select@state=inserted.ydnofrominsertedif<@state='完成'>rollbacktransaction<4>当运单状态为完成时,不能再在此运单上新建转运createtriggerzhuanyun_nofixonzhuanyunforinsertasdeclare@statevarchar<20>select@state=inserted.ydnofrominsertedif<@state='完成'>rollbacktransaction<5>当货物开始转运,新建转运项时,把订单状态设置为'派送'createtriggeradd_zyonzhuanyunforinsertasdeclare@ydnovarchar<20>select@ydno=inserted.ydnofrominsertedupdateyundansetyundan.ydstate='派送'whereyundan.ydno=@ydno<6>在zhuanyun表中设置触发器,计算仓库储存量ckweight的值,每当货物运进该仓库,ckweight加上该货物重量。当仓库货物达到或超过仓库容量时,则给予预警,createtriggerckweight_uponzhuanyunforupdate,insertasdeclare@hwweightbigint,@cknovarchar<20>,@ckweightbigint,@ckcapbigintselect@hwweight=yd.hwweight,@ckno=inserted.cknofromyundanyd,insertedwhereyd.ydno=inserted.ydnoupdatecangkusetcangku.ckweight=cangku.ckweight+@hwweightwherecangku.ckno=@cknoselect@ckweight=ck.ckweight,@ckcap=ck.ckcap,@ckno=inserted.cknofrominserted,cangkuckwhereinserted.ckno=ck.cknoif<@ckweight>=@ckcap>beginrollbacktransactionprint'仓请注意'+@ckno+'仓库以满'endgo<8>在zhuanyun表中设置触发器,当新建配送项时,表示运单已经完成,运单状态ydstate变为'完成'createtriggeradd_psonpeisongforinsertasdeclare@ydnovarchar<20>select@ydno=inserted.ydnofrominsertedupdateyundansetyundan.ydstate='完成'whereyundan.ydno=@ydno<9>在运单表中设置一个触发器,当运单状态为"接受"时,可以取消订单,为其他状态时,无法取消订单createtriggerkehu_droponyundanfordeleteasdeclare@statevarchar<20>,@ydnovarchar<20>select@state=ydstate,@ydno=ydnofromdeletedif<@state='接受'>beginprint'运单已取消'updateyundansetyundan.ydstate='取消'whereyundan.ydno=@ydnoendelsebeginprint'对不起,运单已经在派送路上,不能取消'rollbacktransactionend<10>在peisong表中设置触发器,计算仓库储存量ckweight的值,当货物开始配送时,仓库中的ckweight减去该货物的重量createtriggerckweight_down1onpeisongforupdate,insertasdeclare@hwweightint,@cknovarchar<20>,@ydnovarchar<20>,@mycangkuintselect@hwweight=yd.hwweight,@ydno=inserted.ydno,@ckno=zy.cknofromyundanyd,inserted,zhuanyunzywhereyd.ydno=inserted.ydnoandzy.ydno=inserted.ydnoupdatecangkusetcangku.ckweight=cangku.ckweight-@hwweightwherecangku.ckno=@cknoselect@mycangku=cangku.ckweightfromcangkuwherecangku.ckno=@cknoprint@cknoprint@hwweightprint@mycangkugo<11>在pingjia表中设置一个触发器,当用户修改pingjia表中的speed或者serve评分时,total自动修改createtriggerupdate_pingjiaonpingjiaforupdateasifupdate<speed>orupdate<serve>declare@speedint,@serveint,@pjnovarchar<20>beginselect@speed=inserted.speed,@serve=inserted.serve,@pjno=inserted.pjnofrominsertedupdatepingjiasettotal=@speed/2+@serve/2wherepjno=@pjnoend<12>当运单状态不为完成时,则不能评价createtriggernew_pjonpingjiaforinsertasdeclare@statevarchar<20>select@state=yd.ydstatefrominserted,yundanydwhereinserted.ydno=yd.ydnoif<@state<>'完成'>rollbacktransaction4.2设计物理表示法4.2.1事务需求的实现〔针对2.1.2节的每一个事务需求,给出SQL源代码的实现；可以把其中复杂的事务需求封装成为存储过程,对于这些存储过程1、给出SQL源代码2、给出对存储过程功能的说明3、对每个存储过程给出一个测试用例4.2.1.1数据录入<1>利用存储过程,给yuangong表添加信息createprocinsert_yg@ygnovarchar<20>,@ygnamevarchar<20>,@ygposvarchar<20>,@ygsexvarchar<20>,@ygageint,@ygtelvarchar<20>asinsertintoyuangongvalues<@ygno,@ygname,@ygpos,@ygsex,@ygage,@ygtel>goexecinsert_yg20022,'许十','快递员','男',29,88225811<2>利用存储过程,给cangku表添加信息createprocinsert_ck@cknovarchar<20>,@ckaddvarchar<20>,@ckareavarchar<20>,@cktelvarchar<20>,@ckcapvarchar<20>,@ckweightvarchar<20>asinsertintocangkuvalues<@ckno,@ckadd,@ckarea,@cktel,@ckcap,@ckweight>goexecinsert_ck5,'XX号','XX',88558800,10000,0<3>利用存储过程,给kehu表添加信息createprocinsert_kh@khnovarchar<20>,@khnamevarchar<20>,@khtelvarchar<20>,@khsexvarchar<10>,@khaddvarchar<20>asinsertintokehuvalues<@khno,@khname,@khtel,@khsex,@khadd>execinsert_kh10025,'王九',8804881,'男','XX号'<4>利用存储过程,给shouhuoren表添加信息createprocinsert_shr@shrnovarchar<20>,@shrnamevarchar<20>,@shrsexvarchar<10>,@shraddvarchar<20>,@shrtelvarchar<20>asinsertintoshouhuorenvalues<@shrno,@shrname,@shrsex,@shradd,@shrtel>goexecinsert_shr30013,'何十','男','北京号',88430010<5>利用存储过程,给yundan表添加信息createprocinsert_yd@ydnovarchar<20>,@ydpriceint,@ydstatevarchar<20>,@shrnovarchar<20>,@khnovarchar<20>,@sltimevarchar<20>,@heweightintasinsertintoyundanvalues<@ydno,@ydprice,@ydstate,@shrno,@khno,@sltime,@heweight>go<6>利用存储过程,给zhuanyun表添加信息createprocinsert_zy@zynovarchar<20>,@ydnovarchar<20>,@cknovarchar<20>,@ygnovarchar<20>,@qstimevarchar<20>,@rktimevarchar<20>asinsertintozhuanyunvalues<@zyno,@ydno,@ckno,@ygno,@qstime,@rktime>go<7>利用存储过程,给peisong表添加信息createprocinsert_ps@psnovarchar<20>,@ydnovarchar<20>,@ygnovarchar<20>,@cktimevarchar<20>,@wctimevarchar<20>asinsertintopeisongvalues<@psno,@ydno,@ygno,@cktime,@wctime>go<8>利用存储过程,给pingjia表添加信息createprocinsert_pj@pjnovarchar<20>,@ydnovarchar<20>,@speedint,@serveint,@totalint,@pjvarchar<20>asinsertintopingjiavalues<@pjno,@ydno,@speed,@serve,@total,@pj>go4.2.1.2数据更新UPDATE表名称SET列名称=新值WHERE列名称=某值DELETEFROM表名称WHERE列名称=值1>按照职位按姓名顺序列出员工详细信息。select*fromyuangongygorderby<selectcaseyg.ygposwhen'经理'then1when'主管'then2when'快递员'then3end>,yg.ygno2>利用存储过程,根据运单编号,查询运单状态以及相关信息createprocedureydchaxun@ydnovarchar<20>asdeclare@ydstatevarchar<20>,@qstimedate,@rktimedate,@cktimedate,@wctimedate,@ygname1varchar<20>,@ygname2varchar<20>select@ydstate=ydstatefromyundanwhereydno=@ydnoif<@ydstate='接受'>print@ydno+'号运单已经接受'elseif<@ydstate='派送'>beginselect@qstime=zy.qstime,@rktime=zy.rktime,@ygname1=yg.ygnamefromzhuanyunzy,yuangongyg,yundanydwherezy.ygno=yg.ygnoandyd.ydno=@ydnoandyd.ydno=zy.ydnoprint@ydno+'号运单在'+convert<varchar<20>,@qstime>+'开始为您转运,已于'+convert<varchar<20>,@rktime>+'转运至仓库,转运人'+@ygname1endelseif<@ydstate='完成'>beginselect@qstime=zy.qstime,@rktime=zy.rktime,@ygname1=yg.ygnamefromzhuanyunzy,yuangongyg,yundanydwherezy.ygno=yg.ygnoandyd.ydno=@ydnoandyd.ydno=zy.ydnoprint@ydno+'号运单在'+convert<varchar<20>,@qstime>+'开始为您转运,已于'+convert<varchar<20>,@rktime>+'转运至仓库,转运人'+@ygname1select@cktime=ps.cktime,@wctime=ps.wctime,@ygname2=yg.ygnamefrompeisongps,yuangongyg,yundanydwhereps.ygno=yg.ygnoandyd.ydno=@ydnoandps.ydno=yd.ydnoprint@ydno+'号运单在'+convert<varchar<20>,@cktime>+'已经出库,已于'+convert<varchar<20>,@wctime>+'完成,配送人'+@ygname2endelseif<@ydstate='取消'>print@ydno+'已经取消'execydchaxun500193>在配送表中,快速查询员工的完成的运单数createprocyg_ydsumasselectyg.ygnoas员工编号,yg.ygnameas员工姓名,COUNT<ps.psno>fromyuangongyg,peisongps,yundanydwhereyd.ydno=ps.ydnoandps.ygno=yg.ygnogroupbyyg.ygname,yg.ygnoorderbyCOUNT<ps.psno>descgoexecyg_ydsum4>根据客户评价中serve的评分,查询配送员工所有配送服务中的平均分selectyg.ygnoas员工编号,yg.ygnameas员工姓名,AVG<pj.serve>as服务平均分fromyuangongyg,peisongps,pingjiapjwhereps.ygno=yg.ygnoandps.ydno=pj.ydnogroupbyyg.ygno,yg.ygnameorderbyAVG<pj.serve>desc5>根据客户评价中speed的评分,查询所有客户对物流速度的平均评分selectAVG<speed>as物流速度的平均评分frompingjia6>查看各个仓库所提供的配送服务的总平均评分selectck.cknoas'仓库编号',ck.ckareaas'仓库区域',AVG<pj.total>as'服务平均评分'fromcangkuck,pingjiapj,zhuanyunzywherepj.ydno=zy.ydnoandzy.ckno=ck.cknogroupbyck.ckno,ck.ckareaorderbyAVG<pj.total>desc7>列出指定日期运单总数量createprocydsum_date@mydatevarchar<20>asselectcount<yd.ydno>as订单总数fromyundanydwhereyd.sltime=@mydategoexecydsum_date'2014-5-13'<8>根据用户姓名,查询此人所在物流公司的运营记录以及总金额createprocedurekehu_pricesum@khnamevarchar<20>asdeclare@khnovarchar<20>,@khtelvarchar<20>,@khaddvarchar<20>,@sumpriceintset@sumprice=0selectkh.khnoas客户编号,kh.khnameas客户姓名,sum<yd.ydprice>as总金额fromyundanyd,kehukhwherekh.khname=@khnamegroupbykh.khname,kh.khnogoexeckehu_pricesum赵一<9>根据用户编号,查询此人在我物流公司的交易的详细信息createprocedurejiaoyi_detail@khnovarchar<20>asselectkh.khnoas客户编号,kh.khnameas客户姓名,yd.sltimeas运单受理时间,ps.wctimeas完成时间,shr.shrnameas收货人,yd.ydpriceas运单金额fromkehukh,yundanyd,shouhuorenshr,peisongpswherekh.khno=@khnoandyd.shrno=shr.shrnoandps.ydno=yd.ydnoexecjiaoyi_detail1000110>根据收货人编号,查找其地址createprocshrchaxun@shrnovarchar<20>asselectshr.shrname,shr.shrtel,shr.shraddfromshouhuorenshrwhereshr.shrno=@shrnogoexecshrchaxun3000111>在配送表中,快速查询员工的完成的运单数createprocyg_ydsumasselectyg.ygnoas员工编号,yg.ygnameas员工姓名,COUNT<ps.psno>as配送运单总数fromyuangongyg,peisongps,yundanydwhereyd.ydno=ps.ydnoandps.ygno=yg.ygnogroupbyyg.ygname,yg.ygnoorderbyCOUNT<ps.psno>descgoexecyg_ydsum12>>查询公司某一天的营业额createprocsumprice_day@onedaydateasselectyd.sltimeas日期,SUM<yd.ydprice>as营业总额fromyundanydwhereyd.sltime=@onedaygroupbyyd.sltimeexecsumprice_day'2014-5-9'13>查询公司某一年的营业额createprocsumprice_year@oneyeardateasselectyear<yd.sltime>as年分,SUM<yd.ydprice>as营业总额fromyundanydwhereyear<yd.sltime>=year<@oneyear>groupbyyear<yd.sltime>execsumprice_year'2014'14>计算员工薪水createprocyg_salary@ygnovarchar<20>,@one_monthvarchar<20>asdeclare@ygposvarchar<20>,@month_sumpriceint,@psycountintselect@ygpos=yg.ygposfromyuangongygwhereyg.ygno=@ygnoselect@month_sumprice=SUM<yd.ydprice>fromyundanydwhereconvert<varchar<20>,MONTH<yd.sltime>>=@one_monthgroupbyMONTH<yd.sltime>beginif<@ygpos='经理'>beginprint@ygno+'员工职位为'+@ygpos+',当月薪水为'+convert<varchar<20>,0.01*@month_sumprice>endelseif<@ygpos='主管'>beginprint@ygno+'员工职位为'+@ygpos+',当月薪水为'+convert<varchar<20>,0.005*@month_sumprice>endelseif<@ygpos='快递员'>beginselect@psycount=COUNT<ps.psno>fromyuangongyg,peisongps,yundanydwhereyd.ydno=ps.ydnoandps.ygno=yg.ygnoandyg.ygno=@ygnoandconvert<varchar<20>,MONTH<yd.sltime>>=@one_monthprint@ygno+'员工职位为'+@ygpos+',当月薪水为'+convert<varchar<20>,@