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

翻轉工作室:粘添壽

 

第十章 單表格資料庫設計

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) 資料庫效能分析。