数据库大作业物流管理系统附代码[共27页].doc
数据库设计作品题目： 物流数据库管理系统组员 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 实体文档Entity name Description Occurrence客户 提交货物 公司拥有多个客户员工 管理公司或者配送货物 多名员工收件人 收取货物的人 一个运单包含一个收件人评价 客户对快递服务的评价 用户只能评价一次仓库 储存货物的地方 拥有多个仓库（参考 p126 图 9-2）3.3 实体和关系的有关属性（应该给出实体和关系的所有属性，扩展 p132 图 9-8 的列名为：实体 /关系名、属性、描述、键（主键 /侯选键）、数据类型和长度、是否为空、是否多值、是否复合、是否派生、默认值、属性域约束）客户信息表 kehu列名 数据类型 可否为空 说明 主/ 外键khno varchar(20) NOT NULL 客户编号 主键khname varchar(20) NOT NULL 客户姓名khtel varchar(20) NOT NULL 客户电话khsex varchar(10) 客户性别khadd varchar(20) NOT NULL 客户地址check( khsex in ( “男 ”,”女 ”) )员工信息表 yuangong列名 数据类型 可否为空 说 明 主/ 外键ygno varchar(20) NOT NULL 员工编号 主键ygname varchar(20) NOT NULL 员工姓名ygpos varchar(20) NOT NULL 员工职务ygsex varchar(5) NOT NULL 员工性别ygage int NOT NULL 员工年龄ygtel varchar(20) NOT NULL 员工电话check( ygsex in ( “男 ”,”女 ”) )check(ygage>18 and ygage<65 )check( ygpos in ( “经理 ”,”主管 ”,”快递员 ”) )收货人信息表 shouhuoren列名 数据类型 可否为空 说 明 主/ 外键shrno varchar(20) NOT NULL 收货人编号 主键shrname varchar(20) NOT NULL 收货人姓名shrsex varchar(10) 收货人性别shradd varchar(20) NOT NULL 收货人地址shrtel varchar(20) NOT NULL 收货人电话check( shrsex in ( “男 ”,”女 ”) )仓库 cangku列名 数据类型 可否为空 说 明 主/ 外键ckno varchar(20) NOT NULL 仓库编号 主键ckadd varchar(50) NOT NULL 详细地址ckarea varchar(50) NOT NULL 负责区域cktel varchar （20） NOT NULL 仓库电话ckcap int NOT NULL 仓库容量ckweight int NOT NULL 以存货物重量check( ckarea in ( “上海 ”,”北京 ”,”厦门 ”,”武汉 ”,”广州 ”)运单信息表 yundan列名 数据类型 可否为空 说 明 主/ 外键ydno varchar(20) NOT NULL 货单编号 主键ydprice int 运费总价ydstate varchar(20) 运单状态shrno varchar(20) NOT NULL 收货人编号 外键khno varchar(20) NOT NULL 客户编号 外键hwweight int NOT NULL 货物重量sltime varchar(20) NOT NULL 受理时间check(ydstate in ( “接受 ”,”派送 ”,”完成 ”,”取消 ”)转运信息 zhuanyun列名 数据类型 可否为空 说明 主/ 外键zyno varchar(20) NOT NULL 转运编号 主键ydno varchar(20) NOT NULL 运单编号 外键ckno varchar(20) 仓库编号 外键ygno varchar(20) NOT NULL 员工编号 外键qstime varchar(20) 起送时间rktime varchar(20) 入库时间配送信息 peisong列名 数据类型 可否为空 说明 主/ 外键psno varchar(20) NOT NULL 配送编号 主键ydno varchar(20) NOT NULL 运单编号 外键ygno varchar(20) 员工编号 外键cktime varchar(20) 出库时间wctime varchar(20) 完成时间ckno varchar(20) NOT NULL 仓库编号 外键评价信息 pingjia列名 数据类型 可否为空 说明 主/ 外键pjno varchar(20) NOT NULL 评价编号 主键ydno varchar(20) NOT NULL 运单编号 外键speed int NOT NULL 运输速度serve int NOT NULL 员工服务total int NOT NULL 评分advice varchar （100） 评价3.4 ER 图3.5 表和外键属性存档yuangong(ygno,ygname,ygpos,ygsex,ygage,ygtel)primary key ygnokehu(khno,khname,khtel,khsex,khadd)primary key khnohouhuoren(shrno,shrname,shrsex,shraddr,shrtel)primary key shrnoyundan(ydno,ydprice,shrno,khno,ygno)primarykey ydnoforeign key shrno reference shouhuoren(shr)foreign key khno reference shouhuoren(khno)cangku(ckno,ckadd,ckarea,cktel,ckcap,ckweight)primary key cknozhuanyun(zyno,sno,yno,qstime,rktime)primary key zynoforeign key skno reference cangku(ckno)foreign key ygno reference yuangong(ygno)peisong(psno,shrno,ygno,cktime,qstime)primary key psnoforeign key shrno reference shouhuorenforeign key ygno reference yuangongpingjia(pjno,ydno,pf,pj)primary key pjnoforeign key ydno reference yundan3.7 定义参照完整性约束huowuforeign key ydno reference yundan on update cascade on delete cascadezhuanyunforeign key ckno reference cangku on update cascade on delete no actionforeign key ygno reference yuangong on update cascade on delete no actionpeisongforeign key shrno reference shouhuoren on update cascade on delete no actionforeign key ygno reference yuangong on update cascade on delete no actionpingjiaforeign key ydno reference yundan on update cascade on delete cascade3.8 其他业务规则当运单状态为 ”接受 ”时，客户可以取消订单；当运单状态为 ”配送 ”时，订单生效不可取消，当状态为 ”完成 ”时，客户才可以进行评价。仓库存储的货物总量不能超过其容量；若超过，则运单自动取消。4.物理设计4.1 转换全局逻辑数据模型4.1.1 创建基本表（给出创建每个基本表的 SQL 语句，应有如下实现：表名、列名、主键 /侯选键、外键、参照完整性约束，对每个列，应有数据类型和长度、默认、是否可空等信息）(1)创建客户表create table kehu(khno varchar(20)not null primary key,khname varchar(20) not null,khtel varchar(20) not null,khsex varchar(10),khadd varchar(20) not null,check( khsex in( 男,女)数据插入insert into kehu values(10001,赵一 ,男,上海号 )insert into kehu values(10002,钱二 ,男,广州号 )insert into kehu values(10003,孙三 ,女,北京号 )insert into kehu values(10004,李四 ,男,厦门号 )insert into kehu values(10005,周五 ,男,武汉号 )insert into kehu values(10006,吴六 ,男,北京号 )insert into kehu values(10007,郑七 ,女,上海号 )insert into kehu values(10008,王八 ,男,厦门号 )insert into kehu values(10009,冯九 ,男,武汉号 )insert into kehu values(10010,陈十 ,男,武汉号 )(2)创建员工表create table yuangong(ygno varchar(20) not null primary key,ygname varchar(20) not null,ygpos varchar(20) not null,ygsex varchar(20) not null,ygage int not null,ygtel varchar(20) not null,check(ygsex in (男, 女),check(ygage>18 and ygage< 65),check(ygpos in ( 经理 ,主管 ,快递员 ),)数据插入insert into yuangong values(20001,褚一,经理,男,50,)insert into yuangong values(20002,卫二,主管,男,48,)insert into yuangong values(20003,蒋三,主管,男,46,)insert into yuangong values(20004,沈四,主管,女,39,)insert into yuangong values(20005,韩五,快递员 , 男,30 ,)insert into yuangong values(20006,杨六,快递员 , 男,32 ,)insert into yuangong values(20007,朱七,快递员 , 男,31 ,)insert into yuangong values(20008,秦八,快递员 , 男,34 ,)insert into yuangong values(20009,尤九,快递员 , 男,30 ,)insert into yuangong values(20010,许十,快递员 , 男,29 ,)(3)创建收货人表create table shouhuoren(shrno varchar(20) not null primary key,shrname varchar(20) not null,shrsex varchar(10),shradd varchar(20) not null,shrtel varchar(20) not null,check(shrsex in (男, 女 ),)数据插入insert into shouhuoren values(30001,何一,男, 上海号 ,)insert into shouhuoren values(30002,何二,男, 上海号 ,)insert into shouhuoren values(30003,何三,男, 北京号 ,)insert into shouhuoren values(30004,何四,男, 北京号 ,)insert into shouhuoren values(30005,何五,女, 广州号 ,)insert into shouhuoren values(30006,何六,男, 广州号 ,)insert into shouhuoren values(30007,何七,男, 武汉号 ,)insert into shouhuoren values(30008,何八,女, 武汉号 ,)insert into shouhuoren values(30009,何九,男, 厦门号 ,)insert into shouhuoren values(30010,何十,男, 厦门号 ,)(4)创建仓库表create table cangku(ckno varchar(20) not null primary key,ckadd varchar(20) not null,ckarea varchar(20) not null,cktel varchar(20) not null,ckcap int not null ,ckweight int not null default 0,check(ckarea in (上海,北京 ,广州 ,武汉 ,厦门 ),)数据插入insert into cangku values(1,上海号 ,上海 ,10000,0)insert into cangku values(2,北京号 ,北京 ,10000,0)insert into cangku values(3,广州号 ,广州 ,10000,0)insert into cangku values(4,武汉号 ,武汉 ,10000,0)insert into cangku values(5,厦门号 ,厦门 ,10000,0)(5)创建运单表create table yundan(ydno varchar(20) not null primary key,ydprice varchar(10) not null,ydstate varchar(20) not null default 接受 ,shrno varchar(20) not null,khno varchar(20) not null,sltime varchar(20) not null,hwweight int not null,check(ydstate in ( 接受,派送,完成,取消),foreign key (shrno) references shouhuoren(shrno) on update cascade,)运单数据插入insert into yundan values(50001,10000,接受,30001 , 10001, 2014-05 -08,100 )insert into yundan values(50002,10000,接受,30002 , 10002, 2014-05 -08,100 )insert into yundan values(50003,10000,接受,30003 , 10003, 2014-05 -09,100 )insert into yundan values(50004,10000,接受,30004 , 10004, 2014-05 -09,100 )insert into yundan values(50005,10000,接受,30005 , 10005, 2014-05 -10,100 )insert into yundan values(50006,20000,接受,30006 , 10006, 2014-05 -10,200 )insert into yundan values(50007,20000,接受,30007 , 10007, 2014 -05 -11,200 )insert into yundan values(50008,20000,接受,30008 , 10008, 2014 -05 -11,200 )insert into yundan values(50009,20000,接受,30009 , 10009, 2014-05 -12,200 )insert into yundan values(50010,20000,接受,30010 , 10010, 2014-05 -12,200 )(6)创建转运表create table zhuanyun(zyno varchar(20) not null primary key,ydno varchar(20) not null,ckno varchar(20) not null,ygno varchar(20) not null,qstime varchar(20),rktime varchar(20),foreign key (ckno) references cangku(ckno),foreign key (ygno) references yuangong(ygno),foreign key (ydno) references yundan(ydno) on update cascade)转运数据输入insert into zhuanyun values(60001,50001,1,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60002,50002,2,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60003,50003,3,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60004,50004,4,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60005,50005,5,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60006,50006,1,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60007,50007,1,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60008,50008,1,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60009,50009,1,20001,2014-05-09,2014-5-10)insert into zhuanyun values(60010,50010,1,20001,2014-05-09,2014-5-10)(7)创建配送表create table peisong(psno varchar(20) not null primary key,ydno varchar(20) not null,ygno varchar(20),cktime varchar(20),wctime varchar(20),foreign key(ygno) references yuangong(ygno),foreign key(ydno) references yundan(ydno) on update cascade)配送表数据输入insert into peisong values(70001 , 50001, 20008 ,2014-05 -11,2014 -05- 12)insert into peisong values(70002 , 50002, 20008 ,2014-05 -11,2014 -05- 12)insert into peisong values(70003 , 50003, 20008 ,2014-05 -11,2014 -05- 12)insert into peisong values(70004 , 50004, 20009 ,2014-05 -11,2014 -05- 12)insert into peisong values(70005 , 50005, 20009 ,2014-05 -11,2014 -05- 12)insert into peisong values(70006 , 50006, 20009 ,2014-05 -11,2014 -05- 12)insert into peisong values(70007 , 50007, 20010 ,2014-05 -11,2014 -05- 12)(8)创建评价表create table pingjia(pjno varchar(20) not null,ydno varchar(20) not null,speed int not null default 10,serve int not null default 10,total int not null default 10,pj varchar(100),foreign key(ydno) references yundan(ydno),)insert into pingjia values(80001,50001,10,10,10,好)insert into pingjia values(80002,50002,8,10,9,不错)insert into pingjia values(80003,50003,10,6,8,不错)insert into pingjia values(80004,50004,10,8,9,不错)insert into pingjia values(80005,50005,10,10,10,好)insert into pingjia values(80006,50006,6,6,6,有待加强 )insert into pingjia values(80007,50007,10,8,9,不错)4.1.2 派生数据说明yundan 表中的运费总价 ydprice ，根据货物重量乘以 100pingjia 表中的总评 total，是根据 speed/2+serve/24.1.3 业务规则存档（给出使用约束、触发器等来实现业务规则的情况）(1)在运单表 yundan 上创建触发器，若 price 为 null ，则其值设为货物重量 hwweight 100create trigger yd_price on yundan for insertasdeclare price int ,hwweight int ,ydno varchar(20)select price=inserted.ydprice ,hwweight = inserted.hwweight , ydno = inserted. ydnofrom insertedif (price is null)beginupdate yundan set ydprice=hwweight * 100 where ydno=ydnoend(2)转运表 zhuanyun 上创建触发器，若 zhuanyun 表中的 cangku 为 null ，则由仓库负责区域和收件人地址决定转运到哪个仓库create trigger decide_ck on zhuanyun for insertasdeclare zyno varchar(20),ckno varchar(20),ydno varchar (20 ),shradd varchar(20)select ckno = inserted.ckno , ydno = inserted. ydno,zyno = inserted.zynofrom insertedif (ckno is null)beginselect shradd=shr.shraddfrom inserted,yundan yd,shouhuoren shrwhere inserted. ydno = yd.ydno and yd.shrno=shr.shrnoprint shraddif (shradd like 上海% ) set ckno =1else if (shradd like 北京% ) set ckno = 2else if (shradd like 广州% ) set ckno = 3else if (shradd like 武汉% ) set ckno = 4else set ckno = 5update zhuanyun set ckno=ckno where zhuanyun.zyno=zynoendgo(3)当运单状态为完成时，将不能修改运单create trigger yundan_nofix on yundan for updateasdeclare state varchar (20)select state=deleted.ydstatefrom deletedif (state=完成)rollback transaction(4)当运单状态为完成时，不能在此运单上新建配送create trigger peisong_nofix on yundan for insertasdeclare state varchar (20)select state=inserted.ydnofrom insertedif (state=完成)rollback transaction(4)当运单状态为完成时，不能再在此运单上新建转运create trigger zhuanyun_nofix on zhuanyun for insertasdeclare state varchar (20)select state=inserted.ydnofrom in
