數(shù)據(jù)庫系統(tǒng)實驗指導(dǎo)書新版.doc

實驗一 創(chuàng)建數(shù)據(jù)庫和表一、實驗?zāi)康募耙螅?了解SQL SERVER數(shù)據(jù)庫的邏輯結(jié)構(gòu)和物理結(jié)構(gòu)2了解表的結(jié)構(gòu)特點3了解SQL SERVER的基本數(shù)據(jù)結(jié)構(gòu)二、實驗任務(wù)：1學(xué)會在企業(yè)管理器中創(chuàng)建數(shù)據(jù)庫和表2學(xué)會在T-SQL語句創(chuàng)建數(shù)據(jù)庫和表三、操作要點：1啟動企業(yè)管理器，并創(chuàng)建數(shù)據(jù)庫和表2啟動SQL查詢分析器3掌握創(chuàng)建數(shù)據(jù)庫和表分別是CREATE DATABASE和CREATE TABLE四、注意事項：1在Microsoft SQL Server 2000的查詢分析器(Query Analyzer)中使用單條SQL語句，其末尾不需要分號“；”作為命令結(jié)尾標(biāo)記。2通常，SQL Server 2000對大多數(shù)末尾帶有分號的SQL命令都能順利執(zhí)行，但對少數(shù)的SQL命令，末尾若帶分號，則SQL Server 2000會給出錯誤信息提示。五、實驗學(xué)時：2學(xué)時六、實驗重點及難點：1列級完整性約束條件2空值的概念3CREATE DATABASE和CREATE TABLE 的用法 七、實驗步驟：(一) 企業(yè)管理器創(chuàng)建數(shù)據(jù)庫和表 1啟動企業(yè)管理器； 2創(chuàng)建數(shù)據(jù)庫Company，并創(chuàng)建一個主數(shù)據(jù)文件Company_Data1，一個二級數(shù)據(jù)文件Company_Data2，一個日志文件Company_Log，以及一個自定義文件組Company_Group，該組中有數(shù)據(jù)文件Company_Data3，設(shè)置各文件屬性。 (二) T-SQL語句創(chuàng)建數(shù)據(jù)庫和表1啟動SQL查詢分析器；2創(chuàng)建數(shù)據(jù)庫University，并創(chuàng)建一個主數(shù)據(jù)文件University_Data和一個日志文件University_Log；表5.5 關(guān)系ReportsSnoCnoGradeS01S01S02S02S02S03S03S04C01C03C01C02C03C01C02C039284909482729075表5.4 關(guān)系CoursesCnoCnamePre_CnoCreditsC01C02C03C04C05C06C07英語數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)庫DB_設(shè)計C+網(wǎng)絡(luò)原理操作系統(tǒng)C05C02C03C07C054223333表5.3 關(guān)系StudentsSnoSnameSsexSageSdeptS01S02S03S04S05S06王建平劉華范林軍李偉黃河長江男女女男男男211918191820自動化自動化計算機(jī)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)3. 建立表5.3所示的學(xué)生表Students，各屬性為Sno-學(xué)號、Sname-姓名、Ssex-性別、Sage-年齡、Sdept-所在系。要求Sno和Sname不能為空值，且取值唯一，Sno為鍵碼。 4.建立表5.4所示的課程表Courses，各屬性為Cno-課程號, Cname-課程名, Pre_Cno-先修課程號, Credits-學(xué)分，要求Cno和Cname不能為空值，Cno取值唯一，且為鍵碼。5.建立表5.5所示的成績表Reports。各屬性名為Sno-學(xué)號，Cno-課程號和Grade-考試成績，要求Sno和Cno不能為空，且取值唯一，Sno+ Cno為鍵碼。實驗二 SQL基本表的插入、刪除與修改一、實驗?zāi)康募耙螅菏炀氄莆栈颈淼牟迦?、刪除與修改，為后繼學(xué)習(xí)作準(zhǔn)備。二、實驗任務(wù)：1了解并掌握SQL查詢分析器及企業(yè)管理器的使用;2掌握基本表的插入、刪除與修改。三、操作要點：1.啟動SQL查詢分析器2.掌握進(jìn)行插入、刪除與修改分別是INSERT、DROP和UPDATE 四、注意事項：1.注意每次對表的修改，命令執(zhí)行完后要查看表的變動刷新表。2.特別注意在執(zhí)行插入、刪除與修改等更新操作時，要保證數(shù)據(jù)的完整性。3.SQL Server 2000 增加了刪除屬性的命令。五、實驗學(xué)時：2學(xué)時六、實驗重點及難點1T-SQL語句對基本表的定義進(jìn)行刪除與修改，并插入基本表數(shù)據(jù)。2T-SQL語句對數(shù)據(jù)庫操作的靈活控制功能。七、實驗步驟：1啟動SQL查詢分析器；2選擇SQL SERVER后，按確認(rèn)；3選擇University數(shù)據(jù)庫；4驗證如下例題：表5.4 關(guān)系CoursesCnoCnamePre_CnoCreditsC01C02C03C04C05C06C07英語數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)庫DB_設(shè)計C+網(wǎng)絡(luò)原理操作系統(tǒng)C05C02C03C07C054223333表5.3 關(guān)系StudentsSnoSnameSsexSageSdeptS01S02S03S04S05S06王建平劉華范林軍李偉黃河長江男女女男男男211918191820自動化自動化計算機(jī)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué) 4.1 修改基本表向基本表Students中增加“入學(xué)時間”屬性列，其屬性名為Sentrancedate，數(shù)據(jù)類型為DATETIME型。將Sage(年齡)的數(shù)據(jù)類型改為SMALLINT型。刪除Sname(姓名)必須取唯一值的約束。說明： 為了保證后面例子能夠順利運(yùn)行，請大家一定將屬性列Sentrancedate從Students表中刪除。 為了調(diào)試SQL語句方便，這里沒有在表Reports中增加參照完整性約束，甚至沒有定義主鍵。4.2 刪除Students表。說明：此表刪除后，請立即將其建立起來，以便后面的例子使用。4.3 向表中添加元組將一個學(xué)生元組(S01，王建平，男，21，計算機(jī))添加到基本表Students中。請同學(xué)們用這個命令，按表5.3提供的數(shù)據(jù)，將其余5個學(xué)生的元組也添加到基本表Students中。向Courses表插入元組(C01,英語,4)。請同學(xué)們用這個命令，按表5.4提供的數(shù)據(jù)，將其余6門課程的信息插入Courses表中。將學(xué)習(xí)成績的元組(S01, C01)添加到基本表Reports中。實驗三 數(shù)據(jù)庫的查詢一、實驗?zāi)康募耙?.熟練掌握SELECT查詢語句的使用2.掌握無條件、有條件查詢及查詢結(jié)果排序與分組二、實驗任務(wù)1掌握查詢語句的一般格式。2掌握無條件、有條件查詢及查詢結(jié)果排序與分組三、操作要點1.進(jìn)行查詢時先進(jìn)入查詢分析器2.明白了解無條件、有條件查詢及查詢結(jié)果排序與分組四、注意事項1. SELECT語句的基本語法格式2. SELECT語句的執(zhí)行方法3. SELECT語句和ORDER BY子句的作用和用法4. SELECT語句和GROUP BY子句的作用和用法五、實驗學(xué)時：4學(xué)時六、實驗重點及難點1.SELECT語句的基本語法2.掌握數(shù)據(jù)匯總的方法七、實驗步驟1、查詢?nèi)w學(xué)生的詳細(xì)記錄。2、查詢?nèi)w學(xué)生的姓名(Sname)、學(xué)號(Sno)、所在系(Sdept)。3、查詢選修了課程的學(xué)生學(xué)號。4、查詢數(shù)學(xué)系全體學(xué)生的學(xué)號(Sno)和姓名 (Sname)。5、查詢所有年齡在1922歲(包括19歲和22歲)之間的學(xué)生姓名(Sname)及年齡(Sage)。6、查詢年齡不在19-22歲之間的學(xué)生姓名(Sname)及年齡(Sage)。7、查詢自動化系和計算機(jī)系學(xué)生的學(xué)號(Sno)、姓名(Sname)和性別(Ssex)。8、查詢所有姓劉的學(xué)生的姓名(Sname)、學(xué)號(Sno)和性別(Ssex)。9、查詢課程名為“DB_設(shè)計”的課程號(Cno)和學(xué)分(Credits)。10、查詢以DB_開頭，且倒數(shù)第2個漢字字符為“設(shè)”的課程的詳細(xì)情況。11、假設(shè)某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。試查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號(Sno)和相應(yīng)的課程號(Cno)。12、查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系名(Sdpet)升序排列，同一系中的學(xué)生按年齡(Sage)降序排列。13、求學(xué)生的總?cè)藬?shù)14、求選修了課程的學(xué)生人數(shù)15、求課程和選修了該課程的學(xué)生人數(shù)16、求選修超過2門課的學(xué)生學(xué)號17、查詢計算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生18、查詢計算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集19、查詢計算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的差集20、查詢選修課程C01的學(xué)生集合與選修課程C02的學(xué)生集合的交集21、查詢選修課程C01的學(xué)生集合與選修課程C02的學(xué)生集合的差集實驗四 連接、嵌套和集合查詢一、實驗?zāi)康募耙螅?1熟練掌握連接、嵌套和集合查詢的使用。2學(xué)習(xí)各種連接、嵌套和集合查詢方法。二、實驗任務(wù)1掌握連接、嵌套和集合查詢語句的一般格式。2掌握連接、嵌套和集合查詢的各種使用方法。三、操作要點1創(chuàng)建表時候,在查詢分析器中輸入程序,編譯時查看附錄的出錯信息再進(jìn)行修改。2函數(shù)除了系統(tǒng)函數(shù)以外也可以自定義函數(shù)。3為了避免程序丟失,把程序保存在到規(guī)定的目錄中去。四、注意事項1注意T-SQL各種運(yùn)算符、控制語句的功能及使用方法。2注意各種系統(tǒng)函數(shù)的調(diào)用方法。五、實驗學(xué)時：4學(xué)時六、實驗重點及難點1明白兩個表之間的聯(lián)系是通過公共屬性實現(xiàn)的。2連接、嵌套和集合查詢的各種使用方法。3多重嵌套的使用。七、實驗步驟1、求學(xué)生號以及其選修課程的課程號和成績，但查詢結(jié)果中只能有一個SNO字段。 2、求選修了課程”c01”且成績在70分以下或成績在90分以上的學(xué)生的姓名、課程名稱和成績。 3、求選修了課程的學(xué)生的學(xué)生姓名、課程號和成績。 4、求年齡大于 李偉 的所有學(xué)生的姓名、系和年齡。 5、求選修了課程C02或C03的學(xué)生的學(xué)號、課程號、課程名和成績。 6、求與 李偉 年齡相同的學(xué)生的姓名和系。 7、求選修了課程名為 數(shù)據(jù)結(jié)構(gòu) 的學(xué)生的學(xué)號和姓名。 8、求比數(shù)學(xué)系中某一學(xué)生年齡大的學(xué)生的姓名和系。 9、求比數(shù)學(xué)系中全體學(xué)生年齡大的學(xué)生的姓名和系。 10、求選修了課程C04的學(xué)生的姓名和系。 11、求與 黃河 同系且同齡的學(xué)生的姓名和系。 12、求與 黃河 同系，且年齡大于 黃河 的學(xué)生的信息。 13、求數(shù)學(xué)系中年齡相同的學(xué)生的姓名和年齡。14、檢索至少選修所有授課程中一門課程的男學(xué)生姓名。 15、檢索選修某課程的學(xué)生人數(shù)多于2人的課程名稱。 16、查詢被一個以上的學(xué)生選修的課程號。 17、求未選修課程C04的學(xué)生的姓名。 18、求選修了全部課程的學(xué)生的姓名。 19、求成績比所選修課程平均成績高的學(xué)生的學(xué)號、課程號、和成績。20、查詢所有未選課程的學(xué)生姓名和所在系。實驗五 數(shù)據(jù)庫系統(tǒng)設(shè)計一、實驗?zāi)康募耙?1、通過實驗使學(xué)生加深對數(shù)據(jù)完整性的理解，學(xué)會創(chuàng)建和使用觸發(fā)器。2、通過實驗使學(xué)生加深對數(shù)據(jù)安全性的理解，并掌握SQL Server中有關(guān)用戶，角色及操作權(quán)限的管理方法。3、通過實驗使學(xué)生了解SQL Server的數(shù)據(jù)備份和恢復(fù)機(jī)制，掌握SQL Server中數(shù)據(jù)庫備份和恢復(fù)的方法。4、根據(jù)數(shù)據(jù)庫系統(tǒng)設(shè)計的基本步驟，完成某一具體數(shù)據(jù)庫系統(tǒng)的需求分析、概念結(jié)構(gòu)設(shè)計、邏輯結(jié)構(gòu)設(shè)計、物理結(jié)構(gòu)設(shè)計、數(shù)據(jù)庫實施和數(shù)據(jù)庫運(yùn)行維護(hù)等功能。二、實驗任務(wù)1、使用SQL設(shè)計觸發(fā)器，通過SQL Server企業(yè)管理器定義它們。2、在SQL Server企業(yè)管理器中，設(shè)置SQL Server的安全認(rèn)證模式。通過SQL Server企業(yè)管理器，實現(xiàn)對SQL Server的用戶和角色管理。分別通過SQL Server企業(yè)管理器和SQL的數(shù)據(jù)控制功能，設(shè)置和管理數(shù)據(jù)操作權(quán)限。3、用企業(yè)管理器創(chuàng)建一個備份設(shè)備。利用向?qū)閷W(xué)生選課數(shù)據(jù)庫生成一個完全備份。在企業(yè)管理器中恢復(fù)學(xué)生選課數(shù)據(jù)庫4、完成某一具體系統(tǒng)的需求分析、概念結(jié)構(gòu)設(shè)計、邏輯結(jié)構(gòu)設(shè)計、物理結(jié)構(gòu)設(shè)計、數(shù)據(jù)庫實施和數(shù)據(jù)庫運(yùn)行維護(hù)等功能，并提交數(shù)據(jù)庫。三、實驗學(xué)時：8學(xué)時四、實驗內(nèi)容及步驟（一）創(chuàng)建和使用觸發(fā)器（1）實驗的內(nèi)容使用SQL設(shè)計觸發(fā)器，通過SQL Server企業(yè)管理器定義它們。具體完成下面例題：利用觸發(fā)器來保證學(xué)生選課庫中選課表的參照完整性，以維護(hù)其外碼與參照表中的主碼一致。CREATE TRIGGER SC_inserted ON 選課FOR INSERTAS IF（SELECT COUNT（*） FROM 學(xué)生，inserted，課程WHERE 學(xué)生.學(xué)號 = inserted.學(xué)號 AND inserted.課程號 = 課程.課程號）=0ROLLBACK TRANSACTION（2）實驗步驟1、 在企業(yè)管理器中創(chuàng)建觸發(fā)器1） 在企業(yè)管理器中，由服務(wù)器開始逐步擴(kuò)展到觸發(fā)器所屬表的數(shù)據(jù)庫（本例為學(xué)生選課數(shù)據(jù)庫），打開表文件夾，在表窗口中用鼠標(biāo)右擊觸發(fā)器所屬的表（本例為選課表）。2） 在彈出菜單上選擇“所有任務(wù)”“管理觸發(fā)器”項，則彈出如圖所示的觸發(fā)器屬性對話框。 圖 觸發(fā)器屬性對話框如果要新建觸發(fā)器，在文本T的文本框中輸入創(chuàng)建觸發(fā)器的SQL語句（見實驗內(nèi)容），單擊“檢查語法”按鈕進(jìn)行語法的檢查，檢查無誤后，單擊確定按鈕。3） 如果要修改觸發(fā)器，則在名字彈出項中選擇要修改的觸發(fā)器名（本例為SC_inserted）,然后在文本T的文本框中對已有的內(nèi)容進(jìn)行修改，檢查無誤后單擊“確定”按鈕。4） 如果要刪除觸發(fā)器，則在名字彈出項中選擇要刪除的觸發(fā)器名（本例為SC_inserted），并檢查文本T的文本框中的內(nèi)容，看是否選得正確，最后單擊“刪除”按鈕。2、 驗證觸發(fā)器1） 在企業(yè)管理器中，由服務(wù)器開始逐步擴(kuò)展到觸發(fā)器所屬表的數(shù)據(jù)庫（本例為學(xué)生選課數(shù)據(jù)庫）。此時，可在選課表中輸入一條記錄（學(xué)號：96001，課程號：C8，成績：88）。顯然，這條記錄是不符合參照完整性的。通過企業(yè)管理器輸入時，會出現(xiàn)錯誤提示，違反了數(shù)據(jù)庫的約束（如果約束C5和C6同時存在的話）。2） 如果約束C5和C6存在，則刪除之3） 打開SQL查詢分析器，輸入語句“INSERT INTO 選課 VALUES（96001，C8，88）”，查看執(zhí)行結(jié)果。4） 刪除觸發(fā)器（本例為SC_inserted），重復(fù)步驟3。（二）數(shù)據(jù)安全性1 在SQL Server企業(yè)管理器中為所屬的SQL 服務(wù)器設(shè)置為SQL Server和Windows NT混合安全認(rèn)證模式。其步驟如下：1） 在企業(yè)管理器窗口中展開服務(wù)器組，用鼠標(biāo)右擊需要設(shè)置的SQL服務(wù)器，在彈出的菜單中選擇“屬性”項，則出現(xiàn)SQL Server屬性對話框，如圖1所示。 圖1 SQL Server屬性對話框2） 在SQL Server屬性對話框中選擇“安全性”選項卡。3） 身份驗證選擇“SQL Server和Windows”單選項。2 在SQL Server企業(yè)管理器中為自己建立一個服務(wù)器用戶、數(shù)據(jù)庫用戶和數(shù)據(jù)庫角色。1） 在企業(yè)管理器窗口中展開服務(wù)器組，展開服務(wù)器，用鼠標(biāo)單擊“安全性”文件夾右側(cè)的+，用鼠標(biāo)右擊“登錄”，在彈出的菜單中選擇“新建登錄”項，則出現(xiàn)新建登錄對話框，如圖2所示。圖2 新建登錄對話框2） 在新建登錄對話框中有常規(guī)，服務(wù)器角色和數(shù)據(jù)庫訪問三個選項卡。在常規(guī)選項卡中，輸入用戶名（本例為王平），選擇SQL Server安全驗證，輸入用戶口令。在服務(wù)器角色選項卡中，需要確定用戶所屬的服務(wù)器角色，在本例中采用缺省值即可。在數(shù)據(jù)庫訪問選項卡中，需要指定此登錄可以訪問的數(shù)據(jù)庫（本例選中學(xué)生選課數(shù)據(jù)庫）和“學(xué)生選課”的數(shù)據(jù)庫角色（在本例中采用缺省值即可）。單擊“確定”按鈕，既完成了創(chuàng)建登錄用戶的工作。3 關(guān)閉SQL Server企業(yè)管理器，打開SQL查詢分析器。選擇SQL Server安全驗證，用戶名為王平，輸入用戶口令，連接到SQL Server。在“查詢”窗口中可以輸入SQL語句。如“SELECT * FROM 學(xué)生”。運(yùn)行后，得到消息“拒絕了對對象學(xué)生（數(shù)據(jù)庫學(xué)生選課，所有者dbo）的SELECT權(quán)限?！?。可見用戶王平?jīng)]有對學(xué)生表的SELECT權(quán)限。4 要想將學(xué)生選課數(shù)據(jù)庫的操作權(quán)限賦予數(shù)據(jù)庫用戶王平，有兩種方法。方法一：通過企業(yè)管理器1） 在企業(yè)管理器窗口中展開服務(wù)器組，展開服務(wù)器，用鼠標(biāo)單擊“數(shù)據(jù)庫”文件夾右側(cè)的+，用鼠標(biāo)單擊學(xué)生選課數(shù)據(jù)庫文件夾右側(cè)的+，用鼠標(biāo)右擊“用戶”。在屏幕右側(cè)的“用戶”窗口中選擇“王平”項，用鼠標(biāo)右擊，在彈出的菜單中選擇“屬性”項，則出現(xiàn)數(shù)據(jù)庫用戶屬性對話框，如圖3所示。 圖3 數(shù)據(jù)庫用戶屬性對話框2） 單擊圖3中的權(quán)限按鈕，則出現(xiàn)數(shù)據(jù)庫用戶權(quán)限對話框，如圖4所示。對話框的下面是有關(guān)數(shù)據(jù)庫用戶和角色所對應(yīng)的權(quán)限表。這些權(quán)限均以復(fù)選框的形式表示。復(fù)選框有三種狀態(tài)：“”為授權(quán)；“”為廢除權(quán)；空為撤權(quán)。在表中可以對用戶或角色的各種對象操作權(quán)（SELECT，INSERT，UPDATE，DELETE，EXEC和DRI）進(jìn)行授予或撤消。 圖4 數(shù)據(jù)庫用戶權(quán)限對話框3） 在圖4中找到學(xué)生表，授予SELECT權(quán)限，即讓學(xué)生表與SELECT列交叉的復(fù)選框為“”即可。方法二：通過SQL的數(shù)據(jù)控制功能對用戶王平授權(quán)，必須是數(shù)據(jù)庫對象擁有者以上用戶授予。我們可以以系統(tǒng)管理員或sa用戶登錄進(jìn)入查詢分析器。在查詢分析器中輸入授權(quán)語句“GRANT SELECT ON 學(xué)生 TO 王平；”，然后執(zhí)行即可。（三）數(shù)據(jù)庫備份和恢復(fù)實驗步驟1、在企業(yè)管理器中創(chuàng)建一個備份設(shè)備1）在企業(yè)管理器中擴(kuò)展服務(wù)器，在管理文件夾中找到備份文件夾。用鼠標(biāo)右鍵單擊該文件夾，在彈出的菜單中選擇“新建備份設(shè)備”項，則出現(xiàn)“備份設(shè)備屬性”對話框，如圖所示。在名稱文本框中輸入設(shè)備名（本例為學(xué)生選課設(shè)備1）。單擊“確定”按鈕，備份設(shè)備就創(chuàng)建成功了。 圖 “備份設(shè)備屬性”對話框2） 在備份文件夾的窗口中出現(xiàn)了新創(chuàng)建的設(shè)備。選中該設(shè)備，用鼠標(biāo)右鍵單擊，在彈出的菜單中選擇“屬性”項，則出現(xiàn)“備份設(shè)備屬性”對話框。2、 利用向?qū)閷W(xué)生選課數(shù)據(jù)庫生成一個完全備份。1） 在企業(yè)管理器中擴(kuò)展服務(wù)器，在數(shù)據(jù)庫文件夾中選中要備份的數(shù)據(jù)庫文件夾（學(xué)生選課數(shù)據(jù)庫）。選擇菜單工具|向?qū)?，則出現(xiàn)向?qū)нx擇對話框。在向?qū)нx擇對話框中，單擊管理左邊的“+”號，使之展開。選擇“備份向?qū)А表?，單擊“確定”按鍵。