數(shù)據(jù)庫原理實驗指導書

1、.數(shù)據(jù)庫原理實驗指導書英泰移動通信學院紐瑞孚學院二八年九月;ISQL SERVER數(shù)據(jù)庫原理實驗指導書目 錄實驗一 創(chuàng)建和修改數(shù)據(jù)庫1實驗二 創(chuàng)建和修改數(shù)據(jù)表6實驗三 添加記錄并建立查詢10實驗四 創(chuàng)建視圖13實驗五 創(chuàng)建索引14實驗六 創(chuàng)建觸發(fā)器15實驗七 創(chuàng)建存儲過程16實驗八 備份和恢復數(shù)據(jù)庫17實驗九 綜合練習數(shù)據(jù)庫設計（一）19實驗十 綜合練習數(shù)據(jù)庫設計（二）20課程設計21實驗一 創(chuàng)建和修改數(shù)據(jù)庫目的和意義學習創(chuàng)建和修改用戶數(shù)據(jù)庫的方法。實驗內(nèi)容分別使用SQLServer2000企業(yè)管理器和Transact-SQL語句，按下列要求創(chuàng)建和修改用戶數(shù)據(jù)庫。1. 創(chuàng)建一個數(shù)據(jù)庫，要求如下

2、:(1) 數(shù)據(jù)庫名"testDB"。(2) 數(shù)據(jù)庫中包含一個數(shù)據(jù)文件，邏輯文件名為 testDB_data，磁盤文件名為testDB_data.mdf，文件初始容量為5MB，最大容量為15MB，文件容量遞增值為1MB。(3) 事務日志文件，邏輯文件名為TestDB_log, 磁盤文件名為TestDB_log.ldf,文件 初始容量為5MB, 最大容量為10MB，文件容量遞增值為1MB。2. 對該數(shù)據(jù)庫做如下修改:(1) 添加一個數(shù)據(jù)文件，邏輯文件名為TestDB2_data，實際文件為TestDB2_data.ndf,文件初始容量為1MB，最大容量為6MB，文件容量遞增值為

3、1MB。(2) 將日志文件的最大容量增加為15MB，遞增值改為2MB。實現(xiàn)步驟方法一：使用企業(yè)管理器創(chuàng)建和修改數(shù)據(jù)庫TestDB1. 設置常規(guī)選項卡(1) 在企業(yè)管理器的控制面板目錄中選中結(jié)點"數(shù)據(jù)庫"，單擊鼠標右鍵，在彈出菜單中選擇"新建數(shù)據(jù)庫"命令。(2) 設置新建數(shù)據(jù)庫的常規(guī)選項卡，在"名稱"文本框中鍵入數(shù)據(jù)庫名稱"TestDB"。如圖1-l所示。圖A12. 設置數(shù)據(jù)文件選項卡在“文件名”字段中鍵入數(shù)據(jù)文件名“TestDB_data”。設置該文件初始大小為5MB。圖1-1選中“文件屬性”對話框中的復選框“文件

4、自動增長”，并選中“按兆字節(jié)”設置文件容量遞增值為1。在“最大文件大小”對話框中選擇“將文件增長限制為（MB）”為15。如圖1-2所示。圖1-23. 設置事務日志選項卡(1) 在"文件名"字段中鍵入事務日志文件的邏輯文件名"TestDB_log"。設置該文 件初始大小為5MBo(2) 選中"文件屬性"對話框中復選框"文件自動增長"，并選中"按兆字節(jié)"設置文件容量遞增值為1。(3) 在"最大文件大小"對話框中選擇"將文件增長限制為(MB)"為10。如圖1-3

5、所示。(4) 單擊"確定"按鈕，完成數(shù)據(jù)庫的創(chuàng)建。 圖1-3設置事務日志選項卡4. 修改事務日志文件屬性(1) 從樹狀目錄窗口中找到剛剛創(chuàng)建的數(shù)據(jù)庫TestDB，單擊鼠標右鍵，從彈出菜單中選擇命令"屬性"，打開數(shù)據(jù)庫TestDB的屬性窗口。(2) 選擇數(shù)據(jù)文件選項卡。在該選項卡中添加數(shù)據(jù)文件TestDB2_data,方法是:在"文件名"字段中鍵入數(shù)據(jù)文件邏輯文件名"TestDB2_data"，設置該文件初始大小為 1MB,然后選中"文件屬性"對話框中復選框"文件自動增長"，并

6、選中"按兆字節(jié)"設置文件容量遞增值為1，最后在"最大文件大小"對話框中選擇"將文件增長限制為(MB)6。如圖1-4所示。圖1-4(3) 選擇事務日志選項卡，在該選項卡中將事務日志文件的最大容量改為15，遞增量改為2。如圖1-5所示。圖1-5方法二：使用Transact-SQL語句創(chuàng)建和修改數(shù)據(jù)庫TestDB1. 創(chuàng)建數(shù)據(jù)庫TestDBCREATE DATABASE TestDBON(NAME = TestDB_data,FILENAME=“D:Program filesMicrosoft sql servermssqldataTestDB_da

7、ta.mdf”,SIZE=5,MAXSIZE=15,FILEGROWTH=1)LOG ON(NAME = TestDB_log,FILENAME=“D:Program filesMicrosoft sql servermssqldataTestDB_log.ldf”,SIZE=5,MAXSIZE=10,FILEGROWTH=1)2. 修改數(shù)據(jù)庫TestDBALTER DATABASE TestDBADD FILE(NAME = TestDB2_data,FILENAME=“D:Program filesMicrosoft sql servermssqldataTestDB2_data.ndf”

8、,SIZE=1,MAXSIZE=6,FILEGROWTH=1)GOALTER DATABASE TestDBMODIFY FILE(NAME=TestDB_log,MAXSIZE=15)GOALTER DATABASE TestDBMODIFY FILE(NAME=TestDB_log,FILEGROWTH=2)GO習題 建立學生選課的數(shù)據(jù)庫stuDB, 邏輯文件名為 stuDB_data，磁盤文件名為stuDB_data.mdf，文件初始容量為10MB，最大容量為15MB，文件容量遞增值為2MB。事務日志文件，邏輯文件名為stuDB_log, 磁盤文件名為stuDB_log.ldf,文件初始

9、容量為5MB, 最大容量為10MB，文件容量遞增值為1MB。體會實驗二 創(chuàng)建和修改數(shù)據(jù)表目的和意義熟悉有關(guān)數(shù)據(jù)表的創(chuàng)建和修改等工作，并了解主鍵、外鍵以及約束的創(chuàng)建和應用，熟練掌握使用企業(yè)管理器和CREATE TABLE、ALTER TABLE等Transact-SQL語句對數(shù)據(jù)表的操作方法。實驗內(nèi)容分別使用企業(yè)管理器和Transact-SQL語旬按下列要求創(chuàng)建兩個數(shù)據(jù)表: 表B1項目數(shù)據(jù)表，表B2員工數(shù)據(jù)表，并按照步驟完成對表的相關(guān)修改和約束設置。項目表（Project）字段名數(shù)據(jù)類型字段長度注釋項目編號int主鍵名稱varchar長度為50負責人int客戶int開始日期datetime結(jié)束日

10、期datetime員工數(shù)據(jù)表(Employee)字段名數(shù)據(jù)類型字段長度注釋編號int主鍵姓名varchar長度為50性別varchar長度為50所屬部門varchar長度為50工資money長度為8實現(xiàn)步驟方法一：使用企業(yè)管理器創(chuàng)建數(shù)據(jù)表并添加約束1. 打開企業(yè)管理器，在樹狀目錄窗口中找到數(shù)據(jù)庫節(jié)點TestDB，并選中下一級節(jié)點"表"。2. 單擊鼠標右鍵，從彈出菜單中選擇命令"新建表"，打開表設計窗口，在窗口中按照"上機任務"中所列的"項目數(shù)據(jù)表"的字段及要求鍵入列名、數(shù)據(jù)類型、長度等屬性，并將"項目編號

11、"設置為主鍵。如圖2-l所示。圖2-l創(chuàng)建"項目數(shù)據(jù)表"3. 單擊"保存"按鈕，在彈出的對話框中輸入表名稱"項目數(shù)據(jù)表"，關(guān)閉表設計窗口，完成表"項目數(shù)據(jù)表"的刨建。4. 重復1-3步，創(chuàng)建另一個數(shù)據(jù)表"員工數(shù)據(jù)表"，表設計窗口如圖2-2所示。圖2-2創(chuàng)建"員工數(shù)據(jù)表"5. 添加外鍵約束:在"項目數(shù)據(jù)表"的"負責人"字段上添加外鍵約束，參照字段為"員工數(shù)據(jù)表"中的字段"編號"，約束名為F

12、K_pm。實現(xiàn)方法為:首先在企業(yè)管理器中選中"項目數(shù)據(jù)表"并單擊鼠標右鍵，在彈出菜單中選擇命令"設計表"打開設計表窗口。然后在該窗口中單擊按鈕"表和索引屬性"按鈕，打開表的"屬性"對話框。最后選擇"關(guān)系"選項卡，在改選項卡中做如下的操作。單擊"新建"按鈕;從"主鍵表"中選擇"員工數(shù)據(jù)表"，并選擇字段"編號"從"外鍵表"中選擇"項目數(shù)據(jù)表"，并選擇字段"負責人"

13、;在"關(guān)系名"中輸入FK_pm。單擊"關(guān)閉"按鈕，完成外鍵約束的設置。6. 添加檢查約束:將"員工數(shù)據(jù)表"中的字段"工資"的值限定在1000-10000,約束名CK_Salary。操作方法為:首先在企業(yè)管理器中選中"員工數(shù)據(jù)表"并單擊鼠標右鍵，在彈出菜單中選擇命令"設計表"打開設計表窗口。然后在該窗口中單擊按鈕"表和索引屬性"按鈕，打開表的"屬性"對話框。最后選擇"CHECK約束"選項卡，在改選項卡中做如下的操作:單

14、擊標簽中"新建"按鈕;在"約束表達式"文本框中輸入一個條件表達式;(工資>=1000 AND 工資<=10000)在"約束名"對話框中輸入所建核查約束名"CK_Salary"。如圖2-3所示。圖2-3方法二:使用Transact-SQL語句創(chuàng)建數(shù)據(jù)表并添加約束1. 創(chuàng)建項目數(shù)據(jù)表USE TestDBGOCREATE TABLE 項目數(shù)據(jù)表（項目編號 int PRIMARY KEY, 名稱 varchar(50),負責人 int, 客戶 int,開始日期 datatime,結(jié)束日期 datatime）2.

15、 創(chuàng)建員工數(shù)據(jù)表USE TestDBGO(編號 int PRIMARY KEY,姓名 varchar(50),性別 varchar(50),所屬部門 varchar(50)工資 money(8)3. 添加外鍵約束ALTER TABLE 項目數(shù)據(jù)表 ADD CONSTRAINT PK_pm FOREIGN KEY(負責人) REFERENCES 員工數(shù)據(jù)表（編號）添加檢查約束ALTER TABLE 員工數(shù)據(jù)表ADD CONSTRAINT CK_pm CHECK(工資>=1000 AND 工資<=10000)習題 建立學生的基本信息表student、課程表course和選修課成績表sc

16、.體會實驗三 添加記錄并建立查詢目的和意義熟練掌握查詢語句。實驗內(nèi)容向數(shù)據(jù)庫TestDB中的兩個數(shù)據(jù)表"項目數(shù)據(jù)表"和"員工數(shù)據(jù)表"中添加記錄，如表Cl,C2所示。然后在查詢分析器中書寫Transact-SQL語句完成"實現(xiàn)步驟"中所列的任務。表c1項目數(shù)據(jù)表項目編號名稱負責人客戶開始日期結(jié)束日期1SiS2103/12/200006/12/20002SiS項目21104/06/200005/01/20003Pet2206/17/200008/17/20004Pet項目22209/01/200009/18/20005CCH3303/12

17、/200006/12/20006CCH_LXF4304/06/200005/01/20007CCH_ZHS7306/17/200008/17/20008CCH_LY9309/01/200009/18/20009CCH_LYAN19303/12/200006/12/200010PETER6404/06/200005/01/200011REALIDEA8506/17/200008/17/200012REALIDEA17509/01/200009/18/200013REALIDEA219503/12/200006/12/200014REALIDEA39504/06/200005/01/200015P

18、PA4606/17/200008/17/200016NBA4709/01/200009/18/2000表C2員工數(shù)據(jù)表編號姓名性別所屬部門工資1陳有朋男項目部20002孫曉晴女項目部30003張曉峰男錄入部10004慕容雪男檢驗部15005陳秋萍女檢驗部10006王理斌男檢驗部20007周晴晴女辦公室30008楊亭亭女項目部25009馬明宇男項目部400019劉燕女項目部3000實驗要求1·書寫語句查詢項目數(shù)據(jù)表中客戶字段的唯一值，并查看查詢結(jié)果。參考答案:SELECT UNIQUE客戶 FROM 項目數(shù)表；2·書寫語句查詢工資高于2000的項目部的人員的姓名。參考答案:S

19、ELECT姓名FROM員工數(shù)據(jù)表WHERE所屬部門=“項目部"AND工資>2000；3·書寫查詢語句查詢來自CCH公司的項目名稱(以CCH開始)和負責人姓名。參考答案: SELECT a.名稱，b.姓名 FROM 項目數(shù)據(jù)表 AS a , 員工數(shù)據(jù)表 AS bWHERE a.負責人=b.編號AND a.名稱 LIKE “CCH%”；4書寫語句查詢每個部門的平均工資，結(jié)果按照平均工資的多少排序SELECT 所屬部門，AVG（工資）FROM 員工數(shù)據(jù)表GROUP BY 所屬部門ORDER BY AVG（工資）；5使用外向聯(lián)接輸出所有的員工姓名和負責人的項目名稱SELECT

20、員工數(shù)據(jù)表.姓名，項目數(shù)據(jù)表.名稱FROM 員工數(shù)據(jù)表 LEFT JOIN項目數(shù)據(jù)表ON員工數(shù)據(jù)表.編號=項目數(shù)據(jù)表.負責人；6使用子查詢輸出所有負責 CCH公司項目(以CCH開始)的員工姓名，以及沒有負責REALIDEA公司(以REALIDEA開始)項目的員工姓名 。SELECT 姓名 FROM 員工數(shù)據(jù)表 WHERE 編號 IN (SELECTDISTINCT 負責人 FROM 項目數(shù)據(jù)表WHERE 名稱 LIKE'CCH%') SELECT 姓名 FROM 員工數(shù)據(jù)表WHERE 編號 NOT IN (SELECT DISTINCT 負責人 FROM 項目數(shù)據(jù)表WHERE

21、名稱 LIKE'REALIDEA%');7·將所有REALIDEA公司的項目的結(jié)束日期更改為2002年1月8日 。UPDATE 項目數(shù)據(jù)表SET 結(jié)束日期= 'January 8, 2002'WHERE 名稱 LIKE 'REALIDEA%;8·錄入部的張曉峰決定辭職，請將員工數(shù)據(jù)庫中有關(guān)他的記錄刪除，并將他負責的項目移交給楊亭亭。書寫語句對數(shù)據(jù)表做相應的更改。DELETE員工數(shù)據(jù)表WHERE 姓名=“張曉峰”UPDATE 項目數(shù)據(jù)表 SET負責人=8WHERE負責人=3；習題在student , course , sc中 添加元組1

22、)從student , course , sc表中選擇出當年年齡在20歲以上的學生的學號和姓名。2)統(tǒng)計平均年齡價格。3)“張三”同學要退學，請刪除他的所有信息。 體會實驗四 創(chuàng)建視圖目的和意義掌握使用T -SQL語句創(chuàng)建視圖的方法，包括視圖的建立、刪除、修改；了解如何應用視圖有選擇地查看所需數(shù)據(jù)，并熟悉通過視圖更改數(shù)據(jù)表中數(shù)據(jù)的方法。實驗內(nèi)容在數(shù)據(jù)庫Company_Data中，基于表"項目數(shù)據(jù)表"和"員工數(shù)據(jù)表"創(chuàng)建視圖，要求為:(1) 視圖名為"員工項目"。(2) 包含字段"編號"、"姓名"

23、、"名稱"和"開始日期"。(3) 字段別名分別是"員工編號"、”員工姓名"、"項目名稱"、"項目開始日期"。實現(xiàn)步驟 (1)打開查詢分析器。在查詢窗口書寫CREATE VIEW語句創(chuàng)建視圖，并指定字段別名：USE TestDBGOCREATE VIEW 員工項目(員工編號,員工姓名,項目名稱,項目開始日期)ASSELECT a.編號,a.姓名,b.名稱,b.開始日期,FROM 員工數(shù)據(jù)表 AS a INNER JOIN 項目表 AS bON a.編號=b.負責人WHERE a.編號=b

24、.負責人GO (2)使用INSERT語句通過視圖向員工數(shù)據(jù)表中添加一條記錄，要求"姓名"字段值為"馬中興"。USETestDBGOINSERTINTO 員工項目(員工姓名)VALUES('馬中興')GO (3)使用UPPDATE語句通過視圖將第二步中插入記錄的員工姓名改為"馬中新"。USETestDBGOUPDATE員工項目SET 項目負責人= '馬中新，WHERE 項目負責人=馬中興GO (4)執(zhí)行系統(tǒng)存儲過程 sp_rename將視圖更名為"employee_project_VIEW "

25、。USETestDBGOEXEC sp_rename '員工項目', 'emp1oyee_project_VIEW'實驗五 創(chuàng)建索引目的和意義掌握創(chuàng)建索引的方法。實驗內(nèi)容分別使用企業(yè)管理器和Transact-SQL語句在實驗四的數(shù)據(jù)表"員工數(shù)據(jù)表"中基于"姓名"創(chuàng)建索引，要求索引名為"IDX_Name"，索引類型為非聚集索引。實現(xiàn)步驟方法一，使用企業(yè)管理器 (1)打開企業(yè)管理器，在樹狀目錄中展開數(shù)據(jù)表"員工數(shù)據(jù)表"所在的數(shù)據(jù)庫節(jié)點TestDB，并選擇其下一級節(jié)點"表&quo

26、t;。 (2)選擇表"員工數(shù)據(jù)表"，并單擊鼠標右鍵。在彈出菜單中選擇命令"所有任務管理索引"，打開"管理索引"對話框。 (3)在"管理索引"對話框中的"數(shù)據(jù)庫"下拉菜單中選擇數(shù)據(jù)庫表。 (4)選擇"新建"按鈕，彈出"新建索引"對話框。 (5)在"新建索引"對話框的"索引名"文本框中輸入索引名稱"IDX_Name"，然后選擇字段"姓名"并設置索引屬性。如圖5-l所示。圖5-l創(chuàng)建

27、索引(6)設置索引選項以后，單擊"確定"按鈕，返回到"管理索引"對話框。(7)單擊"關(guān)閉"按鈕，退出"管理索引"對話框，完成索引的創(chuàng)建。方法二:使用T-SQL語旬USE TestDBGOCREATE INDEX IDX_Name ON 員工數(shù)據(jù)表(姓名)GO實驗六 創(chuàng)建觸發(fā)器目的和意義理解觸發(fā)器的觸發(fā)過程和類型，掌握創(chuàng)建觸發(fā)器的方法。實驗內(nèi)容在數(shù)據(jù)庫 Company_Data的表"項目數(shù)據(jù)表"和"員工數(shù)據(jù)表"中分別創(chuàng)建觸發(fā)器。實現(xiàn)步驟 (1)打開查詢分析器。 (2)在查詢窗口

28、書寫 CREATE TRIGGER語句，基于表"員工數(shù)據(jù)表"創(chuàng)建AFTER INSERT 觸發(fā)器 Tigger_NewEmployeeSalary，將插入員工的工資額限制在 5000以內(nèi)。USETestDB.GOCREATE TRIGGERTrigger_NewEmployeeSalaryON 員工數(shù)據(jù)表AFTER INSERTASIF (SELECT 工資 FROM inserted) > 5000BEGINPRINT '新員工工資不能超過5000'ROLLBACKENDGO(3)在查詢窗口書寫CREATE TRIGGER語句，基于表"員工

29、數(shù)據(jù)表"創(chuàng)建AFTER UPDATE觸發(fā)器Trigger_SalaryChange，將員工工資變動額限制在2000以內(nèi)。CREATE TRIGGER Trigger_SalaryChangeON員工數(shù)據(jù)表AFTER UPDATEASIFUPDATE(工資)BEGIN IF (SELECT MAX(ABS(inserted.工資一de1eted.工資) FROMinsertedJOINdeleted ONinserted.編號 = deleted.編號) > 2000BEGIN PRINT 工資變動不能超過 2000' ROLLBACKTRANSACTIONENDF.ND

30、實驗七 創(chuàng)建存儲過程目的和意義了解存儲過程的概念和作用，掌握創(chuàng)建存儲過程的方法。實驗內(nèi)容在數(shù)據(jù)庫Company_Data中創(chuàng)建存儲過程Procedure_SalaryByDept，要求返回某一特定部門所有員工的工資總和，其中特定部門的名稱以存儲過程的輸入?yún)?shù)進行傳遞。實現(xiàn)步驟 (1)打開查詢分析器。 (2)在查詢窗口書寫 CREATE PROCEDURE Transact-SQL語句創(chuàng)建存儲過程Procedure_SalaryByDept，并帶有一個輸入?yún)?shù)Department用于傳遞部門名稱，一個輸出參數(shù)TotalSalary用于傳遞輸出結(jié)果。CREATE PROCEDURE Procedu

31、re_SalaryByDept Department varchar(40),TotalSalary money OUTPUTAS (3)選擇所屬部門為該參數(shù)傳遞的部門名稱的所有記錄，并對其工資字段求和。 SELECT Tota1Sa1ary=sum(工資)FROM員工數(shù)據(jù)表WHERE所屬部門=DepartmentGO(4)報告該部門的工資總額。PRINT'該部門的工資總額+CONVERT(varchar,TotalSalary)GO(5)執(zhí)行這個創(chuàng)建存儲過程的語句。(6)執(zhí)行系統(tǒng)存儲過程sp_help查看該存儲過程的一般信息。(7)執(zhí)行該存儲過程，分別計算項目部、錄入部、和檢驗部的工

32、資總額。(8)用ALTTER PROCEDURE語句將存儲過程加密。實驗八 備份和恢復數(shù)據(jù)庫目的和意義理解數(shù)據(jù)庫備份的過程和屬性設置，掌握使用企業(yè)管理器備份數(shù)據(jù)庫的方法。實驗內(nèi)容使用SQLServer2000企業(yè)管理器備份和恢復數(shù)據(jù)庫TestDB.實現(xiàn)步驟 (1)打開企業(yè)管理器，在控制面板目錄中選擇數(shù)據(jù)庫TestDB。 (2)單擊鼠標右鍵，從彈出菜單中選擇命令"所有任務備份數(shù)據(jù)庫"。 (3)在出現(xiàn)的"SQLServer備份"對話框中選擇"常規(guī)"選項卡。 (4)在該選項卡中的"名稱"文本框中鍵入"TestD

33、B數(shù)據(jù)庫備份"。 (5)在"描述"文本框中鍵入"備份集1"。 (6)在"備份"對話框中選中"數(shù)據(jù)庫-完全"選項，如圖8-1所示。 (7)單擊"添加"按鈕，在彈出的對話框中選擇"備份設備"，然后從下拉列表中選擇備份使用的備份設備(注意：如果沒有現(xiàn)成的備份設備，應在備份以前創(chuàng)建一個 具體方法參見相關(guān)章節(jié))。(8)單擊"重寫現(xiàn)有媒體"選項按鈕。 (9)單擊""選項"選項卡，在該選項卡中選中"完成后驗證備份&quo

34、t;復選框。 (10)單擊"確定"按鈕，出現(xiàn)"備份進度"對話框，備份完成后，將彈出消息框"備份操作及其驗證已成功完成”。 (11)單擊"確定"完成數(shù)據(jù)庫的備份。 (12)完成數(shù)據(jù)庫的還原，方法如下: 從企業(yè)管理器的樹狀目錄窗口中選擇需要還原的數(shù)據(jù)庫TestDB，單擊鼠標右鍵，從彈出菜單中選取命令"所有任務/還原數(shù)據(jù)庫"，打開"還原數(shù)據(jù)庫”對話框。如圖8-2所示。然后在該對話框中選擇"還原-從設備，單擊"選擇設備"按鈕，并從彈出的對話框中選擇備份設備TestDB_ba

35、ckup。 最后選擇"還原備份集"下的"數(shù)據(jù)庫-完全"，單擊"確定"按鈕執(zhí)行數(shù)據(jù)庫的還原。圖8-1圖8-2實驗九 綜合練習數(shù)據(jù)庫設計（一）1.目的:使學生對使用 SQL Server 來設計一個數(shù)據(jù)庫應用系統(tǒng)有一個初步的了解。2要求:在高校物資管理中，入庫時要簽發(fā)入庫單、固定資產(chǎn)卡、登記物資明細帳；出庫時要簽發(fā)領用申請單、登記物資發(fā)放卡和轉(zhuǎn)移單。此外，還有物資報損、調(diào)出情況請分析設計一個高校物資管理信息系統(tǒng)的數(shù)據(jù)庫并在SQL Server2000 上實現(xiàn)。該數(shù)據(jù)庫要能實現(xiàn)以下幾點要求：(1)能在這個數(shù)據(jù)庫上實現(xiàn)高校物資管理的整個業(yè)務流

36、程。(2)能使用SQL Server 提供的默認和規(guī)則來-維護數(shù)據(jù)的正確性。(3)能使用SQL Server 提供的約束、觸發(fā)器來維護數(shù)據(jù)的完整性。 3體會。實驗十 綜合練習數(shù)據(jù)庫設計（二）1.目的:使學生對使用 SQL Server 來設計一個數(shù)據(jù)庫應用系統(tǒng)有一個初步的了解。2要求:同上。在高校物資管理中，入庫時要簽發(fā)入庫單、固定資產(chǎn)卡、登記物資明細帳；出庫時要簽 發(fā)領用申請單、登記物資發(fā)放卡和轉(zhuǎn)移單。此外，還有物資報損、調(diào)出情況請分析設計一個高校物資管理信息系統(tǒng)的數(shù)據(jù)庫，并在SQL Server2000 上實現(xiàn)。該數(shù)據(jù)庫要能實現(xiàn)以下幾點要求：(1)使用存儲過程來實現(xiàn)一部分復雜的應用邏輯。(

37、2)為不同的用戶設計不同的用戶視圖。3體會。l 數(shù)據(jù)庫課程設計示例綜合教務系統(tǒng)分析和設計本節(jié)綜合教務系統(tǒng)為例，說明數(shù)據(jù)庫應用系統(tǒng)的設計過程。本系統(tǒng)的分析和設計過程主要包括：需求分析；概念結(jié)構(gòu)設計；邏輯結(jié)構(gòu)設計；應用系統(tǒng)的模塊設計；應用系統(tǒng)的用戶界面設計。由于本應用系統(tǒng)比較簡單，而我們的重點在于數(shù)據(jù)庫應用系統(tǒng)的分析和設計，所以，本例中沒有給出物理結(jié)構(gòu)設計，讀者可自行考慮物理結(jié)構(gòu)設計，建立合適的索引，提高查詢速度；對數(shù)據(jù)庫系統(tǒng)的實施和維護也沒有給出。5.4.1 需求分析1 系統(tǒng)目標：實現(xiàn)一個計算機綜合教務管理系統(tǒng)，完成班級信息管理，學生信息管理，課程信息管理和學生選課管理等功能。2 系統(tǒng)功能需求：

38、本系統(tǒng)的用戶分為超級用戶和普通用戶兩類，超級用戶負責系統(tǒng)維護，包括對班級信息，學生個人信息，課程信息的錄入，修改，查詢，刪除等。普通用戶即選課學生則只具有為自己選課的權(quán)限。（1） 功能劃分：用戶通過身份驗證后進入主界面。主界面為超級用戶提供6項選擇：學生信息管理，課程信息管理，班級信息管理，選課，口令修改和退出系統(tǒng)。普通用戶有4項功能選擇：選課，課程查詢，口令修改和退出系統(tǒng)。（2） 功能描述：l 班級信息管理：給出所有班級的列表，超級用戶可以實現(xiàn)增加，修改，查找，刪除班級信息，維護指定班級所有學生信息，為指定班級設定必修課和選課學分限制等功能。在維護指定班級學生信息功能中，超級用戶可以實現(xiàn)增加

39、，修改，刪除學生信息，察看某一學生選課情況，為學生更改口令等功能。在指定班級必修課功能中，以課表形式列出班級所有必修課的課程信息，實現(xiàn)為一個班級增加或刪除必修課的功能。l 學生信息管理：給出所有學生的列表，超級用戶可以增加，修改，刪除，查找學生信息，察看某個學生選課情況，為學生修改口令等功能。本項功能與班級信息維護功能中的維護指定班級學生信息功能相似，不同的是本項功能中將列出所有班級的所有學生信息。l 課程信息管理：加入，修改，刪除課程紀錄，察看某門課的選課情況，查詢指定的課程信息。l 選課：以課表的形式列出學生選課情況，學生可以為自己選修或者退選課程，而超級用戶可以為任何學生選修或者退選課程

40、。每個班級都有選課的學分限制，不允許學生選擇課程的總學分超過所在班級的選課最大學分限制，而當退出選課功能時，如果選取課程總學分未達到所在班級選課最小學分限制則給出警告信息（因為學生可以多次進入選課界面選課）。l 修改口令：用戶需要輸入原口令，并且兩次輸入新口令一致后，系統(tǒng)即用新口令代替舊口令。l 退出系統(tǒng)：當用戶退出系統(tǒng)時，如果選取的課程總學分未達到所在班級選課最小學分限制，則給出警告信息。3 開發(fā)工具：該綜合教務系統(tǒng)的數(shù)據(jù)庫采用了Microsoft的SQL Server2000企業(yè)版，前臺應用程序采用了JSP編寫，提供了Web界面方便學生從網(wǎng)上使用。由于采用了JDBC數(shù)據(jù)庫接口，因此很容易修

41、改為支持ORACLE，SYBASE，DB2等數(shù)據(jù)庫，該系統(tǒng)的結(jié)構(gòu)為B/S結(jié)構(gòu)，即瀏覽器/服務器結(jié)構(gòu)。4 系統(tǒng)的數(shù)據(jù)流圖：了解用戶的應用要求，使用信息流程圖分析應用系統(tǒng)中的信息流。綜合教務系統(tǒng)的簡單信息流如下。（1）系統(tǒng)的上下文數(shù)據(jù)流圖如圖5-38錄入或更新學生、課程、班級信息選課/退選學生、班級、選課教學情況報表選課表 成績表監(jiān)控權(quán)限 密碼綜合教務系統(tǒng)學生教務管理員系統(tǒng)管理員系統(tǒng)管理員教務管理員學生圖5-38 綜合教務系統(tǒng)的上下文數(shù)據(jù)流圖 （2） 學生選課的數(shù)據(jù)流圖如圖5-39學生班級信息身份驗證選課申請選課處理審核通過的選課申請學生描述信息學生信息驗證不通過選課信息記錄選課成功已選課程班級描

42、述信息課程信息課程描述信息上課時間信息課程上課時間班級和課程信息班級必修課程圖5-39 學生選課的數(shù)據(jù)流圖5. 數(shù)據(jù)字典數(shù)據(jù)項名：學生編號說明： 標識每個學生身份類型： CHAR長度： 7別名： 學號取值范圍：970000979999數(shù)據(jù)流名：選課申請說明： 由學生的個人信息，欲選課程信息組成選課申請來自過程：無流至過程：身份驗證數(shù)據(jù)結(jié)構(gòu)：學生個人信息 欲選課信息數(shù)據(jù)結(jié)構(gòu)：學生個人信息說明： 說明了學生的個人情況。組成： 帳號 密碼數(shù)據(jù)存儲：上課時間信息說明： 說明了每門課的上課時間，一門課可以有多個上課時間，同一時間可以有多門課程在上課。輸出數(shù)據(jù)流：課程上課時間數(shù)據(jù)描述：課程編號 上課時間數(shù)

43、量： 每學期2030個存取方式：隨機存取處理過程：身份驗證說明： 對學生輸入的帳號，密碼進行驗證，確定正確，得到相應的學生編號。輸入： 學生帳號 密碼 選課的課程編號輸出： 學生編號 選課的課程編號5.4.2 數(shù)據(jù)庫的概念設計1系統(tǒng)的概念模型：班級學分限制班級名學生學號姓名性別生日屬于必修課程選修上課時間上課課上課時間授課教師接納人數(shù)課號課名學分1mmnmmnn選課系統(tǒng)DEMO概念模型的E/R圖上圖是選課系統(tǒng)DEMO的概念模型的E/R圖，該系統(tǒng)涉及的實體集有：班級實體集：具有屬性班級名稱和選課學分限制。學生實體集：具有屬性學號、姓名、性別和生日。課程實體集：具有屬性課程號、課程名、學分、授課教

44、師、接納人數(shù)。上課時間實體集：具有屬性時間。一個班級可以有多個學生，一個學生只能屬于一個班級，所以班級和學生之間的聯(lián)系為1：M的聯(lián)系。一個班級可以有多門必修課程，一門課程是多個班級的必修課，所以班級和課程之間的必修聯(lián)系是M：N的聯(lián)系。一個學生課以選修多門課程，一門課程可以被多個學生選修，所以學生和課程之間的聯(lián)系是M：N的聯(lián)系。一門課程可以有多個上課時間，同一時間內(nèi)可以有多門課程在上課，所以課程和上課時間的聯(lián)系是M：N的聯(lián)系。5.4.3 將概念模型轉(zhuǎn)換成關(guān)系模型1. 將E-R模型轉(zhuǎn)換為關(guān)系模式 (1) 班級實體集可以轉(zhuǎn)換為關(guān)系：CLASS（CLASSNAME,MAXCREDIT,MINCREDI

45、T）CLASSNAME表示班級名稱，MAXCREDIT表示最大學分限制，MINCREDIT表示最小學分限制。 (2) 學生實體集可以轉(zhuǎn)換為關(guān)系STUDENT（STUDENTID , NAME , SEX , BIRTHDAY）STUDENTID表示學號，NAME表示姓名，SEX表示性別，BIRTHDAY表示生日 (3) 課程實體可以轉(zhuǎn)換為關(guān)系COURSE（COURSEID, COURSENAME, CREDIT, TEACHER, ACCEPTION）COURSEID表示課程號，COURSENAM表示課程名，CREDIT表示學分，TEACHER表示授課教師，ACCEPTION表示接納人數(shù)。 (

46、4) 班級和學生之間的聯(lián)系是1：M的聯(lián)系，所以沒有必要為其建立一個關(guān)系，可以通過擴展學生關(guān)系來表示：STUDENT(STUDENTID , NAME , SEX , BIRTHDAY , CLASSNAME)CLASSNAME表示學生所在班級名。 (5) 班級和課程之間的必修聯(lián)系可以轉(zhuǎn)換為關(guān)系：CLASSCOURSE(CLASSNAME , COURSEID)CLASSNAME表示班級名稱，COURSEID表示課程號。 (6) 學生和課程之間的選修聯(lián)系可以轉(zhuǎn)化為關(guān)系：STUDENTCOUSE(STUDENTID, COURSEID)STUDENTID表示學號，COURSEID表示課程號。 (7

47、) 課程和上課之間的聯(lián)系可以轉(zhuǎn)化為關(guān)系：COURSETIME(COURSEID , TIME)COURSEID表示課程號，TIME表示上課時間。2. 數(shù)據(jù)庫表結(jié)構(gòu)設計：把關(guān)系模型轉(zhuǎn)化為表結(jié)構(gòu)：班級信息表(class)：包含所有的班級信息，定義如下：域名含義數(shù)據(jù)類型例子備注CLASSNAME班級名稱字符類型計71主碼MAXCREDIT最大學分限制數(shù)值類型30MINCREDIT最小學分限制數(shù)值類型20學生信息表(student)：包含所有學生的個人信息，定義如下：域名含義數(shù)據(jù)類型例子備注STUDENTID學號字符類型971329主碼NAME姓名字符類型王強SEX性別字符類型男BIRTHDAY生日日

48、期類型79/07/15CLASSNAME班級名稱字符類型計71外碼課程信息表(course)：包含所有課程信息，定義如下：域名含義數(shù)據(jù)類型例子備注COURSEID課程號字符類型000001主碼COURSENAME課程名稱字符類型數(shù)值分析CREDIT學分數(shù)值類型4TEACHER任課教師字符類型劉強ACCEPTION接納人數(shù)數(shù)值類型28班級必修課表(classcourse)：包含所有班級的必修課信息，定義如下：域名含義數(shù)據(jù)類型例子備注CLASSNAME班級名稱字符類型計71外碼COURSEID課程號字符類型000001外碼學生選課表(studentcourse)：包含所有學生的選課信息，定義如下：

49、域名含義數(shù)據(jù)類型例子備注STUDENTID學號字符類型971329外碼COURSEID課程號字符類型000001外碼上課時間表(coursetime)：包含所有課程的上課時間，定義如下：域名含義數(shù)據(jù)類型例子備注COURSEID課程號字符類型000001外碼COURSETIME上課時間數(shù)值類型21表示星期2第1節(jié)COURSETIME是一個整數(shù)，整除10的商表示星期幾上課，余數(shù)表示當天的第幾節(jié)課。用戶賬號表(user)：包含所有用戶的賬號和口令信息，定義如下：域名含義數(shù)據(jù)類型例子備注USERID用戶賬號字符類型ADMINUSERPASSWD用戶密碼字符類型ADMINUSERAUTH用戶權(quán)限數(shù)值類型

50、0STUDENTID用戶學號字符類型971329USERAUTH用0，1表示用戶權(quán)限，0表示管理員，1表示普通用戶。其中普通用戶的STUDENTID為學生的學號。3實現(xiàn)數(shù)據(jù)完整性：通過各種約束，缺省，規(guī)則和觸發(fā)器實現(xiàn)數(shù)據(jù)的完整性。由于教務系統(tǒng)的重要性，和各個數(shù)據(jù)之間的復雜相關(guān)性，保證數(shù)據(jù)的完整性顯得更為重要。不能讓用戶隨意的刪除，修改數(shù)據(jù)。（1） CHECK約束保證最大限選學分要大于最小限選學分。（2） 各種外碼約束保證數(shù)據(jù)的完整性，不能隨意刪除。外碼的設置在上面數(shù)據(jù)庫建表時已經(jīng)提到。（3） 主碼約束保證實體的完整性，主碼的設置在上面數(shù)據(jù)庫建表時也已經(jīng)提到了。（4） 創(chuàng)建規(guī)則保證學生的年齡在1

51、8歲到35歲之間（出生日期在1965-1-1到1982-12-31日之間），不會輸入錯誤數(shù)據(jù)。（5） 創(chuàng)建觸發(fā)器，保證在添加學生信息的時候，自動將該班級的必修課添加到選課表中。以上只是該教務系統(tǒng)中實現(xiàn)數(shù)據(jù)完整的部分方法。實際上要考慮的還有很多方面，實現(xiàn)手法也是多種多樣。在此留給學生自己思考。5. 數(shù)據(jù)庫的安裝安裝SQL SERVER2000，作為數(shù)據(jù)庫服務器，并用ODBC設置SQL SERVER的數(shù)據(jù)源;也可以直接采用JDBC直接進行連接。5.4.4 應用程序設計1. 選課系統(tǒng)DEMO的處理流程圖： 開始 用戶登錄 合法？ 用戶選擇服務 選擇退出？ 執(zhí)行服務操作退出NNYY Y 2. 總體結(jié)構(gòu)

52、和外部模塊設計：口令驗證模塊 主模塊班級信息管理模塊學生信息管理模塊課程信息管理模塊選課模塊口令更改模塊退出5.4.5 編程實現(xiàn)：下面給出系統(tǒng)的主要用戶界面。其他的操作界面用戶可以自行設計實現(xiàn)。1Login.jsp這是系統(tǒng)的登錄界面，用戶可以選擇以學生或管理員身份登錄，并要輸入相應的用戶名稱，密碼。2AdminIndex.jsp這是系統(tǒng)管理員登錄成功的界面，左側(cè)有管理員管理的各項職權(quán)。（1） 按下班級信息管理，進入Class.jsp，進行班級信息維護管理。（2） 按下學生信息管理，進入Student.jsp，進行學生信息維護管理。（3） 按下課程信息管理，進入Course.jsp，進行課程信息

53、維護管理。（4） 按下選課管理，進入CourseSelect.jsp，進行選課。（5） 按下口令修改，進入ChangePasswd.jsp，更改用戶口令。（6） 按下退出，退出本系統(tǒng)。上述界面是在用管理員登錄時才能看到，其中班級信息維護管理和相應的增加新班級功能為登陸后的默認選項。3Class.jsp在右上方的班級信息管理界面中，可以增加新的班級，修改原有班級信息，刪除原有班級。（1） 增加新班級：在右下角的相應文本框中輸入增加的班級名稱，最大、最小學分限制，點擊確定后，如果沒有重復的班級名稱，而且最大學分限制大于最小學分限制，則成功加入新的班級。（2） 修改原有班級：在右下角的相應文本框中輸

54、入修改的班級名稱，點擊確定后，下方文本框中會出現(xiàn)原有的最大最小學分限制。輸入新的學分限制后，如果該班級的所有學生已經(jīng)選課的總學分符合新的學分限制標準，則修改成功。（3） 刪除原有班級：在右下角相應文本框中輸入欲刪除的班級名稱，點擊確定后，如果該班級所有學生都已經(jīng)被刪除，并且該班級所有必修課都已經(jīng)被刪除，則可以將該班級刪除。4Student.jsp在右上方的學生信息管理界面中，可以增加，修改，刪除學生的個人信息。（1） 增加新學生，在右下角的文本框中輸入相應的學生學號，姓名，生日，班級，性別信息，點擊確定按鈕，如果存在相應的學生班級，并且學生學號不重復，則可以成功的添加新學生到該班級。（2） 修

55、改學生信息：在右下角的文本框中輸入相應的學生學號，點擊確定按鈕，則可以在相應文本框中看到該學生原有的個人信息?？梢赃M行修改。注意修改的班級必須為已經(jīng)存在的班級，學號不能與已經(jīng)存在的學號重復。（3） 刪除學生信息：在右下角的文本框中輸入相應的學生學號，點擊刪除按鈕，則可以刪除相應的學生個人信息，注意在選課表中，該學生的所有選課信息必須全部刪除。5Course.jsp在右上方的課程信息管理界面中，可以完成增加，刪除，修改選修課程的功能。（1） 增加新課程：在右下角的相應文本框中輸入課程號，課程名稱等信息，然后點擊確定按鈕，如果在數(shù)據(jù)庫中沒有重復的課程編號，則可以完成添加一門新課程到數(shù)據(jù)庫中。注意在輸入增加的