投影片 1

Size: px

Start display at page:

Download "投影片 1"

太暌实
7 years ago
Views:

1 CH 18 資料庫系統 1

2 學習目標 1. 明瞭檔案系統的不足之處 2. 理解資料庫管理系統的概念 3. 認識何謂關聯式資料庫系統 4. 學習 SQL 的基本語法與應用 5. 知道何謂 ODBC 2

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

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

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

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

7 資料庫管理系統應用程式應用程式資料型錄檔案檔案 ( a ) 檔案系統 ( b) 資料庫系統 7

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

18.1.2 資料庫管理系統 ( 續 ) DBMS 依其架構可區分為下列幾種 : 個人型 : 如微軟公司的 Access 大型 :

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

10 資料庫管理系統 ( 續 ) 主從型 : 當使用人數增多時, 資料庫系統的效率連帶地降低新一代的大型 DBMS 藉由電腦網路, 允許網路上的客戶電腦執行部份的資料庫處理工作 10

11 資料庫管理系統 ( 續 ) 分散處理型 : 一個分散處理型的資料庫系統是由若干資料庫伺服電腦以電腦網路串連而成 11

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

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

14 資料庫的三層架構 14

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

16 客戶帳戶資料模型網路式 : 資料間的關係是以網路形式表現之客戶帳戶張菲大理街台北徐若瑄中正路台中費玉清中華路台北葉玉卿府城路台南

17 資料模型 ( 續 ) 階層式 : 資料間的關係是以階層式 ( 樹狀網路 ) 的形式表現張菲大理街台北徐若瑄中正路台中葉玉卿府城路台南費玉清中華路台北

18 圖 18.8 關聯式資料模型關聯式 : 資料模型 ( 續 ) 資料間的關係是以表格的形式表現姓名街道城市帳號帳號結餘張菲大理街台北 900 費玉清中華路台北 811 費玉清中華路台北 888 徐若瑄中正路台中 647 葉玉卿府城路台南 388 葉玉卿府城路台南

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

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

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

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

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

24 實體關係圖 ( 範例 ) 續方塊代表實體集 (entity set) 實體集是同類實體 (entity) 所組成的集合連接在方塊上的橢圓形是屬於該實體集的一個屬性 (attribute) 能夠用來識別實體集內各實體的屬性稱為鍵屬性 (key attribute), 或簡稱為鍵 (key) 兩個實體集間的關係是以菱形表之, 關係的名稱寫在菱形內 24

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

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

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

28 SQL 的元件敘述 (Statement): 每一個敘述都對應一項 DBMS 的動作, 例如 : 建立一個新表格取出資料加入新的一列資料到表格中等等動詞表格名稱子句關鍵字 DELETE FROM COURSE WHERE NUM = '1433' 欄名常數 28

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

30 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 日期 + 時間

31 SQL 的元件 ( 續 ) 常數 (Constants) 常數數值常數字串常數日期常數時間常數範例 21, -375, , 1.5E3, E-33 Jones, Jone J., Western 美式 mm/dd/yyyy 5/19/1960 May 19, 1960 歐式 dd.mm.yyyy 日式 yyyy-mm-dd ISO yyyy-mm-dd 美式 hh:mm AM/PM 歐式 hh.mm.ss 日式 hh.mm.ss ISO hh.mm.ss 金錢常數 $0.75, $ , $

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

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

34 綱要和表格的宣告及處理 ( 續 ) 表格我們可以用 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

35 綱要和表格的宣告及處理 ( 續 ) 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

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

37 綱要和表格的宣告及處理 ( 續 ) 4. 外來鍵是用 FOREIGN KEY 子句來宣告, 這個子句必須同時列出被參照的表格名稱及其主鍵, 例如 : FOREIGN KEY (TNAME) REFERENCES FACULTY(NAME) 我們可以用 DROP TABLE 敘述來刪除不再需要的表格 ALTER TABLE 敘述來更改表格中的種種宣告例如 : ALTER TABLE SCHOOL.COURSE ADD TIME VARCHAR(12); 37

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

39 8.3.4 更修敘述 INSERT 敘述 INSERT 敘述是用來加入一列資料至表格中它有兩種格式第一種格式列出表格名和所有欄位的資料 ; 例如 INSERT INTO COURSE VALUES ( 1433, 檔案結構, 張雅惠, M117 ) 第二種格式列出表格名和部份欄位的資料 ; 例如 INSERT INTO COURSE (NUM, CNAME) VALUES ( 1433, 檔案結構 ) 39

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

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

42 更修敘述 ( 續 ) 這一個沒有 WHERE 子句敘述, 將刪除表格 COURSE 中的所有資料 ; 刪除之後, COURSE 成了一個空表格 DELETE FROM COURSE 42

43 更修敘述 ( 續 ) PDATE 敘述 UPDATE 敘述是用來修改某些被選出的資料列的屬性值例如 : UPDATE COURSE SET WHERE ROOM = M321 ROOM = M117 43

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

18.3.5 查詢敘述 ( 續 ) 列出所有老師的名字和他的分機號碼 SELECT NAME, EXT FROM

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

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

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

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

49 試闡明檔案系統的缺失課後練習請解釋資料庫資料庫管理系統資料庫系統這三個名詞請敘述資料庫管理師的職掌請說明資料庫的三層資料架構為何? 請比較網路式階層式與關聯式這三種資料模型請敘述 SQL 的用途請依據本章的授課資料庫範例寫出下列資料查詢的 SQL 指令 : 找出戴強廉老師的辦公室號碼 49

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘 ITE 資訊專業人員鑑定資料庫系統開發與設計實務試卷編號 :IDS101 注意事項一本測驗為單面印刷試題, 共計十三頁第二至十三頁為四十道學科試題, 測驗時間 90 分鐘 : 每題 2.5 分, 總測驗時間為 90 分鐘二執行 CSF 測驗系統 -Client