云南大学软件学院ups物流数据库期末大作业(含代码和测试结果)

云南大学软件学院实验报告

学期：2014年春季学期

指导教师: 张璇、刘宇

实验题目: UPS数据库

小组长:

联系电话:

电子邮件:

日期：2014年6月18日

一、角色分析：

1. 客户：客户是形成邮件信息以及智慧标签内容的信息来源：包括发人的名字（昵称），电话，地址，包括收件人的名字、电话、地址，客户提供完整的邮件信息之后，系统将自动为它生成一个运货单号和智慧标签，作为邮件查询以及配送的向导。同时，发件人有跟踪物流信息的权限，发件人只要在优比速使用过一次物流服务，他就理应成为优比速的客户，另外客户可能在不同时间处于发件人或收件人的身份，发件人发出的物品一旦开始派送就不允许再修改运单号、发件人、和收件人信息。用户若想取消发件也必须在一定的时间限制之内。收件人一旦签收则表示邮递服务基本结束，客户可以对物流服务进行评价，反馈和投诉等，但与该次邮递服务无关。

2.管理人员：拥有对整个数据库的最高权限，负责对不能录入或者扫描错误的邮件信息进行及时的补充。也能对ups数据库进行删除，修改，插入，查询等操作。还有实时监督货运状态的权限。

3.处理员工：负责收集邮件的信息：包括邮件的收件人信息、发件人信息，邮件的类型定位，递送时限以及邮件的运输工具。这些信息将用来作为自动化机器生成智慧标签的信息来源；员工还将负责对邮件的下一站目的地进行确定以及给邮件贴上智慧标签。同时，在邮件的中转站，员工还负责将邮件的物流信息及时返还到数据库中。员工在分发邮件的时候将唯一确定由系统为邮件分配的运单号。员工的订单处理量和员工的薪酬有着直接的关系。

4.自动化机器：自动化机器负责将员工收集的邮件信息打包自动生成一张含有二维码、条形码的智慧标签。在贴上智慧标签的邮件经过自动化机器的扫描仪扫描之后，系统将自动地将相应的数据写入到数据库中或者将数据库中与之对应的信息调出来。

二、设计来源：

1.应用背景：

当今世界经济发展呈现出全球化和市场化的两大趋势。经济发展，科技进步和信息革命，将世界带入前所未有的全球化时代。集物流，信息流和资金流于一身的物流配送中心，在这全球化和市场化的时代里，又进一步促进了世界经济与贸易的发展。

UPS物流配送管理系统专注于商品的到货，发货，保、搬运、运输等作业的计划、控制和反馈过程的信息搜集、汇总、查询和分析，达到提高物流配送效率、降低物流成本的目的。可以说是全球最好的物流管理系统之一。深刻了解ups系统有助于我们更好的学习数据库知识以及将知识与实践结合起来。

参考资料：

UPS优比速快递流程纪录片，通过观看纪录片以及网上资料和生活实际来学习ups物流管理系统。

2.功能需求：

登陆功能：数据库是需要有一定权限的人才能对其进行操作，所以必须对员工和管理员设置密码，以方便再验证密码和身份之后登陆数据库。

统计功能：数据库中的数据和ups的运营状态和员工的工作量有着密切的关系，和用户也密切相关，只有能够将这些数据统计出来，管理员才能更好的确定下一

步的策略一级每位员工的工资等等，统计功能还包括对邮件所经过的中转站的统计以及路线的统计，邮件信息的统计等等。

查询功能：即通过运单号或者邮件id查询运单的运送状态，经过的站点和经手的员工，对货物的运送监控是很重要的，因为管理员和发件人都理应时刻监控邮件的运送状态。

跟踪：各物流中心应该能够反馈物流到达的中转站信息并能将数据填填充到数据库中去。

签收功能：当用户发的邮件发出后订单信息就不能随便修改，也不能随便撤销。邮件被签收标志着寄送服务的结束，用户可以对ups的工作人员进行评价，反馈及投诉等。

同步更新数据：发件人和收件人在消费一次快递业务之后，就理应成为ups的一个客户，他们的信息会被保存在数据库中。

三、模型生成：

1.E-R图：

E-R图是根据实体之间的关系图设计做出来的反应试题之间关系的

一种概念模型，根据E-R图可以用powerdesign自动转换成物理关系模型并导出数据库，下面是我们设计并经过规范化的E-R图

对E-R图设计的一些思路：

1.以订单为中心，将邮件的信息放在智慧标签中，并通过邮件和运货单联

系起来，在运货单中包含着运货单号，发送时间，签收时间。运货单还分别于员工，客户，发件人，收件人以及运输路线相关联。

2.在运输路线中，我们通过统计邮件到达的站和下一个站的方式来确定路

线，这样的做法是结合了现实生活中通过单号查询邮件的经验和以及考虑到用户期望得到的信息设计的，每一次的转运我们都把到站的具体时间、地点以及下一站，运输工具，还有处理的员工展示给客户。在这里，客户可以通过对这个实体的信息的检索查询到邮件的信息。

3.在中转站这个实体中记录了中转站的基本信息，其中包括运送的状态，

有异常的情况用户可以在这个实体中检索到，通国运单号追踪邮件经过的中转站可以确实的反应运送中的每个细节，也可以真实的反应出运输的路线。

4.在客户之外还分别设置了发件人和收件人两个实体，主要是为了方便客

户信息的存储与更新。实际应用中如果是老客户，则只需要将客户中的信息提取出来放入智慧标签中即可。

2.物理关系模型：

转换来的物理模型：

四、使用PowerDesigner 生成的脚本语言生成数据库：

创建数据库的代码如下：

/*==============================================================*/ /* DBMS name: Microsoft SQL Server 2008 */

/* Created on: 2014-6-20 2:12:51 */

/*==============================================================*/

if exists (select 1

from sysindexes

where id = object_id('中转站')

and name = '邮件会经过多个中转战_FK'

and indid > 0

and indid < 255)

drop index 中转站.邮件会经过多个中转战_FK

go

if exists (select 1

from sysobjects

where id = object_id('中转站')

and type = 'U')

drop table 中转站

go

if exists (select 1

from sysobjects

where id = object_id('发件人')

and type = 'U')

drop table 发件人

go

if exists (select 1

from sysindexes

where id = object_id('员工')

and name = '员工所属的部门_FK'

and indid > 0

and indid < 255)

drop index 员工.员工所属的部门_FK

go

if exists (select 1

from sysindexes

where id = object_id('员工')

and name = '管理员管着员工_FK'

and indid > 0

and indid < 255)

drop index 员工.管理员管着员工_FK

go

if exists (select 1

from sysobjects

where id = object_id('员工')

and type = 'U')

drop table 员工

go

if exists (select 1

from sysobjects

where id = object_id('客户')

and type = 'U')

drop table 客户

go

if exists (select 1

from sysindexes

where id = object_id('收件人')

and name = '邮件的收件人2_FK'

and indid > 0

and indid < 255)

drop index 收件人.邮件的收件人2_FK

go

if exists (select 1

from sysobjects

where id = object_id('收件人')

and type = 'U')

drop table 收件人

go

if exists (select 1

from sysindexes

where id = object_id('智慧标签')

and name = '智慧标签里含有邮件的信息2_FK'

and indid > 0

and indid < 255)

drop index 智慧标签.智慧标签里含有邮件的信息2_FK

go

if exists (select 1

from sysobjects

where id = object_id('智慧标签')

and type = 'U')

drop table 智慧标签

go

if exists (select 1

from sysobjects

where id = object_id('管理员')

and type = 'U')

drop table 管理员

go

if exists (select 1

from sysindexes

where id = object_id('运货单')

and name = '邮件的收件人_FK'

and indid > 0

and indid < 255)

drop index 运货单.邮件的收件人_FK

go

if exists (select 1

from sysindexes

where id = object_id('运货单')

and name = '运单的运输路线_FK'

and indid > 0

and indid < 255)

drop index 运货单.运单的运输路线_FK

go

if exists (select 1

from sysindexes

where id = object_id('运货单')

and name = '邮件的发件人_FK'

and indid > 0

and indid < 255)

drop index 运货单.邮件的发件人_FK

go

if exists (select 1

from sysindexes

where id = object_id('运货单')

and name = '用户_FK'

and indid > 0

and indid < 255)

drop index 运货单.用户_FK

go

if exists (select 1

from sysindexes

where id = object_id('运货单')

and name = '智慧标签里含有邮件的信息_FK'

and indid > 0

and indid < 255)

drop index 运货单.智慧标签里含有邮件的信息_FK

go

if exists (select 1

from sysindexes

where id = object_id('运货单')

and name = '处理运单的员工_FK'

and indid > 0

and indid < 255)

drop index 运货单.处理运单的员工_FK

if exists (select 1

from sysobjects

where id = object_id('运货单')

and type = 'U')

drop table 运货单

go

if exists (select 1

from sysindexes

where id = object_id('运输路线')

and name = '运单的运输路线2_FK'

and indid > 0

and indid < 255)

drop index 运输路线.运单的运输路线2_FK

go

if exists (select 1

from sysobjects

where id = object_id('运输路线')

and type = 'U')

drop table 运输路线

go

if exists (select 1

from sysobjects

where id = object_id('部门')

and type = 'U')

drop table 部门

go

/*==============================================================*/ /* Table: 中转站*/

/*==============================================================*/ create table 中转站(

station_id char(20) not null,

covery_id char(20) null,

station_name char(20) not null,

station_adress char(40) not null,

station_phone char(11) not null,

covery_status varchar(20) not null,

constraint PK_中转站primary key nonclustered (station_id)

)

/*==============================================================*/ /* Index: 邮件会经过多个中转战_FK */

/*==============================================================*/ create index 邮件会经过多个中转战_FK on 中转站(

covery_id ASC

)

go

/*==============================================================*/ /* Table: 发件人*/

/*==============================================================*/ create table 发件人(

sender_id char(20) not null,

sender_name char(20) not null,

sender_address varchar(30) not null,

sender_phone char(11) not null,

constraint PK_发件人primary key nonclustered (sender_id)

)

go

/*==============================================================*/ /* Table: 员工*/

/*==============================================================*/ create table 员工(

stuff_id char(20) not null,

dep_id char(20) null,

manager_id char(20) null,

stuff_name char(20) not null,

stuff_address varchar(30) not null,

stuff_phone char(11) not null,

stuff_password char(20) not null,

constraint PK_员工primary key nonclustered (stuff_id)

)

go

/*==============================================================*/ /* Index: 管理员管着员工_FK */

/*==============================================================*/ create index 管理员管着员工_FK on 员工(

manager_id ASC

)

go

/*==============================================================*/ /* Index: 员工所属的部门_FK */ /*==============================================================*/ create index 员工所属的部门_FK on 员工(

dep_id ASC

)

go

/*==============================================================*/ /* Table: 客户*/

/*==============================================================*/ create table 客户(

user_id char(20) not null,

user_name char(20) not null,

user_address varchar(30) not null,

user_photo char(11) not null,

constraint PK_客户primary key nonclustered (user_id)

)

go

/*==============================================================*/ /* Table: 收件人*/

/*==============================================================*/ create table 收件人(

receiver_id char(20) not null,

post_id char(20) null,

receiver_name char(20) not null,

receiver_addr varchar(30) not null,

receiver_code char(10) not null,

receiver_pho char(11) not null,

constraint PK_收件人primary key nonclustered (receiver_id)

)

go

/*==============================================================*/ /* Index: 邮件的收件人2_FK */ /*==============================================================*/ create index 邮件的收件人2_FK on 收件人(

post_id ASC

)

go

/*==============================================================*/ /* Table: 智慧标签*/

/*==============================================================*/ create table 智慧标签(

lab_id char(20) not null,

post_id char(20) null,

post_weight float not null,

post_limtime char(10) not null,

post_type char(10) not null,

fcus_code char(10) not null,

post_cost float not null,

constraint PK_智慧标签primary key nonclustered (lab_id)

)

go

/*==============================================================*/ /* Index: 智慧标签里含有邮件的信息2_FK */

/*==============================================================*/ create index 智慧标签里含有邮件的信息2_FK on 智慧标签(

post_id ASC

)

go

/*==============================================================*/ /* Table: 管理员*/

/*==============================================================*/ create table 管理员(

manager_id char(20) not null,

manager_name char(20) not null,

manager_pho char(11) not null,

manager_pwd char(20) not null,

manager_adress char(30) null,

constraint PK_管理员primary key nonclustered (manager_id)

)

go

/*==============================================================*/ /* Table: 运货单*/

/*==============================================================*/ create table 运货单(

post_id char(20) not null,

stuff_id char(20) null,

receiver_id char(20) null,

user_id char(20) null,

sender_id char(20) null,

lab_id char(20) null,

covery_id char(20) null,

post_sendtime char(20) not null,

post_receivetime datetime not null,

constraint PK_运货单primary key nonclustered (post_id)

)

go

/*==============================================================*/ /* Index: 处理运单的员工_FK */

/*==============================================================*/ create index 处理运单的员工_FK on 运货单(

stuff_id ASC

)

go

/*==============================================================*/ /* Index: 智慧标签里含有邮件的信息_FK */

/*==============================================================*/ create index 智慧标签里含有邮件的信息_FK on 运货单(

lab_id ASC

)

go

/*==============================================================*/ /* Index: 用户_FK */

/*==============================================================*/ create index 用户_FK on 运货单(

user_id ASC

)

go

/*==============================================================*/ /* Index: 邮件的发件人_FK */

/*==============================================================*/ create index 邮件的发件人_FK on 运货单(

sender_id ASC

)

go

/*==============================================================*/

/* Index: 运单的运输路线_FK */ /*==============================================================*/ create index 运单的运输路线_FK on 运货单(

covery_id ASC

)

go

/*==============================================================*/ /* Index: 邮件的收件人_FK */

/*==============================================================*/ create index 邮件的收件人_FK on 运货单(

receiver_id ASC

)

go

/*==============================================================*/ /* Table: 运输路线*/

/*==============================================================*/ create table 运输路线(

covery_id char(20) not null,

post_id char(20) null,

covery_nowstation varchar(30) not null,

covery_cometime varchar(30) not null,

covery_nextstation char(20) not null,

covery_tool varchar(20) null,

covery_stuff varchar(30) not null,

constraint PK_运输路线primary key nonclustered (covery_id)

)

go

/*==============================================================*/ /* Index: 运单的运输路线2_FK */ /*==============================================================*/ create index 运单的运输路线2_FK on 运输路线(

post_id ASC

)

go

/*==============================================================*/ /* Table: 部门*/

/*==============================================================*/ create table 部门(

dep_id char(20) not null,

dep_name char(20) not null,

dep_manager char(20) not null,

dep_phone char(11) not null,

constraint PK_部门primary key nonclustered (dep_id)

)

go

五、应用在sql sever2008中生成的数据库

六、完整性约束：

（1）.用存储过程实现员工的登陆验证：

代码如下：

creat proc 员工登录

@emp_id char(50),@emp_pswd char(50)

as

if exists

(select * from 员工

where stuff_name=@emp_id and stuff_password=@emp_pswd) print '登陆成功'

if exists

(select * from 员工

where stuff_name!=@emp_id and stuff_password!=@emp_pswd)

print '用户名不存在'

if exists

(select * from 员工

where stuff_name=@emp_id and stuff_password!=@emp_pswd)

print '您输入的密码错误'

if exists

(select * from 员工

where stuff_name!=@emp_id and stuff_password!=@emp_pswd)

print '用户名不存在'

执行结果截图：

验证：

1.插入一条记录：其中迟学成是员工，123508是密码

2.登陆成功的情况

3.密码错误的情况：

4.用户不存在的情况：

（2）用存储过程统计员工的工作量，以运单处理量为唯一标准：代码如下：

create proc 员工工作量

as

select s.stuff_id,dep_id,stuff_name,COUNT(c.post_id) as stuff_work from 员工s,运货单c

where s.stuff_id=c.stuff_id

group by s.stuff_id,dep_id,stuff_name

执行结果：

验证：

插入一个运货单：

执行存储过程：

显示出了全体员工的工作量以及每个员工的详细信息。

（3）通过存储过程统计中转战中的运送情况；

代码如下：

create proc 运送状态

@运送单号char(20)

as

select covery_id,station_name,station_adress,covery_status,station_phone from 中转站

where covery_id=@运送单号

执行结果：

验证：

插入两条数据：

执行存储过程，‘0123456789’是运单号

（4）给用户查看运送的状态

代码如下：

create proc 查看运输

as

select

c2.covery_nowstation,covery_cometime,covery_nextstation,covery_tool,covery_stuff ,c1.covery_id

from 运货单c1,运输路线c2

where c1.covery_id=c2.covery_id

order by c1.covery_id,c2.covery_nowstation,covery_cometime,covery_tool,covery_nextstation

,covery_stuff

执行结果：

验证：执行查看运输存储过程，则会显示当前到站nowstation，cometime，nextstation，运输工具，处理员工和货运单号。

（五）使用触发器实现运单一旦开始派送则不允许修改运单号，发件人，收件人等信息。

代码如下：

create trigger 禁止修改

on 运货单

instead of update

as

print'订单已生成，内容不允许修改'

执行结果：

验证：试图修改已经存在的运单信息，被触发器阻止。