資料庫系統概論：第 十二章 多表格資料庫設計       下一頁

第十 二章 多表格資料庫設計

 

12-1 範例研討 – 客戶回應系統

 內容：

12-1-1 需求分析與資料收集

『神氣飛昂網路行銷公司』期望在銷售網站上加入『客戶回應系統』，利用此它建立與客戶之間的溝統管道，也藉此探討客戶對公司的評價如何？期望系統的功能如下：

  • 紀錄客戶反映訊息。

  • 可查詢哪產品類別的反應訊息。

  • 可依照日期查詢客戶訊息。

  • 可依照客戶姓名、性別、年齡查詢回應訊息。

  • 訊息包含滿意度調查： 1 ~ 5 分，5 分最高。

  • 可分類評估某一類別產品滿意度如何。

  • 可依製造廠商評估產品滿意度。

另外，系統分析師也到該公司收集了下列資料：

  • 客戶資料：姓名、電話、性別、年齡、電子郵件、地址。

  • 商品資料：製造廠商、產品分類(衣服、褲子、包包、、)。

12-1-2 資料庫邏輯設計

我們由外往內拆解方法，來分析此系統的邏輯架構。

(A) 系統需求

此系統僅提供客戶登陸回應訊息的功能，如下圖所示。

圖 11-1 客戶回應系統概況

(B) 系統 E-R 關係圖

此系統只要兩個實體，一者為客戶實體，另一者為訊息實體，我們將收集的資料依其屬性分別歸類於這兩實體上，如下圖所示：

圖 11-2 歸類兩實體

建立 E-R 圖重點如下：

  • (a) 客戶實體內沒有唯一識別值，產生一個屬性『客戶編碼』，並令為主鍵。

  • (b) 訊息實體上也沒有唯一識別值，也產生『訊息編碼』屬性當為主鍵。

  • (c) 為了讓兩實體之間產生關聯，在訊息實體上增加了一個外來鍵為『客戶編碼』，並參考到客戶實體的『客戶編碼』屬性。

  • (d) 同一位客戶允許發送多筆訊息，但某一訊息只允許一位客戶發送，是『多對一』關係。

得到該系統的 E-R 關係圖如圖 11-3 所示。

圖 11-3 系統E-R 關係圖

12-1-3 E-R 圖轉換資料表

(A) 客戶實體轉換

客戶實體包含了 6 個屬性(Attribute)，且客戶編碼是唯一識別碼，並將它指定為主鍵(Primary key)。

圖 11-3 客戶實體

並且將客戶實體定名為 Customers 資料表，各屬性之欄規劃如下：

客戶編號

姓名

性別

年齡

E-Mail

地址

 Cust_ID

name

sex

age

Email

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：除了主鍵外，所有欄位之間都沒有功能性相依：符合。

12-1-4 資料庫關聯圖

設計完成各個資料表之後，再依照 E-R 關聯圖繪出資料庫內所有資料表之間的關聯圖，如下：

圖 11-5 資料庫關聯圖

12-1-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 資料表的結構如下：

其特性說明如下：

  • (a) Customers 資料表共有 6 個欄位，分別是：Cust_ID，name，sex，age，Email與 address。

  • (b) Cust_ID 欄位是主鍵，是系統自動產生的整數，也是個筆資料之間的唯一識別值(不允許有兩筆以上的紀錄，NO 欄位內容相同)。

  • (c) 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 資料表結構，如下：

其特性說明如下：

  • (a) Messages 資料表共有 7 個欄位，分別是：Mess_ID, Cust_ID, date, credit. Manufacture, classify與 record。

  • (b) Mess_ID 欄位是主鍵，是系統自動產生的整數，也是個筆資料之間的唯一識別值(不允許有兩筆以上的紀錄，NO 欄位內容相同)。

  • (c) Cust_ID 是外來鍵，參考到 Customers 資料表的 Cust_ID 欄位。

(C) Response_db 系統關聯圖

12-1-6 建立測試資料

資料庫與資料表建立完成之後，需產生相關資料來驗證其運作是否能滿足需求。建立測試資料的步驟如下：

  • (a) 依照資料表內個欄位的屬性，利用 Excel 相關資料，注意：Excel 欄位的順序要與資料表欄位順相同。

  • (b) 將 Excel 建立資料後，再『另存新檔』，成『csv(逗點分隔)』格式。

  • (c) 利用『記事本』將 csv 檔開啟後，再另存新檔，編碼選擇『UTF 8』格式。

  • (d) 開啟資料庫，再匯入相關資料表內。

(A) 建立 Customers 資料並匯出 CSV 檔

首先開啟 Excel 並輸入相關資料(取名 Customers，第一行為欄位名稱)，另外需注意的是：『欄位順序要與資料表欄位順序相同』。如下圖所示：(可到教學網站下載)

轉換成 CSV 格式如下：

(B) 建立 Messages 資料並匯出 CSV 檔

利用 Excel 並輸入相關資料(取名 Messages，第一行為欄位名稱)，另外需注意的是：『欄位順序要與資料表欄位順序相同』。如下圖所示：(可到教學網站下載)

轉換成 CSV 格式如下：

12-1-7 匯入測試資料

 匯入資料時必須考慮到資料表的外來鍵是否存在，不然會產生錯誤。由圖 11-7 Response_db 資料庫關聯看出來，匯入資料的順序是『Customers -> Messages』。

(A) 匯入 Customers 的 CSV 檔

由 Response_db 資料庫選擇 Customers 資料表並匯入 Customers CSV 檔，結果如下：

(B) 匯入 Messages 的 CSV 檔

由 Response_db 資料庫選擇 Messages 資料表並匯入 Messages CSV 檔，結果如下：

12-1-8 測試資料庫功能

資料庫建構完成後，我們依照幾個系統需求，來驗證其功能是否滿足 ，還是需要參考資料庫的 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;

執行結果：

翻轉工作室：粘添壽

 

資料庫系統概論(含邏輯設計)

 

 

翻轉電子書系列：