SQL伺服器管理

黃添修 93.10.29修訂

李忠憲 94/11/4刪改


 

一、校務行政SQL2000的安裝

點選【自訂安裝】(若是以典型安裝的話,在裝完後以ODBC測試皆可連線成功;但以校務行政軟體去連時可能會失敗)

輸入密碼(空白或自訂)

 

【定序方式】 Chinese_Taiwan_Stroke【排序方式】更改為【二進位編碼】

安裝後請重新開機.

重開機後請更新SQL2K SP3a(http://www.microsoft.com/sql/downloads/2000/sp3.asp)

  

SQL防護之道

 

 

固定伺服器角色

說明

sysadmin

在 SQL Server 中執行任何活動。這個角色的權限會跨越所有其他的伺服器固定角色。

serveradmin

設定伺服器的所有設定值。

setupadmin

新增/移除連結伺服器,並執行部分系統預存程序,如 sp_serveroption

securityadmin

管理伺服器登入。

processadmin

管理 SQL Server 執行個體中執行的處理。

database creators

建立與更改資料庫。

diskadmin

管理磁碟檔案。

bulkadmin

執行 BULK INSERT 陳述式,亦即可以執行大量插入作業。

 

、資料庫備份與資料匯入、匯出

 

資料庫備份可以分做手動與自動化排程兩種:

 

資料匯入、匯出

、新增資料庫、資料表與欄位設定

 

資料庫的觀念學習

資料庫正規化(或稱標準化)

所謂的資料庫正規化就是在一個資料庫中去減少重複或多餘的資料的過程,亦即減少一個資料庫中多餘的資訊;因為一個資料庫會有異常性其原因往往是其資料庫的內容有不良的資料庫結構所導致,因此資料庫的正規化有其必要。在沒有正規化下的資料庫中,資料會沒有理由的出現在一個或多個資料表中,如此一來對於資料庫的安全性、磁碟空間、資料查詢速度、資料庫更新、資料庫備分及重要的資料完整性皆有不良的影響

在設計資料庫時,正規化(Normalize)是必經過程,它可以儘可能減少資料重覆問題。當資料庫上線後,才可能發現某些查詢慢的離譜,這時可以“選擇性”將某些必要的欄位做反正規化(Denormalize),以期達到提高查詢的速度,但前題必須在不造成資料庫系統錯亂的原則下進行。

正規化的目的在於將資料的重覆降至最低,一旦消除了大部分的資料重覆問題,卻衍生出另一個問題:即資料查詢速度變慢!通常正規化,我們會將資料表由一個細分成數個表格,若要找出其中一筆資料,很可能需要join相關表格,而“join”動作,將直接影響系統效率,造成查詢速度變慢;相對的要新增資料、修改或刪除資料時,也要找出相關表格而予以新增、修改或刪除,更要確保交易時,資料是全部新增/修改/刪除成功,倘有一個表格作任何動作失敗,則須rollback或回至安全點(checkpoint)的狀態中,此作業牽涉到資料庫的安全與保護政策。

故所謂的正規化就是:當我們將自己所設計的資料庫中所有的資料細分成多個資料表格,且使每一個資料表格只描述一種事實為止,其間的過程就稱為正規化。

當設計一個資料庫被設計時,使用者的需求是被視為最先的考量點,畢竟資料庫的設計是為了提供使用者能便利的使用為目的,就像校務行政資料庫是提供教師可以透過使用者的前端工具(按地球圖示)啟動校務行政程式來開啟遠端SQL資料庫,因此設計資料庫時,我們是依使用者的需求來設計資料庫,讓使用者可以操作十分簡易及達到最佳的效能。

當使用一個好的邏輯設計後,資料庫的實體結構是看不到的

正規化(Normal Forms )的步驟  (引用淺談資料庫正規化 台北縣教育網路中心 劉仁宇小姐)

正規化的理念包括下列四點:

STEP 1:將表格中的變動項目展開來,其結果即為一級正規化。

STEP 2:其次移去部分相關性(Partial dependency)得到二級正規化。

STEP 3:再來消除關連間之遞移相關(Transitive dependency)最後得到三級正規化。

正規化的步驟如下:

第一正規化(1NF)形式

第一正規化的目的是將所有的資料分成資料表(Table)的若干邏輯化單位(Logical Units),當資料表設計完成後,主鍵(Primary )會被分配到多數或所有的資料中。而主鍵是使資料表中的每一列資料為一個或多個的唯一資料欄。

第一正規化的表格必須合以下條件:
1.
必須為row-column的二維式table
2.  table
的每一筆資料(row)只描述一件事情
3.
每一欄位只含有單一事物的特性(欄位的唯一性)
4. 每一筆row的欄位內只允許存放單一值
5.
每個欄位名稱必須是獨一無二的
6.
沒有任何兩筆資料是相同的
7. row或欄位的先後順予是無關緊要的

→ 結論:消除重覆性資料

第二正規化(2NF)

第二正規化的目的是將部份相依於主鍵的資料取出 ,並將該資料存入另一個資料表中。

第二正規化的表格必須合下面條件:

移去部分相關性

→ 結論:消除功能相依(Functional Dependency

所謂功能相依是指表格和表格之間的相互關係,若某個表格中有兩個欄位A及B,當A欄位值可推導出B欄位值,稱功能相依性。
即若一關連
R,其屬性Y功能相關於屬性X,記作R.XR.Y;若且唯若R中有二個X值相同時,其Y值亦相同。

第三正規化(3NF)

第三正規化的目的是將不相依於主鍵的資料移除。

第三正規化的表格必須合下面條件:

消除遞移相依(Transitive Dependency

所謂遞移相依是指在一個表格中,如果某一欄位值可決定其他欄位值,但這些欄位中又存在某一欄位可以決定剩餘欄位值,稱遞移相依性。若有上述情況存在.如果在刪除資料時,可能會造成其他資料損毀。
一個FDR.AR.BR.BR.C則,R.AR.C成立,此種相關性稱為遞移相關。

BCNF正規化(BCNF)

一般資料庫進行至第三正規化時,幾乎可以滿足資料修正異常狀況,倘若仍有異常出現,則需再進行BCNF正規化,BCNF請參考所列書籍。

正規化 的好處

  1. 組織比較完整的資料庫。
  2. 縮減多餘的資料(即去除多餘的資料)。
  3. 在資料庫裡面的資料是是一致性的。
  4. 更便利於資料料庫的設計。
  5. 較好處理資料庫的安全性。(可做分區授權給相關人使用)

正規化 的缺點

正規化的資料庫的實質上有其缺點:也就是會降低資料庫的效能。當資料庫的效能降低時,必須去了解其涉及許多的因素:如CPU的使用、記憶體使用、輸入及輸出(I/O)。正規化的資料庫比起反正規化的資料庫,需要較多的CPU、記憶體、I/O,以利處理交易和資料查詢;此乃因為它必須先定為所要求的 資料表,再由這些資料表去做合併資料,以獲取所要求的資訊或處理所想要的資料。

反正規化(Denormalize)

有時,在詳細的正規化,反而會造成資料處理速度上的困擾,因此在執行速度考量之下,有時候不得不做適當的反向正規化,當我們在進行資料庫正規化的同時,可能也要測試系統執行效率,當效率不理想時,必須做適當的反正規化,但必須小心控制反正規化所造成的資料重覆性問題,以期達成最佳的資料庫設計。

所謂的「反正規化」就是處理正規化資料庫的過程,並允許將被控制多餘資料表結構予以修改。反正規化資料庫的理由是為了增進效能,但是反正規化資料庫和尚未正規化資料庫是不一樣的。

反正規化資料庫是一種 將資料庫內正規化層次下降一些的過程,反正規化乃在重新組合分離的資料表,或在資料表內建立重複的資料,以降低在擷取資料時,所需要合併資料表的數量。以降低CPU的使用、記憶體使用、輸入及輸出(I/O),因而使其效能提高了。但是反正規化資料庫,也因原分開的資料表再次的合併起來。造成多餘的資料再次的產生了。

正規化(Normalize)的實例

 

原始資料

員工
編號
ten_no
員工
姓名
ten_name
部門
編號
dept_id
部門
名稱
dept_name
課程
編號1
train
_id1
課程
名稱1
train_name1
訓練
日期
train
_date1
課程
編號2
train
_id2
課程
名稱2
train_name2
訓練
日期
train
_date2
課程
編號3
train
_id3
課程
名稱3
train_name3
訓練
日期
train
_date3
T0001 張一 101 教務處 SQL SQL.Server_Admin 910203 VB Visual Basic 920506 WEB WEB.Server_Admin 920702
T0002 張二 101 教務處 WEB WEB.Server_Admin 920702 ACC Access 920908      
T0003 張三 102 總務處 SQL SQL.Server_Admin 910203            
T0004 張四 103 訓導處 C++ Visual C++ 920809 SQL SQL.Server_Admin 910203 VB Visual Basic 920506
T0005 張五 102 總務處 VB Visual Basic 920506            

 

↓(第一正規化)

 

老師資料表

員工編號
ten_no
員工姓名
ten_name
部門編號
dept_id
部門名稱
dept_name
T0001 張一 101 教務處
T0002 張二 101 教務處
T0003 張三 102 總務處
T0004 張四 103 訓導處
T0005 張五 102 總務處

老師訓練課程紀錄表

員工編號
ten_no
課程編號
train_id
課程名稱
train_name
訓練日期
train_date
T0001 SQL SQL.Server_Admin 910203
T0001 VB Visual Basic 920506
T0001 WEB WEB.Server_Admin 920702
T0002 WEB WEB.Server_Admin 920702
T0002 ACC Access 920908
T0003 SQL SQL.Server_Admin 910203
T0004 C++ Visual C++ 920809
T0004 SQL SQL.Server_Admin 910203
T0004 VB Visual Basic 920506
T0005 VB Visual Basic 920506

 

↓(第三正規化)

↓(第二正規化)

 (理論上同一位老師同樣課程不會去上兩次, ten_no + tain_id 為主鍵)

 
員工編號
ten_no
員工姓名
ten_name
部門編號
dept_id
T0001 張一 101
T0002 張二 101
T0003 張三 102
T0004 張四 103
T0005 張五 102

 

部門編號
dept_id
部門名稱
dept_name
101 教務處
102 總務處
103 訓導處
 
員工編號
ten_no
課程編號
train_id
訓練日期
train_date
T0001 SQL 910203
T0001 VB 920506
T0001 WEB 920702
T0002 WEB 920702
T0002 ACC 920908
T0003 SQL 910203
T0004 C++ 920809
T0004 SQL 910203
T0004 VB 920506
T0005 VB 920506

 

課程編號
train_id
課程名稱
train_name
SQL SQL.Server_Admin
VB Visual Basic
WEB WEB.Server_Admin
ACC Access
C++ Visual C++

 

 

資料表的設計考量

實例操作:

、Client 端 ODBC設定

 

ODBC是一個設計提供底層的資料庫系統使用的通用API(Application Programming Interface)函數庫程式庫。它透過一個程式庫驅動程式和資料庫溝通(有如Windows和印表機的溝通是透過印表機的驅動程式),至於網路驅動程式是否需要與遠端資料庫連線,則取決於正被使用的資料庫。

以校務行政在win98機器上做說明範例

、以 Access 作為 SQL 後端平台(以Access 2000為例)

、使用Query Analyzer 工具查詢資料

SQL其他的指令

  1. 新增紀錄(Insert)

     

  2. 更新紀錄(Update)

     

  3. 刪除紀錄(DELETE)

SQL的檢視表的建立

SQL的檢視表其實是執行了SELECT的查詢結果,而此一結果是模擬成資料表的形式存在,但其又不是一個真實的資料表,所以也可稱為虛擬的資料表。檢視表和資料表的最大不同點為,檢視表示儲存查詢的敘述語法,但不儲存資料記錄;而資料表是真正儲存資料記錄的地方。

檢視表的優點有四:

  1. 增加可讀性:可使用了解的名稱來做欄位名稱,方便使用者檢視查詢結果。

  2. 資料安全:為不同的使用者建立不同的檢視表,並以權限方式限制其所能編輯及檢視的資料,以確保資料的安全性。

  3. 降低查詢的複雜:使用者可以透過檢視表來做複雜的查詢,不需要學習複雜的查詢技巧;就可以找到其所要查詢的資料。

  4. 方便程式的維護:以應用程式使用檢視表來存取資料時,當資料表的結構改變時;只要修改檢視表中的設定,即可以繼續查詢,不需要修改應用程式的內容。

建立檢視表的方法有二:

  1. 利用工具→精靈→資料庫→建立檢表精靈→再依其所詢問的回答即可

  2. 利用Enterprise Manger中,資料庫→JSDB→檢視表→右鍵→新增檢視表→進入檢視表設計視窗中去建立。

建立檢視表的實作:略過不寫,課堂上實作。

 

 

參考資料:

  1.  2003年1月29日趨勢科技毒賣新聞「SQL警戒病毒」專刊

  2. SQL2000效能監控與最佳化   陳俊宇 恆逸資訊

  3. Microsoft SQL Server 7 企業寶典  王牌講座作者聯誼會 姜建志 編著  第三波出版

  4. 微軟網站http://www.microsoft.com

  5. 系統分析與設計理論與實務應用 ------- 吳仁和、林信惠

  6. 資料庫系統理論與實務 -------------------- 曾守正

  7. SQL的奧祕 ------------------------------------- 蔡國瑞 (原著:Allen G. Taylor)

  8. SAMS Teach Yourself SQL 21天自學手冊   原著 Ryan K. Stephens & Ronald R. Plew   編譯 284  大新資訊

  9. 資料庫正規化 ------ http://ccis.nou.edu.tw/access/item02-3-10.htm

  10. 淺談資料庫正規化 台北縣教育網路中心 劉仁宇