PostgreSQL 與 Java 實戰系列：三、資料庫應用實作

本系列是之前應 Newzilla 邀稿而發表刊載的文章，現在在本站重新整理後發表。本篇介紹如何建立使用者、資料庫，以及如何依功能需求來規劃資料庫系統和表格欄位。

1. 資料庫系統簡介
2. PostgreSQL 簡介
3. 資料庫應用實作（本篇）
4. SQL 簡介
5. JDBC 程式撰寫

建立資料庫

文字管理介面

PostgreSQL 在各個平台下都已準備優秀的 RDBMS 工具，可讓我們由文字模式去存取資料庫，接下來要介紹文字指令來新增資料庫使用者及創建資料庫。在初始化資料庫系統目錄後，就可以開始設計資料庫架構了。本篇以留言版系統為例，先新增一名資料庫使用者，然後再新增資料庫。

首先查看目前資料庫系統中所有的資料庫，我們使用的是 psql 指令，其後加上 -l 選項，表示列出資料庫清單：

psql -l

新增資料庫使用者指令是 createuser，其後接使用者名稱。在過程中會先詢問使用者是否有權限建立資料庫，然後會詢問是否能新增其他使用者，你可以依照你的考量去決定。本例中設定的是使用者「可建立資料庫，但不能新增其他使用者」：

createuser gbadmin

在使用者建立完成後，接著要建立資料庫。使用 -U 選項去指定資料庫的擁有者：

createdb -U gbadmin guestbook

建立完成後，你就可以進入資料庫開始使用 SQL 語法來執行定義表格、存取資料等工作了：

psql -U gbadmin guestbook

若要離開的話，輸入 \q 即可：

guestbook=> \q

圖形管理介面 pgAdmin

pgAdmin III是一套相當知名的 PostgreSQL 資料庫管理工具，擁有 Linux、Windows 和 FreeBSD 上的版本，另外 SunOS 及 MacOS X 的版本則正在發展當中。網站上面的版本最新是到 1.2.0 beta1 版，支援 PostgreSQL 7.x 及 8.0。Windows 的 PostgreSQL 安裝程式已經將 pgAdmin III 也一起裝好了，所以不需要再下載安裝。

Linux 上則提供了數種不同發行版本的安裝程式，包括有 deb（Debian）、rpm（Fedora, Mandrake, Redhat, SuSE）、Slackware package、FreeBSD package、tar ball 等形式。請參考 pgAdmin III 下載頁面上的步驟來安裝。

在雙擊左側清單中「PostgreSQL Database Server 8.0-beta1 」的項目並輸入資料庫管理者密碼後，pgAdmin3 會試著去連接資料庫。一旦連接成功，那麼你就可以開始管理「資料庫」、「Tablespaces（表格空間）」、「群組」和「用戶」等四個項目。我們首先要新增使用者，所以在「用戶」上點選右鍵，選取「新建用戶」：

在「新建用戶」的視窗出現後，我們直接輸入「用戶名」，以及勾選「允許用戶建立資料庫」項目：

接著要建立資料庫，在左側清單中的「資料庫」上按右鍵，選取「新建資料庫」：

「新建資料庫」視窗中只需將「名稱」輸入後，並從「所有者」下拉式選單中選取剛剛建立好的 gbadmin：

點選「確定」並經過一小段時間後，資料庫就會創建完成。你可以從左側清單中檢視：

直接點選左上角的 X 即可關閉視窗，離開資料庫管理系統。

規劃資料庫系統

當我們開始使用資料庫之前，必須要先定義好資料架構後，才能將我們所收集到的資料一一地輸入到資料庫之中，那麼應該要怎麼定義和規劃資料庫呢？之前曾經介紹過關聯式資料庫的架構，是儘量將同一種類的資料集合在一起，以切成小單位的方式來避免資料重複性，並且選擇鍵值將表格關聯起來。為了能夠適當地劃分表格和挑選鍵值，我們使用的稱為之正規化（normalization）的程序。

正規化需要經過正規構成（normal form）的步驟，主要是為了達到下列幾項目的：

• 同類資料規劃為一個群組，使得每個群組都可以描述全部資料的一部份
• 分成各個群組後，在未來若要修改資料時，能達到最小限度的資料改變（例如只需改一筆紀錄）
• 資料庫中重複的資料能夠降到最少，以節省空間及增進效率
• 可以快速有效地存取資料庫中的資料

第一正規化

正規構成總共有三項規則，如果你的資料庫符合第一項規則，那麼它就可以稱之為第一正規構成（first normal form）的資料庫。底下是第一正規化的規則：

• 展開目錄中重複的群組，每個欄位只擁有單一值
• 把同類型的資料組成群組，並建立目錄
• 在各個目錄中使用主鍵值去區分不同的資料

這邊我們以留言版為例子的話，一開始的簡易規劃可能會像是底下一樣：

也就是將所有資料儲存在一個表格之中。這樣的架構使得留言內容欄位中的值不只一個，隨著時間經過，其中的值就會愈來愈多，架構也會日益複雜。因此依照第一正規化的步驟，拆開重複的群組，好讓每個欄位中都只有一個值：

第二正規化

在將重複資料拆成每筆的單一記錄後，我們可以發現星座欄位與留言內容欄位之間並沒有相關性，性別與留言內容也沒有相關性，因此我們將它們拆成兩個表格來儲存。如此一來如果需要刪除留言（例如 Cedric 的留言）時，也無須擔心會員資料一起被刪除。

• 需為第一正規構成資料庫
• 把在多項記錄中與主鍵值功能相依的資料皆劃分出來為一目錄
• 用外來鍵值將各個目錄連結起來

把欄位拆成兩個表格來儲存，會員編號（主鍵值、Primary Key 簡稱 PK）、帳號、性別和星座都是和會員有關的資料，所以獨立成會員表格：

帳號與留言內容及留言時間之間具有相關性，所以獨立成留言版表格，並且加上留言編號做為主鍵值，以及會員編碼去參考會員表格中的會員編號主鍵值：

第三正規化

依照相關性而分開表格後，我們繼續檢視表格，可以看到在星座欄位的部份，有可能會有資料重複的情況出現。當會員人數成長到一定數量時，我們就必須耗掉許多空間去儲存重複的部份。因此我們可以將它獨立出來並存放在其他表格中。另外需要處理的是具有遞移相依性（transitive dependencies）的資料：想像一個具有姓名、城市和郵遞區號的表格，姓名欄位會影響城市和郵遞區號欄位的值，但是城市也會影響郵遞區號的值。如果今天修改了城市欄位，那麼郵遞區號欄位也必須修改，這就是遞移相依性。我們需要將會重複和有遞移相依性的資料獨立出來，以利日後的修改及查詢。

• 需為第二正規構成資料庫
• 排除遞移相依性及重複資料群集

星座欄位獨立出來，並且在會員表格內使用鍵值來參考：

將星座欄位更改為星座表格主鍵值後的會員表格：

經過上面步驟後，我們的資料庫就已達成了第三正規構成，資料重複量大幅降低。遇到比較複雜的架構，有可能需要更進一步的正規化，像是 Boyce/Codd 正規化（由 R. F. Boyce 和正規化的首創者 E. F. Codd 共同提出）、第四及第五正規化（由 R. Fagin 提出）。如果過度正規化而導致效率低落和群集過多的話，有時候做適當的反正規化（Denormalization）可以幫助資料庫加快執行速度。

簡易留言版所需要的欄位，我們大致規劃如下：

• 會員表格：編號、帳號、密碼、性別、電子郵件、星座代碼、註冊時間、權限
• 星座表格：編號、名稱
• 留言表格：編號、會員代碼、內容、時間