數據庫規范化三個范式應用實例
2024-07-21 02:07:12
供稿：網友
規范化為什么重要？目前很多的數據庫由于種種原因還沒有被規范化。本文中解釋了其中一些原因，并用不同形式的范式（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_numclaim_statusaccident_dtaccident_yrreported_dt entered_dt123456789 open 20-jun-2000200028-jun-2000 29-jun-2000234567890reviewed 15-feb-1984 1984 19-feb-1984 20-feb-1984147258369reopened 08-apr-2003 2003 10-apr-2003 11-apr-2003258369147closed 18-dec-19801980 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_numclaimant_name claimant_addressclaimant_cityclaimant_stateclaimant_zip123456789jennifer smith1234 mainpittsburghpa 15201234567890bill smith7852 eagle pittsburghpa 15202147258369 john jones4562 edgeeighty four pa 15330258369147 eleanor stillwater7531 west easternsomersetpa 15510
zip_codecitystate15330 eighty fourpa15510somersetpa15201pittsburghpa15202 pittsburghpa15203 pittsburgh pa15204pittsburgh pa15205 pittsburghpa15206pittsburgh pa15207pittsburgh pa15208pittsburgh pa15209 pittsburgh pa15210pittsburgh pa
因為pittsburgh、eighty fou和somerset、pa都不依賴于理賠，而依賴于與信息相關的zip代碼，它不是直接歸屬于支付表的。盡管這并不是此表的唯一問題，但是它消除了與城市、州、zip代碼依賴所引發的困難。
claim_numclaimant_nameclaimant_address claimant_zip123456789jennifer smith 1234 main 15201234567890bill smith7852 eagle15202147258369 john jones 4562 edge15330258369147 eleanor stillwater7531 west eastern 15510
其它的能夠遷移到其它表從而使理賠表符合第二范式的信息包括賠償金編號和賠償金描述組合，只需要把賠償金編號存儲在理賠表中。采用這種方法時，任何對于給定編號而對描述進行的更新需要做一些改變，它可以改變賠償金表中的某行的某列，而且這不會發生更新異常，但是如果你更新某個影響成百上千個實體的表的某一列就可能發生更新異常了。相同的邏輯可以應用在調解人和代理人上，把他們的信息遷移到自己的表中，只需要在理賠表中存儲編號列的值，這樣就容易通過連接訪問輔助信息了。
award_cdaward_desc
adjuster_cdadjuster_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_noinsured_nameinsured_address insured_phoneinsured_zip
第三范式通常是人們能夠得到的規范化的最高等級，它一般也是實踐中規范化和標準化數據的最高層次。但是還有更多的范式。層次越高，采用簡單的步驟完成的困難就越大，而它就越來越靠近理論了。
規范化或者沒有規范化--以及你可以采用的規范化擴展--通常是相關人員綜合的結果。如果有足夠的重要的需求，把某片信息存儲在某個位置，而它不一定嚴格符合某種范式的定義，這種存儲也是應該被尊重的。另外，規范化的結果需要以表和數據庫的使用為基礎。通常，在決策支持系統或數據倉庫中，由于數據倉庫的時間變量組件的映象，我們會強烈渴望得到極端的沒有規范化的信息（特別是在事實表中）。
在面向團隊的環境中，這些決定都是部門的決定（或者是共同的指導決定）。希望這些指導方針能夠幫助你了解規范化，幫助你作出更有見識的決定。
