试谈供应链管理系统的数据库设计样本.doc

供给链管理系统数据库设计
1. 系统需求分析
a. 经过建立供给链数据库建立以方便多种信息查询和管理工作。
b. 系统简述：
本系统包含六个实体：生产商；运输商；商场；仓库；商品；用户
包含四个关系转为四个表：运输；生产；销售；储存。
c. 对该数据库进行以下数据处理：
Ⅰ查询：①查询其商场全部信息，输入商场编号，输出信息
②查询某种商品生产、运输、商场信息，输入商品编号，输出生产商、运输时间、运输商、销售商场
③商场想查询某一类商品储存仓库信息，输入商品编号，输出仓库编号地址、储存时间
④查询商品列表里各类商品个数，并按类型分组。
Ⅱ.插入：①在商品表中插入新商品
②插入新销售统计
③插入新运输统计
Ⅲ.修改：①修改旧商品价格
②修改运输统计

二．系统概念模型（E-R图）
①实体关系图：

运输
生产商

用户

商品

仓库

商场

运输商

生产
销售
储存
②实体属性图：
生产商地址
生产商

生产商类型
生产商名称
生产商编号
生产商注册年份

运输商地址
运输商

运输商名称
运输商编号
运输商注册年份
运输商拥有车辆

商场地址
商场

商场名称
商场编号
编号
商场注册年份
商场规模

用户年纪
用户

用户性别
用户姓名
编号
用户身份证号

仓库

仓库编号
仓库地址
编号
仓库存放价格

商品

商品编号
商品名称
编号
商品价格
商品类型

③关系属性图：
生产商编号
商品编号
生产时间
生产编号
生产

运输编号
编号地址
运输时间
商场编号
生产商编号
运输商编号
运输
商品编号

用户身份证号
商品编号
销售时间
销售编号
销售
商场编号

仓库编号
编号地址
储存编号
时间
所属商场
储存时间
编号
储存
商品编号

三、关系模式（加下划线是主键）
①生产商关系：（生产商编号，生产商名称，生产商地址，生产商类型，生产商注册年份）
②运输商关系：（运输商编号，运输商名称，运输商注册年份，运输商地址，运输商车辆数）
③商场关系：(商场编号, 商场名称, 商场注册年份, 商场地址, 商场规模)
④用户关系：(用户身份证号, 用户姓名, 用户年纪,用户性别)
⑤仓库关系：(仓库编号, 仓库地址, 仓库存放价格)
⑥商品关系：(商品编号, 商品名称, 商品价格, 商品类型)
⑦生产关系：(生产编号, 商品编号,生产商编号, 生产时间)
⑧运输关系：(运输编号, 商场编号, 生产商编号, 商品编号, 运输时间, 运输商编号)
⑨销售关系：(用户身份证号, 商场编号, 商品编号, 销售编号, 销售时间)
⑩储存关系：(储存编号, 商品编号, 仓库编号, 储存时间,所属商场)
四、物理设计
①生产商：
字段名
类型
特殊属性
生产商编号
char(20)
主键
生产商名称
char(20)
not null
生产商地址
char(20)
not null
生产商类型
char(20)

生产商注册年份
datetime
not null

②运输商：
字段名
类型
特殊属性
运输商编号
char(20)
主键
运输商名称
char(10)
not null
运输商注册年份
datetime
not null
运输商地址
char(20)
not null
运输商车辆数
int
not null

③商场：
字段名
类型
特殊属性
商场编号
char(20)
主键
商场名称
char(10)
not null
商场注册年份
datetime
not null
商场地址
char(20)
not null
商场规模
char(20)
not null

④用户：
字段名
类型
特殊属性
用户身份证号
char(20)
主键
用户姓名
char(10)
not null
用户性别
char(2)
not null
用户年纪
int
not null
⑤仓库
字段名
类型
特殊属性
仓库编号
char(20)
主键
仓库地址
char(10)
not null
仓库存放价格
money
not null

⑥商品:
字段名
类型
特殊属性
商品编号
char(20)
主键
商品名称
char(10)
not null
商品类型
char(20)
not null
商品价格
money
not null

⑦生产:
字段名
类型
特殊属性
生产编号
char(20)
主键
生产商编号
char(10)
外键
商品编号
char(20)
not null
生产时间
datetime
not null

⑧运输：
字段名
类型
特殊属性
运输编号
char(20)
主键
运输商编号
char(10)
外键
商场编号
Char（20）
nut null
商品编号
char(20)
not null
生产商编号
char(20)
not null
运输时间
datetime

⑨销售：
字段名
类型
特殊属性
销售编号
Char(10)
主键
商场编号
char(10)
not null
销售时间
char(20)
not null
商品编号
char(20)
外键
用户身份证号
char(20)
not null

⑩储存：
字段名
类型
特殊属性
储存编号
char(20)
主键
仓库编号
char(10)
外键
商品编号
Char(20)
not null
储存时间
char(20)
not null
所属商场
char(20)
not null

五、系统实现
1.数据库及其基础表建立
数据库建立用企业管理器，基础表用T-SQL语言：
数据库名：PM-System
建立数据库T-SQL语句以下：
Create database PM-System
On(name=pm_system_data,filename=’E:\数据库 \sm_system_data.mdf’)
Log on(name=pm_system_log,filename=’E:\ 数据库\sm_system_data.ldf’)
建立表SQL语句以下：
Use PM_System
Create table 生产商
(生产商编号 char(20) primary key,
生产商名称 char(20) not null,
生产商地址 char(20) not null,
生产商类型 char(20),
生产商注册年份 datetime not null
)
Create table 运输商
(运输商编号 char(20) primary key,
运输商名称 char(10) not null,
运输商注册年份 datetime not null,
运输商地址 char(20) not null,
运输商车辆数 int not null)
Create table 商场
(商场编号 char(20) primary key,
商场名称 char(10) not null,
商场地址 char(20) not null,
商场注册年份 datetime not null,
商场规模 char(20) not null)
Create table 用户
(用户身份证号 char(20) primary key,
用户姓名 char(10) not null,
用户年纪 int,
用户性别 char(2) not null
)
Create table 仓库
(仓库编号 char(20) primary key,
仓库地址 char(10) not null,
仓库存放价格 money not null)
Create table 商品
(商品编号 char(20) primary key,
商品名称 char(10) not null,
商品价格 money not null,
商品类型 char(20) not null)
Create table 生产
(生产编号 char(20) primary key,
商品编号 char(20) not null,
生产商编号 char(20) not null references 生产商(生产商编号),
生产时间 datetime not null)
Create table 运输
(运输编号 char(20) primary key,
商场编号 Char(20) nut null,
生产商编号 char(20) not null,
商品编号 char(20) not null,
运输时间 datetime,
运输商编号 char(20) not null references 运输商(运输商编号)
)

Create table 销售
(用户身份证号 char(20) not null,
商场编号 char(20) not null,
商品编号 char(20) not null references 商品(商品编号),
销售编号 Char(10) primary key,
销售时间 char(20) not null
)
Create table 存放
( 储存编号 char(20) pimary key,
商品编号 Char(20) not null,
仓库编号 char(20) not null references 仓库(仓库编号),
储存时间 char(20) not null,
所属商场 char(20) not null)

输入数据：
Insert into 生产商 values(‘0001’,’可口可乐’,’北京’,’食品’,’.01.01’)
Insert into 生产商 values(‘0002’,’百事可乐’,’天津’,’食品’,’.01.02’)
Insert into 生产商 values(‘0003’,’ADIDAS运动鞋’,’北京’,’鞋帽’,’.02.01’)
Insert into 生产商 values(‘0004’,’Audi A4’,’北京’,’汽车’,’.03.01’)
Insert into 生产商 values(‘0005’,’麦当劳’,’北京’,’食品’,’.04.01’)

Insert into 运输 values(‘0001’,’运一’,’.09.01’,’北京’,’10’)
Insert into运输values(‘0002’,’运二’,’.01.01’,’北京’,’20’)
Insert into运输values(‘0003’,’运三’,’.02.02’,’天津’,’30’)
Insert into运输values(‘0004’,’运四’,’.09.01’,’天津’,’40’)
Insert into运输values(‘0005’,’运五’,’1997.01.02’,’上海’,’20’)

Insert into 商场 values(‘0001’,’商场一’,’天津’’.01.01’,’大’)
Insert into商场values(‘0002’,’商场二’,’北京’’.01.01’,’小’)
Insert into商场values(‘0003’,’商场三’,’上海’’.02.02’,’小’)
Insert into商场values(‘0004’,’商场四’,’长沙’’.03.01’,’大’)

Insert into 仓库 values(‘0001’,’上海’’1000’)
Insert into仓库 values(‘0002’,’天津’,’500’)
Insert into仓库 values(‘0003’,’天津’,’1000’)
Insert into仓库 values(‘0004’,’北京’,’’)

Insert into 商品 values(‘0001’,’百事可乐’,’5’,’食品’)
Insert into商品values(‘0002’,’美年达’,’4’,’食品’)
Insert into商品values(‘0003’,’可口可乐’,’4’,’食品’)
Insert into商品values(‘0004’,’汉堡’,’10’,’食品’)
Insert into商品values(‘0005’,’Audi A5’,’300000’,’汽车’)
Insert into商品values(‘0001’,’Audi A6’,’400000’,’汽车’)
Insert into商品values(‘0002’,’Audi A4’,’00’,’汽车’)
Insert into商品values(‘0003’,’ADIDAS运动鞋1’,’300’,’鞋帽’)
Insert into商品values(‘0004’,’ADIDAS运动鞋2’,’400’,’鞋帽’)
Insert into商品values(‘0005’,’ADIDAS运动鞋3’,’500’,’鞋帽’)

Insert into 用户 values(‘300901’,’赵一’,’20’,’女’)
Insert into 用户values(‘300920’,’钱二’,’19’,’男’)
Insert into 用户values(‘’,’李三’,’21’,’女’)
Insert into 用户values(‘’,’王五’,’22’,’男’)

Insert into 生产 values(‘0001’,’0001’,’0002’,’.09.05’)
Insert into 生产 values(‘0002’,’0003’,’0002’,’.09.01’)
Insert into 生产 values(‘0003’,’0003’,’0001’,’.07,03’)
Insert into 生产 values(‘0004’,’0004’,’0005’,’.07.04’)
Insert into生产values(‘0005’,’0005’,’0004’,’.07,05’)
Insert into生产values(‘0006’,’0006’,’0004’,’.07.06’)
Insert into生产values(‘0007’,’0007’,’0003’,’.07.07’)
Insert into生产values(‘0008’,’0008’,’0003’,’.07.08’)
Insert into生产values(‘0009’,’0009’,’0003’,’.07.09’)
Insert into生产values(‘0010’,’0010’,’0003’,’.07.10’)

Insert into 运输 values(‘1’,’0001’,’0002’,’0001’,’.09.05’,’0001’)
Insert into 运输 values(‘2’,’0004’,’0002’,’0002’,’.09.01’,’0002’)
Insert into运输values(‘3’,’0003’,’0001’,’0004’,’.07,03’,’0004’)
Insert into运输values(‘4’,’0003’,’0005’,’0003’,’.07.04’,’0005’)
Insert into运输values(‘5’,’0005’,’0004’,’0003’,’.07,05’,’0002’)
Insert into运输values(‘6’,’0006’,’0004’,’0002’,’.07.06’,’0005’)
Insert into运输values(‘7’,’0003’,’0003’,’0001’,’.07.07’,’0003’)
Insert into运输values(‘8’,’0002’,’0003’,’0002’,’.07.08’,’0002’)
Insert into运输values(‘9’,’0006’,’0003’,’0001’,’.07.09’,’0001’)
Insert into运输values(‘10’,’0005’,’0003’,’0002’,’.07.10’,’0001’)
Insert into 销售 values(‘2107021’,’0001’,’0001’,’0001’,’.01.01’)
Insert into 销售values(‘2030001’,’0001’,’0002’,’20’,’女’)
Insert into 销售values(‘2090008’,’0001’,’0003’,’20’,’女’)
Insert into 销售values(‘18’,’0003’,’0004’,’20’,’女’)
Insert into 销售values(‘3107021’,’0002’,’0005’,’20’,’女’)
Insert into 销售values(‘2109341’,’0002’,’0006’,’20’,’女’)
Insert into 销售values(‘2107021’,’0004’,’0007’,’20’,’女’)

Insert into 储存 values(‘0001’,’0003’,’0001’,’.11.01’ ,’0001’)
Insert into 储存 values(‘0002’,’0002’,’0001’,’.11.05’,’0003’)
Insert into 储存 values(‘0003’,’0001’,’0002’,’.11.10’,’0004’)
2.创建索引
create nonclustered
index idx生产商
on生产商（生产商编号）
create nonclustered
index idx运输
on运输（生产商编号）

create nonclustered
index idx运输2
on运输（商场编号）

create nonclustered
index idx商场
on商场（商场编号）

create nonclustered
index idx储存
on储存（仓库编号）

create nonclustered
index idx仓库
on仓库（仓库编号）
3.创建视图
create view 供给链图
as
select 生产商名称,运输商名称,商场名称,商品.商品编号,运输时间, 商品类型, 生产时间
from 生产商 join 运输 on 生产商.生产商编号=运输.生产商编号 join 运输商 on 运输商.运输商编号=运输.运输商编号 join 商场 on 商场.商场编号=运输.商场编号
join 商品 on 商品.商品编号=运输.商品编号 join 生产 on 生产. 商品编号=商品.商品编号
create view 存放图
as
select 仓库.仓库编号,商品编号,所属商场
from 商场 join 存放 on 商场.商场编号=存放.所属商场 join 仓库 on 仓库.仓库编号=存放.仓库编号

4．创建存放过程
①查询：
create procedure 商场信息 @shopID char(10)
as
begin
print 'list of the information of this shop'
select 商场名称,商场地址,商场规模,商场注册年份
from 商场
where 商场编号=@shopID
end
create procedure 供给链信息 @goodID char(20)
as
begin
print 'list of the information of this good’s provide'
select 生产商名称,运输商名称,商场名称
from 供给链图
where 商品编号=@goodID
end
create procedure 储存信息 @goodID char(20)
as
begin
print 'list of the storage'
select 仓库编号,商品编号,所属商场
from 存放图
where 商品编号=@goodID
end
create procedure 分类信息 @goodID char(20)
as
begin
select 商品类型，count(*)
from 商品
group by 商品类型
end
②插入：
create procedure 插入商品信息 @goodID char(20), @goodname char(10), @goodprice money , @goodtype char(20)
as
insert into 商品
values(@goodID, @goodname, @goodprice, @goodtype)
create procedure 插入销售统计 @personid char(20), @shopnameID char(20), @goodid char(20), @sellID char(20)，@selltime datetime
as
insert into 销售
values(@personid, @shopnameID, @goodid, @sellID, @selltime)

③修改
Create procedure 修改商品价格 @goodprice money,@goodid char(20)
As
Update 商品
Set 商品价格＝@goodprice
Where 商品编号＝@goodid

Create procedure 修改运输统计 @oldtid char(20),@tid char(20),@shopid char(20),@tpid char(20),@goodid char(20),@time datetime,@fid char(20)
As update 运输
Set 运输编号=@tid,运输商编号＝@tpid,生产商编号=@fid,商品编号=@goodid,运输时间＝@time,商品编号＝@shopid
Where 运输编号＝@oldtid