數(shù)據(jù)庫課程設計庫存物資管理系統(tǒng)后臺數(shù)據(jù)庫

1、大型數(shù)據(jù)庫課程設計設計報告題 目：庫存物資管理系統(tǒng)后臺數(shù)據(jù)庫學 號： 學生姓名: 指導教師： 提交時間： 2013-11-22 目錄庫存物資管理系統(tǒng)后臺數(shù)據(jù)庫1第1章 進行需求分析，編寫數(shù)據(jù)字典11.1系統(tǒng)功能要求設計：11.2模塊設計11.3數(shù)據(jù)字典2第2章 面向對象分析和設計4第3章 邏輯結構設計53.1類和對象向關系模式轉換6第4章 數(shù)據(jù)庫物理結構設計74.1存取方法設計74.2存儲結構設計74.3物理設計7第5章 數(shù)據(jù)完整性設計115.1主鍵及索引11表名115.2完整性約束的說明115.3 check約束125.4 default默認值125.5 觸發(fā)器設計12第6章 數(shù)據(jù)庫視圖的設

2、計16第7章 存儲過程設計19總結28參考文獻：28 庫存物資管理系統(tǒng)后臺數(shù)據(jù)庫 第1章 進行需求分析，編寫數(shù)據(jù)字典1.1系統(tǒng)功能要求設計：(1)數(shù)據(jù)表以及數(shù)據(jù)量 1）有一個存放商品的倉庫，每天都有商品出庫和入庫。 2）商品有1000多種，每種商品都有名稱、生產廠家、型號、規(guī)格等。 3）出入庫時必須填寫出入庫單據(jù)，單據(jù)包括商品名稱、生產廠家、型號、規(guī)格、數(shù)量、日期、時間、入庫單位（或出庫單位）名稱、送貨（或提貨）人姓名。(2)設計要求： 1) 進行需求分析，編寫數(shù)據(jù)字典。 2) 設計e-r圖。 3) 設計出入庫單據(jù)的錄入。 4) 實現(xiàn)按商品名稱、出入庫日期的查詢。 5) 實現(xiàn)分別按日、月和年對

3、出入庫商品數(shù)量的統(tǒng)計。(3)主要功能： 1)商品管理：增加商品、修改商品、刪除商品、瀏覽商品 2)庫存管理： 通過觸發(fā)器實現(xiàn)商品的入庫，同時完成對庫存表的更新 通過觸發(fā)器實現(xiàn)商品的出庫，同時完成對庫存表的更新 實現(xiàn)按商品名稱查詢庫存數(shù)量、入庫數(shù)量和出庫數(shù)量 實現(xiàn)按入庫日期查詢入庫數(shù)量、出庫數(shù)量 實現(xiàn)分別按日、月和年對入庫商品數(shù)量的統(tǒng)計 實現(xiàn)分別按日、月和年對入庫商品數(shù)量的統(tǒng)計1.2模塊設計庫存物資管理大體可以分為3大塊，如下圖所示：首先是商品入庫模塊，該模塊主要是描述把采購回來的商品，分類別的放置到指定的倉庫中去，然后是商品出庫模塊，該模塊主要描述從指定的倉庫中拿出商品，最后是商品庫存模塊，這

4、個模塊主要是記錄商品的庫存數(shù)量。 庫存物資管理商品入庫模塊商品庫存模塊商品出庫模塊1.3數(shù)據(jù)字典表商品表倉庫表庫存表入庫表出庫表商品表字段名數(shù)據(jù)類型是否可以為空is pkis fk商品編號char(5)not nullyesno商品名稱varchar(50)not nullnono生產廠家varchar(50)not nullnono型號varchar(50)not nullnono規(guī)格varchar(20)not nullnono倉庫表字段名數(shù)據(jù)類型是否可以為空is pkis fk倉庫號char(10)not nullyesno倉庫名稱varchar(50)not nullnono庫存表字段

5、名數(shù)據(jù)類型是否可以為空is pkis fk商品編號char(5)not nullyesyes倉庫號char(10)not nullyesyes庫存數(shù)量intnot nullnono入庫表字段名數(shù)據(jù)類型是否可以為空is pkis fk入庫號char(10)not nullyesno商品編號char(5)not nullnoyes倉庫號char(10)not nullnoyes入庫數(shù)量intnot nullnono入庫日期smalldatetimenot nullnono入庫單位名稱varchar(50)nullnono送貨人姓名varchar(50)not nullnono性別char(2)nu

6、llnono出庫表字段名數(shù)據(jù)類型是否可以為空is pkis fk出庫號char(10)not nullyesno商品編號char(5)not nullnoyes倉庫號char(10)not nullnoyes出庫數(shù)量intnot nullnono出庫日期smalldatetimenot nullnono出庫單位名稱varchar(50)nullnono提貨人姓名varchar(50)not nullnono性別char(2) nullnono 第2章 面向對象分析和設計 類和對象設計如下：商品商品編號：char商品名稱：varchar生產廠家: varchar規(guī)格:varchar型號：varc

7、har 添加() 刪除() 查詢() 修改() 倉庫倉庫號:char倉庫名稱：varchar 添加() 修改() 刪除() 查詢()庫存商品編號：商品倉庫號：倉庫庫存數(shù)量：int查詢()修改()添加()刪除() 出庫出庫號：int倉庫號：倉庫商品編號: 商品商品名稱：商品出庫數(shù)量：int出庫日期：smalldatetime出庫單位名稱：單位送貨人姓名：人性別：char 添加() 修改() 刪除() 查詢()入庫入庫號：int倉庫號：倉庫商品編號: 商品商品名稱：商品入庫數(shù)量：int入庫日期：smalldatetime入庫單位名稱：單位送貨人姓名：人性別：char 添加() 刪除() 查詢()

8、修改() 第3章 邏輯結構設計生產廠家商品名稱商品編號分e-r圖： 商品 規(guī)格 型號 倉庫倉庫名稱倉庫號庫存關系：庫存 商品 倉庫 n m入庫入庫關系： 商品 倉庫 n m出庫出庫關系： 商品 倉庫 n m 商品名稱商品編號總e-r圖：生產廠家 規(guī)格 商品 型號入庫號出庫號庫存數(shù)量 n n n出庫數(shù)量入庫入庫入庫數(shù)量出庫出庫日期入庫日期提貨人姓名送貨人姓名 m m m 性別出庫單位名稱入庫單位名稱 倉庫 性別倉庫名稱倉庫號3.1類和對象向關系模式轉換關系模式如下：商品（商品編號，商品名稱，生產廠家，型號，規(guī)格） 商品編號為主鍵倉庫（倉庫號，倉庫名稱） 倉庫號為主鍵庫存（商品編號，倉庫號，庫存數(shù)

9、量） （商品編號，倉庫號）為主鍵，同時也分別為外鍵入庫（入庫號，商品編號，倉庫號，入庫數(shù)量，入庫日期，入庫時間，入庫單位名稱，送貨人姓名，性別） （入庫號）為主鍵，（商品編號，倉庫號）為外鍵。出庫（出庫號，商品編號，倉庫號，出庫數(shù)量，出庫日期，出庫時間，出庫單位名稱，提貨人姓名，性別） （出庫號）為主鍵，（商品編號，倉庫號）為外鍵。 第4章 數(shù)據(jù)庫物理結構設計4.1存取方法設計對于庫存物資管理系統(tǒng)來說，為了提高某些屬性(如：商品編號，倉庫號、入庫號，入庫日期，入庫數(shù)量等)的查詢速度，可以選擇聚簇存取的方法，即把這些屬性上具有相同值的元組集中放在連續(xù)的物理塊上。這樣在查詢某種商品就會大大提高查詢

10、速度。因此，該系統(tǒng)中選擇聚簇存取方法。4.2存儲結構設計庫存物資管理系統(tǒng)是一個大型復雜的計算機網(wǎng)絡信息系統(tǒng)，采用基于瀏覽器/服務器（b/s），客戶端/服務器（c/s）混合的應用體系結構來建設庫存物資管理系統(tǒng)。數(shù)據(jù)庫管理系統(tǒng)采用microsoft 公司推出的sql server 2005 或以上版本，并用sql進行數(shù)據(jù)庫的建立和數(shù)據(jù)庫中數(shù)據(jù)的維護和查詢。4.3物理設計實現(xiàn)該設計的環(huán)境為windows xp professional + ms sql server 2005 或以上版本。1、創(chuàng)建material數(shù)據(jù)庫create database material on primary (name

11、=material,filename=c:prografilesmicrosoftsqlservermssql.2mssqldatamaterial.mdf,size=3mb, maxsize=unlimited,filegrowth=1mb)log on (name=material_log,filename=c:programfilesmicrosoftsqlservermssql.2mssqldatamaterial_log.ldf,size=1mb, filegrowth=10%)2、創(chuàng)建商品表create table 商品表(商品編號char(5) not null primary

12、key,商品名稱varchar(50) not null,生產廠家varchar(50) not null,型號varchar(20) not null,規(guī)格varchar(20) not null)3、創(chuàng)建倉庫表create table 倉庫表(倉庫號char(10) not null primary key,倉庫名稱varchar(50) not null,)4、創(chuàng)建庫存表create table 庫存表(商品編號char(5) not null ,倉庫號 char(10) not null,庫存數(shù)量int not null,primary key(商品編號,倉庫號) ,foreign k

13、ey (商品編號) references 商品表(商品編號),foreign key (倉庫號) references 倉庫表(倉庫號)5、創(chuàng)建入庫表create table 入庫表(入庫號char(10) not null primary key,商品編號char(5) not null,倉庫號char(10) not null,foreign key (商品編號) references 商品表(商品編號),foreign key (倉庫號) references 倉庫表(倉庫號), 入庫數(shù)量int not null,入庫日期smalldatetime not null,入庫單位名稱varc

14、har(50) null,送貨人姓名varchar(10) not null,性別char(2) default(男)check (性別in (男女)6、創(chuàng)建出庫表create table 出庫表(出庫號char(10) not null primary key,商品編號char(5) not null,倉庫號char(10) not null,foreign key (商品編號) references 商品表(商品編號),foreign key (倉庫號) references 倉庫表(倉庫號),出庫數(shù)量int not null,出庫日期smalldatetime not null,出庫單位

15、名稱varchar(50) null,提貨人姓名varchar(10) not null,性別char(2) default(男)check (性別in (男女) 第5章 數(shù)據(jù)完整性設計 5.1主鍵及索引 表名主鍵建立索引 商品表(商品編號)create clustered index 商品表on 商品表(商品編號) 倉庫表(倉庫號)create clustered index 倉庫表on倉庫表(倉庫號) 入庫表(入庫號)create clustered index 入庫表on 入庫表(入庫號) 出庫表(出庫號)create clustered index 出庫表on 出庫表(出庫號)5.2完

16、整性約束的說明1、商品表的商品編號1000111000之間（便于查詢的使用），商品名稱不能取空；該倉庫的商品型號統(tǒng)一為da0001da1000格式。1）use material/*定義商品表唯一性的約束*/goalter table 商品表add unique (商品名稱,生產廠家,型號,規(guī)格)2）use material/*定義型號的完整性約束條件:商品型號統(tǒng)一為da0001-da1000格式*/goalter table 商品表add constraint cs1 check(型號likeda0-10-90-90-9)5.3 check約束在商品表中將型號進行check約束：check(型

17、號likeda0-10-90-90-9)在入庫表和出庫表中將性別進行check約束：check (性別in (男女)5.4 default默認值在入庫表和出庫表中性別默認值設為：男。default(男)5.5 觸發(fā)器設計1、 通過觸發(fā)器實現(xiàn)商品的入庫，同時完成對庫存表的更新（1） 入庫use materialgoif exists (select* from sysobjects where name=rukuinsert)drop procedure rukuinsertgo create procedure rukuinsert rukuhao char(10),shangpinbianh

18、ao char(5),cangkuhao char(10),rukushuliang int,rukuriqi smalldatetime,rukudanwei varchar(50),songhuorenname varchar(50) ,sex char(2)asif(exists(select * from 入庫表where 入庫號=rukuhao)print(該商品已經(jīng)存在，不能再次插入)elsebegininsert into 入庫表values( rukuhao ,shangpinbianhao,cangkuhao,rukushuliang,rukuriqi ,rukudanwei

19、,songhuorenname ,sex )print(插入成功)end（2） 更新use materialgocreate trigger rkb_into on 入庫表after updateas begin declare a char(5),b char(10) ,d intselect a=商品編號from insertedselect b=倉庫號from insertedselect d=入庫數(shù)量from inserted update 庫存表set 庫存數(shù)量=庫存數(shù)量+dwhere 商品編號= a and 倉庫號=bend2、 通過觸發(fā)器實現(xiàn)商品的出庫，同時完成對庫存表的更新（1

20、） 出庫use materialgoif exists (select* from sysobjects where name=chukuinsert)drop procedure chukuinsertgo create procedure chukuinsert chukuhao char(10),shangpinbianhao char(5),cangkuhao char(10),chukushuliang int,chukuriqi smalldatetime,chukudanwei varchar(50),tihuorenname varchar(50) ,sex char(2)as

21、if(exists(select * from 出庫表where 出庫號=chukuhao)print(該商品已經(jīng)存在，不能再次插入)elsebegininsert into 出庫表values( chukuhao ,shangpinbianhao,cangkuhao,chukushuliang,chukuriqi ,chukudanwei,tihuorenname ,sex )print(插入成功)end（2）更新use materialgocreate trigger ckb_into on 出庫表after updateas begin declare aa char(10),bb ch

22、ar(5), dd intselect aa=倉庫號from deletedselect bb=商品編號from deletedselect dd=出庫數(shù)量from inserted update 庫存表set 庫存數(shù)量=庫存數(shù)量-ddwhere 商品編號=bb and 倉庫號=aaend 第6章 數(shù)據(jù)庫視圖的設計1、建立商品表視圖use materialgocreate view 商品表視圖as select *from 商品表2、創(chuàng)建商品庫存視圖來瀏覽庫存中相同編號商品的信息，其中包括：商品編號、商品名稱和庫存數(shù)量。use materialgocreate view 商品庫存視圖(商品編號

23、,商品名稱,庫存數(shù)量)as select 庫存表.商品編號,商品表.商品名稱,sum(庫存表.庫存數(shù)量)from 商品表,庫存表where 商品表.商品編號=庫存表.商品編號group by 庫存表.商品編號,商品表.商品名稱3、創(chuàng)建商品入庫視圖use materialgocreate view 商品入庫視圖asselect 商品表.商品編號,商品名稱,生產廠家,型號,規(guī)格,入庫號,倉庫號,入庫數(shù)量,入庫日期from 商品表, 入庫表where 商品表.商品編號=入庫表.商品編號4、創(chuàng)建商品出庫視圖use materialgocreate view 商品出庫視圖asselect 商品表.商品編

24、號,商品名稱,生產廠家,型號,規(guī)格,出庫號,倉庫號,出庫數(shù)量,出庫日期from 商品表, 出庫表where 商品表.商品編號=出庫表.商品編號5、創(chuàng)建商品入庫倉庫視圖use materialgocreate view 商品入庫倉庫視圖asselect 商品表.商品編號,商品名稱,入庫表.倉庫號,倉庫名稱from 商品表, 入庫表,倉庫表where 商品表.商品編號=入庫表.商品編號and 入庫表.倉庫號=倉庫表.倉庫號6、創(chuàng)建商品出庫倉庫視圖use materialgocreate view 商品出庫倉庫視圖asselect 商品表.商品編號,商品名稱,出庫表.倉庫號,倉庫名稱from 商品表

25、, 出庫表,倉庫表where 商品表.商品編號=出庫表.商品編號and 出庫表.倉庫號=倉庫表.倉庫號 第7章 存儲過程設計1、 實現(xiàn)商品表的插入use materialgoif exists (select *from sysobjects where name=shangpininsert )drop procedure shangpininsert gocreate procedure shangpininsert bianhao char(5),name varchar(50),changjia varchar(50),xinghao varchar(50),guige varchar

26、(20)asif(exists(select * from 商品表where 商品編號=bianhao)print(該商品已經(jīng)存在，不能再次插入)elsebegininsert into 商品表values( bianhao,name,changjia,xinghao,guige)print(插入成功)endexec shangpininsert 11111,毛巾,紡織有限公司,da0111,30*70cm2、 實現(xiàn)商品表的刪除use materialgoif exists (select *from sysobjects where name=shangpindelete)drop proc

27、edure shangpindeletegocreate procedure shangpindelete bianhao char(5)asif(exists(select 商品編號from 商品表where 商品編號=bianhao)begindelete from 商品表where 商品編號=bianhaoprint(刪除成功，該商品已被刪除)endelse print(該商品不存在,不能刪除)exec shangpindelete 122003、按商品名稱查詢庫存數(shù)量use materialgoif exists (select *from sysobjects where name=

28、shangpin_info1)drop procedure shangpin_info1gocreate procedure shangpin_info1 shangpinname varchar(50)asselect a.商品編號,商品名稱,b.庫存數(shù)量from 商品表a join 庫存表bon a.商品編號=b.商品編號where 商品名稱=shangpinname exec shangpin_info1 lg冰箱4、按商品名稱查詢入庫數(shù)量use materialgoif exists (select *from sysobjects where name=ruku_info10)dro

29、p procedure ruku_info10gocreate procedure ruku_info10 shangpinname varchar(50)asselect 商品表.商品編號,商品名稱,入庫數(shù)量from 商品表join 入庫表on 商品表.商品編號=入庫表.商品編號where 商品名稱=shangpinnameexec ruku_info10 acer宏基筆記本電腦5、按商品名稱查詢出庫數(shù)量use materialgoif exists (select *from sysobjects where name=chuku_info11)drop procedure chuku_i

30、nfo11gocreate procedure chuku_info11 shangpinname varchar(50)asselect 商品表.商品編號,商品名稱,出庫數(shù)量from 商品表join 出庫表on 商品表.商品編號=出庫表.商品編號where 商品名稱=shangpinnameexec chuku_info11 msi微星電腦主板6、按入庫日期查詢入庫數(shù)量use materialgoif exists (select *from sysobjects where name=ruku_info2)drop procedure ruku_info2gocreate procedur

31、e ruku_info2 rukudate smalldatetimeasselect 商品表.商品編號,商品名稱,入庫日期,入庫數(shù)量from 商品表join 入庫表on 商品表.商品編號=入庫表.商品編號where 入庫日期=rukudateexec ruku_info2 2012-3-5 0:007、按出庫日期查詢出庫數(shù)量use materialgoif exists (select *from sysobjects where name=chuku_info3)drop procedure chuku_info3gocreate procedure chuku_info3 chukuda

32、te smalldatetimeasselect 商品表.商品編號,商品名稱,出庫日期,出庫數(shù)量from 商品表join 出庫表on 商品表.商品編號=出庫表.商品編號where 出庫日期=chukudateexec chuku_info3 2010/2/16 0:00:008、按年對入庫商品數(shù)量的統(tǒng)計use material/*按年對入庫商品數(shù)量的統(tǒng)計*/goif exists (select *from sysobjects where name=ruku_info4)drop procedure ruku_info4gocreate procedure ruku_info4 start_

33、date smalldatetime,end_date smalldatetimeasselect 商品名稱,sum(入庫數(shù)量) as 總入庫數(shù)量from 商品表join 入庫表on 商品表.商品編號=入庫表.商品編號where 入庫日期between start_date and end_dategroup by 商品名稱exec ruku_info4 2010-1-1,2010-12-319、按年對出庫商品數(shù)量的統(tǒng)計use material/*按年對出庫商品數(shù)量的統(tǒng)計*/goif exists (select *from sysobjects where name=chuku_info5)

34、drop procedure chuku_info5gocreate procedure chuku_info5 start_date smalldatetime,end_date smalldatetimeasselect 商品名稱,sum(出庫數(shù)量) as 出總庫數(shù)量from 商品表join 出庫表on 商品表.商品編號=出庫表.商品編號where 出庫日期between start_date and end_dategroup by 商品名稱exec chuku_info5 2012-1-1,2012-12-3110、按月對入庫商品數(shù)量的統(tǒng)計use material/*按月對入庫商品數(shù)量

35、的統(tǒng)計*/goif exists (select *from sysobjects where name=ruku_info6)drop procedure ruku_info6gocreate procedure ruku_info6 start_date smalldatetime,end_date smalldatetimeasselect 商品名稱,sum(入庫數(shù)量) as 總入庫數(shù)量from 商品表join 入庫表on 商品表.商品編號=入庫表.商品編號where 入庫日期between start_date and end_dategroup by 商品名稱exec ruku_in

36、fo6 2010-1-1,2010-1-3111、按月對出出庫商品數(shù)量的統(tǒng)計use material/*按月對出庫商品數(shù)量的統(tǒng)計*/goif exists (select *from sysobjects where name=chuku_info7)drop procedure chuku_info7gocreate procedure chuku_info7 start_date smalldatetime,end_date smalldatetimeasselect 商品名稱,sum(出庫數(shù)量) as 出總庫數(shù)量from 商品表join 出庫表on 商品表.商品編號=出庫表.商品編號wh

37、ere 出庫日期between start_date and end_dategroup by 商品名稱exec chuku_info7 2012-7-1,2012-7-3112、按日對入庫商品數(shù)量的統(tǒng)計use material/*按日對入庫商品數(shù)量的統(tǒng)計*/goif exists (select *from sysobjects where name=ruku_info8)drop procedure ruku_info8gocreate procedure ruku_info8 start_date smalldatetime,end_date smalldatetimeasselect 商品名稱,sum(入庫數(shù)量) as 總入庫數(shù)量from 商品表join 入庫表on 商品表.商品編號=入庫表.商品編號where 入庫日期between start_date and end_dategroup by 商品名稱13、 按日對出庫商品數(shù)量的統(tǒng)計use material/*按日對出庫商品數(shù)量的統(tǒng)計*/goif exists (select *from sysobjects where name=chuku_info9)drop procedure chuku_info9gocreate procedure chuku_info9 st