資料庫正常化三個範式應用執行個體

　　正常化為什麼重要？目前很多的資料庫由於種種原因還沒有被正常化。本文中解釋了其中一些原因，並用不同形式的範式（normal form）正常化了一個保險公司的理賠表。在這個過程中表的改變以及添加的一些附加表使資料庫效率更高、錯誤更少、更容易維護。

　　資料庫的正常化是最佳化表的結構和把資料群組織到表中的實踐，這樣做資料才能更明確。正常化使你能夠改變商務規則、需求和資料而不需要重新構造整個系統。

　　通過改變儲存資料的方式--僅僅改變一丁點--並改變訪問這些資訊的程式，你就可以消除很多錯誤或垃圾資料出現的機會並減輕更新資訊所必要的工作量。

　　公司現實存在的一個問題可以用一句話概括"我們一般都這樣做"。我們一般像採用那種方式儲存資訊；我們一般允許人們把任何資訊寫入<insert field name>；我們一般採用那種方式編程。這通常是一件壞事，特別是對於年輕的和正在學習的公司來說。但是，當有新的系統和更好的完成任務的途徑的時候，有時"採用那種方式任務完成得很好"這句話可能需要重新探討和修改。正常化資料就是公司常常採用的有益的方式之一。

　　儘管對於COBOL程式（例如任何COBOL程式員都熟悉的檔案布局）使用資料來說，把它們（資料）儲存在關聯式資料庫中與儲存在一般檔案中很相似，但是儲存在一般檔案中的方法並不是完成任務的必要的最好的途徑，特別是由於你不瞭解兩者之間的差別或害怕改變，而簡單地把過去的觀念帶入到現在的方式。

　　注意：Dictionary.com是這樣定義正常化的："使其標準，特別使導致它符合某種標準或規範。"或"某種標準的強制接受"。Webopedia認為正常化是"在關聯式資料庫設計中，組織資料以最小化冗餘的過程。正常化通常包括把一個資料庫分成兩個或多個表並定義表之間的關係。其目標是隔離資料，這樣添加、刪除和修改某個欄位只需要在一個表中進行，接著可以通過定義的關係傳遞到資料庫中剩餘的表中"。我更喜歡這個定義。

　　術語

　　在你瞭解現實世界中的一個保險公司的例子之前，你需要瞭解一些在討論中會用到的術語。處理資料庫的時候，特別是在處理正常化問題的時候，下面一部分講到的一組新的關鍵字很有作用：

　　· 關係（Relation）：從本質上說，關係是一個包含行和列的二維表或數組。

　　· 關聯（Relationship）：關聯是不同表之間的資料彼此聯絡的方法。關聯同時存在於形成不同實體的資料項目之間和表實體本身之間，構成了資料庫正常化的基本核心問題。資料關聯有三種基本的類型，對它們有所瞭解是很重要的：

　　　一對一（1:1）：一對一關聯意味著任何給定的每個（而不是大多數）執行個體嚴密地與另一個實體的一個執行個體對應。每個人只有一個正確的指紋就是唯一的。每個電話號碼準確地與一個付帳的獨立私人客戶對應（不是公司）。美國的每個人都只有一個社會保障號碼。

　　　一對多（1:M）：一對多關聯意味著給定實體的一個執行個體可以可以與另一個實體的零個執行個體、一個執行個體或者多個執行個體關聯。每個人可能沒有小孩、有一個小孩或多個小孩。每個人可能沒有汽車、有一輛汽車或多輛汽車。
　
　　　多對多（M:N）：多對多關聯（給定實體的零個、一個或多個執行個體與另一個實體的零個、一個或多個執行個體關聯）是一種直接類比很複雜的關聯，它經常被分解為多個1:M關聯。由於多個家庭混合在一起，一個或多個小孩可能沒有父母親（孤兒）、一個父母（單親家庭），多於一個父母（兩個仍然在一起或者離婚的兩個父母、或者離婚了又複婚了的父母）。房屋或財產可以轉讓給一個人或多個人，而這些人（一個或多個）在遺囑上可能又一個或多個房屋或財產。

　　· 屬性（Attribute）：屬性被認為是程式或資料庫中的某些組件的可以修改的特性或特徵，它可以被設定為不同值或者關係或表中的列。

　　· Tuple：Tuple是關聯式資料庫或非關聯式資料庫中的排序了的一組值或值屬性：關係中的一行。

　　· 刪除異常：刪除異常指由於其它資料故意的刪除而導致的資料矛盾或未預料到的資料（資訊）丟失。

　　· 插入異常：插入異常指由於資料的缺少或缺乏導致沒有能力把資訊添加到資料庫。

　　· 更新異常：更新異常指由於資料冗餘或者冗餘資料的不完整更新造成的資料矛盾。

　　· 關係的分解：關係的分解指把一個關係分解成多個關係，從而使關係符合更高的範式。

　　· 資料冗餘：資料冗餘指資料庫中沒有必要的資料重複。

　　· 資料完整性：資料完整性指資料庫中資料的一致性。保證資料完整性很重要，只有這樣使用者才知道他們依賴的資料是正確的、他們查詢的結果以及程式才是精確的和符合期望的。

　　· 原子值：原子值是一個值，它既不是能被進一步拆分的一組值，也不是一個重複的組。每個列都有一個完整的值，但是只有一個值--這個值不能被分解為多個部分，它要麼被資料庫使用，要麼被使用資料庫的使用者訪問的資訊。

　　· 參考完整性規則：參考完整性規則指儲存在非空的外部健中的值必須是某種關係中的關鍵資料項目。

　　· 外部健：外部健是一個關係中的一組屬性（一個或多個列），它同時也是某種（相同的或其它的）關係中的主鍵。它是關係之間的邏輯連結。參考自己關係的外部健稱為遞迴外部健。

　　· 功能依賴：功能依賴意味著一行中某個屬性的值由該行中另一個屬性的值決定。這通常出現在主鍵（使某行唯一的資訊片斷）與該行的其它資訊之間。城市和州的組合依賴於Zip（郵政）代碼，即使給定的一個州中有很多Zip代碼與某個城市關聯。美國的每個合法的人員身份依賴於他的社會保障號碼。

　　· 決定性：功能依賴左邊的屬性決定行中其它屬性的值（Zip代碼決定了城市和州；社會保障號碼決定了人的身份；執照號碼和州決定了汽車的擁有者）。

　　· 實體完整性規則：實體完整性規則指某一行的關鍵屬性可能為空白（如果你在某個城市就有一個Zip代碼；如果你有一輛汽車就有一個執照號碼）。

　　· 約束：約束是一種規則，它限定了資料庫中的值。電話號碼必須是數位；美元數量必須是數位；state必須是合法的州或省；country必須是合法的國家；日期不能是2月31號。

　　現在你已經知道了很多相關的術語了，我們可以看看相關術語中規範會的意義了。下面的例子並不是典型的僱員―經理―部門樣本，也不是學生―教授―課程提供樣本。我將示範一個假設的保險公司的資料庫。資料庫中的表比本樣本中用到的要複雜得多，但是與人們遇到的比較相近。

　　圖1顯示了理賠（claim）表的非正常化定義。儘管在某個保險公司的資料庫中的表比它多得多，但是這些表為我們提供了一些背景，通過它我們可以看到正常化和其分支。請記住每個章節中的樣本都只有部分列，這樣就簡化了樣本並使你輕易地看到發生變化的東西。

CLAIM_NUM、 OCCURANCE_NUM 、 CLAIM_STATUS、 ACCDNT_YR、 ACCDNT_DT、 REPORTED_DT、 ENTERED_DT、 CLAIM_DT1、 CLAIM_DT2、 CLAIM_DT3 、 CLAIM_DT4、 CLAIM_DT4 、 CLAIM_DT5 、 CLAIM_DT6 、 CLAIM_DT7、 CLAIM_DT8 、 CLAIM_DT9 、 CLAIM_DT10、 CLOSED_DT 、 DEATH_DT、 ASSIGNED_DT、 ADJSTER_CD 、ADJUSTER_NAME 、 AGENT_CD 、 AWARD_CD 、 CAUSE_CD 、 CAUSE_DESC、 LOCATION 、 SITE 、 COVERAGE_CD 、 COVERAGE_DESC、 DED_RECOV、 DEDUCTIBLE_REMAIN 、 PAID_1 、 RESERVED_1 、 PAID_2 、 RESERVED_2 、 PAID_3 、 RESERVED_3 、 PAID_4 、 RESERVED_4 、 PAID_5 、 RESERVED_5 、 PAID_6 、 RESERVED_6 、 PAID_7 、 RESERVED_7 、 PAID_8 、 RESERVED_8、 PAID_9 、 RESERVED_9 、 PAID_10 、 RESERVED_10 、 LEGAL_FLG、 KEY1、 KEY2、 KEY3、 KEY4、 KEY5、 KEY6、 KEY7、 KEY8、 KEY9、 KEY10、 SEVERITY_CD 、 POLICY_NUM 、 PAYMENT_NUM 、 SSN、 STATE、 ACTVY_DT、 ENTRY_DT、 ADMIN_CD、ADMIN_DESC、 REOPEN_DT、 INSURED_NAME、 INSURED_ADDRESS、 INSURED_PHONE、 INSURED_CITY、 INSURED_STATE、 INSURED_ZIP、 CLAIMANT_NAME、 CLAIMANT_ADDRESS、 CLAIMANT_CITY、 CLAIMANT_STATE、 CLAIMANT_ZIP、 CLAIMANT_PHONE、 SPECIAL_DT_1 、 SPECIAL_DT_2、 SPECIAL_DT_3、 SPECIAL_DT_4 、 SPECIAL_DT_5、 SPECIAL_DT_6 、 SPECIAL_DT_7 、SPECIAL_DT_8 、SPECIAL_DT_9 、 SPECIAL_DT_10 、 GROSS_PD、 POLICY_ID

圖1：未正常化的理賠表的列

　　第一範式（1NF）

　　把資料庫或資料庫的錶轉換為第一範式一般都相當簡單。第一範式要求消除資料中重複的組，這是通過建立相關資料的單獨表來實現的。它通過觀察資料和表結構來確定表以完成第一範式。

　　第一範式是通過把重複的組放到每個獨立的表中，把這些表通過一對多關聯聯絡起來這種方式來消除重複的群組的。

　　沒有重複的屬性以及沒有重複的一組值--這聽起來足夠簡單了。但是，有時候由於沒有其它的選擇，使人們相信只有簡單地給設計添加任何其它集合卻很困難，但是這也是你所做的事情。

　　如果我們想使理賠表達到第一範式，我們就需要找到真正與某個理賠相關聯的所有屬性。到底是什麼構成了理賠？

　　· 理賠要有編號。

　　· 理賠要有提出要求的人。

　　· 理賠要有報告日期。

　　· 理賠要有事故或生病日期。

　　· 理賠要有由於事故或者生病引起的某種物品保留的數量。

　　· 理賠屬於或者依據某種策略編寫。

　　· 理賠能夠結束。

　　· 理賠能夠重新開始。

　　· 理賠有某種覆蓋面嗎？或者某種策略有更多的的事情？

　　· 理賠有起因嗎？或者事故或生病有起因嗎？

　　· 支付了理賠嗎？或者支付了發票嗎？

　　· 理賠有社會保障號碼嗎？或者有時候某個社會保障號碼屬於提出要求的人嗎？

　　· 死亡日期是個有趣的部分。理賠的人死亡了嗎？沒有，但是如果是生命保險，它可能與理賠相關，因此應該留著。

　　修改與理賠直接相關的列，得到的結果2所示：

CLAIM_NUM、 CLAIM_STATUS、 ACCIDENT_YR、 ACCIDENT_DT、 REPORTED_DT、 ENTERED_DT、 CLOSED_DT、 DEATH_DT、 ASSIGNED_DT、 ADJSTER_CD、 ADJUSTER_NAME、 AGENT_CD、 AGENT_NAME、 AWARD_CD、 AWARD_DESC、 PAYMENT_NUM、 LOCATION、 SITE、 DEDUCTIBLE_RECOVER、DEDUCTIBLE_REMAIN、 POLICY_NO、 POLICY_DESCRIPTION、 STATE、 RUN_DT、 ACTIVITY_DT、 ENTRY_DT、 REOPEN_DT、 INSURED_NAME、 INSURED_ADDRESS、 INSURED_PHONE、 INSURED_CITY、 INSURED_STATE、 INSURED_ZIP、 CLAIMANT_NAME、 CLAIMANT_ADDRESS、 CLAIMANT_CITY、 CLAIMANT_STATE、 CLAIMANT_ZIP、 CLAIMANT_PHONE、 GROSS_PD

圖2：第一範式的理賠表

符合第一範式的理賠表修訂版本將包含僅僅與理賠相關的資訊，沒有包含支付或發票、策略或事故。

Payment_num	Claim_status	Accident_dt	Accident_yr	Reported_dt	Entered_dt
123456789	Open	20-JUN-2000	2000	28-JUN-2000	29-JUN-2000
234567890	Reviewed	15-FEB-1984	1984	19-FEB-1984	20-FEB-1984
147258369	Reopened	08-APR-2003	2003	10-APR-2003	11-APR-2003
258369147	Closed	18-DEC-1980	1980	18-DEC-1980	19-DEC-1980

　　如果你有支付表，並且儲存了特定理賠的保留數量以供支付其它不同的帳單，為什麼不把它們儲存在支付表中呢？總之，你在支付表中儲存了一些資訊，因此為什麼不把這些內容也放在它裡面而不要放在理賠表中呢？

　　如果把這些資訊放入理賠表的唯一原因是某個使用者在理賠時可能需要這些資訊，那麼理賠表和支付表可以串連（join）起來，而且資訊可以來自單個理賠發生的所有支付總和。並且由於你擁有不同類型的保險策略（因此有不同類型的理賠），為什麼不把所有類型的理賠的支付資訊儲存在一張表中呢？把所有的支付資訊儲存在相同的表中符合邏輯。與某種支付（屬性）相關聯的大多數資訊是相同的，不管是那種類型的支付或那種類型的理賠。但是，不同類型的理賠的帳號資訊有些不同。

　　第二範式（2NF）

　　第二範式處理冗餘資料的刪除問題。當某張表中的資訊依賴於該表中其它的不是主鍵部分的列的時候，通常會違反第二範式。

　　如果新的第一範式理賠表的列如下，那麼可以迅速、輕易看到的冗餘資料就是被保險人的城市和州和提出理賠要求的人的城市和州。城市和州都直接依賴於Zip代碼，而不依賴於與理賠相關的任何東西。

CLAIM_NUM、 CLAIM_STATUS、 ACCIDENT_YR、 ACCIDENT_DT、 REPORTED_DT、 ENTERED_DT、 CLOSED_DT、 DEATH_DT、 ASSIGNED_DT、 ADJSTER_CD、 ADJUSTER_NAME、 AGENT_CD、AGENT_NAME、 AWARD_CD、 AWARD_DESC、 LOCATION、 SITE、 DEDUCTIBLE_RECOVER、 DEDUCTIBLE_REMAIN、 POLICY_NO、 POLICY_DESCRIPTION、STATE、 RUN_DT、 ACTIVITY_DT、 ENTRY_DT、 REOPEN_DT、 INSURED_NAME、 INSURED_ADDRESS、 INSURED_PHONE、 INSURED_CITY、 INSURED_STATE、 INSURED_ZIP、 CLAIMANT_NAME、 CLAIMANT_ADDRESS、 CLAIMANT_CITY、 CLAIMANT_STATE、 CLAIMANT_ZIP

圖3.第二範式的Claim

Claim_num	Claimant_name	Claimant_address	Claimant_city	Claimant_state	Claimant_zip
123456789	Jennifer Smith	1234 Main	Pittsburgh	PA	15201
234567890	Bill Smith	7852 Eagle	Pittsburgh	PA	15202
147258369	John Jones	4562 Edge	Eighty Four	PA	15330
258369147	Eleanor Stillwater	7531 West Eastern	Somerset	PA	15510

Zip_Code	City	State
15330	Eighty Four	PA
15510	Somerset	PA
15201	Pittsburgh	PA
15202	Pittsburgh	PA
15203	Pittsburgh	PA
15204	Pittsburgh	PA
15205	Pittsburgh	PA
15206	Pittsburgh	PA
15207	Pittsburgh	PA
15208	Pittsburgh	PA
15209	Pittsburgh	PA
15210	Pittsburgh	PA

　　因為Pittsburgh、Eighty Fou和Somerset、PA都不依賴於理賠，而依賴於與資訊相關的Zip代碼，它不是直接歸屬於支付表的。儘管這並不是此表的唯一問題，但是它消除了與城市、州、Zip代碼依賴所引發的困難。

Claim_num	Claimant_name	Claimant_address	Claimant_zip
123456789	Jennifer Smith	1234 Main	15201
234567890	Bill Smith	7852 Eagle	15202
147258369	John Jones	4562 Edge	15330
258369147	Eleanor Stillwater	7531 West Eastern	15510

　　其它的能夠遷移到其它表從而使理賠表符合第二範式的資訊包括賠償金編號和賠償金描述組合，只需要把賠償金編號儲存在理賠表中。採用這種方法時，任何對於給定編號而對描述進行的更新需要做一些改變，它可以改變賠償金錶中的某行的某列，而且這不會發生更新異常，但是如果你更新某個影響成百上千個實體的表的某一列就可能發生更新異常了。相同的邏輯可以應用在調解人和代理人上，把他們的資訊遷移到自己的表中，只需要在理賠表中儲存編號列的值，這樣就容易通過串連訪問輔助資訊了。

award_cd

award_desc

adjuster_cd

adjuster_name

agent_cd

agent_name

　　第三範式（3NF）

　　第三範式規則尋找以消除沒有直接依賴於第一範式和第二範式形成的表的主鍵的屬性。我們為沒有與表的主鍵關聯的所有資訊建立了一張新表。每張新表儲存了來自源表的資訊和它們所依賴的主鍵。

　　注意：通常把第三範式說成是"鍵，全都是鍵，除了鍵之外沒有任何資訊"。

CLAIM_Num、CLAIM_STATUS 、ACCIDENT_DT、REPORTED_DT、 ENTERED_DT、 CLOSED_DT 、 DEATH_DT、 ASSIGNED_DT、 ADJSTER_CD、 AGENT_CD、AWARD_CD、 LOCATION、SITE 、 DEDUCTIBLE_RECOVER、 DEDUCTIBLE_REMAIN 、 POLICY_NO 、 STATE、 RUN_DT、 ACTIVITY_DT、 ENTRY_DT、 REOPEN_DT、 INSURED_NAME、 INSURED_ADDRESS、 INSURED_PHONE、 INSURED_ZIP、 CLAIMANT_NAME、 CLAIMANT_ADDRESS、 CLAIMANT_ZIP

圖4：第三範式的理賠表

　　在第三範式中可以看到理賠表的更多的變化，在該表中被保險人姓名、地址、電話號碼、Zip代碼更加依賴於簽訂的策略而不是理賠本身。因此，我們能把被保險人的資訊放入策略表中。這使得理賠表的剩餘資訊與理賠更加直接相關，把其它所有資訊放入自己的表中而保證了足夠（沒有遺漏）資訊。這些表的一個簡單的串連能夠重新構造源表的資訊，這也是關係代數和關係運算（關係理論和關聯式資料庫依賴的基礎）的目標。

POLICY_NO

INSURED_NAME

INSURED_ADDRESS

INSURED_PHONE

INSURED_ZIP

　　第三範式通常是人們能夠得到的正常化的最高等級，它一般也是實踐中正常化和標準化資料的最高層次。但是還有更多的範式。層次越高，採用簡單的步驟完成的困難就越大，而它就越來越靠近理論了。

　　正常化或者沒有正常化--以及你可以採用的正常化擴充--通常是相關人員綜合的結果。如果有足夠的重要的需求，把某片資訊儲存在某個位置，而它不一定嚴格符合某種範式的定義，這種儲存也是應該被尊重的。另外，正常化的結果需要以表和資料庫的使用為基礎。通常，在決策支援系統或資料倉儲中，由於資料倉儲的時間變數組件的映象，我們會強烈渴望得到極端的沒有正常化的資訊（特別是在事實表中）。

　　在面向團隊的環境中，這些決定都是部門的決定（或者是共同的指導決定）。希望這些指導方針能夠協助你瞭解正常化，協助你作出更有見識的決定。