库存物资管理(参考案例)

1、.库存物资管理（案例）1、背景资料： 有一个存放商品的仓库，每天都有商品出库和入库。 每种商品都有商品编号、商品名称、生产厂家、型号、规格等。 入库时必须填写入库单据，单据包括商品名称、生产厂家、型号、规格、入库数量、入库日期、入库仓库号、入库仓库名称、送货人姓名。 出库时必须填写出库单据，单据包括仓库号、仓库名称、商品编号、商品名称、型号、规格、出库数量、出库日期、提货人姓名。 设置商品库存台帐，商品库存台帐是对仓库中目前库存的所有商品的明细记录，商品库存台帐包括商品编号、商品名称、型号、规格、库存数量、库存日期。每当有商品入库或商品出库时都应该自动修改该台帐，最后一次修改的是现在的库存情况

2、。2、设计要求： 进行需求分析，编写数据字典。 设计完整的E-R图。 进行数据库的逻辑设计。 完成物理数据库的设计，包括数据库、表、索引、视图、完整性约束的物理设计。 实现商品的入库，在商品入库时通过触发器或存储过程同时完成商品库存台帐的更新。 实现商品的出库，在商品出库时通过触发器或存储过程同时完成商品库存台帐的更新。 实现按商品名称查询商品的入库情况及目前的库存量。 实现按入库日期查询商品的入库情况及目前的库存量。 实现按商品名称查询商品的出库情况及目前的库存量。 实现按商品名称查询商品的出库情况及目前的库存量。 按时间段查询商品库存情况。 实现分别按年、季度和月对入库商品数量的统计。 实

3、现分别按年、季度和月对出库商品数量的统计。库存物资管理系统一、 进行需求分析，编写数据字典。（李XX负责部分）1、系统功能要求设计 有一个存放商品的仓库，每天都有商品出库和入库。 每种商品都有商品编号、商品名称、生产厂家、型号、规格等。 入库时必须填写入库单据，单据包括商品名称、生产厂家、型号、规格、入库数量、入库日期、入库仓库号、入库仓库名称、送货人姓名。 出库时必须填写出库单据，单据包括仓库号、仓库名称、商品编号、商品名称、型号、规格、出库数量、出库日期、提货人姓名。 设置商品库存台帐，商品库存台帐是对仓库中目前库存的所有商品的明细记录，商品库存台帐包括商品编号、商品名称、型号、规格、库存

4、数量、库存日期。每当有商品入库或商品出库时都应该自动修改该台帐，最后一次修改的是现在的库存情况。 商品的入库和出库过程通过库存台帐更加清晰条理地显示出仓库中商品的库存数量和库存日期等信息，容易对库存内的商品信息进行查询，增加，修改，删除等操作。 该仓库的商品型号统一为DA01-DA99格式，规格为CX100-CX199格式主要功能： 商品管理： 增加商品：修改商品，删除商品，浏览商品增加商品：实现增加商品的详细资料的功能修改商品：实现修改商品资料的功能删除商品：实现删除该商品的所有资料的功能浏览商品：实现浏览所有商品的功能 库存管理： 实现商品的入库，在商品入库时通过触发器或存储过程同时完成商

5、品库存台帐的更新。实现商品的出库，在商品出库时通过触发器或存储过程同时完成商品库存台帐的更新。实现按商品名称查询商品的入库情况及目前的库存量。实现按入库日期查询商品的入库情况及目前的库存量。实现按商品名称查询商品的出库情况及目前的库存量。实现按出库日期查询商品的出库情况及目前的库存量。按时间段查询商品库存情况。实现分别按年、季度和月对入库商品数量的统计。实现分别按年、季度和月对出库商品数量的统计。2、系统模块设计库存物资管理大体可以分为以下3大模块，如下图所示:首先是商品入库模块，该模块主要就是描述把采购回来的商品，分类别的放置到指定的仓库中去，然后是商品出库模块，该模块主要描述从指定的仓库中

6、拿出商品；最后时库存模块，这个模块主要是记录商品的库存数量变化。库存物资管理商 品 入 库 模 块商 品 出 库 模 块商 品 库 存 模 块3、数据字典表表名中文名Ware商品Depot仓库Stock 库存Stocking入库Outbound出库商品Ware字段名数据类型是否可以为空Is PKIs FK中文名WNochar(10)NOT NULLYesNo商品编号WNamevarchar(50)NOT NULLNoNo商品名称WFactoryvarchar(50)NOT NULLNoNo生产厂家WType varchar(20)NOT NULLNoNo型号Specvarc

7、har(20)NOT NULLNoNo规格仓库 Depot字段名数据类型是否可以为空Is PKIs FK中文名DNochar(10)NOT NULLYesNo仓库号DNamevarchar(50)NOT NULLNoNo仓库名称库存Stock 字段名数据类型是否可以为空Is PKIs FK中文名WNochar(10)NOT NULLYesYes商品编号Dnochar(10)NOT NULLYesYes仓库号WAmount IntNOT NULLNoNo库存数量入库Stocking字段名数据类型是否可以为空Is PKIs FK中文名SNochar(10)NOT NULLYes

8、No入库号WNochar(10)NOT NULLNoYes商品编号Dnochar(10)NOT NULLNoYes仓库号SAmountIntNOT NULLNoNo入库数量SDatesmalldatetimeNOT NULLNoNo入库日期Suppliervarchar(50)NOT NULLNoNo送货人姓名出库Outbound字段名数据类型是否可以为空Is PKIs FK中文名ONochar(10)NOT NULLYesNo出库号WNochar(10)NOT NULLNoYes商品编号Dnochar(10)NOT NULLNoYes仓库号OAmountintNOT NULLNoNo出库数量

9、ODatesmalldatetimeNOT NULLNoNo出库日期Buyersvarchar(50)NOT NULLNoNo提货人姓名二、 设计完整的E-R图。（林XX负责部分）商品编号型号生产厂家规格商品名称商品仓库号仓库仓库名称商品仓库库存nm商品仓库入库nm商品仓库出库nm库存关系：入库关系：出库关系：分E-R图：总E-R图：商品编号型号生产厂家规格商品名称商品仓库号仓库仓库名称入库日期送货人姓名入库数量入库入库号nnnmmm库存库存数量提货人姓名出库日期出库数量出库出库号三、 进行数据库的逻辑设计。 关系模式如下：商品（商品编号、商品名称、生产厂家、型号、规格） 商品编号为主键仓库（

10、仓库号、仓库名称） 仓库号为主键库存（商品编号，仓库号，库存数量） （商品编号，仓库号）为主键，同时也分别为外键入库（入库号，商品编号，仓库号，入库数量，入库日期，送货人姓名）（入库号）为主键，（商品编号，仓库号）为外键出库（出库号，商品编号，仓库号，出库数量，出库日期，提货人姓名）（出库号）为主键，（商品编号，仓库号）为外键四、 完成物理数据库的设计，包括数据库、表、索引、视图、完整性约束的物理设计。（胡XX负责部分） 创建数据库create database Material_DB创建商品表use Material_DBcreate table Ware( /*定义商品表*/WNo cha

11、r(10) NOT NULL primary key,WName varchar(50) NOT NULL,WFactory varchar(50) NOT NULL,WType varchar(20) NOT NULL,Spec varchar(20) NOT NULL)use Material_DB /*插入商品信息*/insert into Warevalues ('100000','毛巾','新家园','DA11','CX100');insert into Warevalues ('100001&#

12、39;,'台灯','新家园','DA35','CX110');insert into Warevalues ('100002','电冰箱','海尔','DA61','CX900');insert into Warevalues ('100003','电视','东芝','DA52','CX901');insert into Warevalues ('100004

13、9;,'长城干红','中粮华夏','DA25','CX108');insert into Warevalues ('100005','笔记本','东芝','DA24','CX781'); 创建仓库表use Material_DBcreate table Depot( /*定义仓库表*/DNo char(10) NOT NULL primary key,DName varchar(50) NOT NULL)use Material_DB /*插入仓库信息

14、*/insert into Depotvalues ('2010001','胜昌');insert into Depotvalues ('2010002','德隆');insert into Depotvalues ('2010003','豪友');创建库存表use Material_DBcreate table Stock ( /*定义库存关系*/WNo char(10) NOT NULL,DNo char(10) NOT NULL,-WDate smalldatetime NOT NULL,WA

15、mount int NOT NULL,primary key(WNo,Dno),foreign key (WNo) references Ware(WNo),foreign key (DNo) references Depot(DNo)创建入库表use Material_DBcreate table Stocking( /*定义入库关系*/SNo char(10) NOT NULL primary key,WNo char(10) NOT NULL,DNo char(10) NOT NULL,foreign key (WNo) references Ware(WNo),foreign key

16、(DNo) references Depot(DNo),SAmount Int NOT NULL,SDate smalldatetime NOT NULL,Supplier varchar(50) NOT NULL)创建出库表use Material_DBcreate table Outbound( /*定义出库关系*/ONo char(10)NOT NULL primary key,WNo char(10)NOT NULL ,foreign key (WNo)references Ware(WNo),DNo char(10) NOT NULL,foreign key (DNo) refere

17、nces Depot(DNo),OAmount int NOT NULl ,ODate smalldatetime NOT NULL,Buyers varchar(50) NOT NULL)完整约束的说明：商品Ware的WNo商品编号要求在100000999999之间，WName不能取空；仓库Depot的DNo仓库编号要求在20100012010999之间，DName不能取空；该仓库的商品型号统一为DA01-DA99格式，规格为CX100-CX199格式。use Material_DB /*定义表Ware唯一性的约束条件*/alter Table Ware add unique(WName,W

18、Factory,WType,Spec);alter Table Ware /*定义WType的完整性约束条件：商品型号统一为DA01-DA99格式*/add constraint C2 check(WType like 'DA0-91-9');alter Table Ware /*定义Spec的完整性约束条件：规格为CX100-CX199格式*/add constraint C3 check(Spec like 'CX1-90-90-9');use Material_DBalter table Ware /*添加对表Ware的WNo属性的完整性约束，要求在999

19、9999999之间*/add constraint C4 check(WNo like'1-90-90-90-90-90-90-90-90-90-9');alter table Depotadd constraint C5 check(DNo like'20100-90-90-9');use Material_DBalter table Waredrop constraint C4;use Material_DBalter table Ware /*添加对表Ware的WNo属性的完整性约束，要求在999999之间*/add constraint C4 check

20、(WNo like'1-90-90-90-90-90-9');创建视图通过WLST（提取“物料视图”每个字的头字母）视图来浏览库存中相同编号商品的信息，其中包括：商品编号WNo、商品名WName、商品厂家WFactory、商品型号WType、商品规格Spec、商品数量SNum、create view VWname(商品编号,商品名称, 库存数量)-视图ASSELECT Stock.WNo,Ware.WName,sum(Stock.WAmount)from Ware,Stockwhere Stock.WNo=Ware.WNo group by Stock.WNo,Ware.WNa

21、me;五、 实现商品的入库，在商品入库时通过触发器或存储过程同时完成商品库存台帐的更新。（谢XX负责部分） 入库表的触发器create trigger stocking_into on stocking -这是入库表的触发器 after insert asbegin declare a char(10),b char(10),d int;select a=i.DNo,b=i.WNo,d=i.SAmountfrom inserted as i;if(select Stock.WNo from Stock where Stock.WNo=b and Stock.DNo=a) is not null

22、beginupdate Stockset Stock.WAmount=Stock.WAmount+dwhere Stock.WNo=b and Stock.DNo=a;return;endinsert into stock values(b,a,d);endinsert into stocking values('100','100005','2010001',200,'2006-01-02','小明');insert into stocking values('101','100005&#

23、39;,'2010003',200,'2006-01-02','小兰');insert into stocking values('102','100001','2010003',200,'2006-01-02','小东');insert into stocking values('103','100002','2010001',200,'2006-01-02','小红');insert

24、into stocking values('104','100003','2010001',200,'2007-01-14','小风');insert into stocking values('105','100005','2010002',200,'2006-01-25','小明');insert into stocking values('106','100000','2010002'

25、;,200,'2006-02-02','小明');insert into stocking values('107','100001','2010001',200,'2005-03-02','小东');insert into stocking values('108','100002','2010002',200,'2007-04-02','小红');insert into stocking values

26、('109','100003','2010003',200,'2006-04-02','小风');insert into stocking values('110','100004','2010001',200,'2006-06-09','小敏');insert into stocking values('111','100004','2010002',200,'2005-06-0

27、2','小风');insert into stocking values('112','100005','2010001',200,'2005-06-02','小青');insert into stocking values('113','100000','2010003',200,'2005-06-02','小明');insert into stocking values('114','

28、100001','2010001',200,'2005-06-02','小东');insert into stocking values('115','100002','2010002',200,'2007-07-02','小红');insert into stocking values('116','100003','2010003',200,'2006-08-02','小风')

29、;insert into stocking values('117','100000','2010001',200,'2006-08-02','小明');insert into stocking values('118','100001','2010001',200,'2005-09-04','小东');insert into stocking values('119','100002','201

30、0002',200,'2005-06-02','小红');insert into stocking values('120','100003','2010003',200,'2005-09-11','小风');insert into stocking values('121','100002','2010002',200,'2005-09-01','小明');insert into stockin

31、g values('122','100003','2010002',200,'2005-07-08','小敏');insert into stocking values('123','100002','2010003',200,'2005-04-11','小敏');insert into stocking values('124','100001','2010001',200,'2

32、007-07-08','小红');insert into stocking values('125','100001','2010003',200,'2005-08-21','小青');insert into stocking values('126','100005','2010001',200,'2005-07-08','小青');insert into stocking values('127

33、9;,'100003','2010003',200,'2005-09-01','小兰');insert into stocking values('128','100002','2010002',200,'2007-07-08','小兰');insert into stocking values('129','100004','2010001',200,'2007-07-08','

34、小兰');insert into stocking values('130','100005','2010002',200,'2007-07-08','小红');六、 实现商品的出库，在商品出库时通过触发器或存储过程同时完成商品库存台帐的更新。（龙XX负责部分） 出库表触发器create trigger outbound_d on Outbound -这是出库表的触发器 after insert as begin declare a char(10),b char(10),d int ; select a=

35、i.DNo,b=i.WNo,d=i.OAmount from inserted as iif(select s.WNo from Stock as s where s.DNo=a and s.WNo=b ) is not nullbeginif(select s.WAmount from Stock as s ,inserted where s.WAmount>=inserted.OAmount and s.WNo=inserted.WNo and s.DNo=inserted.DNo) is not nullbeginupdate Stock set Stock.WAmount=Sto

36、ck.WAmount-dwhere Stock.DNo=a and Stock.WNo=bendelsebeginprint '库存量不够'rollbackendendelsebeginprint '库存中没有这种产品'rollbackendendinsert into Outbound values('1','100005','2010001',50,'2010-01-02','小红');insert into Outbound values('2','10

37、0005','2010002',30,'2010-01-02','小红');insert into Outbound values('3','100001','2010003',50,'2010-01-02','小风');insert into Outbound values('4','100005','2010001',30,'2010-01-02','小红');insert i

38、nto Outbound values('5','100002','2010002',20,'2010-02-08','小风');insert into Outbound values('6','100003','2010003',50,'2010-03-09','小红');insert into Outbound values('7','100000','2010002',30,'

39、;2009-04-15','小风');insert into Outbound values('8','100004','2010002',50,'2009-04-15','小风');insert into Outbound values('9','100005','2010001',30,'2010-06-02','小红');insert into Outbound values('10',&#

40、39;100005','2010002',50,'2009-04-15','小红');insert into Outbound values('11','100001','2010003',50,'2010-06-03','小风');insert into Outbound values('12','100005','2010001',30,'2009-04-15','小红');i

41、nsert into Outbound values('13','100002','2010002',20,'2010-07-05','小风');insert into Outbound values('14','100003','2010003',50,'2009-07-05','小红');insert into Outbound values('15','100000','2010002

42、9;,40,'2010-07-05','小风');insert into Outbound values('16','100004','2010002',50,'2009-07-05','小风');insert into Outbound values('17','100005','2010002',20,'2008-09-01','小明');insert into Outbound values(

43、9;18','100001','2010003',30,'2010-09-01','小敏');insert into Outbound values('19','100000','2010002',40,'2010-09-01','小敏');insert into Outbound values('20','100002','2010002',50,'2008-09-01',

44、9;小红');insert into Outbound values('21','100000','2010002',20,'2010-08-21','小青');insert into Outbound values('22','100003','2010001',30,'2008-09-01','小青');insert into Outbound values('23','100002',

45、9;2010002',40,'2008-09-01','小兰');insert into Outbound values('24','100003','2010003',50,'2008-12-30','小兰');insert into Outbound values('25','100004','2010002',20,'2009-12-30','小兰');insert into Outboun

46、d values('26','100005','2010001',40,'2008-12-08','小红');insert into Outbound values('27','100001','2010003',10,'2010-12-30','小敏');insert into Outbound values('28','100002','2010002',30,'2008-12

47、-08','小明');insert into Outbound values('29','100003','2010003',30,'2009-12-18','小红');insert into Outbound values('30','100004','2010001',40,'2010-09-01','小敏');七、 实现按商品名称查询商品的入库情况及目前的库存量。（梁XX负责部分） /*方法一*/selec

48、t SNo,WName,s.WNo,s.DNo,SAmount,WAmount from Stock as s,Ware as w,Stocking as siwhere WName='电冰箱' and s.WNo=w.WNo and w.WNo=si.WNo and s.DNo=si.DNo/*方法二*/ declare WName varchar(32)SET WName ='电冰箱'SELECT * FROM Stocking WHERE WNo IN (SELECT WNo FROM Ware WHERE WName = WName);/*方法三*/CR

49、EATE VIEW LSName(SNo,WName,WNo,DNo,SAmount,WAmount)ASselect SNo,WName,s.WNo,s.DNo,SAmount,WAmountfrom Stock as s,Ware as w,Stocking as si select distinct si.Sno,l.WName,si.SAmount,s.WAmountFROM Stock as s,LSName as l,Ware as w,Stocking as siWHERE l.WName='电冰箱' and si.Sno=l.Sno and w.WName=l.

50、WName and s.WNo=si.WNo and si.WNo=w.WNo and w.WNo=l.WNo and si.DNo=l.DNo and si.SAmount=l.SAmount and s.WAmount=l.WAmount 八、 实现按入库日期查询商品的入库情况及目前的库存量。（田XX负责部分） /*方法一 复合条件查询*/select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s,Stockingwhere SDate='2006-01-02 00:00:00' and s.W

51、No=Stocking.WNo and s.DNo=Stocking.DNo/*方法二建立视图查询*/create view WA_Stocking(SNo,WNo,DNo,SAmount,SDate,Supplier,WAmount)as select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s JOIN Stocking on s.WNo=Stocking.WNo and s.DNo=Stocking.DNo and SDate='2006-01-02 00:00:00'with check

52、option;/*方法三相关子查询*/select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s,Stockingwhere exists (select * where SDate='2006-01-02 00:00:00' and s.WNo=Stocking.WNo and s.DNo=Stocking.DNo);九、 实现按商品名称查询商品的出库情况及目前的库存量。（陈XX负责部分） /*方法一*/select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buye

53、rs,WAmount from Stock as s,Outbound,Warewhere WName='长城干红' and Ware.Wno=s.Wno and s.Wno=Outbound.Wno and s.DNo=Outbound.DNo/*方法二*/* 创建视图*/CREATE VIEW W_S_O(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount,WName)ASSELECT ONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount,Ware.WNameFROM Ware,Stock,O

54、utboundWHERE Stock.WNo=Outbound.WNo AND Stock.DNo=Outbound.DNo AND Ware.WNo=Stock.Wnoselect ONo,WNo,DNo,OAmount,WName,ODate,Buyers,WAmount from W_S_Owhere WName='长城干红' /*方法三*/declare WName varchar(32)set WName='长城干红'select *from Outbound where WNo=(select WNo from Ware where WName=WN

55、ame);/*方法四*/select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount from Stock as s,Outbound,Warewhere exists (select *where WName='长城干红' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo and Ware.Wno=s.Wno);十、 实现按出库日期查询商品的出库情况及目前的库存量。（关XX负责部分） /*方法一*/ select ONo,s.WNo,s.DNo,OAmount,ODate,Buye

56、rs,WAmount from Stock as s,Outboundwhere ODate='2010-01-02 0:00:00' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo/*方法二*/* 创建视图*/CREATE VIEW SO(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount)ASSELECT ONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmountFROM Stock,OutboundWHERE Stock.WNo=Outbound.WNo

57、 AND Stock.DNo=Outbound.DNo select ONo,WNo,DNo,OAmount,ODate,Buyers,WAmountfrom SOwhere ODate='2010-01-02 0:00:00'/*方法三*/select ONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount from Stock as s,Outboundwhere exists (select * where ODate='2010-01-02 0:00:00' and s.WNo=Outbound.WNo and s.DN

58、o=Outbound.DNo);十一、 按时间段查询商品库存情况。（殷XX负责部分） /*时间在-01-02和-01-02之间的入库出库情况*/*方法一*/select w.WName as 商品,sum(SAmount) as 入库数量from Stocking as s ,Ware as w where w.WNo=s.WNo and SDate between '2006-01-02' and '2010-01-02'group by w.WNameselect w.WName as 商品,sum(OAmount) as 出库数量from Outbound

59、 as o ,Ware as w where w.WNo=o.WNo and ODate between '2006-01-02' and '2010-01-02'group by w.WName/*方法二*/select w.WName as 商品,sum(SAmount) as 入库数量from Stocking as s ,Ware as w where w.WNo=s.WNo and SDate in(select SDatefrom Stocking as swhere SDate between '2006-01-02' and &#

60、39;2010-01-02')group by w.WNameselect w.WName as 商品,sum(OAmount) as 出库数量from Outbound as o ,Ware as w where w.WNo=o.WNo and ODate in(select ODatefrom Outbound as owhere ODate between '2006-01-02' and '2010-01-02')group by w.WName/*方法三*/select WName,sum(SAmount) Ssum from Stocking

61、,Ware where exists (select * where SDate between '2006-01-02' and '2010-01-02' and Stocking.WNo=Ware.WNo ) group by WNameselect WName,sum(OAmount) Osum from Outbound,Ware where exists (select * where ODate between '2006-01-02' and '2010-01-02' and Outbound.WNo=Ware.WN

62、o ) group by WName十二、 实现分别按年、季度和月对入库商品数量的统计。（金XX负责部分） /*方法一：年：*/select WName,sum(SAmount) Ssumfrom Stocking,Warewhere year (SDate)=2005 and Stocking.WNo=Ware.WNogroup by WName/*季度：*/select WName,sum(SAmount) Ssum from Stocking,Warewhere SDate between '2005-3-1 0:00:00' and '2005-5-31 0:0

63、0:00' and Stocking.WNo=Ware.WNogroup by WName/*月：*/select WName,sum(SAmount) Ssumfrom Stocking,Warewhere month (SDate)=6 and Stocking.WNo=Ware.WNo and year (SDate)=2005group by WName/*方法二：年：*/create view Stocking_SumNasselect WName,sum(SAmount) Ssumfrom Stocking JOIN Ware on Stocking.WNo=Ware.WN