Transcript 下載/瀏覽Download

第二章
關聯式資料庫的架構
章節概要
• 2.1 關聯式資料庫
• 2.2 範例資料庫說明
• 2.3 資料庫查詢語言
• 2.4 安裝 MySQL 資料庫
• 2.5 建立orders資料庫於SQL Server
• 2.6 設定ODBC資料來源
第二章 關聯式資料庫的架構
2
2.1 關聯式資料庫
• 關聯式資料庫(Relational Database；RDB)
是一種採用關聯式資料模型為基礎之資料庫。
關聯式資料模型是在1970年時由當時IBM公
司所屬的E. F. Codd 博士所提出的一種資料
模型。
• 關聯式資料模型植基於關聯理論Relational
Theory)，因此，它具有嚴謹的數學基礎。
• 關聯式資料模型的特色為不需要具備資料是
如何儲存在實體的裝置中的概念，也可以操
作資料。
第二章 關聯式資料庫的架構
3
關聯式資料庫(續)
• 關聯式資料庫採用容易讓人了解的資料表
(Table)這種概念來作為基礎。
• 關聯式資料庫利用含有列(Row)及欄位
(Column)的二維資料表來處理資料彼此間的
關聯，這種資料表稱為關聯(Relation)。
• 由於關聯式資料庫有許多的優點，因此關聯
式資料庫是目前使用中的主流資料庫。
第二章 關聯式資料庫的架構
4
關聯式資料庫(續)
• 在電腦的實際操作中，關聯式資料庫使用
一種稱為SQL(Structured Query
Language；結構化查詢語言)的資料庫語
言來操作資料庫，而SQL語言一般又稱為
第四代語言(4GL)。
• 使用者可以利用SQL對關聯式資料庫進行
查詢，而關聯式資料庫的資料庫管理系統
(DBMS)稱為關聯式資料庫管理系統
(Relational Database Management
System；RDBMS)。
第二章 關聯式資料庫的架構
5
資料表的架構與資料型態
• 一個資料庫可能會包含有一個到數個表格空間
(Tablespace)，而表格空間則可包含一個到數個資
料表(Base Table)及索引(Index)。
• 資料表(Table)是資料庫的最基本組成單位。
• 關聯式資料庫中的資料表是一個二維的表格，由資
料列(Row)和資料欄位(Column；資料行)所組成。
• 在關聯式資料模型中一筆資料被稱為值組(Tuple)，
而值組即相當於使用檔案來管理資料時的記錄或資
料錄(Record)的概念。
第二章 關聯式資料庫的架構
6
資料表的架構與資料型態(續)
• 值組在資料表中是一個橫列，所以也稱為列
或資料列(Row)，是使用者操作資料時的最
小操作單位。而資料表的值組數或資料錄數
稱為基數(Cardinality)
• 資料表中利用了直向的欄位來整理相同類型
和屬性的資料，稱為屬性(Attribute)。由於
屬性是直向的欄位，故稱為欄位(Column)
第二章 關聯式資料庫的架構
7
資料表的架構與資料型態(續)
• 資料表中的欄位管理相同種類或屬性的值，
這些由相同種類的值所組成的集合稱為值域
或定義域(Domain)。定義域是一組可以接受
值或合法值的集合，這些值是不可分割的單
元值(Atomic)。
第二章 關聯式資料庫的架構
8
關聯(Relation)
或
資料表(Table)
欄位(Column;Field) 或
屬性(Attribute)或
資料行(Column)
欄位名稱或
屬性名稱
列(Row) 或
值組(Tuple)或
資料錄(Record)
定義域/值域(Domain)
產品編號為 1001~ 9999
欄位值或
資料值
(Value)
圖 2.1 資料表的架構
資料表的架構
第二章 關聯式資料庫的架構
9
欄位資料型態
• 由於各家RDBMS所支援的欄位資料型態略
有所不同。以下以 MySQL 為例來說明各資
料型態的特性：
– 符號：
• M最大顯示位數。最大M值為255
• D小數點以下有效位數。最大D值為30，但D <= M-2
• []選擇性(可省略)
第二章 關聯式資料庫的架構
10
字串型態
位元型態
數值型態
數值型態
(浮點型態)
數值型態
字串型態
數值型態
CHAR
VARCHAR
BIT
BOOL
INTEGER
TINYINT
SMALLINT
BIGINT
DECIAML
NUMERIC
FLOAT
DOUBLE
REAL
DATE
日期時間型態 TIME
TIMESTAMP
時間間隔型態 INTERVAL
圖 2.1 資料表的架構
常見之欄位的資料型態分類
第二章 關聯式資料庫的架構
11
欄位資料型態(續)
• TINYINT[(M)] [UNSIGNED] [ZEROFILL]
– 極小型整數。有效範圍： -128 ～127，UNSIGNED時 0
～255。
• SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
– 小型整數。有效範圍： -32768 ～32767，UNSIGNED時
0 ～65535。
• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
– 中型整數。有效範圍： -8388608 to 8388607，
UNSIGNED時. 0 ～16777215。
第二章 關聯式資料庫的架構
12
欄位資料型態(續)
• INT[(M)] [UNSIGNED] [ZEROFILL]
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
(ANSI/ISO SQL92)
– 一般整數。有效範圍：-2147483648 ～
2147483647， UNSIGNED時0～ 4294967295。
• BIGINT[(M)] [UNSIGNED] [ZEROFILL]
– 大型整數。有效範圍：-9223372036854775808 ～
9223372036854775807，UNSIGNED時0
~18446744073709551615。
第二章 關聯式資料庫的架構
13
欄位資料型態(續)
• FLOAT[(M,D)] [ZEROFILL]
– 單精準浮點數。有效範圍： +/-3.4E+38 ～+/1.17E-38。
• DOUBLE[(M,D)] [ZEROFILL]
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
– 雙精準浮點數。有效範圍： +/-1.797E+308 ～+/2.22E-308。
第二章 關聯式資料庫的架構
14
欄位資料型態(續)
• DECIMAL[(M[,D])] [ZEROFILL]
(ANSI/ISO SQL92)
NUMERIC(M,D) [ZEROFILL]
– 使用非緊密(unpack)，即用字串的方式儲存具小
數點的數值資料，無法設定unsigned。
• DATE
– 日期，有效範圍為'1000-01-01' ～'9999-12-31'。
第二章 關聯式資料庫的架構
15
欄位資料型態(續)
• DATETIME
– 日期時間，合併日期和時間。有效範圍為‘100001-01 00:00:00’ ～‘9999-12-31 23:59:59’。
• TIMESTAMP[(M)]
– 時間戳記，有效範圍為‘1970-01-01 00:00:00’ 至
2037年。
• TIME
– 時間，預設顯示格式為 ‘HH:MM:SS’。
第二章 關聯式資料庫的架構
16
欄位資料型態(續)
• YEAR[(2|4)]
– 年序，4位時有效範圍為1901 to 2155, 及 0000。
• CHAR(M) [BINARY]
– 使用固定長度的方式儲存文字，不足位時右側補
空白。
• VARCHAR(M) [BINARY]
– 使用變動長度的方式儲存文字。其餘和CHAR一
樣。
第二章 關聯式資料庫的架構
17
欄位資料型態(續)
• BLOB
TEXT
– 變動長度之文字(TEXT；不分大小寫)或二位元
資料(BLOB；分大小寫)，最多為 65535 (2^16 1) 字元(64KB)。
• TINYBLOB
TINYTEXT
– 極小型TEXT或BLOB，最多為 255 (2^8 - 1) 字
元。
第二章 關聯式資料庫的架構
18
欄位資料型態(續)
• MEDIUMBLOB
MEDIUMTEXT
– 中型TEXT或BLOB，最多為 16777215 (2^24 - 1)
字元(16MB)。
• LONGBLOB
LONGTEXT
– 超長TEXT或BLOB，最多為 4294967295 (2^32 1) 字元(4GB)。
第二章 關聯式資料庫的架構
19
欄位資料型態(續)
• ENUM(‘value1’,‘value2’,...)(多選一)
– 列舉型資料，'value1', 'value2', ..., 或 NULL 中
選一。元素最多可以有65535個不同的列舉值。
• SET(‘value1’,‘value2’,...) (多選多)
– 集合，從'value1', 'value2', ..., ‘valueN’ 中可多
重組合或無，成員最多為64個。
第二章 關聯式資料庫的架構
20
欄位資料型態(其他)
• VARCHAR2 (Oracle)
變動長度之文字。
• CLOB (Oracle)
大型文字
• NCHAR/NVARCHAR/NTEXT/NCLOB
– 使用Unicode的文字資料型態。
第二章 關聯式資料庫的架構
21
CHAR(10)
M y S Q L
M D B S
使用長度 10
M y S Q L
使用長度 10
不足處補空白
A 1 2 0 0 3 4 5 6
使用長度 10
不足處補空白
VARCHAR(10)
M y S Q L
M D B S
使用長度 11
自動補上一個結束符號
M y S Q L
使用長度 6
A 1 2 0 0 3 4 5 6
使用長度 10
圖 2.3
CHAR VARCHAR的不同處
與 VARCHAR 的不同處
CHAR與
第二章 關聯式資料庫的架構
22
主鍵、候選鍵與替代鍵
• 在關聯式資料庫中，我們有時必須要從多個
候選鍵中挑選出一個候選鍵作為主要的鍵來
加以利用，這個鍵被稱為主鍵(Primary Key；
PK)。而未被挑選的其他候選鍵就稱為替代
鍵(Alternate Key)。
• 主鍵的鍵值需為具唯一性(不可重複)。
• 當候選鍵的屬性不只一個，而是多個屬性的
集合時，我們稱之為複合鍵(Composite Key)
或連結鍵(Concatenated Key)。
第二章 關聯式資料庫的架構
23
主鍵、候選鍵與替代鍵(續)
• 一般而言，可以當候選鍵的欄位大多具有特
殊的意義。
• 而每一個資料表，原則上，應該都設有一個
主鍵。
• 而候選鍵的選擇則必須滿足以下兩個條件：
– 唯一性(Uniqueness)：資料表中的鍵值欄位之
值必須是唯一的
– 最小性(Minimality)：鍵值欄位之屬性個數為最
小
第二章 關聯式資料庫的架構
24
主鍵的挑選原則
• 不可為空值(Not NULL)：鍵值欄位一定要有值，
不可以含有空值(NULL)。
• 永遠不會改變(Never Change)：鍵值欄位的值永遠
不會被改變，否則會難以管理和維護資料。
• 非識別值(Non-identifying Value)：鍵值欄位的值
不具有特別意義。
• 簡短且簡單的值(Brevity and Simplicity)：盡量選
取單一屬性及資料量較短的候選鍵作為主鍵，以節
省儲存空間及增進執行效率。
第二章 關聯式資料庫的架構
25
候選鍵
不適合當
候選鍵
候選鍵
不能當
候選鍵
員工檔
候選鍵
複合鍵:訂單編號+產品編號
訂單明細檔
圖 2.4 –候選鍵
候選鍵的選擇
需要具唯一性
第二章 關聯式資料庫的架構
26
外來鍵
• 外來鍵(Foreign Key; FK; 連外鍵) ：當欄位
的資料值為某資料表的主鍵的鍵值時，稱之
為外來鍵。
• 在關聯式資料庫中就是利用設定外來鍵來建
立關聯性，藉由設定外來鍵將可確保資料表
之間沒有矛盾值的存在，這稱為參考完整性
或參照完整性(Referential Integrity)。
• 當欄位的特性為主鍵或外來鍵時，稱為鍵值
欄位(Key)。
第二章 關聯式資料庫的架構
27
外來鍵(續)
• 以下是外來鍵的一些特性：
– 外來鍵一定參考其他資料表的主鍵欄位。
– 外來鍵和其參考的主鍵欄位具相同定義域，即相
同的資料型態和相同的欄位長度，但名稱則可以
不相同。
– 外來鍵參考的主鍵欄位可以是同一資料表中的主
鍵。如：員工檔中的直屬上司工號為員工檔中某
一員工之員工編號。
– 外來鍵可以是空值(NULL)。
第二章 關聯式資料庫的架構
28
人事資料
(未拆解前)
拆解
大量資料重複
關聯欄位:
部門編號
員工檔
部門檔
主鍵(PK)
外來鍵
(FK)
主鍵(PK)
少量資料重複
圖 2.5 主鍵鍵與外來鍵
主鍵(PK)與外來鍵(FK)
第二章 關聯式資料庫的架構
29
2.2 本書之範例資料庫說明
orders_tw.mdb 之資料庫關聯圖。
第二章 關聯式資料庫的架構
使用中文欄位名稱
30
orders.mdb 之資料庫關聯圖
第二章 關聯式資料庫的架構
使用英文欄位名稱
31
orders資料表的意義如下：
• 自動編號記錄檔(AutoNumber)：設計程式
時(可以不用理會這個檔)；用以儲存下一個
訂單之自動編號。
第二章 關聯式資料庫的架構
32
orders資料表的意義如下：(續)
• 郵遞區號檔(Postal)：用於儲存各郵遞區號
的行政區之資料。
• 部門檔(Departments)：用於儲存部門資料。
第二章 關聯式資料庫的架構
33
orders資料表的意義如下：(續)
• 員工檔(Employees)：用於儲存員工資料。
第二章 關聯式資料庫的架構
34
orders資料表的意義如下：(續)
• 客戶檔(Customers)：用於儲存客戶資料。
第二章 關聯式資料庫的架構
35
orders資料表的意義如下：(續)
• 供應商(Suppliers)：用於儲存供應商資料。
第二章 關聯式資料庫的架構
36
orders資料表的意義如下：(續)
• 產品檔(Products)：用於儲存產品資料。
第二章 關聯式資料庫的架構
37
orders資料表的意義如下：(續)
• 產品類別檔(Categories)：用於儲存產品類
別資料。
• 貨運公司：用於儲存貨運公司資料。
第二章 關聯式資料庫的架構
38
orders資料表的意義如下：(續)
• 訂單主檔：用於儲存訂單主檔(主要)資料。
第二章 關聯式資料庫的架構
39
orders資料表的意義如下：(續)
• 訂單明細檔：用於儲存訂單明細檔(明細)資
料。
第二章 關聯式資料庫的架構
40