《數(shù)據(jù)庫原理與應用44學時》實驗指導書(實驗1-4)

1、數(shù)據(jù)庫系統(tǒng)概論An Introduction to Database System實驗指導書(44課時版)中南民族大學計算機科學學院2015.5目 錄概 述21. 實驗簡介及基本要求22. 基本知識2概念結(jié)構(gòu)設計ER圖3邏輯結(jié)構(gòu)設計ER圖向關系模型的轉(zhuǎn)換4第一部分 基礎實驗5實驗環(huán)境簡介51.查看安裝Microsoft SQL Server 2000的目錄結(jié)構(gòu)62.服務管理器的啟動、暫停和停止73.注冊/配置服務器7實驗1 創(chuàng)建并管理數(shù)據(jù)庫、數(shù)據(jù)表（必做）111-1使用企業(yè)管理器創(chuàng)建并管理數(shù)據(jù)庫、數(shù)據(jù)表111-2使用T-SQL語句創(chuàng)建并管理數(shù)據(jù)庫、數(shù)據(jù)表16實驗2 數(shù)據(jù)檢索（必做）18概 述數(shù)

2、據(jù)庫系統(tǒng)概論上機實驗是學習本門課程的重要環(huán)節(jié)之一，實驗的設置是為了配合課堂教學，使學生熟練掌握和深入理解課堂教學內(nèi)容，進一步強化對數(shù)據(jù)庫系統(tǒng)原理的理解。同時，通過上機練習，也可以檢驗用關系數(shù)據(jù)庫標準語言SQL對數(shù)據(jù)庫進行操作的結(jié)果是否正確，使關系數(shù)據(jù)庫的基本概念更加具體、豐富。數(shù)據(jù)庫系統(tǒng)概論的實驗分為兩大部分：第一部分是與理論課同步進行的課程基礎實驗，是非獨立開設的實驗；第二部分是繼理論課后開設，需要獨立開設的課程設計或綜合性大實驗。本書給出了這兩種實驗課的實驗標準，供參考。1. 實驗簡介及基本要求 上機實驗是數(shù)據(jù)庫課程的重要環(huán)節(jié)，它貫穿于整個“數(shù)據(jù)庫原理”課程教學過程中。本課程的實驗分為前期

3、準備階段、基本操作階段和技術(shù)提高階段三個階段，其主要內(nèi)容和基本要求為：1）前期準備階段數(shù)據(jù)庫課程實驗的第一階段為前期準備階段。前期準備階段的主要任務是理解數(shù)據(jù)庫、數(shù)據(jù)模型和數(shù)據(jù)庫系統(tǒng)的基本概念；掌握數(shù)據(jù)庫的概念模型、數(shù)據(jù)模型及數(shù)據(jù)庫系統(tǒng)的設計方法；根據(jù)這些方法自己設計一個數(shù)據(jù)庫系統(tǒng)的實際應用項目，寫出系統(tǒng)分析和系統(tǒng)設計報告，提出在系統(tǒng)中要解決的問題。2）基本操作階段數(shù)據(jù)庫課程實驗的第二階段為基本操作階段?；静僮麟A段的主要任務是掌握數(shù)據(jù)庫系統(tǒng)的基本操作，包括T-SQL語言的應用和利用DBMS的工具進行數(shù)據(jù)庫定義、維護、查詢及掌握數(shù)據(jù)安全性、數(shù)據(jù)完整性等基本操作，并能夠針對實際問題提出解決方法，

4、得出正確的實驗結(jié)果。3）技術(shù)提高階段數(shù)據(jù)庫課程實驗的第三個階段為技術(shù)提高階段。技術(shù)提高階段的實驗，要求學生不僅要把課本上的內(nèi)容掌握好，同時還需要自學一些相關的知識，例如，軟件工程、計算機網(wǎng)絡技術(shù)、C#語言、Java語言及SQL Server2000的深入技術(shù)。技術(shù)提高階段的主要任務是要掌握有關數(shù)據(jù)庫備份和恢復技術(shù)、并發(fā)控制技術(shù)及數(shù)據(jù)庫系統(tǒng)的編程技術(shù)等。2. 基本知識數(shù)據(jù)庫設計與實現(xiàn)的基本過程包括需求分析、概念結(jié)構(gòu)設計、邏輯結(jié)構(gòu)設計、數(shù)據(jù)庫的物理設計以及數(shù)據(jù)庫的實施和維護等內(nèi)容。需求分析確定系統(tǒng)的數(shù)據(jù)需求和處理需求；概念結(jié)構(gòu)設計通過對用戶需求的綜合、歸納和抽象，形成一個獨立的概念模型；邏輯結(jié)構(gòu)設

5、計則將概念模型轉(zhuǎn)換為具體的數(shù)據(jù)庫管理系統(tǒng)所支持的數(shù)據(jù)模型；數(shù)據(jù)庫的物理設計結(jié)合應用確定數(shù)據(jù)模型的存儲結(jié)構(gòu)和存取方式。在這些工作完成之后，就可以建立數(shù)據(jù)庫并開始調(diào)試運行了，在數(shù)據(jù)庫的運行過程中必須對其進行維護包括數(shù)據(jù)庫的備份與恢復、性能分析與改進等等。對于整個過程的詳細描述超出了本實驗指導的范圍，請參見教材，下面簡單介紹設計過程的中兩個關鍵步驟概念結(jié)構(gòu)設計和邏輯結(jié)構(gòu)設計。概念結(jié)構(gòu)設計ER圖概念模型是用戶需求的抽象，通常用實體關系圖（Entity Relationship Diagram，即ER圖）來表示。設計ER圖的出發(fā)點是數(shù)據(jù)流圖層次的選擇，一般選擇中層的數(shù)據(jù)流圖作為分析設計的依據(jù)。此時得到的

6、是局部ER圖，對于局部ER圖而言，實體和屬性區(qū)分是比較關鍵的，但兩者之間沒有絕對的界限，對于屬性而言可以參考的兩條準則是：（1）作為屬性，不能再具有需要描述的性質(zhì)。屬性必須是不可分的數(shù)據(jù)項，不能再包含其他屬性。（2）屬性不能與其他實體具有聯(lián)系，即ER圖中所表示的聯(lián)系是實體之間的關系。在得到各個局部ER圖后，需要將它們合并成一張總的ER圖。首先是簡單的合并，協(xié)調(diào)各個局部ER之間不一致的地方；然后再對得到總ER圖消除冗余。冗余會造成存儲效率低下和更新異常等問題，可通過分析數(shù)據(jù)字典中數(shù)據(jù)項之間的邏輯關系以及規(guī)范化理論來加以消除，當然并不是所有的冗余都需要消除的。事實上必要的冗余是提高查詢效率的關鍵，

7、當然此時需要滿足完整性約束。本實驗要求采用的ER圖圖例如下所示：圖1 ER圖圖例邏輯結(jié)構(gòu)設計ER圖向關系模型的轉(zhuǎn)換關系數(shù)據(jù)庫的數(shù)據(jù)模型（即關系模型）由一組關系模式組成，因而ER圖向關系模型的轉(zhuǎn)換實際上就是將實體（包括實體的屬性）和聯(lián)系轉(zhuǎn)換為關系模式，一般遵循以下基本原則：（1）一個實體型轉(zhuǎn)換為一個關系模式。實體的屬性就是關系的屬性，實體的碼就是關系的碼。（2）一個1：X（X為1或n）聯(lián)系通常與某一端的關系模式合并，其中1：n的聯(lián)系需要與n端對應的關系模式合并。需要在合并的關系模式的屬性中加入另一個關系模式的碼和聯(lián)系本身的屬性。（3）一個m：n聯(lián)系轉(zhuǎn)換為一個關系模式。與該關系相連的各實體的碼以及

8、聯(lián)系本身的屬性均轉(zhuǎn)換為關系的屬性，而關系的碼為各實體碼的組合。（4）三個或三個以上實體間的一個多元聯(lián)系可以轉(zhuǎn)換為一個關系模式。與該多元聯(lián)系相連的各實體的碼以及聯(lián)系本身的屬性均轉(zhuǎn)換為關系的屬性，而關系的碼為各實體的碼的組合。（5）具有相同碼的關系模式可合并。第一部分 基礎實驗實驗環(huán)境簡介數(shù)據(jù)庫管理系統(tǒng)也稱為DBMS，它是對數(shù)據(jù)庫中數(shù)據(jù)進行管理的一個中間層軟件，主要具有以下功能：（1）數(shù)據(jù)定義功能DBMS提供數(shù)據(jù)定義語言（Date Definition Language，簡稱DDL），用戶通過它可以方便地對數(shù)據(jù)庫中的數(shù)據(jù)對象進行定義。（2）數(shù)據(jù)操縱功能DBMS提供數(shù)據(jù)操縱語言（Date Manip

9、ulation Language，簡稱DML），用戶通過它實現(xiàn)對數(shù)據(jù)庫的基本操作。（3）數(shù)據(jù)庫的運行功能數(shù)據(jù)庫在建立、運用和維護時由數(shù)據(jù)庫管理系統(tǒng)統(tǒng)一管理、統(tǒng)一控制，以保證數(shù)據(jù)的安全性、完整性、多用戶對數(shù)據(jù)的并發(fā)使用及發(fā)生故障后的系統(tǒng)恢復。（4）數(shù)據(jù)庫的建立和維護功能它包括數(shù)據(jù)庫初始數(shù)據(jù)的輸入、轉(zhuǎn)換功能，數(shù)據(jù)庫的轉(zhuǎn)儲、恢復功能，數(shù)據(jù)庫的重組織功能和性能監(jiān)視、分析功能等。這些功能通常由一些實用程序完成。SQL Server 2005主要有三個服務，即：SQL Server服務、SQL Server Agent服務和Microsoft Distribute Transaction Coordina

10、tor (MS DTC)服務。Ø SQL Server服務的功能是處理所有Transact-SQL語句、管理服務器上數(shù)據(jù)庫的所有文件；在多個并發(fā)用戶之間分配計算機資源，防止產(chǎn)生邏輯問題，如控制并要求更新服務器上相同數(shù)據(jù)的用戶請求的時序；保證數(shù)據(jù)的安全性、一致性、完整性。Ø SQL Server Agent服務器代理是獨立于SQL Server的一個服務，需要和SQL Server分開啟動。SQL Server Agent服務的主要功能是創(chuàng)建和管理作業(yè)、創(chuàng)建和管理報警、創(chuàng)建和管理操作員。Ø MS DTC服務也是獨立于SQL Server的一個服務，它允許應用程序在一

11、個事務中包括幾個不同的數(shù)據(jù)源。MS DTC協(xié)調(diào)分布式事務的正確完成，保證服務器上所有數(shù)據(jù)的更新都是一致的，或者在發(fā)生錯誤的情況下，取消所有的數(shù)據(jù)更新。1.查看安裝Microsoft SQL Server 2000的目錄結(jié)構(gòu)找到自己電腦上安裝數(shù)據(jù)庫程序的路徑，如圖：主要文件夾含義如下：1）BACKUP：這個目錄最初為空，它是SQL Server創(chuàng)建磁盤備份設備的缺省存儲位置。2）Binn：這是NT客戶和服務器的可執(zhí)行文件、在線幫助文件和擴展存儲過程的DLL文件所在的存儲位置。3）Data：這是所有數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件的缺省存儲位置。4）LOG：是SQL Server存儲日志文件的位置。所有

12、提示、警告和錯誤信息存儲在這里。5）Install：包括安裝SQL Server時所使用的安裝腳本和輸出文件。6）JOBS：這是臨時作業(yè)的輸出文件所在的存儲位置。2.服務管理器的啟動、暫停和停止在圖上選擇“開始”，啟動服務器。3.注冊/配置服務器使用SQL Server之前，必須注冊和配置服務器。在注冊服務器之前應先創(chuàng)建一個服務器組，注冊的服務器放在這個服務器組中。（1）注冊服務器進入企業(yè)管理器，默認已經(jīng)建立了一個本地（local）服務器。（2）配置服務器在SQL Server注冊上單擊右鍵，選擇“屬性”，如下圖所示。1）在“常規(guī)”頁中可以設定啟動策略、網(wǎng)絡配置等。單擊網(wǎng)絡配置，如下圖通常至少

13、應啟用TCP/IP協(xié)議。2）另一項常用的配置選項是“安全性”，可進行身份驗證等設定。實驗1 創(chuàng)建并管理數(shù)據(jù)庫、數(shù)據(jù)表（必做）1-1使用企業(yè)管理器創(chuàng)建并管理數(shù)據(jù)庫、數(shù)據(jù)表一、實驗目的（1）熟悉Microsoft SQL Server 2000的基本操作。（2）理解數(shù)據(jù)庫的概念。（3）掌握使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫的方法。（4）掌握查看、修改數(shù)據(jù)庫屬性的方法。（5）了解由已經(jīng)生成的數(shù)據(jù)庫產(chǎn)生SQL Server腳本的方法。二、實驗內(nèi)容1、使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫。2、創(chuàng)建并管理數(shù)據(jù)庫users該數(shù)據(jù)庫的主數(shù)據(jù)文件的邏輯名稱是usersbin，操作系統(tǒng)文件是users.mdf，大小是4MB，最大是30

14、MB，以10的速度增長；該數(shù)據(jù)庫的日志文件的邏輯名稱是userslog，操作系統(tǒng)文件名是users.ldf，大小是5MB，最大是25MB，以1MB的速度增加。具體創(chuàng)建步驟如下：1）進入SQL Server企業(yè)管理器2）展開目錄樹，在“數(shù)據(jù)庫”選項上單擊右鍵，選擇“新建數(shù)據(jù)庫”，該窗口有3個選項卡：常規(guī)、數(shù)據(jù)文件和事務日志。注意：數(shù)據(jù)庫的名稱必須遵循SQL Server的命名規(guī)范，并且不能與已有的數(shù)據(jù)庫名重復。常規(guī)標識符的命名規(guī)則：標識符長度可以為1128個字符首字符必須為字母、下劃線、或#第一個字符后面的字符可以是字母、數(shù)字、下劃線、或#等符號標識符不能與Transact-SQL中的保留字同名

15、保留字與標識符等都不區(qū)分大小寫定界標識符：需要使用雙引號” ”或方括號 等定界符進行分隔的標識符。在定義的標識符中用到數(shù)據(jù)庫保留字、空格等特殊字符時，必須使用定界標識符。數(shù)據(jù)文件的類型：數(shù)據(jù)類型文件擴展名主要數(shù)據(jù)文件.mdf次要數(shù)據(jù)文件.ndf事務日志文件.ldf3、由已經(jīng)存在的數(shù)據(jù)庫生成創(chuàng)建數(shù)據(jù)庫的TSQL腳本步驟：1）鼠標右鍵單擊已經(jīng)存在的數(shù)據(jù)庫JWGL，選擇“所有任務”；2）選擇“生成SQL腳本”如下圖所示，選擇“全部顯示”，可以選擇數(shù)據(jù)庫中想要生成SQL腳本的數(shù)據(jù)對象3）單擊“選項”頁，如下圖所示，至少選擇“編寫數(shù)據(jù)庫腳本”練習生成數(shù)據(jù)庫腳本“users.sql”4.創(chuàng)建表（1）使用企

16、業(yè)管理器創(chuàng)建表步驟：1）展開數(shù)據(jù)庫目錄樹2）選擇“表”，鼠標右鍵單擊，并選擇“新建表”練習創(chuàng)建表student，表結(jié)構(gòu)如下：列名稱類型寬度允許空值默認值主鍵說明sidchar8否是學號snamevarchar8否學生姓名sexchar2否性別birthsmalldatetime是出生年月classidchar6是班級號entrancedatesmalldatetime是入學日期addrvarchar40是家庭住址5.管理表使用企業(yè)管理器步驟：1）選擇一張已經(jīng)建立好的表2）右鍵單擊，選擇“設計表”，如下圖所示，進入與創(chuàng)建表相同的界面3）可進行表列的增加、刪除和修改操作對表添加、修改、刪除數(shù)據(jù)在出

17、現(xiàn)的窗口中可進行表中數(shù)據(jù)的添加、修改和刪除操作。 練習在學生表中增加一列，字段名：stature（身高），字段類型：numeric，字段長度4.2，允許為空；創(chuàng)建后再刪除。請寫出增加列和刪除列的SQL語句。練習向表中添加數(shù)據(jù)sidsnamesexbirthclassidentranceaddr9940201張紅男1984-9-1994021999-9-1南京市鼓樓區(qū)9940202林麗女1984-6-1994021999-9-1揚州市五庭街9940203曹雨男1983-12-1994021999-9-1南京市玄武區(qū)9940204芮華男1983-9-9994021999-9-1江蘇儀征市青年路99

18、40205李紅女1984-9-8994021999-9-1北京市中關村9940206張海國男1984-4-5994021999-9-1江蘇儀征市青年路9940207李紅女1984-5-23994021999-9-1南京市中山北路9940210趙青男1985-1-9994031999-9-1南京市中山北路1-2使用T-SQL語句創(chuàng)建并管理數(shù)據(jù)庫、數(shù)據(jù)表一、實驗目的（1）了解查詢分析器的使用方法。（2）掌握TSQL語句的基本使用。（3）熟練掌握DDL語句。（4）熟練掌握DML(Insert, Delete, Update)語句。注意：在做刪除操作前，請先備份數(shù)據(jù)庫，否則已刪除的數(shù)據(jù)庫和數(shù)據(jù)表都將不

19、可再恢復二、實驗內(nèi)容1、創(chuàng)建并管理數(shù)據(jù)庫users要求：（1）用T-SQL語言創(chuàng)建數(shù)據(jù)庫users該數(shù)據(jù)庫的主數(shù)據(jù)文件的邏輯名稱是usersbin，操作系統(tǒng)文件是users.mdf，大小是4MB，最大是30MB，以10的速度增長；該數(shù)據(jù)庫的日志文件的邏輯名稱是userslog，操作系統(tǒng)文件名是users.ldf，大小是5MB，最大是25MB，以1MB的速度增加。（2）使用SQL語句將users數(shù)據(jù)庫中的主文件擴大到40MB2、用TSQL語句創(chuàng)建并管理數(shù)據(jù)庫“學生管理數(shù)據(jù)庫”要求：（1）將該數(shù)據(jù)庫存放在D盤的SQL目錄下，數(shù)據(jù)庫文件初始大小為1MB，最大容量為50MB，文件增展率為5MB（2）建

20、立日志文件“學生管理日志”，將該數(shù)據(jù)庫存放在D盤的SQL目錄下，日志文件初始大小為1MB，最大容量為5MB，并按數(shù)據(jù)文件的10增長。（3）修改數(shù)據(jù)庫：將“學生管理數(shù)據(jù)庫”的數(shù)據(jù)庫最大容量更改為無限制（UNLIMITED），然后將“學生管理數(shù)據(jù)庫”的日志文件增長方式改為1MB（4）在“學生管理數(shù)據(jù)庫”中創(chuàng)建一張“學生情況表”，結(jié)構(gòu)如下學號 整型NOT NULL 主鍵姓名 字符串，8字節(jié)NULL性別 字符串，2字節(jié)NULL年齡 短整型NULL專業(yè) 字符串，30字節(jié)NULL家庭地址 字符串，50字節(jié)NULL（5）修改表結(jié)構(gòu)：將“學生情況表”中，家庭地址字段的長度改為80（6）向表中添加記錄學號姓名性

21、別年齡專業(yè)家庭地址200009412汪小麗女22計算機上海市邯鄲路220號200108215南鍵男21計算機青島市高雄路1號200207121曲竹非女21會計學廈門市屯溪路291號（7）修改表中記錄：將“曲竹非”同學的專業(yè)修改為“計算機”（8）刪除記錄：刪除表中性別為“男”的學生記錄（9）刪除表：將“學生情況表”從“學生管理數(shù)據(jù)庫”中刪除實驗2 數(shù)據(jù)檢索（必做）一、實驗目的（1）理解T-SQL語言的使用。（2）熟練掌握數(shù)據(jù)查詢語句。SELECT ALL|DISTINCT *|<表達式>,<表達式>FROM <表名>, <表名>WHERE <

22、條件>GROUP BY <列名>, <列名>HAVING <謂詞>ORDER BY <列名> ASC|DESC , <列名> ASC|DESC二、實驗內(nèi)容在“學生管理數(shù)據(jù)庫”中建立四張表，分別如下：學生表：（主鍵：學號）學號姓名性別年齡所在院系班級入學日期20009001葛文卿女22國際貿(mào)易國貿(mào)2班200082920014019鄭秀麗女21會計學會計1班20019220023001劉成鎧男18計算機軟件2班200282720026002李濤女19電子學電子1班200282720023002沈香娜女18計算機軟件2班2002827

23、20026003李濤男19計算機軟件1班200282720023003肖一竹女19計算機軟件2班2002827課程表：（主鍵：課程號）課程號課程名先修課學分C801高等數(shù)學4C802C+語言C8073C803數(shù)據(jù)結(jié)構(gòu)C8024C804數(shù)據(jù)庫原理C8034C805操作系統(tǒng)C8074C806編譯原理C8034C807離散數(shù)學4成績表：（主鍵：學號、課程號）學號課程號成績學分20023001C80198420023002C80470420026001C80185420023001C80299320026002C803824授課表：（主鍵：課程號、班級名）教師名課程號學時數(shù)班級名蘇亞步C80172軟件

24、2班王文山C80264軟件2班張珊C80372軟件2班王文山C80464軟件2班蘇亞步C80172軟件1班（1） 列出軟件2班女同學的名單；（2） 列出2002年元旦前注冊的學生名單；（3） 列出所有年齡在19歲以下，或者女同學的名單；（4） 列出沒有先修課的課程名；（5） 列出既不是“電子系”，也不是“會計系”的學生的學號、姓名和所在院系；（6） 查詢選修“C801”課程的學生的最高分；（7） 統(tǒng)計男、女學生各自的人數(shù)和平均年齡；（8） 列出選修了一門以上課程的學生學號，及其選修門數(shù)；（9） 查詢“沈香娜”同學所在班級的所有學生的學號和姓名；（10） 統(tǒng)計每一年齡選修課程的學生人數(shù)；（11）

25、 在基本表“成績表”中檢索平均成績最高的學生學號；（12） 求表S中男同學的每一年齡組（超過2人）有多少人？要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同按年齡降序排列；難題：1) 列出選修了全部課程的學生學號和姓名；2) 查詢這樣的學生，該生至少選修了學生20026001所選修的全部課程；實驗3 視圖的創(chuàng)建與使用（必做）一、實驗目的（1）理解視圖的概念；（2）掌握創(chuàng)建視圖的方法；（3）掌握用視圖管理數(shù)據(jù)的方法；二、實驗內(nèi)容1.創(chuàng)建一個名為stud_view1的視圖，從學生管理數(shù)據(jù)庫數(shù)據(jù)庫的學生表中查詢出性別為男的所有學生的學號，姓名，性別，班級，入學時間和出生年。2創(chuàng)建一個名為stud_view2的視

26、圖，查詢出選修了數(shù)據(jù)庫原理的所有同學的選課信息，包括學號，姓名，成績。3從視圖stud_view1中查詢出軟件2班同學的資料。4向視圖stud_view2中插入一行數(shù)據(jù)：學號為20023001，姓名為劉成鎧，成績?yōu)?1，結(jié)果是什么？5修改視圖stud_view1，將20026003號同學的性別更改為女，會發(fā)生什么？然后再次使用SQL語句查詢學生表和視圖stud_view1，看結(jié)果有何改變，為什么？如何防止將視圖stud_view1中，學生的性別改成“女”？實驗4 數(shù)據(jù)完整性（必做）一、實驗目的（1）理解數(shù)據(jù)完整性的概念與分類；（2）掌握規(guī)則、默認的使用方法；（3）掌握用戶自定義完整性的使用；二、實驗內(nèi)容1、創(chuàng)建規(guī)則（會用圖形和語句方法創(chuàng)建）（1）創(chuàng)建入學日期規(guī)則“Enter_Univ