數(shù)據(jù)庫系統(tǒng)原理實驗指導書

數(shù)據(jù)庫系統(tǒng)原理實驗指導書

新疆農(nóng)業(yè)大學計算機與信息工程學院

2005年2月

課程簡介：

《數(shù)據(jù)庫系統(tǒng)原理》課程主要講授數(shù)據(jù)庫技術(shù)的相關(guān)基本原理與概念；T-SQL對數(shù)據(jù)庫

的操作：數(shù)據(jù)庫、數(shù)據(jù)表、索引、視圖、存儲過程的創(chuàng)建和使用，數(shù)據(jù)的插入、修改、刪除

與查詢：數(shù)據(jù)源之間的導入、導出和鏈接原理與概念等。學習本課程的目的是使學生掌握管

理數(shù)據(jù)庫數(shù)據(jù)的基本技術(shù)及原理與實現(xiàn)方式。

課程安排：

教學總周數(shù)：10周

教學總時數(shù)：64學時

理論教學數(shù)：44學時

實驗教學數(shù)：20學時

課程要求：

上課要認真學習，上機要認真實踐，注重理論聯(lián)系實際。要學有所悟，學有所用。

課程考核：

本課程的期末考試分為筆試和實驗兩部分，成績計算方法為：期末考試成績占總成績的

70%,平時成績（含考勤、平時作業(yè)、實驗成績）占總成績的30%。

實驗要求：

1.明確實驗課是本課程的重要組成部分，與理論課有著同等地位，是培養(yǎng)自身的實驗技能

與創(chuàng)新能力的重要途徑；

2.在實驗課前，對實驗的目的、要求和基本內(nèi)容，實驗的重點和難點，應(yīng)進行預(yù)習與討論,

確定實施措施；

3.了解實驗室的規(guī)章制度和安全用電常識、實驗設(shè)備損壞賠償制度等，加強安全意識，愛

惜實驗設(shè)備；

4.實驗課期間不得擅自離開實驗室或從事與本實驗無關(guān)的活動，按時按質(zhì)完成實驗作業(yè)，

培養(yǎng)創(chuàng)造性思維，努力提高自身的實踐能力；

5.每次實驗應(yīng)提交實驗結(jié)果，結(jié)合實驗操作過程給予綜合評分（對于有創(chuàng)造性的實驗成果

酌情給予加分），作為期末考試成績的一部分。

實驗一熟悉DBMS的運行環(huán)境和系統(tǒng)配置

一、實驗?zāi)康?/p>

本章實驗主要講述數(shù)據(jù)庫和數(shù)據(jù)模型的有關(guān)概念及數(shù)據(jù)庫系統(tǒng)的結(jié)構(gòu)。通過本章的

學習，讀者應(yīng)該掌握以下內(nèi)容：數(shù)據(jù)庫和數(shù)據(jù)模型的基本概念、數(shù)據(jù)模型的三要素、概

念模型的表示方法、數(shù)據(jù)庫系統(tǒng)的模式結(jié)構(gòu)與體系結(jié)構(gòu)DBMS的功能與組成。具體包括

了解、熟悉DBMS,了解連接數(shù)據(jù)庫服務(wù)器的身份驗證模式，熟悉樣例數(shù)據(jù)庫，體會DBMS

的三層模式結(jié)構(gòu)，充分利用聯(lián)機幫助了解DBMS中數(shù)據(jù)字典。

二、實驗準備

數(shù)據(jù)模型由三個要素組成：數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)操作和完整性約束。

1.數(shù)據(jù)結(jié)構(gòu)

數(shù)據(jù)結(jié)構(gòu)用于描述系統(tǒng)的靜態(tài)特性，是所研究的對象類型的集合。數(shù)據(jù)模型按其數(shù)據(jù)結(jié)

構(gòu)分為層次模型、網(wǎng)狀模型和關(guān)系模型。

2.數(shù)據(jù)操作

數(shù)據(jù)操作用于描述系統(tǒng)的動態(tài)特性，是指對數(shù)據(jù)庫中各種對象的實例允許執(zhí)行的操作的

集合，包括操作及有關(guān)的操作集合。

3.數(shù)據(jù)的約束條件

數(shù)據(jù)的約束條件是一組完整性規(guī)則的集合。完整性規(guī)則是給定的數(shù)據(jù)及其聯(lián)系所具有的

制約和存儲規(guī)則，用以限定符合數(shù)據(jù)庫狀態(tài)以及狀態(tài)的變化，以保證數(shù)據(jù)的正確、有效和相

容。

數(shù)據(jù)庫系統(tǒng)的三級模式結(jié)構(gòu)：

數(shù)據(jù)庫系統(tǒng)的三級模式結(jié)構(gòu)是指數(shù)據(jù)庫系統(tǒng)是由外模式、模式和內(nèi)模式三級組成。

1.外模式。外模式也稱子模式或用戶模式，它是數(shù)據(jù)庫用戶（包括應(yīng)用程序員和最終用

戶）看見和使用的局部數(shù)據(jù)的邏輯結(jié)構(gòu)和特征的描述，是數(shù)據(jù)庫用戶的數(shù)據(jù)視圖，是與某一

應(yīng)用有關(guān)的數(shù)據(jù)的邏輯表示。一個數(shù)據(jù)庫可以有多個外模式。

2.模式。模式也稱邏輯模式，是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結(jié)構(gòu)和特征的描述，是所有用

戶的公用數(shù)據(jù)視圖。一個數(shù)據(jù)庫只有一個模式。

3.內(nèi)模式。內(nèi)模式也稱存儲模式，它是數(shù)據(jù)物理和存儲結(jié)構(gòu)的描述，是數(shù)據(jù)在數(shù)據(jù)庫內(nèi)

部的表示方式。一個數(shù)據(jù)庫只有一個內(nèi)模式。

DBMS的功能：

1.數(shù)據(jù)定義

數(shù)據(jù)定義包括定義構(gòu)成數(shù)據(jù)庫結(jié)構(gòu)的外模式、模式和內(nèi)模式，定義各個外模式與模式之

間的映射，定義模式與內(nèi)模式之間的映射，定義有關(guān)的約束條件（例如，為保證數(shù)據(jù)庫中數(shù)

據(jù)具有正確語義而定義的完整性規(guī)則，為保證數(shù)據(jù)庫安全而定義的用戶口令和存取權(quán)限等）。

2.數(shù)據(jù)操縱

數(shù)據(jù)操縱包括對數(shù)據(jù)庫數(shù)據(jù)的檢索、插入、修改和刪除等基本操作。

3.數(shù)據(jù)庫運行管理

對數(shù)據(jù)庫的運行進行管理是DBMS運行時的核心部分，包括對數(shù)據(jù)庫進行并發(fā)控制、安

全性檢查、完整性約束條件的檢查和執(zhí)行、數(shù)據(jù)庫的內(nèi)部維護（如索引、數(shù)據(jù)字典的自動維

護）等。所有訪問數(shù)據(jù)庫的操作都要在這些控制程序的統(tǒng)一管理下進行，以保證數(shù)據(jù)的安全

性、完整性、一致性以及多用戶對數(shù)據(jù)庫的并發(fā)使用。

4.數(shù)據(jù)組織、存儲和管理

數(shù)據(jù)庫中需要存放多種數(shù)據(jù)，如數(shù)據(jù)字典、用戶數(shù)據(jù)、存取路徑等，DBMS負責分門別

類地組織、存儲和管理這些數(shù)據(jù)，確定以何種文件結(jié)構(gòu)和存取方式物理地組織這些數(shù)據(jù)，如

何實現(xiàn)數(shù)據(jù)之間的聯(lián)系，以便提高存儲空間利用率以及提高隨機查找、順序查找、增、刪、

改等操作的時間效率。

5.數(shù)據(jù)庫的建立和維護

建立數(shù)據(jù)庫包括數(shù)據(jù)庫初始數(shù)據(jù)的輸入與數(shù)據(jù)轉(zhuǎn)換等。維護數(shù)據(jù)庫包括數(shù)據(jù)庫的轉(zhuǎn)儲與

恢復、數(shù)據(jù)庫的重組織與重構(gòu)造、性能的監(jiān)視與分析等。

6.數(shù)據(jù)通信接口

DBMS需要提供與其他軟件系統(tǒng)進行通信的功能。例如，提供與其他DBMS或文件系統(tǒng)的

接口，從而能夠?qū)?shù)據(jù)轉(zhuǎn)換為另一個DBMS或文件系統(tǒng)能夠接受的格式，或者接收其他DBMS

或文件系統(tǒng)的數(shù)據(jù)。

三、實驗內(nèi)容

1.熟悉網(wǎng)絡(luò)環(huán)境和數(shù)據(jù)庫環(huán)境

數(shù)據(jù)庫服務(wù)器：

操作系統(tǒng):Windows2000AdvancedServer

IP地址:202,201.224.123

數(shù)據(jù)庫管理系統(tǒng)：MicrosoftSQLServer2000

服務(wù)器名稱：HP

客戶機：

操作系統(tǒng)：Windows2000Professional

IP地址:202,201.X.X

MicrosoftSQLServer2000客戶端

啟動MicrosoftSQLServer2000查詢分析器:

1..開始-＞程序-＞MicrosoftSQLServer2000-＞查詢分析器出現(xiàn)如下畫面

連接到MicrosoftSQLServer2000

SQLServer：HP(或23)

選擇SQLServer身份驗證；登錄名：student密碼：student

2.創(chuàng)建數(shù)據(jù)庫sql望g

createdatabaseon

(name=姓名_dat.

filename=

size=10,

maxsize=50,

filegrowth=5)

logon

(name=，姓名」0晨,

filename=

size=5MB,

maxsize=25MB,

filegrowth=5MB

3.身份驗證模式：在安裝SqlServer2000過程中，出現(xiàn)［身份驗證對話框］,提示選擇

身份驗證模式。Windows身份驗證模式或混合模式（Windows身份驗證和SqlServer身份驗

證）并提示添加sa用戶登陸密碼（一般選中空密碼）。安裝完SQL后，打開企業(yè)管理器，逐

級展開節(jié)點到［安全性"［登陸］,在右邊視圖中可以看到登陸的用戶名稱，右擊sa用戶，打

開屬性，彈出屬性對話框，可以看到當前用戶的身份驗證模式。

4.體會DBMS的三層模式結(jié)構(gòu)：（以理解為主）從數(shù)據(jù)庫管理系統(tǒng)角度看，數(shù)據(jù)庫描述由

三級抽象模式組成：概念模式（邏輯模式）、物理模式（內(nèi)模式）和外模式：從數(shù)據(jù)庫最終

用戶角度看，數(shù)據(jù)庫系統(tǒng)的結(jié)構(gòu)分為單用戶結(jié)構(gòu)、主從式結(jié)構(gòu)、分布式結(jié)構(gòu)和客戶/服務(wù)器

結(jié)構(gòu)。概念模式體會：打開任意所建立［數(shù)據(jù)庫］,選擇一個［表］,右擊［設(shè)計表］,打開［設(shè)

計表］框。這里描述了存儲的數(shù)據(jù)的屬性和實體及實體關(guān)系。物理模式：描述存儲細節(jié)。外

模式：視圖和來自概念模式的關(guān)系組成。

5.安裝SqlServer2000后,安裝程序?qū)⒃谒鶆?chuàng)建的數(shù)據(jù)庫實例中創(chuàng)建數(shù)據(jù)庫和II志文

件。Master、Model、Msdb和Tempdb都是系統(tǒng)數(shù)據(jù)庫。Pubs和Northwind示例數(shù)據(jù)庫作為

學習工具提供。我們提供基于樣例數(shù)據(jù)庫Northwind的一些操作。打開［企業(yè)管理器］|［數(shù)據(jù)

庫］［Northwind］。Northwind示例數(shù)據(jù)庫包含了一個名為NorthwindTraders的虛構(gòu)公司的

銷售數(shù)據(jù)，打開［表］,可以看到該數(shù)據(jù)庫包括了Categories、Customers等13張用戶表和

Alphabeticallistproduct、CategoryScalefor1997等16張視圖?？梢匀我獯蜷_用

戶表查看信息。

6.從［開始］|［程序］|MirosoftSQLServer|聯(lián)機叢書。學習數(shù)據(jù)字典。

四、思考題

1.分別給出一個層次、網(wǎng)狀和關(guān)系模型的實例。

2.從用戶角度看，數(shù)據(jù)庫系統(tǒng)都有哪些體系結(jié)構(gòu)？

3.數(shù)據(jù)庫管理系統(tǒng)通常山哪幾部分組成？

實驗二數(shù)據(jù)庫安全管理

一、實驗?zāi)康?/p>

了解SQLServer2000中的兩種用戶登錄模式；

熟悉SQLServer2000中登錄帳戶、用戶、角色、權(quán)限的概念;

掌握SQLServer2000中創(chuàng)建帳戶、數(shù)據(jù)庫用戶的方法，會使用角色來分配權(quán)限

二、實驗準備

SQLServer的用戶權(quán)限控制是SQLServer一個最重要的管理概念，也是眾多管理任務(wù)

之■?SQLServer的登錄方式有標準SQLServer登錄和集成Windows系統(tǒng)登錄兩種。

SQLServer集成的安全登錄模式即將Windows系統(tǒng)的用戶和工作組映射為SQLServer

的登陸帳戶。Windows集成登錄模式實際上是讓Windows系統(tǒng)代替SQLServer執(zhí)行對登陸

審查的任務(wù)。

如果要采用SQLServer管理的安全登錄模式實現(xiàn)SQLServer服務(wù)器的登錄連接，用戶

必須擁有合法的帳號和正確的密碼。由SQLServer系統(tǒng)本身來進行用戶的身份驗證。

三、實驗內(nèi)容

本次實驗采用SQLServer管理的安全登錄模式來連接服務(wù)器，也就是要求用戶輸入帳

戶和密碼并且經(jīng)系統(tǒng)驗證通過后方可登錄。

首先打開SQLServer本地客戶端的查詢分析器，如下圖所示，選擇或指定要連接的服

務(wù)名稱（這里為HP或23）,輸入帳戶：student,密碼為student。

1.創(chuàng)建登錄帳戶

用戶可以通過企業(yè)管理器或系統(tǒng)提供的存儲過程來進行登錄帳戶的創(chuàng)建。這里使用系統(tǒng)

存儲過程來創(chuàng)建，創(chuàng)建帳戶的語法可參見SQLServer2000聯(lián)機幫助中的相關(guān)內(nèi)容。

下面的例子創(chuàng)建了?個登陸帳戶：

sp_addloginxs,123456.northwind

這個例子創(chuàng)建了一個名為XS,密碼是123456,默認數(shù)據(jù)庫為northwind的帳戶。在建

立用戶的登錄帳號信息時，用戶應(yīng)該選擇默認的數(shù)據(jù)庫，以后每次連接上服務(wù)器后，系統(tǒng)都

會自動轉(zhuǎn)到默認的數(shù)據(jù)庫上。這里也可以不指定數(shù)據(jù)庫，系統(tǒng)默認為master庫。

請同學將上例中的xs帳戶改為各自學號，建立各自的登錄帳戶，默認數(shù)據(jù)庫為student

數(shù)據(jù)庫，密碼自行設(shè)計。下面的各個實驗均參照示例將xs修改為各自的帳戶。

2.修改和刪除帳戶

建立完成的帳戶信息還可以進行修改。用戶可以使用系統(tǒng)存儲過程

spdefaultdb,spdefaultlanguage來修改默認數(shù)據(jù)庫和默認語言。例如：

sp_defaultdbxs,班級名稱

班級名稱包括（信管011、信管013、信管022、信管024、信管026、、計科021、計科

022、計科024、計科026）,以上的班級名稱是已經(jīng)建好的數(shù)據(jù)庫名，將你的帳戶的默認數(shù)

據(jù)庫改為所屬班級的數(shù)據(jù)庫。

sp_defaultlanguagexs.English

（系統(tǒng)默認是'SimplifiedChinese',建議使用默認項。）

返回的結(jié)果是：

默認數(shù)據(jù)庫已更改。

xs的默認語言已改為English,

使用系統(tǒng)存儲過程sppassword可修改SQLServer的帳戶密碼，如：

sp_password123456.456789.xs

該例子修改了XS的帳戶密碼。

要刪除SQLServer標準登錄帳戶可以使用系統(tǒng)存儲過程sp_droplogin,例如：

sp_droploginxs

在SQLServer中刪除帳戶信息時，有很多限制。主要有以下幾種情況：

1）系統(tǒng)帳戶sa不能被刪除；

2）已經(jīng)映射到數(shù)據(jù)庫用戶上的帳戶不能被刪除；

3）正在使用或連接的帳戶不能被刪除；

4）擁有數(shù)據(jù)庫的帳戶不能被刪除

3.添加數(shù)據(jù)庫用戶

以上的操作只是建立了登錄SQLServer的帳戶，用戶登錄后還不能對數(shù)據(jù)庫進行訪問，

將登錄帳戶添加為數(shù)據(jù)庫用戶后，使用登錄帳戶登錄的SQLServer的用戶就可以實現(xiàn)對數(shù)

據(jù)庫的訪問。

添加數(shù)據(jù)庫用戶可通過存儲過程sp_grantdbaccess來實現(xiàn)，參照下面的例子將你的帳

戶添加為student（該庫是本課程的示例庫）和班級名稱（你所在班級的數(shù)據(jù)庫名）兩個數(shù)據(jù)

庫的用戶。

usestudent

go

sp_grantdbaccessxs

use庫名（即你的班級名稱）

go

sp_grantdbaccessxs

4.刪除數(shù)據(jù)庫用戶

可以使用系統(tǒng)存儲過程sprevokedbaccess來刪除數(shù)據(jù)庫用戶，更確切地說，是斷開SQL

Server的登錄帳戶與數(shù)據(jù)庫用戶之間的對應(yīng)關(guān)系。

sp_revokedbaccessxs

返回結(jié)果為：

用戶已從當前數(shù)據(jù)庫中除去。

5.角色

角色是SQLServer2000引進的用來集中管理數(shù)據(jù)庫或服務(wù)器權(quán)限的概念。數(shù)據(jù)庫管理

員將數(shù)據(jù)庫的權(quán)限賦予角色，然后將角色再賦予給數(shù)據(jù)庫用戶或登錄帳戶，從而是數(shù)據(jù)庫用

戶或登錄帳戶擁有相應(yīng)的權(quán)限。

SQLServer提供了固定服務(wù)器角色和數(shù)據(jù)庫角色，用戶可以修改固定數(shù)據(jù)庫角色的權(quán)

限，也可以自己創(chuàng)建新的數(shù)據(jù)庫角色，在分配權(quán)限給新的角色。

1）固定服務(wù)器角色

使用系統(tǒng)存儲過程sphelpsrvrole可以瀏覽固定服務(wù)器角色的內(nèi)容。

usemaster

go

sp_helpsrvrole

存儲過程sp_addsrvrolemeniber可以將某固定服務(wù)器角色分配給某給定的登錄帳戶。

下面的操作將登錄帳戶xs添加為服務(wù)器角色dbcreatord的成員，使其具有創(chuàng)建和修改數(shù)據(jù)

庫的權(quán)利：

sp_addsrvrolememberxs,dbcreator

存儲過程sp_dropsrvro1emember可以收回分配給某給定的登錄帳戶的指定固定服務(wù)器角

色。

這個例子收回了分配給登錄帳戶xs的固定服務(wù)器角色dbcreator.

sp_dropsrvrolememberxs,dbcreator

2）固定數(shù)據(jù)庫角色

使用系統(tǒng)存儲過程sp_helpdbfixedrole可以瀏覽所有的固定數(shù)據(jù)庫角色的相關(guān)內(nèi)容。如：

usemaster

go

sp_he1pdbfixedrole

用存儲過程sp_addrolemember可將某個登錄帳戶增加到某個固定數(shù)據(jù)庫角色中，從而使該

帳戶擁有指定固定數(shù)據(jù)庫角色所擁有的所有權(quán)限。

進行下面的操作給你的帳戶授予對student庫的只讀權(quán)限，xs換為你的帳戶名。

usestudent

go

sp_addrolememberdb_datareader>xs

下面的操作給你的登錄帳戶授予對你所在班級數(shù)據(jù)庫的所有者權(quán)限，XS換為你的帳戶名。

use庫名（即你的班級名稱）

go

sp_addrolememberdb_owner,xs

同樣使用spdroprolemember可以從某個固定數(shù)據(jù)庫角色中刪除指定的登錄帳戶，從而收回

分配的對student庫的只讀權(quán)限。

usestudent

go

sp_droprolememberdb_datareader.xs

完成所有上面操作以后，斷開當前連接，使用本實驗創(chuàng)建的個人登錄帳戶登錄SQLServer

服務(wù)器，登錄成功后，可以看到當前默認操作數(shù)據(jù)庫為你所在班級的數(shù)據(jù)庫名即你的班級名

稱。

四.思考題

1.思考身份驗證模式與登錄帳號的關(guān)系及如何用各種帳號進行登錄；

2.登錄帳號、數(shù)據(jù)庫用戶及數(shù)據(jù)庫角色之間的關(guān)系；

3.數(shù)據(jù)庫用戶、數(shù)據(jù)庫角色與數(shù)據(jù)庫對象之間的關(guān)系，并說出直接對用戶授權(quán)與間接對

用戶授權(quán)（系統(tǒng)權(quán)限與對象權(quán)限）的方法；

4.固定服務(wù)器角色、固定數(shù)據(jù)庫角色、自定義數(shù)據(jù)庫角色與應(yīng)用程序角色的區(qū)別與驗證

其權(quán)限的方法。

實驗三E-R模型與關(guān)系模型的轉(zhuǎn)換

一、實驗?zāi)康?/p>

本章實驗通過給學生一個設(shè)計實例，要求設(shè)計E-R模型，并分析。然后選擇熟悉的DBMS

將給出的E-R模型轉(zhuǎn)換為關(guān)系模型，并按要求實現(xiàn)創(chuàng)建數(shù)據(jù)庫、數(shù)據(jù)表、表間關(guān)系等（修改

表結(jié)構(gòu)）。

二、實驗準備

1.數(shù)據(jù)庫設(shè)計

數(shù)據(jù)庫設(shè)計的一個最基本的問題是如何建立一個好的數(shù)據(jù)庫模式。即給出一組數(shù)據(jù)，如

何構(gòu)造一個適合于它們的數(shù)據(jù)模式，使數(shù)據(jù)庫系統(tǒng)無論是在數(shù)據(jù)存儲方面，還是在數(shù)據(jù)操縱

方面都有較好的性能。E-R模型方法討論了實體與實體之間的數(shù)據(jù)聯(lián)系，現(xiàn)在來討論實體內(nèi)

部屬性與屬性之間的數(shù)據(jù)關(guān)聯(lián)，目標是要設(shè)計?個“好”的數(shù)據(jù)庫模型。

2概念結(jié)構(gòu)設(shè)計

在需受基礎(chǔ)上，用數(shù)據(jù)模型表示數(shù)據(jù)及其聯(lián)系。

設(shè)計E—R圖步驟：

1.設(shè)計局部E-R圖。概念結(jié)構(gòu)設(shè)計依據(jù)是需求分析階段的DFD/DD。在DFD中選擇適當

層次的DFD,作為設(shè)計局部E-R圖的出發(fā)點。中層允許有一定的重疊。

1）確定實體集合

第一步（關(guān)鍵一步）數(shù)據(jù)流/數(shù)據(jù)源/目的/數(shù)據(jù)存儲根據(jù)具體情況決定,常作為

實體集合。

2）聯(lián)系

標明：1:1,1:N,N:Mo原則上：與處理框相關(guān)的輸入流（數(shù)據(jù)流），輸出流（數(shù)據(jù)目的

地）,輸入或輸出的工作之間的可能存在的聯(lián)系。

3）屬性

屬性名盡量和數(shù)據(jù)流中數(shù)據(jù)項名相同。為簡化E-R圖，屬性可僅在DFD中描述。

4）主關(guān)鍵字

屬性中標明作為PK（primarykey）的屬性集合.

5）其它

建E2圖，要完善DD（DD：包括實體集，聯(lián)系，屬性的描述）某些情況：描述產(chǎn)生頻率

（每年/月/季），是否長期保存，變化快慢，保密級別，存在的約束。2、集成局部E-R圖

在設(shè)計局部E-R圖的基礎(chǔ)上，將局部E-R圖集成為全局E-R圖。集成時要解決的問題：

消除沖突、消除冗余

2.合并局部E-R圖

合并局部E-R圖中相同部分，盡可能的保留特殊部分，刪除冗余部分，用累加的方式-

次集成兩個局部E-R圖。

3.優(yōu)化全局E-R圖必要時應(yīng)對全局E-R圖進行修改，重構(gòu)和優(yōu)化得到最佳的全局E-R

圖方案。

三、實驗內(nèi)容

1.數(shù)據(jù)模型分析

?設(shè)計一個教學管理系統(tǒng)

各部門對教學管理系統(tǒng)的要求：

學生處：管理各院系班級學生的基本情況

教務(wù)處：掌握學生各門課程的成績情況

計財處：管理學生每學期的學費收繳情況

各院系：登錄本院系學生各門課程的成績

?具體E-R模型

2.E?R模型轉(zhuǎn)換為關(guān)系模型

經(jīng)過轉(zhuǎn)換得到5個關(guān)系：

Department(dept_id,deparment,phone,director,no_class)

Student(studentjd,class_id,name,gender,birthday,address,zip_code,householder)

Class(classid,dept_id,class,abbreviation,monitor,levels,enroll_data,no_student)

Course(coursejd,course,period,practice_period,credit)

Grade(studentid,courseid,grade)

3.數(shù)據(jù)字典設(shè)計

1)表名(具體建表時，在你的班級數(shù)據(jù)庫中創(chuàng)建表，表名稱由學生學號+示例表名構(gòu)

成。比如student_idDepartment)

表名含義

Department院系

Class班級

Student學生

Course課程

Grade成績

2）表結(jié)構(gòu)

?Department

字段名數(shù)據(jù)類型字段含義約束

Dept_IDDecimal(5)院系編號PrimaryKey

DepartmentvarChar(50)院系名稱NotNull

AbbreviationvarChar(10)院系簡稱

PhonevarChar(50)聯(lián)系電話

DirectorvarChar(50)院系主任

No_ClassDecimal(5)班級數(shù)NotNullDefault0

?Class

字段名數(shù)據(jù)類型字段含義約束

Class_IDChar(7)班級編號PrimaryKey

Dept_IDDecimal(5)院系編號ForeignKey

ClassvarChar(30)班級名稱NotNull

AbbreviationvarChar(20)班級簡稱

MonitorvarChar(24)班長

LevelvarChar(4)層次NotNull大專、本科、碩士、博士

Enroll_DateDecimal(5)入學年度NotNull

No_StudentDecimal(5)人數(shù)NotNullDefault0

?Student

字段名數(shù)據(jù)類型字段含義約束

Student_IDvarChar(9)學號PrimaryKey

Class_IDvarChar(7)班級編號ForeignKey

NamevarChar(24)姓名NotNull

GendervarChar(2)性別NotNull男，女

BirthdayDatetime出生日期NotNull

AddressvarChar(50)家庭地址

Zip_CodeDecimal(6)郵編

HouseholderVarchar(50家長

?Course

字段名數(shù)據(jù)類型字段含義約束

Course_IDvarChar(8)課程編號PrimaryKey

CoursevarChar(50)課程NotNull

CreditDecimal(5)學分

PeriodDecimal(5)學期

Practice_periodDecimal(5)實習

?Grade

字段名數(shù)據(jù)類型字段含義約束

Student_IDvarChar(9)學號ForeignKey

PrimaryKey

Course_IDvarChar(8)課程編號ForeignKey

GradeDecimal(5)成績NotNull

3）創(chuàng)建數(shù)據(jù)庫

開始->程序->MicrosoflSQLServer2000->查詢分析器出現(xiàn)如下畫面

I

■開吆卜，》藥。J3?山質(zhì)J@一…1SBJR3?I西K］如…|際f>?4口旦,通Q4，土rSI

4）創(chuàng)建表SQL語句

典營理］,,咨?刀6:\20050410Ig^^-McMoftL.JfjSQLScgEntw.llnsQi杳i酎Hffirj.4Q昱多0目力,53322?)

*開苗⑴*J3二”4狡的文檔|幻笑狂Ma.IfSQISerpEnJID50不釗折.切羽后一物?|&?Q昱印.，①切545E32的

婁開珀|T-l闞文衿|色]實習五干6]￥]50.5?皿￡也.|回50（..邢）析竺）實由一3?.|AQ昱多，0口「,5332204

■開匍①窗冷劣2》"毒網(wǎng)5I邑制五…I爭SQUEnJlGsoQ題析…Qg?-，＜ta：?…I&Q昱出--①Eh45屬22:05

，開珀|TJM'X臺文衿|也實習五Ma.IfSaSerpEn…llQsqtH的析竺）實蛉一物?.|昱多0日個,5屆笈中

■開匍處嘉岸5g?!"建#5I邑制五…I爭SQUEnJlGsQl蚤矽析…Qg?-，＜ta：?…I&Q昱出--①Eh45屬22:08

■開匍力嘉岸5g?!"建#5I邑制五…I爭5?8—..||0501蚤矽析…Qg?-，dx?…I&Q昱出--①Eh,5型22?9

實驗四關(guān)系數(shù)據(jù)庫標準語言SQL

一、實驗?zāi)康?/p>

掌握向表中添加數(shù)據(jù)的方法

掌握如何快速的把?個表中的多行數(shù)據(jù)插入到另一個表中

掌握更新表中多行數(shù)據(jù)的方法。

學會如何刪除表中的一行數(shù)據(jù)

學會如何刪除表中的所有行

掌握SELECT語句的基本用法

使用WHERE子句進行有條件的查詢

掌握使用IN,NOTIN,BETWEEN來縮小查詢范圍的方法

利用LIKE子句實現(xiàn)模糊查詢，利用ORDER子句為結(jié)果排序

學會用SQLSERVER的統(tǒng)計函數(shù)進行統(tǒng)計計算

掌握用GROUPBY子句實現(xiàn)分組查詢的方法。

掌握多表查詢的技術(shù)和嵌套查詢的方法。

二、實驗準備：

1.常用的統(tǒng)計函數(shù)

sum()返回一個數(shù)字或計算列的總和

avg()對一個數(shù)字列或計算求列平均值

min()返回一個數(shù)字或一個數(shù)字表達式的最小值

max()返回一個數(shù)字或一個數(shù)字表達式的最大值

count()返回滿足SELECT語句中指定條件的記錄的值

count(*)返回找到的行數(shù)

注意：

對于以下類型的數(shù)據(jù)values值不用使用引號

1)整型數(shù)據(jù)類型：bit,int,smallint,tinyint

2)貨幣數(shù)據(jù)類型：money,smallmoney

3)數(shù)字數(shù)據(jù)類型：decimal,numeric

4)浮點類型數(shù)據(jù)：real,float

5)簡單的日期/時間類型數(shù)據(jù):datetime,smalldatetime

對于以下數(shù)據(jù)類型就要對values值加單引號

1)字符數(shù)據(jù)類型：char,varchar,text

2)復雜的日期/時間類型數(shù)據(jù)：datetime,smalldatetime

2.添加語句

INSERT[INTO]table_or_view[(column_list)]data_values

刪除語句：

DELETE

[FROMJtable_name

WHEREsearch_condition

更新語句：

UPDATEtable_name

SET

column_name={expressionIDEFAULTINULL}[,...nJ

[FROMtable_name[,...n]]

WHEREsearchcondition

查詢語句：

SELECTselect_list

FROMtable_source

[WHEREsearch_condition]

[GROUPBYgroup_by_expression]

[HAVINGsearch_condition]

fORDERBYorder_expression[ASCIDESC]]

三.實驗內(nèi)容：

操作「INSERT(插入)

1.首先檢查表結(jié)構(gòu)：

I上

sp_helpdepartment

sp-helpclass

sp_helpstudent

sp_helpcourse

sp_helpgrade

sp_helptuition

2.單條語句插入樣例：

奏DEPARTMENT插入樣例

INSERTINTODEPARTMENT

VALUES(11J計算機及信息工程’計算機‘，’8762898',‘張?zhí)t'，22)

表class插入樣例

INSERTINTOclass

VALUES04463121,11/信管044',null/本科',2004,.22)

表student插入樣例

INSERTINTOstudent

VALUES('044631201','0446312','王泊'，null,null,null,null,null)

表course插入樣例

INSERTINTOcourse

VALUES('26110033','fff,3_.2,5)

表grade插入樣例

INSERTINTOgrade|

VALUES('044631201'.(261100331.85)

請同學練習使用單條SQL語句給自己的各個表進行插入。

請同學練習使用單條SQL語句給自己的各個表進行插入。

3.批量插入樣例數(shù)據(jù)

insertintodepartmentselect?fromstudents,dbo.department

insertintoclassselect*fromstudents,dbo.class

insertintostudentselect?fromstudents,dbo.student

insertyintocourseselect*fromstudents.dbo.course

insertlintogradeselect*fromstudents,dbo.grade

insertintotuitionselect*fromstudents.dbo.tuition

多數(shù)同學插入的數(shù)據(jù)有限，為保證后續(xù)實習的順利進行，請各位按以下步驟生成統(tǒng)?的數(shù)據(jù)!

執(zhí)行以上SQL腳本，同時也可以加帶附加條件

注：如果你所選用的數(shù)據(jù)源表不在本庫的話，就要引用源表的庫名.dbo.表。

insertStudent（STUDENT_ID,CLASS.ID.NAME,GENDER.BIRTHDAY,ADDRESS,

,ZIP_CODE,HOUSEHOLDER；

select*fromstudent.dbo.student

whereCLASS_ID='024%'

操作二：UPDATE（更新）

1.在student表中，GENDER（性別），BIRTHDAY（生日），ADDRESS（家庭地址），ZIP_CODE

（家庭郵編），HOUSEHOLDER（家長姓名）均不正確或為空值，請將自己以上的準確信息

更新到自己的數(shù)據(jù)庫中，同時更新到同班同學的其它數(shù)據(jù)庫中。

updat章student

setgander-*男’,BIRTHDAY=,820204",ADDRESS='山東省'，

|ZIP_CODE=,899999',HOUSEHOLDER?王'

fromstudentwherestudent_id=f994631227,

<1I

STUDENT.IDCLASS.IDNAMEGENDERBIRTHDAYADDRESS]ZIP-CODEHO...

19946312279946312董征...男1982...山東省899999王

2.查詢你的數(shù)據(jù)庫中同班其他同學的信息是否亡更新。

3,執(zhí)行以下更新

在department表中，no_class(班級數(shù))均不正確或為空值，請根據(jù)class表中每個學院實有

班級&目更新department表中的no_class,例如：

updatedepartment

setno_class=32fromdepartmentwheredept_id=l1|

select*fromdepartmentwheredept_id=l1

4.執(zhí)行以下更新

在class表中，no_student(學生數(shù))均不正確或為空值，請根據(jù)student表中每個班級實有

學生人數(shù)更新class表中的no_studento

updateclass

setno_student二班級人數(shù)frem(

selectabbreviation'班級’1班級人數(shù)=count(*)

fromstudentass,classasc

wherec.class_id=s.class_idgroupbyabbreviation)asd

whereclass,abbreviation=d.班級

操作三：DELETE（刪除）

請從student表中刪除一個student_id=，994631225,的記錄:

為什么會出現(xiàn)以下的結(jié)果？

deletestudentwherestudentid='994631225'

11

服務(wù)器：消息547,級別16,狀態(tài)1,行1

DELETE語句與COLUMNREFERENCE約束'FK_GRADE_STUDENT沖突。

該沖突發(fā)生于數(shù)據(jù)庫’STUDENT,表’GRADE',column'STUDENT.ID,o

語句已終止。

假若刪除的是你自己的學號可以嗎？為什么？

操作四：SELECT（查詢）

1.掌握SELECT語句的基本用法

select*fromdepartment

select*fromclass

select*fromstudent

select*fromcourse

select*fromgrade

select*fromtuition

2.查詢你的各個表中所有的記錄數(shù)目o

selectcount(*)fromdepartment

selectcount(*)fromclass

selectcount(*)fromstudent

selectcount(*)fromcourse

selectcount(*)fromgrade

selectcount(*)fromtuition

3.查詢你自己的平均成績（用avg（）函數(shù)）

SELECTavg（grade）'平均成績'FROMgrade

WHEREstudent_id="994631225,

4.查詢你班級所有同學的平均成績，查詢輸出以別名：學號，姓名，平均成績；并按平均成

績由高到低排序（用到orderby）

SELECTstudent.student_id,,avg(grade)'平均成績’

FROMstLdentgrade

wherestudent,student_id=grade.student_idandstudent.class_id=，9946312'

groupbystudent,student_id,

orderbyavg(grade)desc

5?查詢沒有成績記錄的學生，查詢輸出：學號，姓名、的在班級簡稱。

|SELECTstudent,student_id,student,name.,class,abbreviation

FROMstudent,classwherestudent.class_id=class.class_id

andstudent.class_id=,9946312'

andstudent_idnotin(selectstudent_idfromgrade)

6.查詢你自己的各課成績，結(jié)果格式如下：

SELECTgrade.course_id,course,course.,gradeFROMcourse.,grade

wherecourse.course_id=grade.course_idandgrade.student_id=，994631227,

7.查詢你班兩門及兩門以上課程不及格的學生，查詢輸出：學號，姓名、不及格課程數(shù)量。

SELECTstudent,student_id,student,name,FROMstudent.,

(selectstudent_id^count(*)ascnfromgradewheregrade<60

groupbystudent_idhavingcount(*)>1)b

wherestudent,student_id=b.student_idandstudent.class_id=，9946312’

8?查詢你班男生人數(shù)。

SELECTcount(*)FROMstudentwherestudent.class_id=，0246312,

andstudent.gender='男’

9.查詢你班女生人數(shù)。

SELECTcount(*)FROMstudentwherestudent.class_id=,0046312?

andstudent.gender='女’

10.查詢班級ClassJDlikeU2%，的班級。

select*fromclasswhereclass_idlike'02%'

11.請查詢輸出：班級編號，班級名稱，男生，女生。

SELECTC.Class.idas班級編號.C.ABBREVIAHONas班級名稱，

SOT(CASES.GENDERWHEN'男'THEN1ELSE0END)AS勇.

SUM(CASfcS.GENDERWHEN'文'THEN1ELSE0END)AS文

FROMStudentASs.ClassASc

WHERES.Class_ID=C.Class_ID

GROUPBYC.Class.ID,C.ABBREVIATION

12.請查詢輸出：班級編號，班級名稱，男生，女生，合計。

SELECTsc.*,(sc.男+sc.女〉A(chǔ)S合計

FROM(SELECTC.Classidas班級編號.C.ABBREVIATIONas班級名稱、

SUM(CASES.GENDERWHEN'男’THEN1ELSE0END)AS男.

SUM(CASES.GENDERWHEN'女'THEN1ELSE0END)AS女

FROMStudentASs,Clas?ASc

WHERES.Class_ID-C.Class.ID

GROUPBYC.Class.ID.C.ABBREVIATION)ASSC

13.執(zhí)行以下查詢，解釋該查詢的含義

selectdepartment.no-class,c.no_classfromdepartment..

(selectdept_id,count(*)asno_classfromclassgroupbydept_id)c

wheredepartment.dept_id=c.dept_idanddepartment.dept_id=l1

四、思考題

1.如何使用UNION子句，它必須符合那兩條基本準則？

2.怎樣把STUDENT表中前10個數(shù)據(jù)ADDRESS列的數(shù)據(jù)更改為‘新疆'

實驗五查詢規(guī)則及查詢優(yōu)化

一、實驗?zāi)康?/p>

1.了解并掌握查詢優(yōu)化的途徑。

2.學習查詢優(yōu)化的最優(yōu)執(zhí)行策略,掌握代價估算優(yōu)化。

二、實驗準備

1.復習相關(guān)知識點,熟悉并掌握相關(guān)概念

2.根據(jù)理論知識,運用相關(guān)規(guī)則進行優(yōu)化處理，并上機驗證

三、實驗內(nèi)容

1.在查詢分析器中輸入下列語句，并查看執(zhí)行時間和執(zhí)行計劃，根據(jù)結(jié)果得出結(jié)論:

1)

口網(wǎng)格執(zhí)行計劃投統(tǒng)計面消息|

批查詢完成.23(8.0)sa(52)student0:00:011935行

?將鼠標移動到結(jié)果集顯示窗口的"SELECT成本:0%”對象上時,可查看SELECT操作

相應(yīng)信息：

SELECT

從數(shù)據(jù)庫中檢索行.允許從一個或多

個表中選擇一個或多個行或列.

物理操作：SELECT

邏輯操作：SELECT

行計數(shù)：1,935

執(zhí)行次數(shù):1

成本：0.000194(0%)

子樹成本：0.0451

計行計數(shù)：1,935

參數(shù)：

SELECT[studentJd]=[student_id]j[grad

e]=[grade]FROM[grade]

?將鼠標移動到結(jié)果集顯示窗口的箭頭上時，顯示下列信息:

行計數(shù)：1,935

行大?。?1

?將鼠標移動到結(jié)果集顯示窗口的"SELECT成本:0%”對象上時,可查看SELECT操作

相應(yīng)信息：

ClusteredIndexScan

掃描聚集索引，可以掃描全部內(nèi)容，也可以

只掃描一個范圍。

物理操作：ClusteredIndexScan

邏輯操作：ClusteredIndexScan

行計數(shù)：1,935

計行大?。?1

I/O成本：0.0427

CPU成本：0.00220

執(zhí)行次數(shù)：1

成本：0.044971(100%)

子樹成本：0.0449

計行計數(shù)：1,935

參數(shù)：

OBJECT:([student].[dbo].[GRADE],[PK_GRADE

])

2)輸入下列語句，并查看執(zhí)行時間和代價及相關(guān)信息:

(1)

口網(wǎng)格強執(zhí)行計劃搜統(tǒng)計即消息

批查詢完成202,201.224.123(8.0)sa(52)student0:00:01—[fl亍府3,列8至：

執(zhí)行時間：0:00:01

⑵

加,查詢一23.master.sa一無標題1*

usestudent

go

selectstudent_id,name

fromstudent

wheregender二‘男‘

go

⑶

ni'"查詢一23.master.sa—無標題1*____-1_□__!_x|

usestudent

go

selectstudent.id.,name

fromstudent

wheregender=’男'andbirthday>=y1982-01-0T

go

(4)

即查詢一23.master.sa-無標ggl*-1□!x|

usestudent

go

selectstudent_id,name

fromstudent

wheregender='男'orbirthday>=*1982-01-0T

go

⑸

1而"查詢一23.shm.sa—無標題1*」口|X

--___________________

usestudent

go

selectstudent.student_id,student,name,,grade

fromstudent.,grade

wherestudent.student_id=grade.student_id

go

(6)

”他查詢一23.shm.sa-無標造1*-!□!>

usestudent

go

selectstudent,student_id,.,grade

fromstudent.,grade

wherestudent,student_idegrade.student_idandcourse_id=，11140003,

go

⑺

而"查詢一23.student.sa—無標1*-|D|X

usestudent

go

SELECTsi.student_id,.,si.class_id

FROMstudentsi,students2

WHERESl.class_id=S2.class.idANDS2.najn”'吳寒'

go

(8)

查詢一23.shnxsa-無標Jfil，，!□1x|

usestudet

go.

SELECTstudent.id,name,.class_id

FROMstudent

WHEREclass.id：,K

?SELECTclass.id

FROMstudent

VHEREname二?吳家?

(9)

前查詢一23.student.sa—無標蔻1*-1□1x

IFEXISTS(SELECTNAMEFROMSYSINDEXES▲

WHERENAME='STU_IND,)—

DROPINDEXSTUDENT.STU_IND

GO

USEstudent

CREATEclusteredINDEXSTU_IND

ONSTUDENT(STUDENT.ID)

go

SELECTstudent_id,name,class_id

FROMstudent

WHEREclass.idIN

(SELECTclass.id

FROMstudent

WHEREname二'吳塞’)■

go~

(10)

?"查詢一23.student.sa-無標Sgl*，!□1x

DROPINDEXSTUDENT.STU_IND

GO

IF