數(shù)據(jù)庫(kù)課程設(shè)計(jì)——數(shù)據(jù)庫(kù)設(shè)計(jì)部分

1、數(shù)據(jù)庫(kù)應(yīng)用課程設(shè)計(jì)數(shù)據(jù)庫(kù)設(shè)計(jì)部分學(xué)號(hào)： 姓名：_ _項(xiàng)目名稱:_倉(cāng)庫(kù)管理系統(tǒng)_1.創(chuàng)建數(shù)據(jù)庫(kù)及數(shù)據(jù)表：建立Test數(shù)據(jù)庫(kù)，存儲(chǔ)倉(cāng)庫(kù)信息、倉(cāng)庫(kù)員工信息、貨物信息、單位信息和貨物存取信息，該數(shù)據(jù)庫(kù)包含八張數(shù)據(jù)表，即倉(cāng)庫(kù)基本信息表“倉(cāng)庫(kù)”，倉(cāng)庫(kù)員工信息表“倉(cāng)庫(kù)員工”，貨物基本信息表“貨物”，單位基本信息表“單位”，貨物入庫(kù)信息表“入庫(kù)”，貨物出庫(kù)信息表“出庫(kù)”，貨物庫(kù)存信息表“庫(kù)存”，倉(cāng)庫(kù)盤庫(kù)信息表“盤庫(kù)”。1)各數(shù)據(jù)表的邏輯結(jié)構(gòu)如下：倉(cāng)庫(kù)員工(員工編號(hào),姓名,性別,聯(lián)系方式，職稱，銀行卡號(hào)，所屬倉(cāng)庫(kù)編號(hào))，其中主碼為員工編號(hào)；倉(cāng)庫(kù)(倉(cāng)庫(kù)編號(hào),倉(cāng)庫(kù)名稱,管理員編號(hào),管理員)，其中主碼為倉(cāng)庫(kù)編號(hào)，外碼

2、為管理員編號(hào)；貨物(貨物編號(hào),貨物名稱,所屬單位)，其中主碼為貨物編號(hào)；單位(單位編號(hào),單位名稱,聯(lián)系方式,地址)，其中主碼為單位編號(hào)；庫(kù)存(倉(cāng)庫(kù)編號(hào),貨物編號(hào),數(shù)量)，其中主碼為（倉(cāng)庫(kù)編號(hào)，貨物編號(hào)）；入庫(kù)(入單號(hào),倉(cāng)庫(kù)編號(hào),貨物編號(hào),入庫(kù)數(shù)量，入庫(kù)時(shí)間)，其中主碼為入單號(hào),外碼為倉(cāng)庫(kù)編號(hào)和貨物編號(hào)；出庫(kù)(出單號(hào),倉(cāng)庫(kù)編號(hào),貨物編號(hào),出庫(kù)數(shù)量，出庫(kù)時(shí)間)，其中主碼為出單號(hào),外碼為倉(cāng)庫(kù)編號(hào)和貨物編號(hào)；盤庫(kù)(盤庫(kù)號(hào),倉(cāng)庫(kù)編號(hào),盤庫(kù)時(shí)間,記錄是否有誤)，其中主碼為盤庫(kù)號(hào),外碼為倉(cāng)庫(kù)編號(hào)；2)創(chuàng)建數(shù)據(jù)庫(kù)及數(shù)據(jù)表的SQL語(yǔ)句如下：create database Testuse Test;create

3、table 倉(cāng)庫(kù)員工 (員工編號(hào)char(10) primary key, 姓名char(10) not null, 性別char(10) not null, 聯(lián)系方式char(15) not null, 職稱char(10) not null,銀行卡號(hào)char(20) not null,所屬倉(cāng)庫(kù)編號(hào)char(10);create table 倉(cāng)庫(kù) (倉(cāng)庫(kù)編號(hào)char(10) primary key, 倉(cāng)庫(kù)名稱char(10) not null, 管理員char(10) not null, 管理員編號(hào)char(10) not null, foreign key (管理員編號(hào)) referenc

4、es 倉(cāng)庫(kù)員工(員工編號(hào)) );create table 貨物 (貨物編號(hào)char(10) primary key, 名稱char(10) not null, 所屬單位char(10) not null );create table 單位 (單位編號(hào)char(10) primary key, 單位名稱char(20) not null, 聯(lián)系方式char(15) not null, 地址char(40) not null );create table 庫(kù)存 (倉(cāng)庫(kù)編號(hào)char(10) not null, 貨物編號(hào)char(10) not null, 數(shù)量int not null, primar

5、y key (倉(cāng)庫(kù)編號(hào),貨物編號(hào)) );create table 入庫(kù) (入單號(hào)char(20) primary key not null, 倉(cāng)庫(kù)編號(hào)char(10) not null, 貨物編號(hào)char(10) not null, 入庫(kù)時(shí)間char(20) not null, 入庫(kù)數(shù)量int not null );create table 出庫(kù) (出單號(hào)char(20) primary key not null, 倉(cāng)庫(kù)編號(hào)char(10) not null, 貨物編號(hào)char(10) not null, 出庫(kù)時(shí)間char(20) not null, 出庫(kù)數(shù)量int not null );cr

6、eate table 盤庫(kù) (盤庫(kù)號(hào)char(20) primary key not null, 倉(cāng)庫(kù)編號(hào)char(10) not null, 盤庫(kù)時(shí)間char(20) not null, 記錄是否有誤char(10) not null );3)輸入數(shù)據(jù)到所創(chuàng)建的數(shù)據(jù)庫(kù)表。具體數(shù)據(jù)如下：倉(cāng)庫(kù)員工表的數(shù)據(jù)：?jiǎn)T工編號(hào) 姓名 性別 聯(lián)系方式 職稱 銀行卡號(hào) 所屬倉(cāng)庫(kù)編號(hào)1500001 張三 男 12345678901 管理員 6221234567890123451 0011500002 李四 男 12345678902 管理員 6221234567890123452 0021500003 王紅 女

7、12345678903 管理員 6221234567890123453 0031500004 張軍 男 12345678904 普工 6221234567890123454 0011500005 林靜 女 12345678905 普工 6221234567890123455 002倉(cāng)庫(kù)表的數(shù)據(jù)：倉(cāng)庫(kù)編號(hào) 倉(cāng)庫(kù)名稱 管理員編號(hào) 管理員 001 cangku001 1500001 張三 002 cangku002 1500002 李四 003 cangku003 1500003 王紅 貨物表的數(shù)據(jù)： 貨物編號(hào) 貨物名稱 所屬單位 h00001 杯子 xx公司 h00002 筷子 xx公司 單位表的

8、數(shù)據(jù)： 單位編號(hào) 單位名稱 聯(lián)系方式 地址 d001 xx公司福建省漳州市薌城區(qū)xx街道庫(kù)存表數(shù)據(jù)： 倉(cāng)庫(kù)編號(hào) 貨物編號(hào) 數(shù)量 001 h00001 1000 001 h00002 1500 002 h00001 500 入庫(kù)表數(shù)據(jù)： 入單號(hào) 倉(cāng)庫(kù)編號(hào) 貨物編號(hào) 入庫(kù)數(shù)量 入庫(kù)時(shí)間 r15110001 001 h00001 400 2015.11.20 10:21:10出庫(kù)表數(shù)據(jù)： 出單號(hào) 倉(cāng)庫(kù)編號(hào) 貨物編號(hào) 出庫(kù)數(shù)量 出庫(kù)時(shí)間 C15110001 001 h00001 100 2015.11.22 09:27:35盤庫(kù)表數(shù)據(jù)： 盤庫(kù)號(hào) 倉(cāng)庫(kù)編號(hào) 貨物編號(hào) 盤庫(kù)數(shù)量

9、盤庫(kù)時(shí)間 P15110001 001 h00001 1250 2015.11.24 20:15:282.創(chuàng)建視圖和存儲(chǔ)過(guò)程，能夠根據(jù)給定參數(shù)進(jìn)行信息查詢。輸入貨物名稱，可以查詢?cè)撠浳锼鶎?duì)應(yīng)的貨物的編號(hào)、名稱、所屬單位、所在倉(cāng)庫(kù)編號(hào)和對(duì)應(yīng)的數(shù)量。1) 創(chuàng)建視圖use Test create view 貨物_庫(kù)存 as select 貨物.貨物編號(hào),名稱,所屬單位,倉(cāng)庫(kù)編號(hào),數(shù)量 from 貨物,庫(kù)存 where 貨物.貨物編號(hào)=庫(kù)存.貨物編號(hào) with check option;2) 測(cè)試視圖：查看視圖內(nèi)容select * from 貨物_庫(kù)存;3) 創(chuàng)建存儲(chǔ)過(guò)程use Testcreate p

10、rocedure 貨物_info2 name varchar(40)asselect * from 貨物_庫(kù)存where 名稱=name;4) 測(cè)試存儲(chǔ)過(guò)程：給出所要查詢的貨物名稱，執(zhí)行存儲(chǔ)過(guò)程測(cè)試1：exec 貨物_info2 '杯子'執(zhí)行結(jié)果如下：貨物編號(hào) 名稱 所屬單位 倉(cāng)庫(kù)編號(hào) 數(shù)量h00001 杯子 xx公司 001 1000h00001 杯子 xx公司 002 500測(cè)試2：exec 貨物_info2 '筷子'執(zhí)行結(jié)果如下：貨物編號(hào) 名稱 所屬單位 倉(cāng)庫(kù)編號(hào) 數(shù)量h00002 筷子 xx公司 001 15003.為Test數(shù)據(jù)庫(kù)的貨物表創(chuàng)建一個(gè)存儲(chǔ)過(guò)

11、程insert_貨物信息。該存儲(chǔ)過(guò)程的功能是向貨物表添加一條記錄，該記錄各字段的值是在執(zhí)行存儲(chǔ)過(guò)程時(shí)給出的相應(yīng)參數(shù)值。1)創(chuàng)建一個(gè)帶有三個(gè)輸入?yún)?shù)的存儲(chǔ)過(guò)程insert_貨物信息use Test; create procedure insert_貨物信息 (貨物編號(hào)char(10), 名稱char(10), 所屬單位char(10)as insert into 貨物(貨物編號(hào),名稱,所屬單位)values (貨物編號(hào),名稱,所屬單位)go2)測(cè)試存儲(chǔ)過(guò)程，有2種方法第1種方法use Test;exec insert_貨物信息'h00003','圓珠筆','

12、;xx公司'go測(cè)試結(jié)果如下：（所影響的行數(shù)為 1 行）打開(kāi)貨物表，發(fā)現(xiàn)新增加了一個(gè)條記錄('h00003','圓珠筆','xx公司')第2種方法 use Test;exec insert_貨物信息貨物編號(hào)='h00004',名稱='鉛筆',所屬單位='xx公司'Go測(cè)試結(jié)果如下：（所影響的行數(shù)為 1 行）打開(kāi)貨物表，發(fā)現(xiàn)新增加了一個(gè)條記錄('h00004','鉛筆','xx公司')4.使用T-SQL創(chuàng)建觸發(fā)器監(jiān)視屬性值的修改。在貨物表建立觸發(fā)

13、器，監(jiān)視名稱屬性列是否有被修改, 如果是，給出提示，并將修改前后的數(shù)據(jù)保存到另外的表中。1) 先創(chuàng)建一個(gè)新表貨物_loguse Testcreate table 貨物_log (貨物編號(hào)char(10), 名稱char(10); go2) 在貨物表上創(chuàng)建一個(gè)觸發(fā)器，監(jiān)視名稱屬性列是否有被修改, 如果名稱屬性列被修改，則給出提示,并將修改前后的數(shù)據(jù)保存到另外的表貨物_log中。create trigger 貨物s_logon 貨物for updateas if update(名稱) begin print('名稱被修改了！') /*保存舊的信息*/ insert into 貨物_

14、log (貨物編號(hào),名稱) select 貨物編號(hào),名稱 from deleted /*保存新的信息*/ insert into 貨物_log (貨物編號(hào),名稱) select 貨物編號(hào),名稱 from inserted endgo3)測(cè)試觸發(fā)器。對(duì)貨物表的名稱屬性列進(jìn)行修改，在查詢分析器上輸入以下代碼：update 貨物set 名稱='橡皮擦' where 貨物編號(hào)='h00004'測(cè)試結(jié)果如下：名稱被修改了！(1 行受影響)(1 行受影響)(1 行受影響)即在結(jié)果欄中看到該代碼執(zhí)行后影響的行數(shù)為3，分別是更新了貨物表中的一行，向新表貨物_log中插入了2行。

15、 對(duì)貨物表的所屬單位屬性列進(jìn)行修改，在查詢分析器上輸入以下代碼：update 貨物set 所屬單位='xx有限公司' where 貨物編號(hào)='h00004'測(cè)試結(jié)果如下：(1 行受影響)即在結(jié)果欄中看到該代碼執(zhí)行后影響的行數(shù)為1行，這1行僅是更新了貨物表中的數(shù)據(jù)，因?yàn)闆](méi)有更新到名稱屬性列的數(shù)據(jù)，因此，并沒(méi)有向新表貨物_log插入任何數(shù)據(jù)。5.創(chuàng)建DELETED觸發(fā)器。1)在倉(cāng)庫(kù)員工表中增加一列：statu，類型為：bit。倉(cāng)庫(kù)員工表的結(jié)構(gòu)如下：倉(cāng)庫(kù)員工表中的數(shù)據(jù)如下：2)在倉(cāng)庫(kù)員工表中建立觸發(fā)器：當(dāng)從表中刪除課程信息時(shí)，檢查statu屬性的狀態(tài)，如果為True則

16、不允許刪除，否則可以刪除。1)創(chuàng)建觸發(fā)器use Testcreate trigger Delete_倉(cāng)庫(kù)員工on 倉(cāng)庫(kù)員工for deleteas declare status bit select status=statu from deleted if status=1 begin raiserror('該員工信息不允許刪除！',16,1) rollback transaction end else raiserror('成功刪除！',16,1)go2)測(cè)試觸發(fā)器：a)先刪除statu值為“False”的員工信息，語(yǔ)句如下：delete 倉(cāng)庫(kù)員工where

17、員工編號(hào)='1500004'根據(jù)設(shè)計(jì)要求，該記錄應(yīng)該可以符合成功刪除要求，其返回結(jié)果如下：消息50000，級(jí)別16，狀態(tài)1，過(guò)程Delete_倉(cāng)庫(kù)員工，第13 行成功刪除！(1 行受影響)b)再刪除statu值為“True”的員工信息，語(yǔ)句如下：delete 倉(cāng)庫(kù)員工where 員工編號(hào)='1500005'按照觸發(fā)器的設(shè)計(jì)要求，該記錄不能刪除，執(zhí)行此語(yǔ)句后的服務(wù)器返回以下信：消息50000，級(jí)別16，狀態(tài)1，過(guò)程Delete_倉(cāng)庫(kù)員工，第9 行該員工信息不允許刪除！消息3609，級(jí)別16，狀態(tài)1，第18 行事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。6.創(chuàng)建帶返回值的存儲(chǔ)

18、過(guò)程進(jìn)行查詢1)創(chuàng)建存儲(chǔ)過(guò)程。創(chuàng)建一個(gè)帶有1個(gè)輸入?yún)?shù)、2個(gè)返回值的存儲(chǔ)過(guò)程search_貨物，其功能是：接受外部傳入的貨物編號(hào)，在數(shù)據(jù)表貨物中查找貨物信息，然后輸出貨物的名稱、所屬單位。use Testcreate procedure search_名稱 (貨物編號(hào)s char(10), 名稱s char(10) output, 所屬單位s char(10) output)asselect 名稱s=名稱, 所屬單位s=所屬單位from 貨物where 貨物編號(hào)=貨物編號(hào)s2)測(cè)試該存儲(chǔ)過(guò)程，use Testdeclare 名稱s char(10)declare 所屬單位s char(10)exec search_名稱'h00002',名稱s output,所屬單位s outputprint '貨物名稱：'+rtrim(名稱s)+'；所屬單位：'+rtrim(所屬單位s)go運(yùn)行結(jié)果如下：貨物名稱：筷子；所屬單位：xx公司7.使用T-SQL創(chuàng)建帶有輸入?yún)?shù)，并使用集聚函數(shù)的存儲(chǔ)過(guò)程進(jìn)行查詢。創(chuàng)建存儲(chǔ)過(guò)程，分別查詢存儲(chǔ)n種貨物的倉(cāng)庫(kù)名單，要求顯示倉(cāng)庫(kù)編號(hào)、倉(cāng)庫(kù)名稱。其中n作為輸入?yún)?shù)在執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程時(shí)給出。1)創(chuàng)建存儲(chǔ)過(guò)程use Testcreate procedure P_倉(cāng)庫(kù) num int