1、課程設計課程名稱：數據庫系統概論 設計題目： 商品信息管理系統設計與實現院系名稱 信息科學與工程學院 班 級 交通運輸1202班 姓名與學號： 指導教師 1課題簡介隨著現代科技的高速發展，設備和管理的現代化，在實際工作中如何提高工作效率成為一個很重要的問題。而建立管理信息系統是一個很好的解決辦法。 經過半年的學習，我們對計算機方面的知識有了很大的提升，本著理論聯系實際的宗旨，通過學校提供的這次軟件技術實踐的機會，在指導教師的幫助下，歷經兩周時間，我們自行設計一套商品信息管理系統，在下面的各章中，我將以這套商品管理系統為例,談談其開發過程和所涉及到的問題。2設計目的 應用對數據庫系統原理的理論學

2、習,通過上機實踐的方式將理論知識與實踐更好的結合起來,鞏固所學知識。 數據庫應用 課程實踐：實踐和鞏固在課堂教學中學習有關知識，熟練掌握對于給定結構的數據庫的創建、基本操作、程序系統的建立和調試以及系統評價。 數據庫原理 軟件設計實踐：實踐和鞏固在課堂教學中學習的關于關系數據庫原理的有關知識和數據庫系統的建立方法，熟練掌握對于給定實際問題，為了建立一個關系數據庫商品信息管理系統，必須得經過系統調研、需求分析、概念設計、邏輯設計、物理設計、系統調試、維護以及系統評價的一般過程，為畢業設計打下基礎。3設計內容 運用基于E-R 模型的數據庫設計方法和關系規范化理論做指導完成從系統的分析到設計直至系統

3、的最終實現,開發商品信息系統,完成商品信息管理系統的全部功能,商品入庫子系統，商品出庫子系統，商品庫存子系統。 首先做好需求分析，并完成數據流圖和數據字典。 其次做概念分析，利用實體聯系的方法將需求分析的用戶需求抽象為信息結構，得到E-R 圖。 然后就是邏輯結構設計，將E-R 圖轉換為計算機系統所支持的邏輯模型4 設計具體實施一、 進行需求分析，編寫數據字典。1、系統功能要求設計 有一個存放商品的倉庫，每天都有商品出庫和入庫。 每種商品都有商品編號、商品名稱、生產廠家、型號、規格等。 入庫時必須填寫入庫單據，單據包括商品名稱、生產廠家、型號、規格、入庫數量、入庫日期、入庫倉庫號、入庫倉庫名稱、

4、送貨人姓名。 出庫時必須填寫出庫單據，單據包括倉庫號、倉庫名稱、商品編號、商品名稱、型號、規格、出庫數量、出庫日期、提貨人姓名。 設置商品庫存臺帳，商品庫存臺帳是對倉庫中目前庫存的所有商品的明細記錄，商品庫存臺帳包括商品編號、商品名稱、型號、規格、庫存數量、庫存日期。每當有商品入庫或商品出庫時都應該自動修改該臺帳，最后一次修改的是現在的庫存情況。 商品的入庫和出庫過程通過庫存臺帳更加清晰條理地顯示出倉庫中商品的庫存數量和庫存日期等信息，容易對庫存內的商品信息進行查詢，增加，修改，刪除等操作。 該倉庫的商品型號統一為DA01-DA99格式，規格為CX100-CX199格式主要功能： 商品管理：

5、增加商品：修改商品，刪除商品，瀏覽商品增加商品：實現增加商品的詳細資料的功能修改商品：實現修改商品資料的功能刪除商品：實現刪除該商品的所有資料的功能瀏覽商品：實現瀏覽所有商品的功能 庫存管理： 實現商品的入庫，在商品入庫時通過觸發器或存儲過程同時完成商品庫存臺帳的更新。實現商品的出庫，在商品出庫時通過觸發器或存儲過程同時完成商品庫存臺帳的更新。實現按商品名稱查詢商品的入庫情況及目前的庫存量。實現按入庫日期查詢商品的入庫情況及目前的庫存量。實現按商品名稱查詢商品的出庫情況及目前的庫存量。實現按出庫日期查詢商品的出庫情況及目前的庫存量。按時間段查詢商品庫存情況。實現分別按年、季度和月對入庫商品數量

6、的統計。實現分別按年、季度和月對出庫商品數量的統計。2、系統模塊設計庫存物資管理大體可以分為以下3大模塊，如下圖所示:首先是商品入庫模塊，該模塊主要就是描述把采購回來的商品，分類別的放置到指定的倉庫中去，然后是商品出庫模塊，該模塊主要描述從指定的倉庫中拿出商品；最后時庫存模塊，這個模塊主要是記錄商品的庫存數量變化。商品信息管理商 品 入 庫 模 塊商 品 出 庫 模 塊商 品 庫 存 模 塊3、數據字典表表名中文名Ware商品Depot倉庫Stock 庫存Stocking入庫Outbound出庫商品Ware字段名數據類型是否可以為空Is PKIs FK中文名WNochar(10)NO

7、T NULLYesNo商品編號WNamevarchar(50)NOT NULLNoNo商品名稱WFactoryvarchar(50)NOT NULLNoNo生產廠家WType varchar(20)NOT NULLNoNo型號Specvarchar(20)NOT NULLNoNo規格倉庫 Depot字段名數據類型是否可以為空Is PKIs FK中文名DNochar(10)NOT NULLYesNo倉庫號DNamevarchar(50)NOT NULLNoNo倉庫名稱庫存Stock 字段名數據類型是否可以為空Is PKIs FK中文名WNochar(10)NOT NULLYe

8、sYes商品編號Dnochar(10)NOT NULLYesYes倉庫號WAmount IntNOT NULLNoNo庫存數量入庫Stocking字段名數據類型是否可以為空Is PKIs FK中文名SNochar(10)NOT NULLYesNo入庫號WNochar(10)NOT NULLNoYes商品編號Dnochar(10)NOT NULLNoYes倉庫號SAmountIntNOT NULLNoNo入庫數量SDatesmalldatetimeNOT NULLNoNo入庫日期Suppliervarchar(50)NOT NULLNoNo送貨人姓名出庫Outbound字段名數據類型是

9、否可以為空Is PKIs FK中文名ONochar(10)NOT NULLYesNo出庫號WNochar(10)NOT NULLNoYes商品編號Dnochar(10)NOT NULLNoYes倉庫號OAmountintNOT NULLNoNo出庫數量ODatesmalldatetimeNOT NULLNoNo出庫日期Buyersvarchar(50)NOT NULLNoNo提貨人姓名二、 設計完整的E-R圖。商品編號型號生產廠家規格商品名稱商品倉庫號倉庫倉庫名稱商品倉庫庫存nm商品倉庫入庫nm商品倉庫出庫nm庫存關系：入庫關系：出庫關系：分E-R圖：總E-R圖：商品編號型號生產廠家規格商品名

10、稱商品倉庫號倉庫倉庫名稱入庫日期送貨人姓名入庫數量入庫入庫號nnnmmm庫存庫存數量提貨人姓名出庫日期出庫數量出庫出庫號三、 進行數據庫的邏輯設計。關系模式如下：商品（商品編號、商品名稱、生產廠家、型號、規格） 商品編號為主鍵倉庫（倉庫號、倉庫名稱） 倉庫號為主鍵庫存（商品編號，倉庫號，庫存數量） （商品編號，倉庫號）為主鍵，同時也分別為外鍵入庫（入庫號，商品編號，倉庫號，入庫數量，入庫日期，送貨人姓名）（入庫號）為主鍵，（商品編號，倉庫號）為外鍵出庫（出庫號，商品編號，倉庫號，出庫數量，出庫日期，提貨人姓名）（出庫號）為主鍵，（商品編號，倉庫號）為外鍵四、 完成物理數據庫的設計，（包括數據庫

11、、表、索引、視圖、完整性約束的物理設計。）創建數據庫create database Material_DB創建商品表use Material_DBcreate table Ware( /*定義商品表*/WNo char(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 ('

12、100000','毛巾','新家園','DA11','CX100');insert into Warevalues ('100001','臺燈','新家園','DA35','CX110');insert into Warevalues ('100002','電冰箱','海爾','DA61','CX900');insert into Warevalues ('

13、100003','電視','東芝','DA52','CX901');insert into Warevalues ('100004','長城干紅','中糧華夏','DA25','CX108');insert into Warevalues ('100005','筆記本','東芝','DA24','CX781'); 創建倉庫表use Material_DBcreate

14、 table Depot( /*定義倉庫表*/DNo char(10) NOT NULL primary key,DName varchar(50) NOT NULL)use Material_DB /*插入倉庫信息*/insert into Depotvalues ('2010001','勝昌');insert into Depotvalues ('2010002','德隆');insert into Depotvalues ('2010003','豪友');創建庫存表use Material_D

15、Bcreate table Stock ( /*定義庫存關系*/WNo char(10) NOT NULL,DNo char(10) NOT NULL,-WDate smalldatetime NOT NULL,WAmount 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

16、 primary key,WNo char(10) NOT NULL,DNo char(10) NOT NULL,foreign key (WNo) references Ware(WNo),foreign key (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 ke

17、y,WNo char(10)NOT NULL ,foreign key (WNo)references Ware(WNo),DNo char(10) NOT NULL,foreign key (DNo) references Depot(DNo),OAmount int NOT NULl ,ODate smalldatetime NOT NULL,Buyers varchar(50) NOT NULL)完整約束的說明：商品Ware的WNo商品編號要求在100000999999之間，WName不能取空；倉庫Depot的DNo倉庫編號要求在20100012010999之間，DName不能取空；該倉

18、庫的商品型號統一為DA01-DA99格式，規格為CX100-CX199格式。use Material_DB /*定義表Ware唯一性的約束條件*/alter Table Ware add unique(WName,WFactory,WType,Spec);alter Table Ware /*定義WType的完整性約束條件：商品型號統一為DA01-DA99格式*/add constraint C2 check(WType like 'DA0-91-9');alter Table Ware /*定義Spec的完整性約束條件：規格為CX100-CX199格式*/add constr

19、aint C3 check(Spec like 'CX1-90-90-9');use Material_DBalter table Ware /*添加對表Ware的WNo屬性的完整性約束，要求在9999999999之間*/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 Waredr

20、op constraint C4;use Material_DBalter table Ware /*添加對表Ware的WNo屬性的完整性約束，要求在999999之間*/add constraint C4 check(WNo like'1-90-90-90-90-90-9');創建視圖通過WLST（提取“物料視圖”每個字的頭字母）視圖來瀏覽庫存中相同編號商品的信息，其中包括：商品編號WNo、商品名WName、商品廠家WFactory、商品型號WType、商品規格Spec、商品數量SNum、create view VWname(商品編號,商品名稱, 庫存數量)-視圖ASSELEC

21、T Stock.WNo,Ware.WName,sum(Stock.WAmount)from Ware,Stockwhere Stock.WNo=Ware.WNo group by Stock.WNo,Ware.WName;五、 實現商品的入庫，在商品入庫時通過觸發器或存儲過程同時完成商品庫存臺帳的更新。入庫表的觸發器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.SAmou

22、ntfrom inserted as i;if(select Stock.WNo from Stock where Stock.WNo=b and Stock.DNo=a) is not nullbeginupdate 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&#

23、39;,200,'2006-01-02','小明');insert into stocking values('101','100005','2010003',200,'2006-01-02','小蘭');insert into stocking values('102','100001','2010003',200,'2006-01-02','小東');insert into stocking valu

24、es('103','100002','2010001',200,'2006-01-02','小紅');insert into stocking values('104','100003','2010001',200,'2007-01-14','小風');insert into stocking values('105','100005','2010002',200,'2006-01

25、-25','小明');insert into stocking values('106','100000','2010002',200,'2006-02-02','小明');insert into stocking values('107','100001','2010001',200,'2005-03-02','小東');insert into stocking values('108',

26、9;100002','2010002',200,'2007-04-02','小紅');insert into stocking values('109','100003','2010003',200,'2006-04-02','小風');insert into stocking values('110','100004','2010001',200,'2006-06-09','小敏'

27、;);insert into stocking values('111','100004','2010002',200,'2005-06-02','小風');insert into stocking values('112','100005','2010001',200,'2005-06-02','小青');insert into stocking values('113','100000','2

28、010003',200,'2005-06-02','小明');insert into stocking values('114','100001','2010001',200,'2005-06-02','小東');insert into stocking values('115','100002','2010002',200,'2007-07-02','小紅');insert into stock

29、ing values('116','100003','2010003',200,'2006-08-02','小風');insert into stocking values('117','100000','2010001',200,'2006-08-02','小明');insert into stocking values('118','100001','2010001',200,'

30、;2005-09-04','小東');insert into stocking values('119','100002','2010002',200,'2005-06-02','小紅');insert into stocking values('120','100003','2010003',200,'2005-09-11','小風');insert into stocking values('121&

31、#39;,'100002','2010002',200,'2005-09-01','小明');insert into stocking values('122','100003','2010002',200,'2005-07-08','小敏');insert into stocking values('123','100002','2010003',200,'2005-04-11',

32、9;小敏');insert into stocking values('124','100001','2010001',200,'2007-07-08','小紅');insert into stocking values('125','100001','2010003',200,'2005-08-21','小青');insert into stocking values('126','100005'

33、;,'2010001',200,'2005-07-08','小青');insert into stocking values('127','100003','2010003',200,'2005-09-01','小蘭');insert into stocking values('128','100002','2010002',200,'2007-07-08','小蘭');insert in

34、to stocking values('129','100004','2010001',200,'2007-07-08','小蘭');insert into stocking values('130','100005','2010002',200,'2007-07-08','小紅');六、 實現商品的出庫，在商品出庫時通過觸發器或存儲過程同時完成商品庫存臺帳的更新。出庫表觸發器create trigger outbound_d on O

35、utbound -這是出庫表的觸發器 after insert as begin declare a char(10),b char(10),d int ; select a=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

36、=inserted.WNo and s.DNo=inserted.DNo) is not nullbeginupdate Stock set Stock.WAmount=Stock.WAmount-dwhere Stock.DNo=a and Stock.WNo=bendelsebeginprint '庫存量不夠'rollbackendendelsebeginprint '庫存中沒有這種產品'rollbackendendinsert into Outbound values('1','100005','2010001

37、9;,50,'2010-01-02','小紅');insert into Outbound values('2','100005','2010002',30,'2010-01-02','小紅');insert into Outbound values('3','100001','2010003',50,'2010-01-02','小風');insert into Outbound values('

38、4','100005','2010001',30,'2010-01-02','小紅');insert into Outbound values('5','100002','2010002',20,'2010-02-08','小風');insert into Outbound values('6','100003','2010003',50,'2010-03-09','小紅&

39、#39;);insert into Outbound values('7','100000','2010002',30,'2009-04-15','小風');insert into Outbound values('8','100004','2010002',50,'2009-04-15','小風');insert into Outbound values('9','100005','201000

40、1',30,'2010-06-02','小紅');insert into Outbound values('10','100005','2010002',50,'2009-04-15','小紅');insert into Outbound values('11','100001','2010003',50,'2010-06-03','小風');insert into Outbound values

41、('12','100005','2010001',30,'2009-04-15','小紅');insert into Outbound values('13','100002','2010002',20,'2010-07-05','小風');insert into Outbound values('14','100003','2010003',50,'2009-07-05'

42、,'小紅');insert into Outbound values('15','100000','2010002',40,'2010-07-05','小風');insert into Outbound values('16','100004','2010002',50,'2009-07-05','小風');insert into Outbound values('17','100005'

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

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

45、8-09-01','小青');insert into Outbound values('23','100002','2010002',40,'2008-09-01','小蘭');insert into Outbound values('24','100003','2010003',50,'2008-12-30','小蘭');insert into Outbound values('25','

46、;100004','2010002',20,'2009-12-30','小蘭');insert into Outbound values('26','100005','2010001',40,'2008-12-08','小紅');insert into Outbound values('27','100001','2010003',10,'2010-12-30','小敏');ins

47、ert into Outbound values('28','100002','2010002',30,'2008-12-08','小明');insert into Outbound values('29','100003','2010003',30,'2009-12-18','小紅');insert into Outbound values('30','100004','2010001'

48、,40,'2010-09-01','小敏');七、 實現按商品名稱查詢商品的入庫情況及目前的庫存量。/*方法一*/select 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 * F

49、ROM Stocking WHERE WNo IN (SELECT WNo FROM Ware WHERE WName = WName);/*方法三*/CREATE 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

50、as w,Stocking as siWHERE l.WName='電冰箱' and si.Sno=l.Sno and w.WName=l.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 八、 實現按入庫日期查詢商品的入庫情況及目前的庫存量。/*方法一 復合條件查詢*/select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from

51、Stock as s,Stockingwhere SDate='2006-01-02 00:00:00' and s.WNo=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

52、.DNo=Stocking.DNo and SDate='2006-01-02 00:00:00'with check 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);九、 實現按商品名稱查詢商品的出庫情況及目前的

53、庫存量。/*方法一*/select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,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.DN

54、o,OAmount,ODate,Buyers,WAmount,Ware.WNameFROM Ware,Stock,OutboundWHERE 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 *fr

55、om Outbound where WNo=(select WNo from Ware where WName=WName);/*方法四*/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);十、 實現按出庫日期查詢商品的出庫情況及目前的庫存量。/

56、*方法一*/ select ONo,s.WNo,s.DNo,OAmount,ODate,Buyers,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,WAmoun

57、tFROM Stock,OutboundWHERE Stock.WNo=Outbound.WNo 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

58、-02 0:00:00' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo);十一、 按時間段查詢商品庫存情況。/*時間在-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 a

59、s 商品,sum(OAmount) as 出庫數量from Outbound 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 SDa

60、te between '2006-01-02' and '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

61、 WName,sum(SAmount) Ssum from Stocking,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 '20

62、10-01-02' and Outbound.WNo=Ware.WNo ) group by WName十二、 實現分別按年、季度和月對入庫商品數量的統計。/*方法一：年：*/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:00: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/*方法二：