投影片 1

CH 18 資料庫系統 1

學習目標 1. 明瞭檔案系統的不足之處 2. 理解資料庫管理系統的概念 3. 認識何謂關聯式資料庫系統 4. 學習 SQL 的基本語法與應用 5. 知道何謂 ODBC 2

大綱 18.1 資料庫系統的概念 18.2 資料庫設計 18.3 SQL 資料庫語言 18.4 資料庫應用程式介面 3

CH 18 資料庫系統 資料庫是一群經過整合及電子化後的資料 這些資料用以提供企業機構 ( 如公司 銀行 工廠 學校 政府機關等 ) 的日常運作所需 管理資料庫的軟體則稱為資料庫管理系統 (database management system) 資料庫和資料庫管理系統兩者的組合則稱為資料庫系統 (database system) 4

18.1 資料庫系統的概念 在資料庫未盛行之前, 資料的儲存和處理通常是利用檔案 (files) 的方式 檔案是由一群資料錄 (records) 所組成 資料錄則是一個儲存相關資料的基本單位 傳統應用程式之基本運行模式是 : 1. 程式根據已知的檔案格式從 ( 一個或多個 ) 檔案中讀取資料, 處理資料, 2. 然後把結果印成報表或寫入另外一個檔案 5

18.1.1 檔案系統的缺失 系統缺乏彈性 資料重複 資料不一致 資料安全性不佳 不易達成資料完整性 同時存取容易發生問題 6

18.1.2 資料庫管理系統 應用程式 應用程式 資料型錄 檔案 檔案 ( a ) 檔案系統 ( b) 資料庫系統 7

18.1.2 資料庫管理系統 ( 續 ) 資料庫管理系統 (database management system, DBMS) 的功能 : 1. 制定資料庫 2. 增刪和修改資料 3. 查詢資料 4. 保護資料 5. 資料共享 6. 便利應用程式的發展 8

18.1.2 資料庫管理系統 ( 續 ) DBMS 依其架構可區分為下列幾種 : 個人型 : 如微軟公司的 Access 大型 : Oracle SyBase Informix Ingres SQL Server DB2 IMS MySQL 和 PostgreSQL 9

18.1.2 資料庫管理系統 ( 續 ) 主從型 : 當使用人數增多時, 資料庫系統的效率連帶地降低 新一代的大型 DBMS 藉由電腦網路, 允許網路上的客戶電腦執行部份的資料庫處理工作 10

18.1.2 資料庫管理系統 ( 續 ) 分散處理型 : 一個分散處理型的資料庫系統是由若干資料庫伺服電腦以電腦網路串連而成 11

18.1.3 資料庫系統的使用者 資料庫管理師 (Database Administrator, DBA) DBA 的工作包含 : 1. 制訂或修正資料庫的綱要 (scheme) 2. 制訂或修正儲存體 (storage) 的結構和存取方法 3. 訂定資料完整性條件 4. 設定資料庫使用的權限 5. 訂定資料備份 (backup) 和錯誤還原 (error recovery) 的策略 6. 監控和改進資料庫系統的效率 7. 取得系統所需的軟硬體設備 12

18.1.3 資料庫系統的使用者 ( 續 ) 資料庫設計師 : 分析及設計資料庫 資料庫應用程式設計師 : 用 DBMS 所提供的軟體發展工具來撰寫具有特定用途的資料庫應用軟體 資料庫操作與維護人員 : 負責資料庫系統日常的運作, 以及軟硬體的維護 終端用戶 : 用資料庫來查詢資料或印製報表以完成工作所需 13

18.1.4 資料庫的三層架構 14

18.1.4 資料庫的三層架構 ( 續 ) 目的 : 一. 可以分隔使用層面和資料庫實際儲存的方式, 而降低兩者的依存度 二. 可以透過 分而治之 的方式以簡化資料庫設計的過程 15

客戶 帳戶 18.1.5 資料模型 網路式 : 資料間的關係是以網路形式表現之 客戶 帳戶 張菲大理街台北 900 55 徐若瑄中正路台中 647 10000 費玉清中華路台北 葉玉卿府城路台南 811 1000 888 30000 388 15900 16

18.1.5 資料模型 ( 續 ) 階層式 : 資料間的關係是以階層式 ( 樹狀網路 ) 的形式表現 張菲大理街台北 徐若瑄中正路台中 900 55 647 10000 葉玉卿府城路台南 費玉清中華路台北 388 15900 888 30000 888 30000 17 811 1000

圖 18.8 關聯式資料模型 關聯式 : 18.1.5 資料模型 ( 續 ) 資料間的關係是以表格的形式表現 姓名街道城市帳號 帳號 結餘 張菲 大理街 台北 900 費玉清 中華路 台北 811 費玉清 中華路 台北 888 徐若瑄 中正路 台中 647 葉玉卿 府城路 台南 388 葉玉卿 府城路 台南 888 900 55 811 1000 888 30000 647 10000 388 15900

8.1.6 資料庫系統的模組 檔案管理程式 (file manager): 負責實際儲存資料於磁碟中, 以及資料存放的結構 資料庫管理程式 (database manager): 應用程式和資料質詢命令可透過此介面以存取磁碟中的資料 質詢處理器 (query processor): 負責轉譯質詢指令成資料庫管理程式所能能瞭解格式 在轉換過程當中也可以分析質詢指令, 以便找出最好的方式來執行它 資料定義語言編譯器 (Data Definition Language compiler):ddl 程式編譯器, 此處的 DDL 是用來定義資料庫的一種電腦語言 資料處理語言前處理器 (DML precompiler): 把應用程式中所含的 DML 指令抽取出送到 DML 編譯器加以編譯 此處的 DML 是用來處理資料庫的一種電腦語言 19

8.1.6 資料庫系統的模組 ( 續 ) 20

18.2 資料庫設計 實體 關係圖 : 1. 先描繪出資料庫中各類資料組合及其間的關係 2. 而後將所得的圖示轉換成對應的表格 表格設計只是資料庫系統設計的一個步驟 另外一個重要的步驟是功能分析, 也就是說 : 設計出能夠滿足使用者需求而且執行效率高的的功能 ( 透過應用程式或質詢語言 ) 21

18.2.1 實體 關係圖 ( 範例 ) 科目 : 每一科目具有選課代號 科目名稱 和上課教室這三項基本資料 教師 : 教師的基本資料有 : 姓名 職稱 辦公室 分機號碼等四項資料 兩者具有 教師教科目 這樣的關係 我們又假設底下的幾個條件成立 : 1. 一位教師教的科目可能不只一科 ; 2. 一個科目只有一位教師教授 ; 3. 每一位教師至少必須教一個科目 ; 4. 有些科目可能因選修人數不足而無法開成, 因此沒有教師教授 此關係是一種 一對多 的關係 22

18.2.1 實體 關係圖 ( 範例 ) 續 姓名職稱辦公室分機號碼 選課代號科目名稱上課教室 教師 1 N 教授 科目 23

18.2.1 實體 關係圖 ( 範例 ) 續 方塊代表實體集 (entity set) 實體集是同類實體 (entity) 所組成的集合 連接在方塊上的橢圓形是屬於該實體集的一個屬性 (attribute) 能夠用來識別實體集內各實體的屬性稱為鍵屬性 (key attribute), 或簡稱為鍵 (key) 兩個實體集間的關係是以菱形表之, 關係的名稱寫在菱形內 24

8.2.2 從實體 關係圖到表格 教師表格 姓名 職稱 辦公室 分機號碼 蔡奇偉 副教授 M533 3406 張雅惠 副教授 M111 3410 科目表格 選課代號 科目名稱 課教室 教師姓名 1432 電腦圖學概論 M411 蔡奇偉 1433 檔案結構 M117 張雅惠 25

18.3 SQL 資料庫語言 SQL 是 Structured Query Language( 結構化查詢語言 ) 的簡稱 它的前身是 SEQUEL(Structured English QUEryLanguage) SEQUEL 是 IBM 公司的實驗性關聯式資料庫 SYSTEM/R 所用的資料庫語言 因此 SQL 除了可讀作 S.Q.L 外, 又可讀作 SEQUEL 26

18.3.1 SQL 在 DBMS 中的角色 SQL 提供 DBMS 所有的使用者介面功能, 如下表所示 : 定義資料庫 處理資料庫 查詢語言 嵌入式資料庫處理語言 存取控制 保持資料完整性 異動控制 SQL 可用為 DDL 來定義資料庫的綱要和表格的綱要 SQL 可用為 DML 來加入 刪除 或更改資料庫內的資料 SQL 可用為查詢語言來取出資料庫內的資訊 SQL 可用於其他程式語言 ( 如 C/C++ COBOL PHP 等 ) 中來撰寫資料庫應用程式 SQL 可用於限制某些使用者讀取 寫入 或修改某些資料庫, 因而避免非法地使用資料庫 SQL 可用來定義資料庫的完整性限制條件以保護資料庫免於錯誤資料而導致的破壞 SQL 可設定異動 (Transaction) 的起始和結束 27

18.3.2 SQL 的元件 敘述 (Statement): 每一個敘述都對應一項 DBMS 的動作, 例如 : 建立一個新表格 取出資料 加入新的一列資料到表格中 等等 動詞 表格名稱 子句 關鍵字 DELETE FROM COURSE WHERE NUM = '1433' 欄名 常數 28

18.3.2 SQL 的元件 ( 續 ) 名稱 (Name) SQL 中有些物件, 如綱要 表格 欄和使用者 (users) 等, 必須給予唯一的名稱 每一個名稱是由 1 到 18 個字元所組成 ; 除了第一個字元必須是英文字母外, 其餘字元可以是英文字母 數字 或某些特殊字元 ( 如 _ 底線符號 ) 譬如 : 表格名稱 : COURSE 目前綱要定義下的表格名稱 SCHOOL.COURSE 綱要 SCHOOL 內的表格 COURSE 欄名 : NUM 目前表格的欄名 COURSE.NUM 表格 COURSE 內的欄名 NUM 29

18.3.2 SQL 的元件 ( 續 ) 資料型態 (Data Types) SQL 中有許多預定的資料型態 下表列出部份 ANSI/ISO SQL 的資料型態 資料型態意義資料型態意義 CHAR(len), CHARACTER(len) 定長字串 FLOAT 浮點數 VARCHAR(len) 變長字串 REAL 實數 INTEGER, INT 整數 DOUBLE PRECISON 倍準實數 SMALLINT 小額整數 DATE 日期 BIT(len) 定長 BIT 串 TIME 時間 BIT VARYING(len) NUMERIC(precision, scale) DECIMAL(precision, scale) DEC(precision, scale) MONEY, CURRENCY 變長 BIT 串 TIMESTAMP(precisio n) 十進位數 INTERVAL 時段 金錢 30 日期 + 時間

18.3.2 SQL 的元件 ( 續 ) 常數 (Constants) 常數數值常數字串常數日期常數時間常數 範例 21, -375, 2000.21, 1.5E3, 0.7839E-33 Jones, Jone J., Western 美式 mm/dd/yyyy 5/19/1960 May 19, 1960 歐式 dd.mm.yyyy 19.5.1960 日式 yyyy-mm-dd 1960-5-19 ISO yyyy-mm-dd 1960-5-19 美式 hh:mm AM/PM 歐式 hh.mm.ss 14.18.08 日式 hh.mm.ss 14.18.08 ISO hh.mm.ss 14.18.08 金錢常數 $0.75, $5000.00, $-567.48 31

18.3.2 SQL 的元件 ( 續 ) 算式 (Expression) 底下我們列出 SQL 的算式寫法 ( 它們和普通的程式語言類似 ) 運算子 算式範例 + - * / (SALES/TARGET) * 100 = <> > >= < <= SALES >= $3000 ( 字串連結 ) Mr./Mrs. FIRST_NAME LAST_NAME AND, OR, NOT SEX = F AND SALARY >= $20000 32

18.3.3 綱要和表格的宣告及處理 綱要在 SQL 中我們是用敘述 CREATE SCHEMA 來制定資料庫綱要 CREATE SCHEMA SCHOOL AUTHORIZATION John; 宣告一個名稱為 SCHOOL 的綱要, 且使用者 John 擁有此綱要 如果想刪除整個綱要, 我們可以用 DROP SCHEMA 這個敘述 33

18.3.3 綱要和表格的宣告及處理 ( 續 ) 表格 我們可以用 CREATE TABLE 這個敘述來宣告表格 1. 表格必須給定一個名稱, 例如 : CREATE TABLE COURSE 表格名稱 ( CREATE SCHEMA SCHOOL AUTHORIZATION John; CREATE TABLE COURSE ( NUM CHAR(4) NOT NULL, CNAME VARCHAR(15), TNAME VARCHAR(5) NOT NULL, ROOM CHAR(4), PRIMARY KEY (NUM), FOREIGN KEY (TNAME) REFERENCES FACULTY(NAME)); 34

18.3.3 綱要和表格的宣告及處理 ( 續 ) CREATE TABLE FACULTY ( NAME CHAR(4) NOT NULL, TITLE VARCHAR(15), OFFICE VARCHAR(5), EXT CHAR(4), PRIMARY KEY (NAME), FOREIGN KEY (TNAME) REFERENCES FACULTY(NAME)); (* 其他表格的內容 *) ); 35

18.3.3 綱要和表格的宣告及處理 ( 續 ) 2. 每一欄包含其名稱及其資料型態 如果其內容不得為空值的話, 可以加上 NOT NULL 的宣告, 例如 : NUM CHAR(4) NOT NULL 3. 每一個表格都需要有主鍵 SQL 是用 PRIMARY KEY 子句來宣告主鍵, 例如 : PRIMARY KEY (NUM) 宣告 NUM 欄是表格 COURSE 的主鍵 如果主鍵是由一個以上的欄所組成 36

18.3.3 綱要和表格的宣告及處理 ( 續 ) 4. 外來鍵是用 FOREIGN KEY 子句來宣告, 這個子句必須同時列出被參照的表格名稱及其主鍵, 例如 : FOREIGN KEY (TNAME) REFERENCES FACULTY(NAME) 我們可以用 DROP TABLE 敘述來刪除不再需要的表格 ALTER TABLE 敘述來更改表格中的種種宣告 例如 : ALTER TABLE SCHOOL.COURSE ADD TIME VARCHAR(12); 37

18.3.3 綱要和表格的宣告及處理 ( 續 ) 這一個敘述加入一個資料型態為 VARCHAR(12) 的新欄位 TIME 至表格 SCHOOL.COURSE 中 ALTER TABLE SCHOOL.COURSE DROP ROOM; 38

8.3.4 更修敘述 INSERT 敘述 INSERT 敘述是用來加入一列資料至表格中 它有兩種格式 第一種格式列出表格名和所有欄位的資料 ; 例如 INSERT INTO COURSE VALUES ( 1433, 檔案結構, 張雅惠, M117 ) 第二種格式列出表格名和部份欄位的資料 ; 例如 INSERT INTO COURSE (NUM, CNAME) VALUES ( 1433, 檔案結構 ) 39

8.3.4 更修敘述 ( 續 ) 如果加入的資料有錯誤或違反整合性的限制, DBMS 會拒絕接受該筆資料 ; 例如 : INSERT INTO COURSE (CNAME) VALUES ( 檔案結構 ) 這筆資料由於沒有提供 NUM 的資料, NUM 自動設為空值, 因而違反其 NOT NULL 的條件, 所以 DBMS 不會接受這筆資料 40

8.3.4 更修敘述 ( 續 ) DELETE 敘述 DELETE 敘述是用來刪除表格中滿足某些條件的資料列 例如 : 這一個敘述刪掉表格 COURSE 中選課課號為 1433 的那一筆資料 DELETE FROM COURSE WHERE NUM = 1433 這一個敘述刪掉表格 COURSE 中所有授課老師為張雅惠的那些資料 DELETE FROM COURSE WHERE TNAME = 張雅惠 41

18.3.4 更修敘述 ( 續 ) 這一個沒有 WHERE 子句敘述, 將刪除表格 COURSE 中的所有資料 ; 刪除之後, COURSE 成了一個空表格 DELETE FROM COURSE 42

18.3.4 更修敘述 ( 續 ) PDATE 敘述 UPDATE 敘述是用來修改某些被選出的資料列的屬性值 例如 : UPDATE COURSE SET WHERE ROOM = M321 ROOM = M117 43

18.3.5 查詢敘述 SQL 中查詢是用 SELECT 敘述來達成 它的基本格式如下 : SELECT <attribute list> FROM <table list> [WHERE <condition>] [ORDER BY <attribute list>] ( 方括號 [ ] 內的子句是可有可無的選項 ) 44

18.3.5 查詢敘述 ( 續 ) 列出所有老師的名字和他的分機號碼 SELECT NAME, EXT FROM FACULTY 列出所有副教授的名字 SELECT NAME FROM FACULTY WHERE TITLE = 副教授 45

18.3.5 查詢敘述 ( 續 ) 列出會在 M411 教室上課老師的分機號碼 SELECT EXT FROM FACULTY, COURSE WHERE NAME = TNAME AND ROOM = M411 將科目名稱按照選課代號依序排列 SELECT CNAME FROM COURSE ORDERED BY NUM 46

18.4 資料庫應用程式介面 ODBC(Open Database Connectivity) 定義標準的應用程式介面 (Application Program Interface, API) 來使用 DBMS 這些 API 利用 SQL 來完成其大部分任務 ODBC 本身也支援 SQL 語言, 用戶可以直接將 SQL 指令輸入 ODBC 在 UNIX 系統上, 有兩個開放源碼的 ODBC: unixodbc 和 iodbc 47

18.4 資料庫應用程式介面 應用程式 ODBC 驅動管理器 可載式驅動程式 (loadable driver) 資料庫系統 應用程式 ODBC 驅動管理器可載式驅動程式特定資料庫的 API 資料庫系統 48

試闡明檔案系統的缺失 課後練習 請解釋資料庫 資料庫管理系統 資料庫系統這三個名詞 請敘述資料庫管理師的職掌 請說明資料庫的三層資料架構為何? 請比較網路式 階層式 與關聯式這三種資料模型 請敘述 SQL 的用途 請依據本章的授課資料庫範例寫出下列資料查詢的 SQL 指令 : 找出戴強廉老師的辦公室號碼 49