數(shù)據(jù)庫實驗第二部分1,2

1、徐州工程學(xué)院管理學(xué)院實驗報告實驗課程名稱 ： 數(shù)據(jù)庫原理及應(yīng)用 實 驗 地 點： 經(jīng)濟管理實驗教學(xué)中心 20132014學(xué)年（一）學(xué)期 2013 年 9月至 2013 年 12 月專 業(yè) 信息管理與信息系統(tǒng)班 級 11信管1班 學(xué)生姓名 學(xué) 號 指導(dǎo)老師 蔣 秀 蓮 數(shù)據(jù)庫課程實驗第二部分實驗1 建立數(shù)據(jù)庫實驗名稱：建立數(shù)據(jù)庫。實驗內(nèi)容：在SQL Server環(huán)境下建立數(shù)據(jù)庫和維護數(shù)據(jù)庫。實驗?zāi)康模豪斫釹QL Server數(shù)據(jù)庫的存儲結(jié)構(gòu)，掌握SQL Server數(shù)據(jù)庫的建立方法和維護方法。實驗方法：在SQL Server環(huán)境下用CREATE DATABASE命令建立數(shù)據(jù)庫（注意：需要是系統(tǒng)管

2、理員用戶，或者被指定dbcreator固定服務(wù)器角色的成員，或者被顯式地授予CREATE DATABASE權(quán)限）實驗要求：1. 根據(jù)具體要求創(chuàng)建如下3個數(shù)據(jù)庫：（1） 創(chuàng)建數(shù)據(jù)庫test1（使用一個數(shù)據(jù)文件和一個日志文件），要求指定一個數(shù)據(jù)文件，一個事務(wù)日志文件，并把它們安排在不同的硬盤上。（2） 創(chuàng)建數(shù)據(jù)庫test2（使用多個數(shù)據(jù)文件和多個日志文件），要求使用3個數(shù)據(jù)文件和兩個事務(wù)日志文件，主數(shù)據(jù)文件是列表中的第一個文件或者用關(guān)鍵字PRIMARY指定（文件擴展名：主數(shù)據(jù)文件使用.mdf，次數(shù)據(jù)文件使用.ndf，事務(wù)日志文件使用.ldf）。（3） 創(chuàng)建數(shù)據(jù)庫test3（使用文件組），要求使用3

3、個文件組（含主文件組，即默認的文件組），每個文件組包含兩個數(shù)據(jù)文件，并且每個文件組要安排在不同的硬盤上。2. 修改建立的test1數(shù)據(jù)庫，為之添加一個5MB大小的新數(shù)據(jù)文件。3. 修改建立的test2數(shù)據(jù)庫，將其中的主數(shù)據(jù)文件增加5MB的存儲空間。4. 刪除建立的數(shù)據(jù)庫test1。答題如下：第1題第1小題第1題第2小題第1題第3小題第2題第3題第4題實驗2 建立表和定義完整性約束實驗名稱：建立模式，表和定義完整性約束。實驗內(nèi)容：在實驗1建立數(shù)據(jù)庫的基礎(chǔ)上，參照下圖和下表建立數(shù)據(jù)表并定義完整性約束。 實驗?zāi)康模菏炀氄莆毡淼慕⒑蛿?shù)據(jù)完整性約束的定義方法，實踐DBMS提供的數(shù)據(jù)完整性功能，加深對數(shù)

4、據(jù)完整性的理解。實驗方法：在實驗1建立數(shù)據(jù)庫的基礎(chǔ)上用CREATE SCHEMA命令建立模式，用CREATE TABLE命令建立表并定義數(shù)據(jù)完整性約束（注意：需要有CREATE SCHEMA和CREATE TABLE的權(quán)限，或者其他具有相應(yīng)權(quán)限的身份），用ALTER TABLE命令修改表結(jié)構(gòu)。實驗要求：1. 用CREATE SCHEMA命令建立模式（自己命名模式名稱）。2. 在定義的模式下參照圖3-4和表3-10使用CREATE TABLE命令建立表并定義完整性約束。倉庫訂購單訂購單明細供應(yīng)商職工3. 使用ALTER TABLE命令按如下要求修改表結(jié)構(gòu)：（1） 為訂購單表增加一個新的字段“完成

5、日期”，類型為日期時間型，默認是空值。（2） 為訂單明細表的“數(shù)量”字段重新定義約束：大于0，小于等于1000的正整數(shù)，不允許為空值。（3） 將供應(yīng)商表的供應(yīng)商名字段的類型修改為varchar（30）。 實驗3 數(shù)據(jù)操作和完整性約束的作用實驗名稱：數(shù)據(jù)操作和完整性約束的作用。實驗內(nèi)容：在實驗2的基礎(chǔ)上完成數(shù)據(jù)的插入操作，然后進行部分修改和刪除操作，在這些操作中體會數(shù)據(jù)完整性約束的作用。實驗?zāi)康模菏炀氄莆誗QL的INSERT，UPDATE和DELETE命令，深刻理解數(shù)據(jù)完整性約束的作用以及約束時機。實驗方法：在實驗2的基礎(chǔ)上首先用INSERT命令插入各個表的記錄，然后使用UPDATE和DELET

6、E命令對部分記錄進行修改和刪除操作。實驗要求：1. 自行設(shè)計各個表的記錄，其中倉庫表至少5條記錄，職工表至少10條記錄，供應(yīng)商表至少5條記錄、訂購單表至少20條記錄、訂購單名細表至少50條記錄。2. 使用INSERT命令完成記錄的插入操作，要求分別設(shè)計若干違反實體完整性、參照完整性和用戶自定義完整性約束的插入操作，并分析原因。原因：INSERT 語句與CHECK 約束CK_倉庫沖突。該沖突發(fā)生于數(shù)據(jù)庫test，表dbo.倉庫, column 倉庫號。3. 設(shè)計若干刪除操作，體會執(zhí)行刪除操作時檢查參照完整性規(guī)則的效果（要求涉及拒絕刪除、空值刪除和級聯(lián)刪除等不同的處理方式）。違反外鍵約束,訂購單表

7、中找到子記錄空值刪除4. 設(shè)計若干更新操作，體會執(zhí)行更新操作時檢查實體完整性規(guī)則、參照完整性規(guī)則和用戶自定義完整性規(guī)則的效果。違反檢查約束違反外鍵約束5. 將x城市倉庫的職工的工資提高10%。6. 用UPDATE語句完成訂購單表金額字段的計算（提示：某張訂購單的金額為該訂購單所有訂購項目（訂購單明細）單價乘以數(shù)量的總和）。7. 設(shè)計23個刪除操作，它需要另一個表中的數(shù)據(jù)作為刪除條件（例如，按指定的供應(yīng)商名稱刪除訂購單記錄）。根據(jù)指定的供應(yīng)商名稱刪除訂購單記錄根據(jù)指定的員工號刪除訂購單記錄根據(jù)指定的訂購單號刪除訂購單明細記錄8. 在實驗報告中給出具體的記錄和設(shè)計的操作，并對各種數(shù)據(jù)完整性檢查，給

8、出具體的分析和討論。表一 倉庫表二 訂購單表三 訂購單明細表四 供應(yīng)商表五 職工實驗4 數(shù)據(jù)查詢實驗名稱：使用SQL SECLECT語句的數(shù)據(jù)查詢操作。實驗內(nèi)容：在實驗2和3的基礎(chǔ)上完成規(guī)定的查詢操作。實驗?zāi)康模菏炀氄莆誗QL SECLECT命令。實驗方法：在實驗2和3的基礎(chǔ)上使用SQL SECLECT命令完成實驗要求中所規(guī)定的各種查詢。實驗要求： 用SQL SECLECT命令完成以下查詢（在完成查詢時將題目中的X，Y等根據(jù)實驗3產(chǎn)生的數(shù)據(jù)變化）： （1）至（12）是一般簡單查詢 （1）從職工表中查詢所有工資值（考慮去掉和不去掉重復(fù)值兩種情況）。 -去除重復(fù) -查詢所有 （2）查詢倉庫表中的全

9、部信息。 （3）查詢工資多于x元的職工號。 （4）查詢哪些倉庫有工資多于y元的職工。 （5）給出在倉庫m或n(m,n是倉庫號)工作，并且工資少于z元的職工號。（6）查詢工資在xy元范圍內(nèi)的職工信息（xy）。（7）從供應(yīng)商表中查詢出全部公司的信息（不要工廠或其他供應(yīng)商的信息，即供應(yīng)商名最后兩個漢字是“公司”的記錄）。（8）查詢城市不是a的全部倉庫信息。（9）查詢尚未確定供應(yīng)商的訂購單（即訂購單的供應(yīng)商號為空值的記錄）。（10）查詢已經(jīng)確定了供應(yīng)商的訂購單信息。（11）按職工的工資升序列出全部職工信息。（12）先按倉庫號升序，再按工資降序列出全部職工信息。（13）（21）使用連接查詢（13）查詢工

10、資多于x元的職工號和其所在的城市。（14） 查詢工作在面積大于s的倉庫的職工號以及這些職工工作所在的城市。 （15） 根據(jù)職工表列出上一級經(jīng)理及其職工（被其領(lǐng)導(dǎo)）的清單（即列出誰領(lǐng)導(dǎo)誰的清單）。 （16） 列出倉庫表和職工表的廣義笛卡爾積的運算結(jié)果。 （17） 根據(jù)倉庫表和職工表的廣義笛卡爾積得到倉庫表和職工表進行自然連接的結(jié)果。 （18）（21）分別用內(nèi)連接、左連接、右連接和全連接完成，分析它們的效果（18）查詢供應(yīng)商-訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段。（19） 查詢供應(yīng)商-訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，

11、要求結(jié)果中列出所有供應(yīng)商信息（包括目前沒有訂購單的）。 （20） 查詢供應(yīng)商-訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，要求結(jié)果中能夠反映目前沒有確定供應(yīng)商的訂購單情況。 （21）查詢供應(yīng)商-訂購單信息，查詢結(jié)果中包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期這4個字段，要求結(jié)果中既能反應(yīng)目前沒有訂購單的供應(yīng)商信息，又能反映目前沒有確定供應(yīng)商的訂購單情況。（22）（31）使用嵌套查詢（注意使用ANY，All，EXISTS）（22）查詢哪些城市至少有一個倉庫的職工的工資為x元。（23） 查詢所有職工的工資都多于x元的倉庫的信息。 （24） 查詢和職工a同樣工資的所有

12、職工信息。 （25） 查詢哪些城市的倉庫向地址為d的供應(yīng)商發(fā)出了訂購單。 （26） 查詢地址為x的供應(yīng)商目前有y城市倉庫的訂購單的供應(yīng)商的名稱（分別用嵌套查詢和連接查詢完成，分析各自的效率）。（27） 查詢有職工的工資大于或等于x倉庫中任何一名職工的工資的倉庫號。 （28） 查詢有職工的工資大于或等于x倉庫中所有職工的工資的倉庫號。 （29） 列出每個職工經(jīng)手的具有最高金額的訂購單信息。 （30） 查詢目前還沒有職工的倉庫的信息。 （31） 查詢目前至少已經(jīng)有一個職工的倉庫的信息。 （32）（42）匯總和分組查詢（32）查詢目前倉庫所在城市的數(shù)目。（33） 查詢目前需要支付的職工工資的總數(shù)。

13、（34） 查詢x和y兩個城市的倉庫職工的工資總和。 （35） 查詢所有職工的工資都大于x元的倉庫的平均面積。 （36） 查詢工資大于x元的職工所經(jīng)手的訂購單的最高金額。 （37） 查詢每個倉庫的職工的平均工資。 （38） 查詢每個倉庫的訂購單的最高金額、最低金額和平均金額。 （39） 查詢至少有5個訂購單項目的每張訂購單的平均金額。 （40）列出職工全部記錄并計算各倉庫的平均工資和工資小計，最后給出全體職工的平均工資和工資總和（使用COMPUTEBY和COMPUTE）。（41）列出全部訂購單明細并計算各訂購單的金額，最后給出所有訂購單的平均金額和金額總計（使用COMPUTEBY和COMPUTE

14、）。（42）列出全部訂購單記錄并計算所有訂購單的平均金額和金額總計（使用COMPUTE）。實驗5 視圖的應(yīng)用實驗名稱：視圖的建立和應(yīng)用。實驗內(nèi)容：在實驗2和3的基礎(chǔ)上定義視圖，并在視圖上完成查詢、插入、更新和刪除操作。實驗?zāi)康模赫莆找晥D定義和應(yīng)用的方法，體會視圖和基本表的異同之處。實驗方法：在實驗2和3的基礎(chǔ)上用CREATE VIEW命令定義視圖，然后使用SELECT命令完成查詢，使用INSERT，UPDATE和DELETE命令分別完成插入，更新和刪除操作。實驗要求：1. 在實驗2建立的基本表的基礎(chǔ)上，按如下要求進行設(shè)計和建立視圖：（1）基于單個表按投影操作定義視圖。（2） 基于單個表按選擇操

15、作定義視圖。 （3）基于單個表按選擇和投影操作定義視圖。 （4）基于多個表根據(jù)連接操作定義視圖。 （5）基于多個表根據(jù)嵌套查詢定義視圖。 （6）定義含有虛字段的視圖。 2. 分別在定義的視圖上設(shè)計一些查詢（包括基于視圖和基本表的連接或嵌套查詢）。3. 在不同的視圖上分別設(shè)計一些插入、更新和刪除操作，分情況討論哪些操作可以成功完成，哪些操作不能完成，并分析原因。4. 在實驗報告中要給出具體的視圖定義要求和操作要求，并針對各種情況做出具體的分析和討論。實驗6 游標(biāo)實驗名稱：游標(biāo)的設(shè)計和使用。實驗內(nèi)容：在SQL Server環(huán)境下使用T-SQL的游標(biāo)功能完成指定的操作。實驗?zāi)康模豪斫夂驼莆沼螛?biāo)的使用

16、方法。實驗方法：在SQL Server環(huán)境下使用游標(biāo)完成指定的操作。實驗要求：1. 定義一個游標(biāo)，其內(nèi)容是按單價升序列出的全部訂購單以及其明細信息（基于實驗2建立的表和實驗3插入的數(shù)據(jù)）。2. 逐一讀出游標(biāo)中的記錄，并顯示。實驗7 存儲過程實驗名稱：存儲過程的設(shè)計和使用。實驗內(nèi)容：在SQL Server環(huán)境下設(shè)計、創(chuàng)建并執(zhí)行存儲過程。實驗?zāi)康模豪斫夂驼莆諗?shù)據(jù)庫存儲過程的創(chuàng)建和調(diào)用方法。實驗方法：在SQL Server環(huán)境下，按要求創(chuàng)建和調(diào)用存儲過程完成指定的操作。實驗要求：1. 建立如下存儲過程（基于實驗2建立的表和實驗3插入的數(shù)據(jù)）。（1）查詢工資范圍在x元到y(tǒng)元范圍內(nèi)的職工信息。說明：l

17、該存儲過程有兩個參數(shù)；l 要求查詢的職工信息包括倉庫號、所在城市、職工號和工資。（2）查詢在某年某月某日之后簽訂的訂購單的有關(guān)供應(yīng)商信息。說明：l 以訂購單中的訂購日期作為參數(shù)；l 分別按訂購單號顯示有關(guān)供應(yīng)商信息。（3）更新操作，將某個倉庫的訂購單的訂購日期統(tǒng)一修改為一個指定日期（參數(shù)為倉庫號和新的訂購日期）。2. 在客戶端以存儲過程和輸入SQL語句的方式分別執(zhí)行相同的查詢或操作，比較使用和不使用存儲過程的區(qū)別。實驗8 觸發(fā)器實驗名稱：DML觸發(fā)器的設(shè)計和應(yīng)用。實驗內(nèi)容：在SQL Server環(huán)境下設(shè)計，創(chuàng)建DML觸發(fā)器，并設(shè)定相關(guān)操作使觸發(fā)器運行。實驗?zāi)康模豪斫夂驼莆諗?shù)據(jù)庫中觸發(fā)器的創(chuàng)建方

18、法，體會觸發(fā)器執(zhí)行的條件和作用。實驗方法：在SQL Server環(huán)境下，按要求設(shè)計DML觸發(fā)器，并使用相關(guān)操作使觸發(fā)器運行。實驗要求：1. 為職工表建立一個更新觸發(fā)器，當(dāng)修改工資值超過6%時給出警示信息。2. 為訂購單表建立一個插入和更新觸發(fā)器，約束規(guī)則是：當(dāng)訂購單金額小于3000時必須使用本地供應(yīng)商（發(fā)出訂購單的倉庫和供應(yīng)商在同一個城市），如果不滿足要求，則拒絕操作，并給出錯誤信息。3. 設(shè)計并執(zhí)行相關(guān)的插入操作和更新操作，體會DML觸發(fā)器的效果和作用。實驗9 用戶管理和權(quán)限管理實驗名稱：用戶管理和權(quán)限管理。 實驗內(nèi)容：在SQL Server環(huán)境下完成數(shù)據(jù)庫的用戶管理、角色管理和操作權(quán)限管理

19、。實驗?zāi)康模豪斫夂腕w會數(shù)據(jù)庫安全性的內(nèi)容，加強對數(shù)據(jù)庫管理系統(tǒng)的安全管理功能的認識。實驗方法：在SQL Server環(huán)境下分別以系統(tǒng)管理員、數(shù)據(jù)庫管理員、對象擁有者和普通用戶的身份完成所要求的各項操作。實驗要求：1. 以系統(tǒng)管理員身份完成如下實驗：（1）建立3個不同名稱的注冊用戶。（2）使用ALTER LOGIN命令對建立的注冊用戶作不同的修改。（3）建立一個數(shù)據(jù)庫管理員用戶。2. 以數(shù)據(jù)庫管理員身份完成如下實驗：（1）根據(jù)已有的注冊用戶建立幾個當(dāng)前數(shù)據(jù)庫的用戶（部分用戶可以指定默認模式等）。（2）使用ALTER USER命令修改部分用戶設(shè)置。（3）建立若干角色，部分角色指定其他用戶管理。（4

20、）授權(quán)一些用戶可以創(chuàng)建表等數(shù)據(jù)庫對象。（5）完成角色管理及其他授權(quán)管理。3. 以下實驗由若干學(xué)生組成一組共同完成：（1）每個用戶有建立對象的權(quán)限，各自建立自己的對象（如表和視圖等）。（2）各用戶名之間就表或視圖的查詢、修改、刪除、插入等互相授權(quán)，在授權(quán)過程中體會GRANT命令中WITH GRANT OPTION短語的作用。（3）分情況收回權(quán)限，并體會REVOKE命令中GRANT OPTION FOR和CASCADE短語的作用。4. 在實驗報告中要給出具體的操作要求和過程，并針對各種情況做出具體的分析和討論。實驗10 事務(wù)管理和災(zāi)難恢復(fù)實驗名稱：事務(wù)管理及備份與恢復(fù)實驗內(nèi)容：在SQL Serve

21、r環(huán)境下完成事務(wù)管理的基本實驗，完成數(shù)據(jù)庫的備份和恢復(fù)操作。實驗?zāi)康模豪斫夂腕w會數(shù)據(jù)庫事務(wù)管理的內(nèi)容，掌握數(shù)據(jù)庫備份和恢復(fù)的基本方法，加強對數(shù)據(jù)庫管理系統(tǒng)的事務(wù)管理功能的認識。實驗方法：事務(wù) 在SQL Server環(huán)境下實驗事務(wù)管理的基本內(nèi)容；掌握數(shù)據(jù)庫備份和恢復(fù)的基本方法。實驗要求：1. 軟故障后的恢復(fù)。（1）設(shè)計事務(wù)及事務(wù)處理的程序。（2）模擬軟故障的情形，使設(shè)計的事務(wù)及事務(wù)處理程序的各個分支都可以執(zhí)行到。（3）體會事務(wù)的原子性和一致性。2. 硬故障后的恢復(fù)。（1）針對一個具體的數(shù)據(jù)庫設(shè)計一個備份恢復(fù)策略。（2）作數(shù)據(jù)庫全備份和若干增量備份（每次備份后都有一些數(shù)據(jù)操作）。（3）模擬一個硬故

22、障（假設(shè)數(shù)據(jù)庫損壞）。（4）備份當(dāng)前日志。（5）恢復(fù)數(shù)據(jù)庫。3. 在實驗報告中要給出具體的操作要求和過程，并針對各種情況做出具體的分析和討論。實驗11 并發(fā)事務(wù)控制實驗名稱:并發(fā)事務(wù)管理及控制實驗內(nèi)容: 在SQL Server環(huán)境下完成封鎖，死鎖和設(shè)置隔離級別等基本實驗。實驗?zāi)康? 理解和體會數(shù)據(jù)庫事務(wù)管理的內(nèi)容，掌握設(shè)計并發(fā)事務(wù)的基本方法，加強對數(shù)據(jù)庫管理系統(tǒng)的事務(wù)管理功能的認識。實驗方法: 若干人一組在SQL Server環(huán)境下同時操作共享數(shù)據(jù)，設(shè)計各種情形進行實驗，深刻體會并發(fā)控制的方法和重要性。實驗要求：1.若干學(xué)生一組共同完成本實驗。2.設(shè)計一組操作產(chǎn)生“臟”讀問題，然后通過封鎖避免“臟”讀問題。3.設(shè)計一組操作產(chǎn)生不可重復(fù)讀問題，然后通過封鎖避免不可重復(fù)讀問題。4.設(shè)計一組操作產(chǎn)生丟失更新問題，然后通過封鎖避免丟失更新問題。5.設(shè)計一組產(chǎn)生死鎖的操作，再利用相同順序法和一次封鎖法有效避免死鎖。6.設(shè)計一個相對完整的應(yīng)用，使用設(shè)置隔離級別來進行并發(fā)控制，