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

数据库设计作品题目：物流数据库管理系统组员1姓名：组员学号：组员2姓名：组员2学号：完成时间:书目（供参考）.数据库规划1.l任务陈述本数据库的设计，是为了物流公司能够快捷高效的处理快递邮件，提高物流信息化水平，提高运输效率，同时增加企业管理者对公司运营状况的了解，和便利用户查询快递信息。1.2 任务目标维护（录入、更新和删除）企业员工的数据维护（录入、更新和删除）客户的数据维护（录入、更新和删除）收件人的数据维护（录入、更新和删除）运单的数据维护（录入、更新和删除）快递线路的数据维护（录入、更新和删除）仓库的数据维护（录入、更新和删除）客户评价信息数据实现对货物物流信息的查询实现对快递员的查询实现对运单状态的查询实现对仓库的查询实现对收货人的查询实现对客户的查询跟踪物流信息跟踪运单状态信息报告公司运营状况报告公司员工状况报告客户状况报告仓库状况报告运单的状况1.3 定义系统边界1.4 主要用户视图经理：维护（录入、更新、和删除）公司的数据维护（录入、更新、和删除）员工的数据维护（录入、更新、和删除）用户的数据维护（录入、更新、和删除）仓库的数据实现对仓库的状况实现对全部员工的检索报告全部公司运营的状况报告全部客户的状况主管：维护（录入、更新、和删除）配送的数据维护（录入、更新、和删除）转运的数据维护（录入、更新、和删除）运单的数据维护（录入、更新、和删除）客户的数据实现对仓库检索实现对员工的检索报告仓库的状况报告所在分公司货单的状况派送员：查看运单信息查看配送信息查看配送信息查看收货人信息客户：查看快递配送信息维护（录入、更新、和删除）评价信息2 .需求分析2.2 用户需求说明（重点论述）2.Ll数据需求快递物流公司职员分为经理、主管、派送员，经理拥有最高权限，可以对随意数据进行添加修改删除，主管可以增加运单信息和更改物流信息，查询客户资料，查看仓库状况，派送员只能查询客户资料，客户可以依据运单编号，查询运单详情.客户在提交货物时，要求填写基本信息，包括客户名称、客户电话、收货地址、等信息，并且由系统在注册用户注册完后自动为其生成一个客户编号，并且嵌入到客户注册信息内，形成完整的客户资料，同时，客户还将填写收件人信息，生成收货人信息表。系统自动生成运单，运单有唯一的编号，依据货件类型、邮寄地址，自动生成邮寄费用。每个仓库有具体负责的区域,依据收货人地址,可以确定货物转运到的仓库，若仓库已经满了，则不能转运到此仓库。当公司接受运单时，初始运单状态为“接受"，若为此状态，用户还可以取消运单，则状态变为"取消"；当货物被转运至仓库时，订单状态变为"配送"，之后再由配送员配送至收货人地址，由收件人接收后状态变为“完成"。当订单完成后，客户才可以赐予评价。运单价格是依据货物重量计算得出，运单价格为货物重量XlOO.同时，公司记录统计运营状况，即收取快递的件数，营业额度等信息，并查看仓库存储货物的信息，推断仓库是否会出现爆满。员工工资计算为，当员工职位为经理时，当月薪水为当月接受运单总金额的1%,当员工职位为主管时，薪水为当月接受运单总金额的5%"当员工职位为其配送运单数量乘以50。2.1.2事务需求2.L2.1数据录入1）录入公司的新员工信息2）录入公司仓库信息3）录入新顾客信息4）录入新运单的具体状况5）录入新转运表信息6）录入新配送表信息7）录入新收件人信息8）录入客户的评价信息2.L2.2数据更新/删除1）更新/删除给定员工的信息2）更新删除给定仓库信息3）更新删除给定客户信息4）更新删除给定收货人信息5）更新/删除给定运单的信息6）更新删除给定转运表信息7）更新删除给定配送表信息8）更新/删除客户评价信息2.L2.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.L1实体定义客户仓库员工收货人评价3.1.2实体文档EntitynameDescriptionOccurrence客户提交货物公司拥有多个客户员工管理公司或者配送货物多名员工收件人收取货物的人一个运单包含一个收件人评价客户对快递服务的评价用户只能评价一次仓库储存货物的地方拥有多个仓库（参考pl26图9-2）3.3实体和关系的有关属性（应当给出实体和关系的全部属性，扩展pl32图9-8的列名为：实体/关系名、属性、描述、键（主键/侯选键）、数据类型和长度、是否为空、是否多值、是否复合、是否派生、默认值、属性域约束）客户信息表kehu列名数据类型可否为空说明主/外键khnovarchar(20)NOTNULL客户编号主键khnamevarchar(20)NOTNULL客户姓名khtelvarchar(20)NOTNULL客户电话khsexvarchar(10)客户性别khaddvarchar(20)NOTNULL客户地址check(khsexin(“男"女")员工信息表yuangong列名数据类型可否为空说明主/外键yg11ovarchar(20)NOTNULL员工编号主键ygnamevarchar(20)NOTNULL员匚姓名ygposvarchar(20)NOTNULL员工职务ygsexvarchar(5)NOTNULL员工性别ygageintNOTNULL员工年龄ygtelvarchar(20NOT员工电话)NULLcheck(ygsexin(“男"，"女")check(ygage>18andygage<65)check(ygposin(“经理"J主管"J快递员”)收货人信息表Shouhuoren列名数据类型可否为空说明主/外键shrnovarchar(20)NOTNULL收货人编号主键shrnamevarchar(20)NOTNULL收货人姓名shrsexVarchar(IO)收货人性别shraddvarchar(20)NOTNULL收货人地址shrtelvarchar(20)NOTNULL收货人电话check(shrsexin(“男”/女”)仓库cangku列名数据类型可否为空说明主/外键cknovarchar(20)NOTNULL仓库编号主键ckaddvarchar(50)NOTNULL具体地址ckareavarchar(50)NOTNULL负责区域cktelvarchar(20)NOTNULL仓库电话ckcapintNOTNULL仓库容量CkweightintNOTNULL以存货物重量check(ckareain(“上海”，"北京“J厦门“J武汉“J广州”)运单信息表yundan列名数据类型可否为空说明主/外键ydnovarchar(20)NOTNULL货单编号主键ydpriceint运费总价ydstatevarchar(20)运单状态shrnovarchar(20)NOTNULL收货人编号外键khnovarchar(20)NOTNULL客户编号外键hwweightintNOTNULL货物重量sltimevarchar(20)NOTNULL受理时间check（ydstatein（“接受”派送“J完成“J取消”）转运信息Zhuanyun列名数据类型可否为空说明主/外键zynovarchar(20)NOTNULL转运编号主键ydnovarchar(20)NOTNULL运单编号外键cknovarchar(20)仓库编号外键Ygnovarchar(20)NOTNULL员工编号外键qstimevarchar(20)起送时间rktimevarchar(20)入库时间配送信息eisong列名数据类型可否为空说明主/外键psnovarchar(20)NOTNULL配送编号主键ydnovarchar(20)NOTNULL运单编号外键ygnovarchar(20)员工编号外键cktimevarchar(20)出库时间wctimevarchar(20)完成时间cknovarchar(20)NOTNULL仓库编号外键评价信息Pingjia列名数据类型可否为空说明主/外键PjnOvarchar(20)NOT评价编号主键NULLydnovarchar(20)NOTNULL运单编号外键speedintNOTNULL运输速度serveintNOTNULL员工服务totalintNOTNULL评分advicevarchar(100)评价3.4ER图3.5 表和外键属性存档yuangong(ygno,ygname,ygpos,ygsex,ygage,ygtel)primarykeyygnokehu(ldmo,khname,khtel,khsex,khadd)primarykeykhnohouhuoren(shmo,shmame,shrsex,shraddr,shrtel)primarykeyshmoyundan(ydno,ydprice,shmo,khno,ygno)primarykeyydnoforeignkeyshmoreferenceshouhuoren(shr)foreignkeykhnoreferenceshouhuoren(khno)cangku(ckno,ckadd,ckarea,cktel,ckcap,ckweight)primarykeycknozhuanyun(zyno,sno,yno,qstime,rktime)primarykeyzynoforeignkeysknoreferencecangku(ckno)foreignkeyygnoreferenceyuangong(ygno)peisong(psno,shmo,ygno,cktime,qstime)primarykeypsnoforeignkeyshmoreferenceShouhuorenforeignkeyygnoreferenceyuangongpingjia(pjno,ydno,pf,pj)primarykeypjnoforeignkeyydnoreferenceyundan3.7 定义参照完整性约束huowuforeignkeyydnoreferenceyundanonupdatecascadeondeletecascadezhuanyunforeignkeycknoreferencecangkuonupdatecascadeondeletenoactionforeignkeyygnoreferenceyuangongonupdatecascadeondeletenoactionpeisongforeignkeyshmoreferenceShouhuorenonupdatecascadeondeletenoactionforeignkeyygnoreferenceyuangongonupdatecascadeondeletenoactionpingjiaforeignkeyydnoreferenceyundanonupdatecascadeondeletecascade3.8 其他业务规则当运单状态为“接受”时，客户可以取消订单；当运单状态为“配送”时，订单生效不行取消，当状态为“完成"时，客户才可以进行评价。仓库存储的货物总量不能超过其容量；若超过，则运单自动取消。4.物理设计4.1 转换全局逻辑数据模型4.Ll创建基本表(给出创建每个基本表的SQL语句，应有如下实现：表名、列名、主键/侯选键、外键、参照完整性约束，对每个列，应有数据类型和长度、默认、是否可空等信息)创建客户表createtablekehu(khnovarchar(20)notnullprimarykey,khnamevarchar(20)notnull,khtelvarchar(20)notnull,khsexvarchar(10),khaddvarchar(20)notnull,check(khsexin(男','女')数据插入insertintokehuVaIUeS(IOoo1,'赵一；'男?上海号')insertintokehuVaIUeS(IoOe)2,'钱二；'男','广州号')insertintokehuVaIUeS(IOoo3,'孙三；'女'北京号')insertintokehuVaIUeS(IoOo4；李四；'男；厦门号')insertintokehuVaIUeS(IOoo5,'周五；'男；武汉号')insertintokehuValUeS(IoOe)6,'吴六；'男；北京号')insertintokehuVaIUeS（100o8：王八；男','厦门号'）insertintokehuVaIUeS（100o9,冯九'，'男：武汉号'）insertintokehuVaIUeS（IOoI0,'陈十；男；武汉号'）创建员工表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（'经理；主管？快递员'）,）数据插入insertintoyuangongVaIUeS（20001褚一':经理'男'，50,）insertintoyuangongVaIUeS（20002,'卫二？主管男'，48,）insertintoyuangongVaIUeS（20003,蒋三主管'男'，46,）insertintoyuangongValUeS（20004,'沈四'主管女',39,）insertintoyuangongValUeS（20006,'杨六','快递员；男',32,）insertintoyuangongVaIUeS（20007朱七:快递员；男',31,）insertintoyuangongValUeS（20008秦八快递员；男',34,）insertintoyuangongVaIUeS（20009尤九快递员；男',30,）insertintoyuangongValUeS（20010许十快递员；男',29,）创建收货人表createtableshouhuoren（shrnovarchar（20）notnullprimarykey,shrnamevarchar（20）notnull,shrsexvarchar（10）,shraddvarchar（20）notnull,shrtelvarchar（20）notnull,check（shrsexin（男','女'），）数据插入insertintoShouhuorenVaIUeS（30001,'何一','男'上海号',）insertintoShouhuorenVaIUeS（30002,'何二'男上海号',）insertintoShouhuorenValUeS（30003何三'男北京号',）insertintoShouhuorenVaIUeS（30004,'何四；男；北京号',）insertintoShouhuorenValUeS（30005何五女'广州号',）insertintoShouhuorenVaIUeS（30006,'何六'男'广州号',）insertintoShouhuorenVaIUeS（30008,'何八','女'武汉号',）insertintoShouhuorenValUeS（30009何九'男厦门号',）insertintoShouhuorenVaIUeS（30010,'何十',男；厦门号',）创建仓库表createtablecangku（cknovarchar（20）notnullprimarykey,ckaddvarchar（20）notnull,ckareavarchar（20）notnull,cktelvarchar（20）notnull,ckcapintnotnull,ckweightintnotnulldefault0,check（ckareain（'上海','北京；广州武汉':厦门'），）数据插入insertintocangkuValUeS（I;上海号；上海；100Oo,0）insertintocangkuValUeS（2；北京号'北京',，10000,0）insertintocangkuValUeS（3,'广州号','广州；Iooe）0,0）insertintocangkuValUeS（4；武汉号'武汉；IooOO,0）insertintocangkuVaIUeS'厦门号厦门；IooOo,0）创建运单表createtableyundan(ydnovarchar(20)notnullprimarykey,ydpricevarchar(10)notnull,ydstatevarchar(20)notnulldefault'接受',shrnovarchar(20)notnull,khnovarchar(20)notnull,sltimevarchar(20)notnull,hwweightintnotnull,check(ydstatein('接受','派送？完成',取消'),foreignkey(shmo)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)insertintoyundan values(50004,10000/ 接 受,30004,10004,2014-05-09,100)insertintoyundanVahIeS(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)接 受接 受接 受接 受接 受接 受创建转运表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)values(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)创建配送表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)values(70003,50003,20008,2014-05-11,2014-05-12)insertintopeisongvalues(70004,50004,20009,2014-05-l1,2014-05-12)insertintopeisongvalues(70005,50005,20009,2014-05-l1,2014-05-12)insertintopeisongvalues(70006,50006,20009,2014-05-11,2014-05-12)insertintopeisongvalues(70007,50007,20010,2014-05-l1,2014-05-12)创建评价表createtablepingjia(pjnovarchar(20)notnull,ydnovarchar(20)notnull,speedintnotnulldefault10,serveintnotnulldefault10,totalintnotnulldefault10,pjvarchar(100),foreignkey(ydno)referencesyundan(ydno),)insertintopingjiaVaIUeS(80001,50001/OjO,10,好)insertintopingjiaValUeS(80002,50002,8,10,9；不错')insertintopingjiaVaIUeS(80003,50003,10,6,8,不错')insertintopingjiaVaIUeS(80004,50004,10,8,9,不错')insertintopingjiaVaIUeS(80005,50005,10,10,10好)insertintopingjiaVaIUeS(80006,50006,6,6,6,'有待力口强')insertintopingjiaValUeS(80007,50007,10,8,9,不错')4.1.2派生数据说明yundan表中的运费总价ydrice,依据货物重量乘以100pingjia表中的总评total,是依据speed2+serve24.L3业务规则存档(给出访用约束、触发器等来实现业务规则的状况)(1)在运单表yundan上创建触发器，若PriCe为null,则其值设为货物重量hwweight×100createtriggeryd_priceonyundanforinsertasdeclarepriceint,hwweightint,ydnovarchar(20)selectprice=inserted.ydprice,hwweight=inserted.hwweight,ydno=inserted.ydnofrominsertedif(priceisnull)beginupdateyundansetydprice=hwweight*100whereydno=ydnoend转运表zhuanyun上创建触发器，若zhuanyun表中的cangku为null,则由仓库负责区域和收件人地址确定转运到哪个仓库createtriggerdecide_ckonzhuanyunforinsertasdeclarezynovarchar(20),cknovarchar(20),ydnovarchar(20),shraddvarchar(20)selectckno=inserted.ckno,ydno=inserted.ydno,zyno=inserted.zynofrominsertedif(cknoisnull)beginselectshradd=shr.shraddfrominserted,yundanyd,Shouhuorenshrwhereinserted.ydno=yd.ydnoandyd.shrno=shr.shrnoprintshraddif(shraddlike，上海setckno=1elseif(shraddlike'北京')setckno=2elseif(shraddlike广州')setckno=3elseif(shraddlike'武汉')setckno=4elsesetckno=5updatezhuanyunsetckno=cknowherezhuanyun.zyno=zynoendgo当运单状态为完成时，将不能修改运单createtriggeryundan_nofixonyundanforupdateasdeclarestatevarchar(20)selectstate=deleted.ydstatefromdeletedif（State='完成'）rollbacktransaction当运单状态为完成时，不能在此运单上新建配送createtriggerpeisong_nofixonyundanforinsertasdeclarestatevarchar（20）selectstate=inserted.ydnofrominsertedif（State='完成'）rollbacktransaction当运单状态为完成时，不能再在此运单上新建转运createtriggerzhuanyun_nofixonzhuanyunforinsertasdeclarestatevarchar（20）selectstate=inserted.ydnofrominsertedif（State='完成'）rollbacktransaction当货物起先转运，新建转运项时，把订单状态设置为派送createtriggeradd_zyonzhuanyunforinsertasdeclareydnovarchar(20)selectydno=inserted.ydnofrominsertedupdateyundansetyundan.ydstate='派送whereyundan.ydno=ydno在zhuanyun表中设置触发器，计算仓库储存量Ckweight的值，每当货物运进该仓库，CkWeight加上该货物重量。当仓库货物达到或超过仓库容量时，则赐予预警，createtriggerckweight_uponzhuanyunforupdate,insertasdeclarehwweightbigint,cknovarchar(20),ckweightbigint,ckcapbigintselecthwweight=yd.hwweight,ckno=inserted.cknofromyundanyd,insertedwhereyd.ydno=inserted.ydnowherecangku.ckweight=cangku.ckweight+hwweightcangku.ckno=cknoselectckweight=ck.ckweight,ckcap=ck.ckcap,ckno=inserted.cknofrominserted,cangkuckwhereinserted.ckno=ck.cknoif(ckweight>=ckcap)beginrollbacktransactionprint'仓请留意'+CknO+'仓库以满endgo在ZhUanyUn表中设置触发器,当新建配送项时，表示运单已经完成，运单状态ydstate变为完成createtriggeradd_psonpeisongforinsertasdeclareydnovarchar(20)selectydno=inserted.ydnofrominsertedupdateyundansetyundan.ydst