数据库大作业 物流管理系统附代码.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 nameDescriptionOccurrence客户提交货物公司拥有多个客户员工管理公司或者配送货物多名员工收件人收取货物的人一个运单包含一个收件人评价客户对快递服务的评价用户只能评价一次仓库储存货物的地方拥有多个仓库（参考p126图9-2）3.3实体和关系的有关属性（应该给出实体和关系的所有属性，扩展p132图9-8的列名为：实体/关系名、属性、描述、键（主键/侯选键）、数据类型和长度、是否为空、是否多值、是否复合、是否派生、默认值、属性域约束）客户信息表kehu列名数据类型可否为空说明主/外键khnovarchar(20)NOT NULL客户编号主键khnamevarchar(20)NOT NULL客户姓名khtelvarchar(20)NOT NULL客户电话khsexvarchar(10)客户性别khaddvarchar(20)NOT NULL客户地址check( khsex in (“男”,”女”) )员工信息表yuangong列名数据类型可否为空说明主/外键ygnovarchar(20)NOT NULL员工编号主键ygnamevarchar(20)NOT NULL 员工姓名ygposvarchar(20)NOT NULL员工职务ygsexvarchar(5)NOT NULL员工性别ygageintNOT NULL员工年龄ygtelvarchar(20)NOT NULL员工电话check( ygsex in (“男”,”女”) )check(ygage18 and ygage18 and ygage=ckcap)beginrollback transactionprint 仓请注意+ckno+仓库以满endgo(8)在zhuanyun表中设置触发器,当新建配送项时，表示运单已经完成，运单状态ydstate变为完成create trigger add_ps on peisong for insertasdeclare ydno varchar(20)select ydno=inserted.ydnofrom insertedupdate yundan set yundan.ydstate=完成 where yundan.ydno=ydno(9)在运单表中设置一个触发器，当运单状态为“接受”时,可以取消订单，为其他状态时，无法取消订单create trigger kehu_dropon yundan for deleteasdeclare state varchar(20),ydno varchar(20)select state=ydstate,ydno=ydnofrom deletedif (state=接受)beginprint运单已取消update yundan set yundan.ydstate=取消 where yundan.ydno=ydnoendelse beginprint对不起，运单已经在派送路上，不能取消rollback transactionend(10)在peisong表中设置触发器，计算仓库储存量ckweight的值，当货物开始配送时，仓库中的ckweight减去该货物的重量create trigger ckweight_down1 on peisong for update,insertasdeclare hwweight int,ckno varchar(20),ydno varchar(20),mycangku intselect hwweight=yd.hwweight,ydno=inserted.ydno,ckno=zy.cknofrom yundan yd,inserted,zhuanyun zywhere yd.ydno=inserted.ydno and zy.ydno=inserted.ydnoupdate cangku set cangku.ckweight=cangku.ckweight-hwweight where cangku.ckno=ckno select mycangku=cangku.ckweightfrom cangkuwhere cangku.ckno=cknoprint cknoprint hwweightprint mycangku go(11)在pingjia表中设置一个触发器，当用户修改pingjia表中的speed或者serve评分时,total自动修改create trigger update_pingjia on pingjia for updateasif update (speed) or update (serve)declare speed int ,serve int,pjno varchar(20)beginselect speed=inserted.speed,serve=inserted.serve,pjno=inserted.pjnofrom insertedupdate pingjiaset total=speed/2+serve/2 where pjno=pjnoend(12)当运单状态不为完成时，则不能评价create trigger new_pj on pingjia for insertasdeclare state varchar(20)select state=yd.ydstatefrom inserted,yundan ydwhere inserted.ydno=yd.ydnoif (state完成)rollback transaction4.2设计物理表示法 4.2.1事务需求的实现（针对2.1.2节的每一个事务需求，给出SQL源代码的实现；可以把其中复杂的事务需求封装成为存储过程，对于这些存储过程1、给出SQL源代码2、给出对存储过程功能的说明3、对每个存储过程给出一个测试用例）4.2.1.1数据录入(1)利用存储过程，给yuangong表添加信息create proc insert_ygygno varchar(20),ygname varchar(20),ygpos varchar(20),ygsex varchar(20),ygage int,ygtel varchar(20)asinsert into yuangong values(ygno,ygname,ygpos,ygsex,ygage,ygtel)goexec insert_yg 20022,许十,快递员,男,29,88225811(2)利用存储过程，给cangku表添加信息create proc insert_ckckno varchar(20),ckadd varchar(20),ckarea varchar(20),cktel varchar(20),ckcap varchar(20),ckweight varchar(20)asinsert into cangku values(ckno,ckadd,ckarea,cktel,ckcap,ckweight)goexec insert_ck 5,厦门号,厦门,88558800,10000,0(3)利用存储过程，给kehu表添加信息create proc insert_khkhno varchar(20),khname varchar(20),khtel varchar(20),khsex varchar(10),khadd varchar(20)as