翻轉電子書系列：資料庫系統概論(含邏輯設計)  

翻轉工作室：粘添壽

 

第十章 單表格資料庫設計

10-1 範例研討 – 圖書管理系統

在鄉下許多善心人士在廟宇內建立圖書館，讓當地人士有一個閱讀交誼的地方，但各地捐贈圖書眾多，當村民借用圖書時，產生許多困擾期望建立一套圖書管理系統。

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

(A) 需求與系統規格

當圖書很多的情況，我們要找一本書也許變得非常困難，我們希望『圖書管理系統』具有下列功能：

(B) 資料收集

系統分析師到現場收集資料如下：

10-1-2 資料庫邏輯設計 – Books_db

(A) 系統概況

我們將此資料庫命名為 Books_db，它僅登錄圖書資料，概況如圖 10-1 所示。由此可見只要一個關聯(資料表)，即可達成所需。

圖 10-1 圖書管理系統概況

 

(B) 系統 E-R 關聯圖

此系統僅有一個關聯並命名為 Books，依照系統需求將其設計如下：

圖 10-2 E-R 關聯圖

我們依照系統需求，給予 Books 關聯有六個屬性(書籍編碼、作者、出版商、書名、放置區域與書籍分類)。並選擇『書籍編碼』為主鍵。

 

10-1-3 轉換資料表與正規化

(A) E-R 圖轉換資料表

我們將書籍的資料表取名為 Books，個欄位(屬性)規劃如下：

Books 資料表

書籍編號

 作者

出版商

書名

放置位置

書籍分類

No

author

publisher

title

place

classify

int

CHAR(20)

CHAR(20)

CHAR(20)

CHAR(20)

CHAR(20)

其中，書籍編號(NO)是書籍之間的唯一識別值，吾人將它設定為主鍵(primary key)，並由系統自動產生。

(B) 正規化分析

正規化分析：

10-1-4 實體建置

我們利用 AppServ – MySQL 資料庫伺服器建立此資料庫系統。首先利用瀏覽器(IE 或 Chrome)，URL: http://localhost 進入 phpMySQLAdmin 管理介面(輸入帳戶與密碼)，並選擇『資料庫』功能選項。

有兩種建立方法，一者為利用『視窗選單』功能，另一者利用 SQL 命令產生，兩者皆示範操作。

(A) 建立資料庫

吾人將此資料庫定名為：books_db，則下列兩種方法產生，如下：

Create Database Books_db;

(B) 建立資料表

吾人將圖書資料表定名為 Books，同樣也有兩種方法產生，如下：(選擇 Books_db 資料庫)

Create Table Books (

NO int auto_increment,

author  CHAR(20) NOT NULL,

publisher  CHAR(20) NOT NULL,

title  CHAR(50) NOT NULL,

place  CHAR(20) ,

classify  CHAR(20) ,

primary key (NO)

)

接著，再輸入各個欄位的名稱與屬性，如下：

其中，還要將 NO 欄位設定主鍵(Primary key)，以及 auto_increment 屬性。

如果設定完成之後，由 Mysql 介面可以觀察到 Books_db 的結構，如下：

其特性說明如下：

10-1-5 匯入測試資料

資料庫與資料表建立完成之後，需產生相關資料來驗證其運作是否能滿足需求。一般我們都會利用 Excel 建立資料後，再將其匯入。

 

(A) Excel 建立資料並轉換成 CSV 檔

 

(B) 將 CSV 檔匯入資料表內

10-1-6 測試資料庫功能

資料庫建構完成後，我們依照幾個系統需求，來驗證其功能是否滿足，如下：

 

(A) 驗證 1：瀏覽所有書籍

瀏覽目前圖書館內有哪些藏書，SQL 命令如下：

Select * from books;

執行結果：

(B) 驗證 2：查詢作者有哪些書

查詢作者粘添壽有哪些書的書名與出版公司，SQL 命令如下：

Select  title, publisher

From books

Where author = “粘添壽”;

執行結果：

(C) 驗證 3：由圖書公司查詢有哪些書

查詢東南圖書公司有那些書，請列印出書名作者名稱，SQL 命令如下：

select title, Author

from books

where publisher = "東南圖書公司";

執行結果：

 

(D) 驗證 4：由圖書分類查詢書籍

查詢古書類別中有那些書，請列印出書名作者名稱，SQL 命令如下：

select title, Author

from books

where classify = "古書";

執行結果：

10-1-7 資料庫檔案匯出

完成設計資料庫之後，可以將其匯出並攜帶客戶處展示，或在其他系統上執行，匯出步驟如下：

到其他電腦上載入方法，如同 course_db.sql 載入方法相同。

10-1-7 資料庫效能分析

雖然 Books_db 資料庫系統僅有一個資料表，應用上比較簡單，但我們也來探討這樣設計有何優缺點。如下：

(1) 查詢關鍵字可能發生不一致的現象，譬如出版公司( publisher)可能輸入東南圖書公司或東南出版社，沒有統一命名可能會造成搜尋不倒資料。

(2) 書籍分類(classify)沒有專屬分類，可能造成混亂。

欲解決上述兩問題，可能須建立其他資料表來輔助，我們在下一章會說明到。

 

10-2 範例研討 – 客戶管理系統

10-2-1 需求規格與資料收集

(A) 規格需求

『真善美健身工廠』需要一套客戶管理系統，將發放每位客戶一只會員卡(VIP 條碼卡)，可利用讀卡機讀入客戶 VIP 卡儲存的會員逼號，做查詢或繳費的運作，系統希望有下列功能：

(B) 收集資料

系統分析師到該公司收集到客戶資料如下：

 

10-2-2 資料庫邏輯設計

(A) 系統概況

我們將此資料庫定名為：sport_house，此系統僅登錄客戶資料，概況如圖 10-3 所示。由此可見只要一個關聯(資料表)，即可達成所需。

圖 10-3 Sport_house 資料庫概況

(B) 系統 E-R 實體關聯圖

此系統僅有一個關聯並命名為『客戶』，依照系統需求將其設計如下：

圖 10-4 Sport_house 資料庫 E-R 關聯圖

我們依照系統需求，給予客戶關聯有七個屬性，並選擇『會員編號』為主鍵。

10-2-3 轉換資料表與正規化

(A) 客戶實體轉換資料表

我們將書籍的資料表取名為 Customers，個欄位(屬性)規劃如下：

客戶實體(Customer 資料表)

會員編號

 姓名

電話

性別

地址

會員等級

會費

NO

name

tel

sex

address

membership

due

int

CHAR(20)

CHAR(20)

CHAR(20)

CHAR(50)

CHAR(20)

int

其中，書籍編號(NO)是書籍之間的唯一識別值，吾人將它設定為主鍵(primary key)，並由系統自動產生。

(B) 正規化分析

正規化分析：

                (備註：表示本範例僅一個表格無法滿足 3NF 條件，當變更『會費』時，可能產生資料不一致問題)

10-2-4 實體建置

 (A) 建立資料庫

吾人將此資料庫定名為：sport_house，利用 SQL 命令產生(亦可利用視窗選單產生)，如下：

Create Database sport_house;

Show databases;

 (B) 建立資料表

吾人將客戶資料表定名為 Customers，同樣也有兩種方法產生，利用 SQL 命令產生，如下(選擇 sport_house 資料庫)

Create Table Customers (

NO int auto_increment,

name  CHAR(20) NOT NULL,

tel  CHAR(20),

sex  CHAR(20),

address  CHAR(50) ,

membership  CHAR(20) ,

due   int,

primary key (NO)

)

如果設定完成之後，由 Mysql 介面可以觀察到 sport_house 資料庫內 Customers 資料表的結構，如下：

        其中，NO 欄位為主建。

 

10-2-5 匯入測試資料

(A) 利用Excel 建立資料並匯出 CSV 檔

將此檔案另存新檔成為『csv(逗點分隔)』格式(成為 Customers.csu)，再利用『記事本』將其編碼改成『UFT 8』編碼，如下結果：(如同 10-1-4 節操作)

10-2-6 測試資料庫功能

資料庫建構完成後，我們依照幾個系統需求，來驗證其功能是否滿足，如下：

 

(A) 驗證 1：查詢某位會員資料

查詢陸美女小姐的會員資料， SQL 命令如下：

select *

from customers

where name = "陸美女";

執行結果：

 

(B) 驗證 2：查詢 A 級會員的名單

查詢目前公司內 A 級會員名單， SQL 命令如下：

select *

from customers

where membership = "A";

執行結果：

 

(C) 驗證 3：查詢住台南市有哪些會員，及其資料

請查詢住在『台南市』的會員姓名、電話、地址， SQL 命令如下：

select name, tel, address

from customers

where address Like "台南市%";

執行結果：

 

 

(C) 驗證 4：查詢會員編號 8 的會費多少

查詢東南圖書公司有那些書，請列印出書名作者名稱，SQL 命令如下：

select name, due

from customers

where No= "8";

執行結果：

 

 

10-2-7 資料庫效能分析

如果『真善美健身工廠』僅需要客戶資料管理，Sport_house 資料內一個 customers 資料表就足夠應用，該資料表最大的缺點是會費與會員等級應該是相關聯的，操作者輸入會員等級時，如果會費輸入不相同時，會造成資料不一致現。解決此方法是需產生一個會員等級資料表，由該表內紀錄會費，就不會發生不一致現象，但這樣就需要兩個資料表，我們下一章再說明。

 

10-3 自我挑戰

有了上述基本概念後，接下來有幾個自我挑戰題目，讓同學自我練習。

10-3-1 柑仔店管理系統

(A) 系統需求與規格

『半路口柑仔店』老闆有感於近來所販售商品越來越多，對於每樣商品成本、售價與庫存量有難掌控，期望建立一套管理系統可供查詢，與進出貨管理。他希望有下列功能：

 

(B) 資料收集

系統分析師到現場收集到商品資料如下：

 

(C) 完成事項

(1) 資料庫邏輯設計。

(2) 關聯轉換資料表。

(3) 實體建置。

(4) 匯入測試資料。

(5) 測試資料庫功能。

(6) 資料庫效能分析。

 

10-3-2 員工薪資管理系統

(A) 系統需求與規格

『農業技術團聯盟』最近招募許多各地有志於農業發展的年輕人，投入農業生產事業，成為農業技術師，短期訓練並給予薪資，並給予日薪、加班費與交通津貼計算薪資。但每位技術師背景不同，日薪與交通薪資不同，如此造成計算薪資時非常複雜，期望建立資訊系統來管理。他們希望有下列功能：

(B) 資料收集

系統分析師到該聯盟收集到農業技術師的資料如下：

 

(C) 完成事項

(1) 資料庫邏輯設計。

(2) 關聯轉換資料表。

(3) 實體建置。

(4) 匯入測試資料。

(5) 測試資料庫功能。

(6) 資料庫效能分析。

 

10-3-3 停車場管理系統

(A) 系統需求與規格

『美崙停車場』期望建立自動化管理系統，期望有下列功能：

(B) 資料收集

系統分析師預估應有下列資料：

(C) 完成事項

(1) 資料庫邏輯設計。

(2) 關聯轉換資料表。

(3) 實體建置。

(4) 匯入測試資料。

(5) 測試資料庫功能。

(6) 資料庫效能分析。