翻轉電子書系列：資料庫系統概論(含邏輯設計)
翻轉工作室：粘添壽
第十一章 多表格資料庫設計
11-1 範例研討 – 客戶回應系統
『神氣飛昂網路行銷公司』期望在銷售網站上加入『客戶回應系統』，利用此它建立與客戶之間的溝統管道，也藉此探討客戶對公司的評價如何？期望系統的功能如下：
紀錄客戶反映訊息。
可查詢哪產品類別的反應訊息。
可依照日期查詢客戶訊息。
可依照客戶姓名、性別、年齡查詢回應訊息。
訊息包含滿意度調查： 1 ~ 5 分，5 分最高。
可分類評估某一類別產品滿意度如何。
可依製造廠商評估產品滿意度。
另外，系統分析師也到該公司收集了下列資料：
客戶資料：姓名、電話、性別、年齡、電子郵件、地址。
商品資料：製造廠商、產品分類(衣服、褲子、包包、、)。
11-1-2 資料庫邏輯設計
我們由外往內拆解方法，來分析此系統的邏輯架構。
(A) 系統需求
此系統僅提供客戶登陸回應訊息的功能，如下圖所示。
圖 11-1 客戶回應系統概況
(B) 系統 E-R 關係圖
此系統只要兩個實體，一者為客戶實體，另一者為訊息實體，我們將收集的資料依其屬性分別歸類於這兩實體上，如下圖所示：
圖 11-2 歸類兩實體
建立 E-R 圖重點如下：
客戶實體內沒有唯一識別值，產生一個屬性『客戶編碼』，並令為主鍵。
訊息實體上也沒有唯一識別值，也產生『訊息編碼』屬性當為主鍵。
為了讓兩實體之間產生關聯，在訊息實體上增加了一個外來鍵為『客戶編碼』，並參考到客戶實體的『客戶編碼』屬性。
同一位客戶允許發送多筆訊息，但某一訊息只允許一位客戶發送，是『多對一』關係。
得到該系統的 E-R 關係圖如圖 11-3 所示。
圖 11-3 系統E-R 關係圖
(A) 客戶實體轉換
客戶實體包含了 6 個屬性(Attribute)，且客戶編碼是唯一識別碼，並將它指定為主鍵(Primary key)。
圖 11-3 客戶實體
並且將客戶實體定名為 Customers 資料表，各屬性之欄規劃如下：
|
客戶編號 |
姓名 |
性別 |
年齡 |
|
地址 |
|
Cust_ID |
name |
sex |
age |
|
address |
|
CHAR(20) |
CHAR(20) |
CHAR(20) |
int |
CHAR(20) |
CHAR(50) |
正規化分析：
1NF：所有欄位都是最小單元(Atomic value)：符合。
2NF：除了主鍵外，所有欄位都與主鍵相依：符合。
3NF：除了主鍵外，所有欄位之間都沒有功能性相依：符合。
(B) 訊息實體轉換
客戶實體包含了 7 個屬性(Attribute)，且客戶編碼是唯一識別碼，並將它指定為主鍵(Primary key)。
圖 11-4 訊息實體
並且將訊息實體定名為 Messages 資料表，各屬性之欄規劃如下：
|
訊息編號 |
客戶編碼 |
日期 |
評分 |
製造商 |
產 品類別 |
訊息紀錄 |
|
Mess_ID |
Cust_ID |
date |
credit |
manufacture |
classify |
record |
|
int |
CHAR(20) |
DATE |
int |
CHAR(20) |
CHAR(20) |
CHAR(200) |
正規化分析：
1NF：所有欄位都是最小單元(Atomic value)：符合。
2NF：除了主鍵外，所有欄位都與主鍵相依：符合。
3NF：除了主鍵外，所有欄位之間都沒有功能性相依：符合。
設計完成各個資料表之後，再依照 E-R 關聯圖繪出資料庫內所有資料表之間的關聯圖，如下：
圖 11-5 資料庫關聯圖
我們將此資料庫定名為『Response_db』，並利用 AppServ – MySQL 建立，步驟如下：
(備註：僅介紹用 SQL 命令產生，選單方式建立請自行參考)
(A) 建立資料庫
吾人將此資料庫定名為：Response_db，則下列兩種方法產生，如下：
利用 SQL 命令為：
|
Create Database Response_db; Show databases; use Response_db; |
執行結果：
(B) 建立 Customers 資料表
建立 Customers 命令如下：(選擇 response_db 資料庫)
|
Create Table Customers ( Cust_ID CHAR(20) NOT NULL, name CHAR(20) NOT NULL, sex CHAR(20) NOT NULL, age int NOT NULL, Email CHAR(20) , address CHAR(50) , primary key (Cust_ID) ) |
如果設定完成之後，可觀察 Customers 資料表的結構如下：
其特性說明如下：
Customers 資料表共有 6 個欄位，分別是：Cust_ID，name，sex，age，Email與 address。
Cust_ID 欄位是主鍵，是系統自動產生的整數，也是個筆資料之間的唯一識別值(不允許有兩筆以上的紀錄，NO 欄位內容相同)。
Cust_ID、name、sex 與 age等欄位不允許空值，一定必須填入內容，其他兩個欄位允許空值。
(B) 建立 Messages 資料表
建立 Messages 命令如下如下：(選擇 response_db 資料庫)
|
Create Table Messages ( Mess_ID int auto_increment, Cust_ID CHAR(20) NOT NULL, date DATE , credit int , manufacture CHAR(20) , classify CHAR(20), record CHAR(200), primary key (Mess_ID), Foreign Key (Cust_ID) References Customers(Cust_ID) ) |
如果設定完成之後，可觀察 Messages 資料表結構，如下：
其特性說明如下：
Messages 資料表共有 7 個欄位，分別是：Mess_ID, Cust_ID, date, credit. Manufacture, classify與 record。
Mess_ID 欄位是主鍵，是系統自動產生的整數，也是個筆資料之間的唯一識別值(不允許有兩筆以上的紀錄，NO 欄位內容相同)。
Cust_ID 是外來鍵，參考到 Customers 資料表的 Cust_ID 欄位。
(C) Response_db 系統關聯圖
資料庫與資料表建立完成之後，需產生相關資料來驗證其運作是否能滿足需求。建立測試資料的步驟如下：
依照資料表內個欄位的屬性，利用 Excel 相關資料，注意：Excel 欄位的順序要與資料表欄位順相同。
將 Excel 建立資料後，再『另存新檔』，成『csv(逗點分隔)』格式。
利用『記事本』將 csv 檔開啟後，再另存新檔，編碼選擇『UFT 8』格式。
開啟資料庫，再匯入相關資料表內。
(A) 建立 Customers 資料並匯出 CSV 檔
首先開啟 Excel 並輸入相關資料(取名 Customers，第一行為欄位名稱)，另外需注意的是：『欄位順序要與資料表欄位順序相同』。如下圖所示：(可到教學網站下載)
轉換成 CSV 格式如下：
(B) 建立 Messages 資料並匯出 CSV 檔
利用 Excel 並輸入相關資料(取名 Messages，第一行為欄位名稱)，另外需注意的是：『欄位順序要與資料表欄位順序相同』。如下圖所示：(可到教學網站下載)
轉換成 CSV 格式如下：
匯入資料時必須考慮到資料表的外來鍵是否存在，不然會產生錯誤。由圖 11-7 Response_db 資料庫關聯看出來，匯入資料的順序是『Customers -> Messages』。
(A) 匯入 Customers 的 CSV 檔
由 Response_db 資料庫選擇 Customers 資料表並匯入 Customers CSV 檔，結果如下：
(B) 匯入 Messages 的 CSV 檔
由 Response_db 資料庫選擇 Messages 資料表並匯入 Messages CSV 檔，結果如下：
資料庫建構完成後，我們依照幾個系統需求，來驗證其功能是否滿足 ，還是需要參考資料庫的 E-R 關係圖如下：
(A) 驗證 1：查詢客戶回應訊息
查詢郭大豪先生的回應訊息如何，SQL 命令如下：
|
select * from messages where cust_ID = (SELECT Cust_ID from customers where name = "郭大豪"); |
執行結果：
(B) 驗證 2：查詢某家製造商的回應訊息
查詢有關真美公司的回應訊息如何，SQL 命令如下：
|
select * from messages where manufacture = "真美公司"; |
執行結果：
(C) 驗證 3：查詢滿意度的回應訊息
查詢滿意度低於 3 的相關回應訊息如何，SQL 命令如下：
|
select * from messages where credit < 3; |
執行結果：
(D) 驗證 4：依客戶年紀查詢
查詢年紀在 20 ~ 30 歲之間客戶的相關回應訊息如何，SQL 命令如下：
|
Select * From messages Where Cust_ID in (Select Cust_ID From customers Where age < 30 AND age > 20); |
執行結果：
(E) 驗證 5：依產品類別查詢
查詢客戶對本公司銷售『衣服』的滿意度低於 4 的相關回應訊息，SQL 命令如下：
|
select * from messages where classify = "衣服" and credit < 4; |
執行結果：
11-2 範例研討 – 圖書館管理系統
起初萬聖宮圖書室建立時，收藏圖書還不是很多，村民借書/還書都還不是很困難。但經過幾年之後，圖書室功能漸漸顯著，村民閱讀風氣業逐漸打開，除了各地捐輸之外，萬聖宮也採購許多書籍以供村民閱讀。如此一來，圖書室亦發展成規模不小的圖書館。萬聖宮總幹事有感於此，原來圖書管理系統已不符所需，期望建立一套較完整的圖書館管理系統，期望功能如下：
所有圖書皆登錄於該系統內。
可瀏覽圖書館內所有書籍。
可經由作者姓名、出版商或書名，查詢到那些書籍。
圖書可以任意增加或刪除。
每一本書都有固定位置，借書或還書都可以由固定區域存取。
可登錄村民借用那些書籍。
可催收逾期借用書籍。
可由書籍名稱查詢出哪位村民借用，以及借書日期。
另外，總幹事也希望系統能登錄下列相關資料：
書籍資料：書名、作者、出版社、放置位置、書籍分類、書籍來源、購買金額。
借書者：姓名、性別、電話、地址、電話。
圖書館也準備了借書卡，以供村民申請借書、還書之依據。
此系統有比較複雜一點，我們由外往內拆解方法，來分析此系統的邏輯架構。
(A) 第一層分析：系統需求
吾人可以發現，此管理系統提供圖書登錄與村民閱讀的功能，如下圖所示。
圖 11-6 圖書館管理系統概況
(B) 第二層分析：圖書分類
首先，我們先拆解圖書出版狀況，每一本書大多由某一家出版社發行，分析如下：
(C) 第三層分析：村民借用圖書閱讀
村民借用圖書閱讀之關聯。我們假設一位村民可以借用多本圖書，則村民與圖書總表之間的關係是一對多的關係。另外，一家出版商也可能發行多本圖書，之間也是一對多的關係。每位村民也可以多次借書，村民與借書紀錄之間也是一對多的關係。如下分析：
(D) 填入各實體的屬性
接著我們依照系統所需的資料填入各個實體上，如下圖：
(E) 選定各實體的主鍵
對各實體的主鍵選定步驟如下：
圖書總表：將所有屬性填入後，找不到唯一識別值，增加『圖書編碼』屬性，並指定為主鍵。
出版商：增列『出商編碼』屬性，並指定為主鍵。
村民：增列『身分證字號』屬性，並指定為主鍵。
借書紀錄：因每位村民可以同時借用多本書，增列 {身分證號、圖書編碼} 兩屬性，並指定為主鍵。
(F) 建立外來鍵連結 – 完整系統 E-R 關聯圖
在圖書總表增加『出版編碼』為外來鍵，並參考到出版商的『出商編碼』，建立兩實體之間關係。
且將借書紀錄的『身分證號』設定為外來鍵，並參考到村民實體的『身分證號』，建立借書紀錄與村民實體之間的關係。
且將借書紀錄的『圖書編碼』設定為外來鍵，並參考到圖書總表實體的『圖書編碼』，建立借書紀錄與圖書總表之間的關係。
完成後結果，如下圖所示：
圖 11-11 E-R 關聯圖
(A) 圖書總表關聯
下圖為圖書總表的關聯(Relation)，它包含了 6 個屬性(Attribute)，我們找出圖書編碼可以當作每一筆紀錄的唯一識別碼，並將它指定為主鍵(Primary key)。
圖 11-12 圖書總表關聯
並且將此關聯定名為 Books 資料表，各屬性之欄規劃如下：
|
圖書編號 |
圖書名稱 |
作者 |
出版商編碼 |
備註 |
價格 |
放置位置 |
|
Book_ID |
title |
author |
Pub_ID |
remark |
price |
place |
|
int |
CHAR(50) |
CHAR(20) |
CHAR(20) |
CHAR(50) |
int |
CHAR(20) |
其中，備註欄位記錄書籍來源(採購、捐贈)或其他訊息。價格該本書購買金額或捐贈人認為價值多少錢。
正規化分析：
1NF：所有欄位都是最小單元(Atomic value)：符合。
2NF：除了主鍵外，所有欄位都與主鍵相依：符合。
3NF：除了主鍵外，所有欄位之間都沒有功能性相依：符合。
(B) 出版商關聯
出版商關聯(Relation)包含了 5 個屬性(Attribute)，我們找出版商編碼可以當作每一筆紀錄的唯一識別碼，並將它指定為主鍵(Primary key)。
圖 11-13 出版商關聯
並且將此關聯定名為 Publishers 資料表，各屬性之欄規劃如下：
|
出版商編碼 |
出版商名稱 |
聯絡人 |
電話 |
地址 |
|
Pub_ID |
pub_name |
contact |
tel |
address |
|
int |
CHAR(20) |
CHAR(20) |
CHAR(20) |
CHAR(50) |
其中，書籍來源有採購與捐贈兩種可能。價格該本書購買金額或捐贈人認為價值多少錢。
正規化分析：
1NF：所有欄位都是最小單元(Atomic value)：符合。
2NF：除了主鍵外，所有欄位都與主鍵相依：符合。
3NF：除了主鍵外，所有欄位之間都沒有功能性相依：符合。
(C) 村民關聯
村民關聯(Relation)包含了 5 個屬性(Attribute)，很容易地可以看出，選用身分證字號當為主鍵(Primary key)。
圖 11-14 村民關聯
並且將此關聯定名為 Guests 資料表，各屬性之欄規劃如下：
|
身分證字號 |
姓名 |
性別 |
電話 |
地址 |
|
Guest_ID |
name |
sex |
tel |
address |
|
CHAR(20) |
CHAR(20) |
CHAR(20) |
CHAR(20) |
CHAR(50) |
其中，書籍來源有採購與捐贈兩種可能。價格該本書購買金額或捐贈人認為價值多少錢。
1NF：所有欄位都是最小單元(Atomic value)：符合。
2NF：除了主鍵外，所有欄位都與主鍵相依：符合。
3NF：除了主鍵外，所有欄位之間都沒有功能性相依：符合。
(C) 借書紀錄關聯
借書紀錄關聯(Relation)包含了 3 個屬性(Attribute)。系統允許每位村民同時可以借用多本書籍，因此不能只用身分證字號當主鍵，必須 {身分證字號, 書籍編碼}兩欄位當為主鍵(Primary key)。
圖 11-15 借書紀錄關聯
並且將此關聯定名為 Records 資料表，各屬性之欄規劃如下：
|
身分證字號 |
圖書編號 |
借書日期 |
|
Guest_ID |
Book_ID |
date |
|
CHAR(20) |
CHAR(20) |
DATE |
1NF：所有欄位都是最小單元(Atomic value)：符合。
2NF：除了主鍵外，所有欄位都與主鍵相依：符合。
3NF：除了主鍵外，所有欄位之間都沒有功能性相依：符合。
設計完成各個資料表之後，再依照 E-R 關聯圖繪出資料庫內所有資料表之間的關聯圖，如下：
圖 11-16 資料庫關聯圖
我們將此資料庫定名為『temple_library』，並利用 AppServ – MySQL 建立，步驟如下：(備註：僅介紹用 SQL 命令產生，選單方式建立請自行參考)
(A) 建立資料庫
吾人將此資料庫定名為：temple_library，則下列兩種方法產生，如下：
利用 SQL 命令為：
|
Create Database temp_library; |
利用選單方式：
(B) 建立 Books 資料表
建立 Books 命令如下如下：(選擇 temple_library 資料庫)
|
Create Table Books ( Book_ID int auto_increment, title CHAR(50) NOT NULL, author CHAR(20) NOT NULL, Pub_ID int NOT NULL, price int, place CHAR(20) , remark CHAR(50) , primary key (Book_ID) ) |
如果設定完成之後，可觀察 Books 資料表的結構如下：
其特性說明如下：
Books 資料表共有 7 個欄位，分別是：Book_ID，author，Pub_ID，title，place、price與 remark。
Book_ID 欄位是主鍵，是系統自動產生的整數，也是個筆資料之間的唯一識別值(不允許有兩筆以上的紀錄，NO 欄位內容相同)。
Book_ID、author、Pub_ID 與 title等欄位不允許空值，一定必須填入內容，其他三個欄位允許空值。
(B) 建立 Publishers 資料表
建立 publishers 命令如下如下：(選擇 temple_library 資料庫)
|
Create Table publishers ( Pub_ID int auto_increment, Pub_name CHAR(50) NOT NULL, contact CHAR(20) , tel CHAR(20) , address CHAR(50) , primary key (Pub_ID) ) |
如果設定完成之後，可觀察 Publishers 資料表結構，如下：
其特性說明如下：
publishers 資料表共有 5 個欄位，分別是：Pub_ID，Pub_name，contact，tel與 address。
Pub_ID 欄位是主鍵，是系統自動產生的整數，也是個筆資料之間的唯一識別值(不允許有兩筆以上的紀錄，NO 欄位內容相同)。
Pub_ID與Pub_name欄位不允許空值，一定必須填入內容，其他三個欄位允許空值。
(C) 建立 Guests 資料表
建立 Guests 命令如下如下：(選擇 temple_library 資料庫)
|
Create Table Guests ( Guest_ID CHAR(20) NOT NULL, name CHAR(20) NOT NULL, sex CHAR(20) , tel CHAR(20) , address CHAR(50) , primary key (Guest_ID) ) |
如果設定完成之後，可觀察 Guests 資料表結構，如下：
其特性說明如下：
Guests 資料表共有 5 個欄位，分別是：Guest_ID，name，sex，tel與 address。
Guest_ID 欄位是主鍵，是系統自動產生的整數，也是個筆資料之間的唯一識別值(不允許有兩筆以上的紀錄，NO 欄位內容相同)。
Guest_ID與name欄位不允許空值，一定必須填入內容，其他三個欄位允許空值。
(D) 建立 Records 資料表
建立 Records 命令如下如下：(選擇 temple_library 資料庫)
|
Create Table Records ( Guest_ID CHAR(20) NOT NULL, Book_ID int NOT NULL, date DATE, primary key (Guest_ID, Book_ID) ) |
如果設定完成之後，可觀察 Records 資料表結構如下：
其特性說明如下：
Records 資料表共有 3 個欄位，分別是：Guest_ID，Book_ID與 date。
Guest_ID 與 Book_ID 兩欄位組合成主鍵。
(D) 觀察 Temple_Library 資料庫結構
建立完成四個資料表之後，可以觀察 Temple_library 資料庫的結構如何，如下：
(E) 建立資料表之間關連
我們依照圖 11-8 資料庫關聯圖內，可以觀察到各個資料表之間的關聯，如下：
Books 資料表的 Pub_ID 是外來鍵(Foreign Key)，是參考到 Publishers 資料表的 Pub_ID 欄位。
Records 資料表的 Book_ID 是外來鍵，是參考到 Books 資料表的 Book_ID 欄位。
Records 資料表的 Guest_ID 是外來鍵，是參考到 Guests 資料表的 Guest_ID 欄位。
建立外來鍵命令如下：
|
Alter Table Books ADD Foreign key (Pub_ID) References publishers(Pub_ID); Alter Table Records ADD Foreign key (Book_ID) References Books(Book_ID); Alter Table Records ADD Foreign key (Guest_ID) References Guests(Guest_ID); |
執行成功後，點選 AppServ 的『設計』選單，可以觀察到 Temple_Library 資料內各個資料表之間的關聯，如下：
資料庫與資料表建立完成之後，需產生相關資料來驗證其運作是否能滿足需求。建立測試資料的步驟如下：
依照資料表內個欄位的屬性，利用 Excel 相關資料，注意：Excel 欄位的順序要與資料表欄位順相同。
將 Excel 建立資料後，再『另存新檔』，成『csv(逗點分隔)』格式。
利用『記事本』將 csv 檔開啟後，再另存新檔，編碼選擇『UFT 8』格式。
開啟資料庫，再匯入相關資料表內。
(A) 建立 Books 資料並匯出 CSV 檔
首先開啟 Excel 並輸入相關資料(取名 Books，第一行為欄位名稱)，如下圖所示：(可到教學網站下載)
轉換成 CSV 格式如下：
(B) 建立 Publishers 資料並匯出 CSV 檔
利用 Excel 建立 Publishers 檔案內容如下：
轉換成 CSV 檔內容如下：
(C) 建立 Guests 資料並匯出 CSV 檔
利用 Excel 建立 Guests 檔案內容如下：
轉換成 CSV 檔內容如下：
(D) 建立 Records 資料並匯出 CSV 檔
利用 Excel 建立 Records 檔案如下：
轉換成 CSV 檔內容如下：
匯入資料時必須考慮到資料表的外來鍵是否存在，不然會產生錯誤。由圖 11-8 Temple_library 資料庫關聯看出來，匯入資料的順序是『Publishers -> Books -> Guests -> Records』。
(A) 匯入 Publishers 的 CSV 檔
由 Temple_Library 資料庫選擇 Publishers 資料表並匯入 Publishers CSV 檔，結果如下：
(B) 匯入 Books 的 CSV 檔
由 Temple_Library 資料庫選擇 Books 資料表並匯入 Books CSV 檔，結果如下：
(C) 匯入 Guests 的 CSV 檔
由 Temple_Library 資料庫選擇 Guests 資料表並匯入 Guests CSV 檔，結果如下：
(D) 匯入 Records 的 CSV 檔
由 Temple_Library 資料庫選擇 Records 資料表並匯入 Records CSV 檔，結果如下：
資料庫建構完成後，我們依照幾個系統需求，來驗證其功能是否滿足。(但還需要參考該資料庫的 E-R 關係圖)
(A) 驗證 1：查詢某位作者有哪些藏書
查詢圖書館有收藏作者粘添壽那些書籍，請書籍名稱、出版社與價格，SQL 命令如下：
|
select A.title, B.pub_name, A.price from Books as A, publishers as b where A.author ="粘添壽" And A.Pub_ID = B.Pub_ID; |
執行結果：
(B) 驗證 2：查詢村民借用那些書，以及借書日期
查詢郭大偉先生借了那些書籍，以及借用日期，SQL 命令如下：
|
select A.title, B.date from books as A, (select Book_ID, date from records where Guest_ID = (select guest_ID from guests where name = "郭大豪")) as B where A.Book_ID = B.Book_ID; |
執行結果：
(C) 驗證 3：由圖書公司查詢有哪些書
查詢圖書館有那些翻轉工作室出版的書籍，請列印出書名、作者與價格，SQL 命令如下：
|
select title, author, price from books where pub_ID = (select Pub_ID from publishers where pub_name = "翻轉工作室"); |
執行結果：
(D) 驗證 4：查詢書本出借狀況
查詢『資料庫程式設計』這本書出借的情況，如已出借請列印出借書人與日期。
|
select A.name, B.date from guests as A, (select Guest_ID, date from records where Book_ID = (select Book_ID from books where title = "資料庫程式設計")) as B where A.guest_ID = B.guest_ID; |
執行結果：
(D) 驗證 5：更新 Records 資料表
測試當村民編號 Guest_ID = A013 借用書籍編號 Book_ID = 12 時，更新 Records 資料表，SQL 命令如下：
|
Insert into records values("A013", "12", ""); update records set date = (select date(now())) where Guest_ID = "A013" and Book_ID = "12"; |
接著查詢 Records 資料表是否有增列此筆資料，SQL 命令如下：
|
select * from records where Guest_ID = "A013"; |
執行結果：
11-3-1 級聯 (Cascade)
『外來鍵』(Foreign Key) 表示資料表某一欄位參考到另一資料表的某一欄位，但該欄位大多是被參考資料表的『主鍵』(Primary Key)。亦是，外來鍵是跟隨另一資料表的主鍵而生，當被參考的主鍵內的『值』被刪除或更新時，外來鍵相對的該『值』，是否隨著被刪除或更新，這就是『Cascade』的問題。但發生 Cascade 狀況並非僅兩個資料表之間，也許會發生在多個資料表之間。吾人以『Temple_Library』資料庫為例，下圖是它的 E-R 關聯圖，從中可以看出，Records 的 Book_ID 是主鍵也是外來鍵，它參考到 Books 的 Book_ID 欄位，又 Books 的 Pub_ID 欄位是拽來鍵，參考到 Publishers 表的 Pub_ID 欄位。
Pub_ID (Publishers) -> Pub_ID (Books) -> Book_ID (Books) -> Book_ID (Records)
當 publishers 表中的『翻轉工作室』出版社被更新或刪除時，是否 Books 表中有『翻轉工作室』的 Pub_ID 的紀錄是否要隨著被更新或刪除，又當 Books 表的相關紀錄被更新或刪除時，Records 的相關紀錄是否又隨之更改，因此稱之為『級聯』(Cascade)。
MySQL 資料庫的儲存引擎有兩種型態：MyISAM 與 InnoDB 兩種型態，前者是早期版本，目前較少用。對於多人使用、安全性要求較高的環境大多使用 InnoDB 型態，才具有 Cascade 之功能，目前在 MySQL 建立表格時，預設型態都指定 InnoDB 型態(功能比較請自行參考其他資料)，如下圖所示
宣告 Cascade 語法如下：(在 Foreign Key 語句增加)
|
Create Table Table_name ( 欄位宣告, ....,
Foreign Key .. References .. On Delete {Cascade | Set Null | Not Action| Restrict} Foreign Key .. References .. On Update {Cascade | Set Null | Not Action| Restrict} ) Engine InnoDB; |
選項敘述如下：
Ø On Delete 或 On Update：當發生刪除或更新動作時，依後面宣告處理。
Ø Cascade：所有關聯的紀錄也會跟隨刪除或更新。
Ø Set Null：將所有關聯的紀錄設定成 Null。(對應欄位不可以 Not Null)
Ø No Action：即是不處理。(預設值)(系統會禁止刪除或更新動作)
Ø Restrict：與 No Action 相同。
原來 temp_library 資料庫中表格並沒有規劃 Cascade 功能，吾人需要將原來設定的外來鍵刪除後，再重新建立。MySQL 對於刪除外來鍵有所限制，須找出所該外來鍵的 "Foreign Key id"，方法是：由桌面 => 開始 => 由選單中找出 AppServ => MySQL Command Line，如下圖所示。
進入交談式視窗後，輸入密碼 (12345678)，接著如下操作，則能查出 Books 與 Records 表外來鍵的識別碼。
|
Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. mysql> use temp_library; Database changed mysql> show create table books; .... | books | CREATE TABLE `books` ( `book_ID` int(11) NOT NULL AUTO_INCREMENT, `title` char(50) NOT NULL, `author` char(20) NOT NULL, `Pub_ID` int(11) NOT NULL, `price` int(11) DEFAULT NULL, `place` char(20) DEFAULT NULL, `remark` char(50) DEFAULT NULL, PRIMARY KEY (`book_ID`), KEY `Pub_ID` (`Pub_ID`), CONSTRAINT `books_ibfk_1` FOREIGN KEY (`Pub_ID`) REFERENCES `publishers` (`pub_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | ..... mysql> show create table records; ..... records | CREATE TABLE `records` ( `Guest_ID` char(20) NOT NULL, `Book_ID` int(11) NOT NULL, `date` date DEFAULT NULL, PRIMARY KEY (`Guest_ID`,`Book_ID`), KEY `Book_ID` (`Book_ID`), CONSTRAINT `records_ibfk_1` FOREIGN KEY (`Book_ID`) REFERENCES `books` (`book_ID`), CONSTRAINT `records_ibfk_2` FOREIGN KEY (`Guest_ID`) REFERENCES `guests` (`Guest_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | ..... |
回到 phpMyAdmin 網頁則以 SQL 命令刪除外來鍵如下：
|
Alter Table Books Drop Foreign key `books_ibfk_1`; Alter Table Records Drop Foreign key `records_ibfk_1`; Alter Table Records Drop Foreign key `records_ibfk_2`; |
刪除完外來鍵之後，由 AppServ 功能選單內『設計』可觀察到各資料表之間都沒有聯繫。
(下載已建立完成 Temple-Library 資料庫)
吾人在『 Temple-Library』資料庫具有 Cascade 功能。吾人希望系統具有下類規範：
1. Books 外來鍵(Pub_ID) 沒有宣告 Cascade 功能 (預設值為 Not Action)，則表示當 Publishers 表內記錄有被 Books 表參考到時，則不允許刪除或更新，除非先除刪除 Books 記錄，再刪除 Publishers 記錄。(Update/Delete Not Action)
2. Books 表中某筆資料被刪除，則 Records 表內相關紀錄也被刪除。(Delete Cascade)
3. Guests 表中某筆記錄(Guest_ID) 變更時，Records 表內相關紀錄也隨之變更。(Update cascade)
4. Guests 表內某筆記錄被刪除時，則 Records 表內相對的紀錄也被刪除。(Delete Cascade)
(A) 外來鍵設定
依照上述規範，吾人規劃外來鍵具有 Cascade 的命令如下：
|
/* 第一條規範 */ Alter Table Books ADD Foreign key (Pub_ID) References publishers(Pub_ID); /* 第二條規範 */ Alter Table Records ADD Foreign key (Book_ID) References Books(Book_ID) On Delete Cascade; /* 第三、四條規範 */ Alter Table Records ADD Foreign key (Guest_ID) References Guests(Guest_ID) On Delete Cascade On Update Cascade; |
重新建立之後，可觀察到各資料表之間的關係圖，如下：
(B) 驗證：Not Action (第一條規範)
由 Publishers 表中刪除某一出版社，觀察是否允許刪除，如不行，則先刪除 Books 有參考到記錄，再刪除 Publishers 中記錄。
觀察 Books 是否有『翻轉工作室』(Pub_ID = 3)的相關紀錄，再刪除 Publishers 表『真美出版公司』(Pub_ID = 3)，記錄是否允許。
n 首先 Books 表內是否有『真美出版公司』的相關紀錄。如有，則刪除 publishers 表內『真美出版公司』記錄是否可行。
|
select * from books where Pub_id = (select Pub_ID from publishers where pub_name = "翻轉工作室");
Delete from publishers where pub_name = "翻轉工作室";
(無法刪除，表示該筆記錄有被其它表格參考到。 Not Action 功能) |
n 吾人必須先刪除掉 Books 有參考到 publishers 表『翻轉工作室』的所有記錄，才允許刪除 publishers 表內的『翻轉工作室』記錄。
|
Delete from Books where pub_id = (select Pub_ID from publishers where pub_name = "翻轉工作室"); select * from Books where pub_id = (select Pub_ID from publishers where pub_name = "翻轉工作室");
傳回空值，表示 Books 表內相關 "翻轉工作室" 記錄被刪除 Delete from publishers where pub_name = "翻轉工作室";
select * from publishers where pub_name = "翻轉工作室";
傳回空值，表示 Publishers 表內 "翻轉工作室" 記錄被刪除 |
(C) 驗證：刪除某一書籍，Records 表是否隨之改變。(第二條規範)
由 Books 表中刪除『紅樓夢』書籍，觀察 Records 是否還有人租用該本書。
n 首先查詢『三國演義』的 Book_id 為何。
|
select Book_ID from Books where title = "三國演義";
|
n 再由 Books 表內刪除 Book_id = 1 的記錄，之後觀察 Records 記錄中是否還有接用該本書的記錄，如果沒有則表示 Records 跟隨著刪除相關紀錄，符合 Delete Cascade 之功能。
|
delete from Books where Book_id = "1"; select * from Records where Book_id= "1";
傳回空值，表示 Delete Cascade 正確 |
(D) 驗證：變更某 Guest_ID，Records 表是否隨之改變。(第三條規範)
將『張彥宏』先生的 Guest_ID 改為 B002，觀察 Records 表內相關紀錄是否被更改。
n 首先查詢『張彥宏』的 Guest_id 為何，並觀察他的借書紀錄(Records)。
|
select guest_ID from Guests where name= "張彥宏";
select * from records where guest_id = (select guest_ID from Guests where name= "張彥宏");
|
n 再由 Guests 表將『張彥宏』的 Guest_id 改為 B002，再觀察他的借書紀錄是否有所改變。之後觀察 Records 記錄中『張彥宏』借書記錄是否有改變，如果沒有則表示符合 Update Cascade 之功能。
|
update Guests set guest_id = "B002" where name= "張彥宏"; select * from records where guest_id = (select guest_ID from Guests where name= "張彥宏");
|
(E) 驗證：刪除某 Guest_ID，Records 表紀錄是否跟隨刪除。(第四條規範)
由 Guests 表中將『張彥宏』(Guest_ID=B002)先生記錄刪除，觀察 Records 表內是否還有它的借書紀錄。
n 首先查詢『張彥宏』的 Guest_id 為何，並觀察他的借書紀錄(Records)。
|
select guest_ID from Guests where name= "張彥宏";
select * from records where guest_id = (select guest_ID from Guests where name= "張彥宏");
|
n 再由 Guests 表中刪除『張彥宏』記錄，再觀察 Records 記錄中『張彥宏』(Guest_id = "B002"是否還存在，如果沒有則表示符合 Delete Cascade 之功能。
|
delete from Guests where Guest_id = "B002";
select * from records where guest_id = " B002";
|
11-4 自我挑戰
(A) 系統需求與規格
『大石精密公司』接受委託製造各種生產機械，目前大約有 300 位員工，老闆期望建立一套人事管理系統，希望有下列功能：
可登錄、刪除或更新員工資料。
可查詢每位員工服務部門、底薪、職務加級、加班時數。
每月可計算員工薪資表。
可查詢各部門負責人、員工名單，並計算各部門薪資結構如何。
全公司內員工的薪點、加級與津貼都有統一計算標準。
(B) 資料收集
系統分析師到現場收集到商品資料如下：
員工資料有：員工編號、姓名、地址、電話、性別、出生日期、服務部門、薪點、與職務。
薪資類別：薪點、薪點金額、加級金額、加級基點。
服務部門：部門名稱、部門地區(高雄、台北、上海、、)。
(C) 完成事項
(1) 資料庫邏輯設計。
(2) E-R 關係圖轉換資料表與正規化。
(3) 實體建置。
(4) 匯入測試資料。
(5) 測試資料庫功能。
(6) 資料庫效能分析。
(C) 提示
以下是提示資料庫關聯圖，並非完整設計。
(A) 系統需求與規格
『研技半導體設計公司』是接受委託設計多項功能半導體的公司，目前大約有 500 位員工，每天透過電腦設計半導體結構，公司有感於員工過於忙碌，需要有適當休閒活動以調適心身，才能發揮最大工作效益。在公司裡成立許多休閒活動讓同仁參加，但已希望了解每位同仁參與程度，才能給予適當輔導。因此，委託建立一套員工休閒活動管理系統，希望有下列功能：
員工可以到各項活動社團報名參加。
可查詢員工參加了那些社團，並可量化員工身心調適量多寡。
可查詢社團裡有哪些員工參加，並可計算社團花費成本多寡。
可查詢調適量過低或過高的員工。
可查詢花費成本過高或過低的社團。
(B) 資料收集
系統分析師到現場收集到員工與社團資料如下：
員工資料有：員工編號、姓名、地址、電話、性別、出生日期。
社團資料：社團名稱、每位參與者費用、身心調適量。
(C) 提示
(D) 完成事項
(1) 資料庫邏輯設計。
(2) E-R 關係圖轉換資料表與正規化。
(3) 實體建置。
(4) 匯入測試資料。
(5) 測試資料庫功能。
(6) 資料庫效能分析。
(A) 系統需求與規格
『真善美健康美食餐廳』是一家提供健康美食的專屬餐廳，期望建立一套餐廳管理系統，希望有下列功能：
按照桌次點菜，點菜完畢後除了顯示金額外，還顯示卡路里的總和。
可隨時查詢桌次菜單。
會計部門可依照桌次收費。
可統計分析各樣菜被點用的狀況。
(B) 資料收集
系統分析師到現場收集到該餐廳的資料如下：
菜單資料：菜單名稱、單價(大、中、小)、卡路里(大、中、小)。
桌次：1 ~ 10 桌。
(C) 完成事項
(1) 資料庫邏輯設計。
(2) E-R 關係圖轉換資料表與正規化。
(3) 實體建置。
(4) 匯入測試資料。
(5) 測試資料庫功能。
(6) 資料庫效能分析。
(A) 系統需求與規格
『長青高爾夫球場』期望建立一套會員管理系統，紀錄與分析員會繳費與消費情況如何，希望有下列功能：
可登錄會員資料與查詢繳費狀況。
可查詢會員消費狀況。
可查詢消費最高與最低會員，消費較高會員可以升級會員等級，享受不同待遇。
(B) 資料收集
系統分析師到現場收集到的資料如下：
會員資料有：姓名、地址、電話、性別、地址、出生日期。
會員等級：金卡(年費 2 萬元)、白金卡(年費 1.5 萬元)、貴賓卡(年費 1 萬元)。
臨場消費：假日：1200 元、非假日：1000 元。
(C) 提示
(D) 完成事項
(1) 資料庫邏輯設計。
(2) E-R 關係圖轉換資料表與正規化。
(3) 實體建置。
(4) 匯入測試資料。
(5) 測試資料庫功能。
(6) 資料庫效能分析。
Book_ID
傳回空值，表示
