供应链管理系统的数据库设计.docx

供应链管理系统的数据库设计.docx

《供应链管理系统的数据库设计.docx》由会员分享，可在线阅读，更多相关《供应链管理系统的数据库设计.docx（16页珍藏版）》请在冰豆网上搜索。

供应链管理系统的数据库设计

供应链管理系统的数据库设计

1.系统需求分析

a.通过建立供应链的数据库建立以方便各种信息的查询与管理工作。

b.系统简述：

本系统包括六个实体：

生产商；运输商；商场；仓库；商品；顾客

包括四个关系转为四个表：

运输；生产；销售；储存。

c.对该数据库进行如下数据处理：

Ⅰ查询：

①查询其商场所有的信息，输入商场编号，输出信息

②查询某种商品的生产、运输、商场的信息，输入商品的编号，输出生产商、运输时间、运输商、销售商场

③商场想查询某一类的商品储存仓库信息，输入商品编号，输出仓库编号地址、储存时间

④查询商品列表里的各类商品个数，并按类型分组。

Ⅱ.插入：

①在商品表中插入新商品

②插入新的销售记录

③插入新的运输记录

Ⅲ.修改：

①修改旧商品的价格

②修改运输的记录

二．系统概念模型（E-R图）

①实体的关系图：

②实体的属性图：

③关系属性图：

三、关系模式（加下划线的是主键）

①生产商关系：

（生产商编号，生产商名称，生产商地址，生产商类型，生产商注册年份）

②运输商关系：

（运输商编号，运输商名称，运输商注册年份，运输商地址，运输商车辆数）

③商场关系：

（商场编号,商场名称,商场注册年份,商场地址,商场规模）

④顾客关系：

（顾客身份证号,顾客姓名,顾客年龄,顾客性别）

⑤仓库关系：

（仓库编号,仓库地址,仓库存储价格）

⑥商品关系：

（商品编号,商品名称,商品价格,商品类型）

⑦生产关系：

（生产编号,商品编号,生产商编号,生产时间）

⑧运输关系：

（运输编号,商场编号,生产商编号,商品编号,运输时间,运输商编号）

⑨销售关系：

（顾客身份证号,商场编号,商品编号,销售编号,销售时间）

⑩储存关系：

（储存编号,商品编号,仓库编号,储存时间,所属商场）

四、物理设计

①生产商：

字段名

类型

特殊属性

生产商编号

char（20）

主键

生产商名称

char（20）

notnull

生产商地址

char（20）

notnull

生产商类型

char（20）

生产商注册年份

datetime

notnull

②运输商：

字段名

类型

特殊属性

运输商编号

char（20）

主键

运输商名称

char（10）

notnull

运输商注册年份

datetime

notnull

运输商地址

char（20）

notnull

运输商车辆数

int

notnull

③商场：

字段名

类型

特殊属性

商场编号

char（20）

主键

商场名称

char（10）

notnull

商场注册年份

datetime

notnull

商场地址

char（20）

notnull

商场规模

char（20）

notnull

④顾客：

字段名

类型

特殊属性

顾客身份证号

char（20）

主键

顾客姓名

char（10）

notnull

顾客性别

char

（2）

notnull

顾客年龄

int

notnull

⑤仓库

字段名

类型

特殊属性

仓库编号

char（20）

主键

仓库地址

char（10）

notnull

仓库存储价格

money

notnull

⑥商品:

字段名

类型

特殊属性

商品编号

char（20）

主键

商品名称

char（10）

notnull

商品类型

char（20）

notnull

商品价格

money

notnull

⑦生产:

字段名

类型

特殊属性

生产编号

char（20）

主键

生产商编号

char（10）

外键

商品编号

char（20）

notnull

生产时间

datetime

notnull

⑧运输：

字段名

类型

特殊属性

运输编号

char（20）

主键

运输商编号

char（10）

外键

商场编号

Char（20）

nutnull

商品编号

char（20）

notnull

生产商编号

char（20）

notnull

运输时间

datetime

⑨销售：

字段名

类型

特殊属性

销售编号

Char（10）

主键

商场编号

char（10）

notnull

销售时间

char（20）

notnull

商品编号

char（20）

外键

顾客身份证号

char（20）

notnull

⑩储存：

字段名

类型

特殊属性

储存编号

char（20）

主键

仓库编号

char（10）

外键

商品编号

Char（20）

notnull

储存时间

char（20）

notnull

所属商场

char（20）

notnull

五、系统实现

1.数据库与其基本表的建立

数据库的建立用企业管理器，基本表用T-SQL语言：

数据库名：

PM-System

建立数据库的T-SQL语句如下：

CreatedatabasePM-System

On（name=pm_system_data,’E:

\数据库\sm_system_data.mdf’）

Logon（name=pm_system_log,’E:

\数据库\sm_system_data.ldf’）

建立表的SQL语句如下：

UsePM_System

Createtable生产商

（生产商编号char（20）primarykey,

生产商名称char（20）notnull,

生产商地址char（20）notnull,

生产商类型char（20）,

生产商注册年份datetimenotnull

）

Createtable运输商

（运输商编号char（20）primarykey,

运输商名称char（10）notnull,

运输商注册年份datetimenotnull,

运输商地址char（20）notnull,

运输商车辆数intnotnull）

Createtable商场

（商场编号char（20）primarykey,

商场名称char（10）notnull,

商场地址char（20）notnull,

商场注册年份datetimenotnull,

商场规模char（20）notnull）

Createtable顾客

（顾客身份证号char（20）primarykey,

顾客姓名char（10）notnull,

顾客年龄int,

顾客性别char

（2）notnull

）

Createtable仓库

（仓库编号char（20）primarykey,

仓库地址char（10）notnull,

仓库存储价格moneynotnull）

Createtable商品

（商品编号char（20）primarykey,

商品名称char（10）notnull,

商品价格moneynotnull,

商品类型char（20）notnull）

Createtable生产

（生产编号char（20）primarykey,

商品编号char（20）notnull,

生产商编号char（20）notnullreferences生产商（生产商编号）,

生产时间datetimenotnull）

Createtable运输

（运输编号char（20）primarykey,

商场编号Char（20）nutnull,

生产商编号char（20）notnull,

商品编号char（20）notnull,

运输时间datetime,

运输商编号char（20）notnullreferences运输商（运输商编号）

）

Createtable销售

（顾客身份证号char（20）notnull,

商场编号char（20）notnull,

商品编号char（20）notnullreferences商品（商品编号）,

销售编号Char（10）primarykey,

销售时间char（20）notnull

）

Createtable存储

（储存编号char（20）pimarykey,

商品编号Char（20）notnull,

仓库编号char（20）notnullreferences仓库（仓库编号）,

储存时间char（20）notnull,

所属商场char（20）notnull）

输入数据：

Insertinto生产商values（‘0001’,’可口可乐’,’北京’,’食品’,’2010.01.01’）

Insertinto生产商values（‘0002’,’百事可乐’,’天津’,’食品’,’2010.01.02’）

Insertinto生产商values（‘0003’,’ADIDAS运动鞋’,’北京’,’鞋帽’,’2010.02.01’）

Insertinto生产商values（‘0004’,’AudiA4’,’北京’,’汽车’,’2010.03.01’）

Insertinto生产商values（‘0005’,’麦当劳’,’北京’,’食品’,’2010.04.01’）

Insertinto运输values（‘0001’,’运一’,’2009.09.01’,’北京’,’10’）

Insertinto运输values（‘0002’,’运二’,’2010.01.01’,’北京’,’20’）

Insertinto运输values（‘0003’,’运三’,’2010.02.02’,’天津’,’30’）

Insertinto运输values（‘0004’,’运四’,’2009.09.01’,’天津’,’40’）

Insertinto运输values（‘0005’,’运五’,’1997.01.02’,’上海’,’20’）

Insertinto商场values（‘0001’,’商场一’,’天津’’2009.01.01’,’大’）

Insertinto商场values（‘0002’,’商场二’,’北京’’2010.01.01’,’小’）

Insertinto商场values（‘0003’,’商场三’,’上海’’2010.02.02’,’小’）

Insertinto商场values（‘0004’,’商场四’,’长沙’’2010.03.01’,’大’）

Insertinto仓库values（‘0001’,’上海’’1000’）

Insertinto仓库values（‘0002’,’天津’,’500’）

Insertinto仓库values（‘0003’,’天津’,’1000’）

Insertinto仓库values（‘0004’,’北京’,’2000’）

Insertinto商品values（‘0001’,’百事可乐’,’5’,’食品’）

Insertinto商品values（‘0002’,’美年达’,’4’,’食品’）

Insertinto商品values（‘0003’,’可口可乐’,’4’,’食品’）

Insertinto商品values（‘0004’,’汉堡’,’10’,’食品’）

Insertinto商品values（‘0005’,’AudiA5’,’300000’,’汽车’）

Insertinto商品values（‘0001’,’AudiA6’,’400000’,’汽车’）

Insertinto商品values（‘0002’,’AudiA4’,’200000’,’汽车’）

Insertinto商品values（‘0003’,’ADIDAS运动鞋1’,’300’,’鞋帽’）

Insertinto商品values（‘0004’,’ADIDAS运动鞋2’,’400’,’鞋帽’）

Insertinto商品values（‘0005’,’ADIDAS运动鞋3’,’500’,’鞋帽’）

Insertinto顾客values（‘3009201001’,’赵一’,’20’,’女’）

Insertinto顾客values（‘3009202001’,’钱二’,’19’,’男’）

Insertinto顾客values（‘3009203001’,’李三’,’21’,’女’）

Insertinto顾客values（‘3009204001’,’王五’,’22’,’男’）

Insertinto生产values（‘0001’,’0001’,’0002’,’2009.09.05’）

Insertinto生产values（‘0002’,’0003’,’0002’,’2009.09.01’）

Insertinto生产values（‘0003’,’0003’,’0001’,’2010.07,03’）

Insertinto生产values（‘0004’,’0004’,’0005’,’2010.07.04’）

Insertinto生产values（‘0005’,’0005’,’0004’,’2010.07,05’）

Insertinto生产values（‘0006’,’0006’,’0004’,’2010.07.06’）

Insertinto生产values（‘0007’,’0007’,’0003’,’2010.07.07’）

Insertinto生产values（‘0008’,’0008’,’0003’,’2010.07.08’）

Insertinto生产values（‘0009’,’0009’,’0003’,’2010.07.09’）

Insertinto生产values（‘0010’,’0010’,’0003’,’2010.07.10’）

Insertinto运输values（‘1’,’0001’,’0002’,’0001’,’2009.09.05’,’0001’）

Insertinto运输values（‘2’,’0004’,’0002’,’0002’,’2009.09.01’,’0002’）

Insertinto运输values（‘3’,’0003’,’0001’,’0004’,’2010.07,03’,’0004’）

Insertinto运输values（‘4’,’0003’,’0005’,’0003’,’2010.07.04’,’0005’）

Insertinto运输values（‘5’,’0005’,’0004’,’0003’,’2010.07,05’,’0002’）

Insertinto运输values（‘6’,’0006’,’0004’,’0002’,’2010.07.06’,’0005’）

Insertinto运输values（‘7’,’0003’,’0003’,’0001’,’2010.07.07’,’0003’）

Insertinto运输values（‘8’,’0002’,’0003’,’0002’,’2010.07.08’,’0002’）

Insertinto运输values（‘9’,’0006’,’0003’,’0001’,’2010.07.09’,’0001’）

Insertinto运输values（‘10’,’0005’,’0003’,’0002’,’2010.07.10’,’0001’）

Insertinto销售values（‘2150021’,’0001’,’0001’,’0001’,’2010.01.01’）

Insertinto销售values（‘2030001’,’0001’,’0002’,’20’,’女’）

Insertinto销售values（‘2090008’,’0001’,’0003’,’20’,’女’）

Insertinto销售values（‘2120058’,’0003’,’0004’,’20’,’女’）

Insertinto销售values（‘3150011’,’0002’,’0005’,’20’,’女’）

Insertinto销售values（‘2192021’,’0002’,’0006’,’20’,’女’）

Insertinto销售values（‘2150021’,’0004’,’0007’,’20’,’女’）

Insertinto储存values（‘0001’,’0003’,’0001’,’2010.11.01’,’0001’）

Insertinto储存values（‘0002’,’0002’,’0001’,’2010.11.05’,’0003’）

Insertinto储存values（‘0003’,’0001’,’0002’,’2010.11.10’,’0004’）

2.创建索引

createnonclustered

indexidx生产商

on生产商（生产商编号）

createnonclustered

indexidx运输

on运输（生产商编号）

createnonclustered

indexidx运输2

on运输（商场编号）

createnonclustered

indexidx商场

on商场（商场编号）

createnonclustered

indexidx储存

on储存（仓库编号）

createnonclustered

indexidx仓库

on仓库（仓库编号）

3.创建视图

createview供应链图

as

select生产商名称,运输商名称,商场名称,商品.商品编号,运输时间,商品类型,生产时间

from生产商join运输on生产商.生产商编号=运输.生产商编号join运输商on运输商.运输商编号=运输.运输商编号join商场on商场.商场编号=运输.商场编号

join商品on商品.商品编号=运输.商品编号join生产on生产.商品编号=商品.商品编号

createview存储图

as

select仓库.仓库编号,商品编号,所属商场

from商场join存储on商场.商场编号=存储.所属商场join仓库on仓库.仓库编号=存储.仓库编号

4．创建存储过程

①查询：

createprocedure商场信息@shopIDchar（10）

as

begin

print'listoftheinformationofthisshop'

select商场名称,商场地址,商场规模,商场注册年份

from商场

where商场编号=@shopID

end

createprocedure供应链信息@goodIDchar（20）

as

begin

print'listoftheinformationofthisgood’sprovide'

select生产商名称,运输商名称,商场名称

from供应链图

where商品编号=@goodID

end

createprocedure储存信息@goodIDchar（20）

as

begin

print'listofthestorage'

select仓库编号,商品编号,所属商场

from存储图

where商品编号=@goodID

end

createprocedure分类信息@goodIDchar（20）

as

begin

select商品类型，count（*）

from商品

groupby商品类型

end

②插入：

createprocedure插入商品信息@goodIDchar（20）,@goodnamechar（10）,@goodpricemoney,@goodtypechar（20）

as

insertinto商品

values（@goodID,@goodname,@goodprice,@goodtype）

createprocedure插入销售记录@personidchar（20）,@shopnameIDchar（20）,@goodidchar（20）,@sellIDchar（20），@selltimedatetime

as

insertinto销售

values（@personid,@shopnameID,@goodid,@sellID,@selltime）

③修改

Createprocedure修改商品价格@goodpricemoney,@goodidchar（20）

As

Update商品

Set商品价格＝@goodprice

Where商品编号＝@goodid

Createprocedure修改运输记录@oldtidchar（20）,@tidchar（20）,@shopidchar（20）,@tpidchar（20）,@goodidchar（20）,@timedatetime,@fidchar（20）

Asupdate运输

Set运输编号=@tid,运输商编号＝@tpid,生产商编号=@fid,商品编号=@goodid,运输时间＝@time,商品编号＝@shopid

Where运输编号＝@oldtid