數(shù)據(jù)庫綜合設計實驗

1、綜合實驗報告課程名稱數(shù)據(jù)庫原理與應用實驗名稱_數(shù)據(jù)庫綜合設計實驗學生學院專業(yè)班級學 號學生姓名指導教師20年 月曰實驗報告一、實驗方案：(1) 實驗軟件環(huán)境： Windows XP中文版，SQL SERVER200(中文版(2) 項目名稱：訂貨數(shù)據(jù)管理系統(tǒng)(3) 完成方式：由課程實驗分多次完成，在最后一次實驗提交。(4) 提交內(nèi)容：數(shù)據(jù)庫數(shù)據(jù)文件，日志文件，綜合實驗報告。二、實驗結果與數(shù)據(jù)1 系統(tǒng)功能1 系統(tǒng)功能本系統(tǒng)的工作環(huán)境是可在訂貨數(shù)據(jù)管理系統(tǒng)局域網(wǎng)內(nèi)操作，供商家查詢訂貨及查看供應記錄。為訂貨一一供應的運用提供一個方便快捷的工具，方便商家尋找貨源，提高生產(chǎn)資料購買效率。(1)基礎數(shù)據(jù)維護

2、。管理員可以進行倉庫信息，職工信息的數(shù)據(jù)添加，刪除，修改，管理 員可以進行訂購單數(shù)據(jù)的添加，刪除，修改。(2) 新訂購單管理。管理員可以訂購新的貨物形成新的訂購單，對新訂購單驗收并編號。(3 )訂購單明細管理。管理員可以進行訂購單明細管理。非管理員可以查看自已訂購單明 細信息。(4)訂購單查詢。全體人員可使用訂購單查詢功能。查詢可按職工號，供應商號，訂購單號等訂貨數(shù)據(jù)管理系統(tǒng)圖1系統(tǒng)功能?？鞂哟谓Y構圖2. 數(shù)據(jù)庫設計:根據(jù)上面對系統(tǒng)做的功能分析、系統(tǒng)設計，規(guī)劃出本系統(tǒng)中使用的數(shù)據(jù)庫實體，得出數(shù)據(jù)庫概念模型系統(tǒng)使用中涉及的實體包括：職工，倉庫，供應商，訂購單，訂購單明細 隱含的實體：經(jīng)理，工資，

3、城市，面積（1）職工（職工號，倉庫，經(jīng)理，工資） 倉庫（倉庫號，城市，面積）（3）供應商（供應商號，供應商名，地址）（4）訂購單（職工號，供應商，訂購單號，訂購日期，金額）（5）訂購單明細（訂購單號，序號，產(chǎn)品名稱，單價，數(shù)量）實體E-R圖如下:圖2系統(tǒng)E-R圖從概念模型，得到數(shù)據(jù)庫的邏輯模型如下:表名字段名字段類型關鍵字約束倉庫倉庫號6個字符是倉庫號 >='A0000 ' AND 倉庫號 <='ZZ9999 '城市10個字符UNIQUE ， NOT NULL面積數(shù)值面積=50職工倉庫是NOT NULL FOREIGN KEY REFERENCES倉

4、庫（倉庫號）職工號8個字符是PRIMARY KEY經(jīng)理工資貨幣工資 BETWEEN 1000 AND 10000供應供應商號4個字符是PRIMARY KEY商substring(供應商號,1,1) = 'S' and right(供應商號,3) '000' and right(供應商號,3)< '999')供應商名16個字符地址30個字符訂購職工號8個字符是NOT NULL單供應商號16個字符訂購單號6個字符是PRIMARY KEY訂購單號 BETWEEN 'OR0000' AND'OR9999'訂購日期日期

5、DEFAULT (GETUTCDATE()金額貨幣DEFAULT (n ull)訂購訂購單號6個字符是訂購單號='OR0000' and單明訂購單號='OR9999')細序號2位數(shù)字是產(chǎn)品名稱20個字符單價貨幣單價 0數(shù)量整數(shù)數(shù)量 0數(shù)據(jù)庫的關系圖系統(tǒng)相關存儲過程：(1) 查詢工資范圍在1000到3000元范圍內(nèi)的職工信息CREATE PROCEDURE uspsalaryxyx smallm on ey,y smallm oneyASSELECT倉庫號,所在城市,職工號,工資FROM smwh倉庫JION smwh.職工ON倉庫.倉庫號二職工.倉庫WHEREC

6、 BETWEEN x and yEXECUTE uspsalaryxy x=1000 , y=2000(2) 查詢在某年某月某日之后簽訂的訂購單的有關供應商信息CREATE PROCEDURE uspAtdateOrderdt smalldatetimeASselect 供應商號,供應商名,地址from smwh.供應商join smwh.訂購單.訂購單號on供應商.供應商號=訂購單.供應商號where 訂購日期 >dtorder by 訂購單號exec uspAtdateOrder '2000.01.03'(3) 更新操作 , 將某倉庫的訂購單的訂購日期統(tǒng)一改為一個指定

7、日期 (參數(shù)為 倉庫號和新的訂購日期)CREATE PROCEDURE uspUdOrderdt smalldatetime,sid char(6)ASUPDATED購單SET訂購日期=dtWHER職工號 LIKE sid+'%'EXECUTE USPDATETIMECURSOR '2010-3-9','aa0001'系統(tǒng)相關視圖：(1) 基于單個表按投影操作定義視圖CREATE VIEW 倉庫面積 (城市, 面積)ASSELECT城市,面積FROM倉庫(2) 基于單個表按選擇操作定義視圖CREATE VIEW 高薪職工(職工號, 經(jīng)理,工資)A

8、SSELECT 職工號，經(jīng)理，工資FROM職工 WHERE工資2000(3) 基于單個表按投影和選擇操作定義視圖CREATE VIEW 高薪職工 (職工號, 工資)ASSELECT 職工號,工資FROM職工 WHEREX資1500(4) 基于多個表根據(jù)連接操作定義視圖CREATE VIEW 訂單匯總(訂購單號,訂購日期 ,職工號,供應商號,序號,產(chǎn) 品名稱,單價,數(shù)量,金額)ASSELECT 訂購單 . 訂購單號 , 訂購日期 , 職工號 , 供應商號 , 序號 , 產(chǎn)品名稱 , 單價，數(shù)量，金額FROM訂購單JOIN 訂購單明細 ON 訂購單.訂購單號=訂購單明細 .訂購單號(5) 基于多個

9、表根據(jù)嵌套查詢定義視圖CREATE VIEW 低薪倉庫的職工 as SELECT城市FROM smwh倉庫 WHERE 倉庫號=ANY(SELEC倉庫 FROM smwh職工 WHERET資 <3000)(6) 定義含有虛字段的視圖CREATE VIEW 訂單匯總計算 (訂購單號,訂購日期,職工號,供應商號 ,序 號,產(chǎn)品名稱,單價,數(shù)量,金額)ASSELECT 訂購單.訂購單號,訂購日期,職工號,供應商號,序號,產(chǎn)品名稱, 單價,數(shù)量,單價*數(shù)量AS單項金額FROM訂購單JOIN 訂購單明細 ON 訂購單.訂購單號=訂購單明細 .訂購單號2. 視圖上的查詢設計SELECT城市,面積FR

10、OM倉庫3. 視圖上的插入，更新，和刪除操作。 (設計錯誤語句，回答出錯原因，給 出改正方法 )(1) 插入錯 誤 語 句 ： INSERT INTO 職 工 號 ( 職 工 號 , 經(jīng) 理 , 工 資)VALUES('',JOHN,3000)出錯原因：職工號為主鍵 , 不能為空改 正 方 法 ： INSERT INTO 職 工 號 ( 職 工 號 , 經(jīng) 理 , 工 資)VALUES('AA000302',J0HN,3000)(2) 更新錯誤語句：UPDATE職工信息SET面積=20 WHERE庫號='AA0001' 出錯原因：違反用戶定義完整

11、性 , 面積>50有約束改正方法：UPDATE職工信息SET面積=100 WHERE庫號='AA0001'(3) 刪除錯誤語句：DELETE職工 WHERE經(jīng)理='AA000108'出錯原因：違反了參照完整性,經(jīng)理'AA000108'不存在改正方法：DELETE職工 WHERE經(jīng)理='AA00001'游標的使用：DECLAREeid char(8),sid char(4),oid char(6),odate char(10),osum money,odid char(2),pname char(20),pprice mone

12、y,pcount smallintDECLARE ORDERDETAILS_CURSOR FORSELECT職工號,供應商號,訂購單號,訂購日期,金額,序號,產(chǎn)品名稱,單價, 數(shù)量FROM訂購單JOIN訂購單明細 ON訂購單.訂購單號二訂購單明細.訂購單 號ORDER BY單價 ASCOPEN ORDERDETAILS_CURSORPRINT ' 訂購單詳情 'PRINT '職工號,供應商號,訂購單號,訂購日期,金額,序號,產(chǎn)品名稱 ,單價, 數(shù)量'FETCHFROMORDERDETAILS_CURSORINTOeid,sid,oid,odate,osum,od

13、id,pname,pprice,pcountWHILE FETCH _STATUS=0BEGINPRINTeid+''+sid+''+oid+''+odate+''+osum+''+odid+''+pname+''+pp rice+''+pcountFETCHFROMORDERDETAILS_CURSORINTOeid,sid,oid,odate,osum,odid,pname,pprice,pcountENDCLOSE ORDERDETAILS_CURSORDEAL

14、LOCATE ORDERDETAILS_CURSOR設置觸發(fā)器：1)為職工建立一個更新觸發(fā)器 ,當修改工資值超過 6%時發(fā)出警示信息CREATE TRIGGER reminder1ON DBO. 職工FOR UPDATEASDECLARE new money,old moneySELECT new二工資 FROM INSERTEDSELECT old二工資 FROM DELETEDIF new/old>1.06BEGINRAISERROR('修改工資值超過6% 取消修改',16,10)ROLLBACKEND2)為訂購單表建立一個插入和更新觸發(fā)器 ,約束規(guī)則是： 當訂購單金

15、額小于 3000 時必須使用本地供應商(發(fā)出訂購單的倉庫和供應商在同一城市) ，如果不滿主 要求，則拒絕操作，并給出錯誤信息CREATE TRIGGER reminder2ON DBO.訂購單FOR INSERT，UPDATEASDECLARE storeid char(6),sid char(20),saddr char(20), citychar(4)SELECT storeid=Substring職工號,1,6),SID=供應商號 FROM INSEREDWHERE 金額 < 3000IF NOT storeid IS NULLBEGINSELECT CITY=城市 FROM 倉庫

16、 WHERE 倉庫號=storeidSELECT saddr=ft址 FROM 供應商 WHERE 供應商號=sidIF saddr not like '%'+city+'%'BEGINRAISERROR('3000 元以下訂單請用本地供應商 ,取消訂單修改 ',16,10)ROLLBACKENDEND(3)當“工資”發(fā)生更改時，向職工顯示消息CREATE TRIGGER manageON SMWH. 職工AFTER insert,UPDATE,DELETEASRAISERROR（' 注意人事部門 ',16,1）UPDATE SM

17、WH.職工 set 工資=5000 where 職工號='AA000101'3系統(tǒng)詳細設計（1 ）數(shù)據(jù)維護添加倉庫信息：倉庫5條記錄職工10供應商5訂購表20訂購表明細50INSERT INTO倉庫（倉庫號，城市，面積）VALUES （'AA0001', ' 廣州', 2500）INSERT INTO職工（職工號,倉庫,經(jīng)理,工資）VALUES （'AA000101', 'AA0001','AA000101',5000）INSERT INTO供應商（供應商號，供應商名，地址）VALUES （'

18、;S001', ' 廣州市蓮香樓有限公司 ',' 廣州市城西'）INSERT INTO訂購單（職工號,供應商號,訂購單號,訂購日期,金額）VALUES （'AA000101', 'S001', 'OR0001','',2000）INSERT INTO訂購單明細（訂購單號,序號,產(chǎn)品名稱,單價,數(shù)量）VALUES （'OR0001', '01', ' 雙黃白蓮月餅 4個裝', 100 , 50）修改職工信息：（將x城市倉庫的職工工資提高10%）U

19、PDATE 職工 SET 工資 = 工資*(1.1)WHERE 倉庫 IN (SELECT 倉庫號 FROM 倉庫 WHERE 城市='廣州') 刪除倉庫信息：DELETE FROM 倉庫 WHERE倉庫號='AB0002'（2） 查詢用 SELECT 語句完成以下查詢(1) 考慮不去掉重復值： Select 工資 from 職工 去掉重復值： Select DISTINCT 工資 from 職工 (2)Select * from 倉庫(3)當 x=1100 時 SELECT 職工號 from 職工 where 工資1100(4) 當 y=1100 時 sele

20、ct倉庫 from 職工 where 工資 1100(5) 當 x=AA0001 ，y=AA0002,z=1300 時select 職工號 from 職工 where 倉庫='AA0001'and 工資 1300UNIONselect職工號from 職工 where倉庫=''and工資1300(6) 當 x=1600,y=1300select * from 職工 where 工資 between 1300 and 1600(7) select * from 供應商 where 供應商名 like '% 公司'(8) 當x=廣州時select *

21、from 倉庫 where 城市 != '廣州'(9) select * from 訂購單 where 供應商號 is null(10) select * from 訂購單 where 供應商號 is not null(11) select * from 職工 order by 工資(12) select * from 職工 order by 倉庫 asc ,工資 desc(13) 當 x=1000select 職工號，城市where 工資1000from 倉庫 join 職工 On 職工 .倉庫 =倉庫 .倉庫號(14) 當 x=200 select 職工號，城市 where

22、 面積200from 倉庫 join 職工 On 職工 .倉庫 =倉庫 .倉庫號15)SELECT 經(jīng) 理 , 職 工 號 FROM 職 工 WHERE 職 工 號 LikeSUBSTRING(職工號,1,6) + '%' ORDER BY 職工號(16) SELECT * FROM 倉庫 CROSS JOIN 職工(17) SELECT 倉庫號 ,城市,面積,職工號 ,經(jīng)理,工資 FROM 倉庫 CROSSJOIN 職工 WHERE 職工.職工號 LIKE 倉庫.倉庫號+'%'ORDER bY 倉庫 .倉庫號(18) select 供應商名，供應商號，訂購單號

23、，訂購單日期From 訂購單 join 供應商 on 訂購單.供應商號 =供應商.供應商號(19) select 供應商名，供應商號，訂購單號，訂購單日期From 訂購單 left join 供應商 on 訂購單 .供應商號 =供應商 .供應商號(20) select 供應商名，供應商號，訂購單號，訂購單日期From 訂購單 right join 供應商 on 訂購單 .供應商號 =供應商 .供應商號(21) select 供應商名，供應商號，訂購單號，訂購單日期From 訂購單 full join 供應商 on 訂購單 .供應商號 =供應商 .供應商號(22) 當 x=1100 時SELEC

24、T 城市 FROM 倉庫 WHERE倉庫號 =ANY(SELECT 倉庫 FROM 職工 WHERE 工資>1100)(23) SELECT * FROM 倉 庫 WHERE NOT 倉 庫 號 =ANY(SELECT DISTINCT 倉庫 FROM 職工 WHERE 工資 <1100)(24) 當 x=AA000114 時SELECT * FROM 職 工 WHERE 工 資 =(SELECT 工 資 FROM 職 工WHERE 職工號 ='AA000114')(25) 查詢哪些城市的倉庫向地址為 x 的供應商發(fā)出了訂購單。SELECT * FROM 倉庫 WH

25、ERE 倉庫號=ANY(SELECT SUBSTRING(職工 號,1,6) AS 倉庫號FROM 訂購單 WHERE 供應商號 =ANY(SELECT 供應商號 FROM 供應 商 WHERE 地址 ='廣州市城西 ')(26) SELECT 供應商名 FROM 供應商 wHERE 地址 ='廣州天河 ' AND 供 應商號 IN (SELECT 供應商號 FROM 訂購單WHERE SUBSTRING(職工號,1,6)=(SELECT 倉庫號 FROM 倉庫 WHERE 城市='廣州')SELECT 供應商名 ,城市FROM 供應商 JOIN

26、 訂購單 ON 供應商.供應商號=訂購單.供應商號 JOIN 職工ON 訂購單 .職工號 =職工.職工號 JOIN 倉庫 ON 職工.倉庫=倉庫.倉庫號WHERE 供應商.地址='廣州天河 ' AND 倉庫.城市='廣州'（27）SELECT * FROM 職工WHERE 工資=ALL（SELECT 工資 FROM 職工 WHERE SUBSTRING（職 工號,1,6）='AA0001'）（28）SELECT * FROM 倉庫 WHERE 倉庫號 IN（SELECT 倉庫 FROM 職工 WHERE 工 資 =ALL（SELECT 工 資 F

27、ROM 職 工 WHERE SUBSTRING（職工號,1,6）='AA0001'）（29）SELECT職工號,MAX（金額）AS訂單最高金額 FROM 訂購單GROUP BY 職工號（30）SELECT * FROM 倉 庫 WHERE NOT 倉 庫 號 =ANY（SELECT DISTINCT 倉庫 FROM 職工 WHERE 職工號 IS NOT NULL）（31）SELECT * FROM 倉庫 WHERE 倉庫號=ANY（SELECT DISTINCT 倉 庫 FROM 職工 WHERE 職工號 IS NOT NULL）（32）SELECT COUNT（城市）AS倉庫數(shù)目FROM倉庫（33）SELECT SUM（工資）AS支付工資 FROM 職工（34）SELECT SUM（工資）AS支付工資 FROM 職工 WHERE城市IN （'韶 關','廣州'）（35）SELECT AVG（面積）AS平均面積 FR