7.2 數據規范化

本節介紹幾個真實的數據組織案例，來討論關系范例，再介紹一些實用的建模技術。理解SQL的關鍵是理解關系范例，并能把數據規范化到關系結構中。規范化是系統分析員的工作，因為他們要把業務數據建模為適合存儲在關系表中的形式。這門學科需要研究多年，有許多人開發出了自己的方法和記號。

真實場景

這個向導使用幾個假想的場景，包括兩個Oracle提供的內置場景HR和OE，它們常常用作試題的上下文，來演示各種SQL概念。在討論新概念時，下面的場景會進一步演化。

汽車代理公司 Sid經營一家汽車代理公司，需要一個系統跟蹤她買賣的汽車。她發現，業務下降很多，希望進入21世紀時，能創建一個網站，為庫存的汽車做廣告。她需要一個系統記下她買賣的汽車和這些事務的細節。

地質巖心 地球巖心的樣本由本地地理研究機構收集。為了確保科學、嚴謹，GeoCore的開發人員決定，系統必須跟蹤準確的地理位置、巖心樣本的元素成分和收集日期。

訂單項 訂單項(OE)場景由Oracle提供為一個樣本，包含了一個假想商業系統的信息，該系統跟蹤產品、客戶和已下的銷售訂單。

人力資源 人力資源(HR)場景由Oracle提供為一個樣本，記錄了員工、部門、辦公地址和與一般HR部門的工作相關的信息。

盡管前面描述的假想場景在復雜性方面各有不同，但它們有幾個共同的特性，包括潛在的數據增長最終可能摧毀基于紙質或電子表格的數據組織方案，以及需要以高效的方式操縱(插入、更新和刪除)和檢索數據。獲得高效的數據組織設計方案(也稱為數據模型)的挑戰，可以通過理解如何利用要組織的數據和幾個基本的數據建模技術來克服。目標是在數據存儲和數據訪問之間維持優化的平衡，提供長期的、節約成本的下游優勢。

7.2.1 數據建模

有各種形式化的數據建模方法，例如Zachman框架和Rational Unified Process，在根本上是為了提供系統的、基于標準的方法，來表示企業中的對象。有許多記號法可用于建立實體及其關系的模型。Oracle在其計算機輔助軟件工程(CASE)工具和最近的SQL Developer采用了一種流行的記號法：鳥足記號法，本章將討論它。其他記號法，例如關系模式記號法和UML，也很流行，但必須選擇一種有效的、熟悉的記號法。

邏輯建模基于概念化的對象，它把感興趣的對象看作實體，把它們之間的交互操作看作關系。實體-關系圖有許多繪制方法，每種方法都有優缺點。下面簡要討論實體-關系圖及其記號。

7.2.2 實體和關系

許多Oracle專業人士都采用一種架構，該架構在關系數據庫建模時包含3個建模階段。在構思邏輯模型時，一般在一張圖中表示高級結構，該結構稱為實體，它包含各種屬性及其關系。邏輯模型中的實體通常表示為圓角矩形，它包含屬性或標識符，有時表示為“o”符號。唯一標識實體實例的屬性稱為主鍵，有時表示為“#*”符號。表示屬性的數據類型可以在這個階段確定，但一般不反映在設計中。

接著要把邏輯模型轉換為關系模型，為此要把實體轉換為關系，通常稱為表。這里的理念是實體的實例集統一建模為一個表。屬性轉換為表列。實體的每個實例反映為元組或數據行，每行都有不同的屬性值或列值。表中的行數就是元組的基數。通常，屬性在每行中都唯一地稱為唯一鍵，一般選擇唯一鍵作為主鍵(稍后討論)。實體之間的關系常常建模為外鍵，本章后面會討論。

關系模型中的關系通常表示為矩形。在這個階段，一般有更多的細節涉及屬性的數據類型，主鍵和外鍵屬性也在關系模型中分別用P和F表示。最后，通過在關系數據庫中實現設計，將關系模型設計為物理模型。

鳥足記號法常常用于表示邏輯和關系數據模型中的關系。實體之間的關系可以是如下之一，并在汽車代理公司場景中展開討論：

● 1:N一對多

● N: 1 多對一

● 1:1 一對一

● M:N多對多

考慮前面介紹的Sid汽車代理公司。可以把可能的數據建模為一個實體，其中包括如下與汽車相關的屬性：廠商、型號、引擎容量和顏色。也需要與汽車買賣相關的信息，所以可以添加購買日期、銷售日期、銷售員姓名、銷售員SSN(社會安全號)、銷售公司、買主的相同信息，最后是購買價格和銷售價格，如圖7-2所示。

圖7-3顯示了表中基于這個實體的樣本事務數據，表CAR_DEALERSHIP包含3行、14列數據。創建表和填充數據的命令在本書后面討論。現在，要注意幾個更重要的地方。表把數據存儲在行(也稱為記錄)中，每個數據元素都位于行和列的交叉處(也稱為單元格)。表相當直觀，很像電子表格。

表CAR_DEALERSHIP中的前兩個記錄包含如下信息：

● 一輛銀色的梅賽德斯A160，引擎容量1600cc，屬于SSN是12345的私人銷售員Coda，該車由Sid's Cars的Sid在2013年6月1日以10 000美元的價格購買，Sid的SSN是12346。

● 一輛銀色的梅賽德斯A160，引擎容量1600cc，隸屬于Sid's Cars的Sid, Sid的SSN是12346，該車由Wags Auto的Wags在2013年8月1日以12 000美元的價格購買， Wags的SSN是12347。

注意數據的重復。每個記錄都包含所買賣汽車的重復數據，以及買賣汽車的客戶的重復數據。數據的不必要重復通常表示設計不良，因為這很浪費，而且常常需要不必要的維護。如果這種維護不仔細，這種設計會提高出錯率(有時稱為插入更新和刪除異常)，降低數據的總體完整性。

數據庫規范化是指使用多個實體及其之間的關系給數據建模，可以減少或完全消除數據冗余。理論上定義了許多類型的范式，但關系數據庫設計主要考慮如下3種范式：

● 第一范式(1NF)解決了刪除不必要重復的數據組的問題。圖7-3中重復組的一個例子是第1、2行的前4列，其關于汽車的描述信息是重復的。可以定義一個新的Cars實體，使用Car ID主鍵屬性，以及Make、Model、Engine Capacity和Color屬性，唯一地標識特定的汽車。Car ID標識符在相關的Transactions實體中用以避免重復數據組。

● 第二范式從實體(1NF)中刪除不依賴主鍵的屬性。在前述的Cars實體中，Color屬性不依賴特定的汽車。可以定義一個新的Colors實體，使用Color ID主鍵屬性，唯一地標識特定的顏色。于是，Color ID就可以由Cars實體引用。

● 第三范式從2NF實體中刪除所有獨立的屬性。汽車的買家和賣家都有一個唯一標識的社會安全號(SSN)。但他們的名字獨立于SSN屬性。所以可以定義一個新的Customers實體，使用Customer ID主鍵屬性唯一地標識客戶。其中，存儲了獨立的信息，例如客戶的名字和公司。

注意在調整性能時，重復實體中的數據是故意的，也是可接受的。規范化多個實體中的數據時，如果多個實體實例化為必須連接在一起的多個表，Oracle服務器進程就需要從多個表中物理提取出數據，并在內存緩存中把它們連接起來，生成需要的結果集。查詢或操縱規范化數據所需的額外I/O有時能證明，反規范化數據模型，會減少磁盤IO操作，因此提高性能。這在數據倉儲(DWH)和決策支持系統(DSS)中很常見，但在OLTP系統中，是一個例外，而不是規則。

考慮圖7-4中的邏輯數據模型。與汽車相關的數據建模為Cars實體。客戶(買家和賣家)信息其實是相同的，所以客戶建模為Customers實體，用Customer Type屬性區分Purchasers和Sellers。買賣信息記錄在Transactions實體中，而查找實體Colors跟蹤不同的顏色。

把這個設計概念化為4個相互關聯的實體，有幾個優點。第一，數據已規范化，沒有重復的數據。有多個實體。每個實體都跟蹤一個結構，如Cars、Customers、Colors和Transactions，便于數據維護。可以添加新顏色，每種顏色都有唯一的代碼。購買新車時，這些顏色在一個地方定義和維護，可以用一種顏色描述多輛汽車。還可以為輪胎、安全系統、跟蹤設備或音視頻插件定義實體，提高這個模型的復雜度。還可以改善為每輛汽車收集的細節，例如車輛標識號(VIN)和引擎號，或者每個客戶的地址和銀行信息，但這個假想的場景用于演示幾個概念，顯然沒有進一步改進，就不能用于產品應用程序場景。

主鍵

圖7-4中的每個實體都有一個主鍵屬性，唯一地標識一個元組或數據行，在該主鍵屬性名的旁邊用“#*”表示。Car ID主鍵的每個值在實體中都是唯一的。多個行不能有相同的主鍵值。同樣，Color ID唯一地標識Colors實體中的每一行，Customers實體中的Customer ID和Transactions實體中的Transaction ID也是如此。

關系

圖7-4中鏈接各個實體的線稱為關系。鳥足記號法可以表達實體間關系的基數——一對一、一對多、多對一和多對多。鳥足記號法用多個“足”明確演示了位于關系中“多”端的實體，而“一”端的實體只有一足。一對一關系中的屬性是相同的，而多對多關系表示，實體A中的多個元組與實體B中的多個元組具備相同的屬性值。一對一和多對多關系并不常見，有時表示關系模型中的缺陷。給關系實體建模時，一對多和多對一關系很常見。它們在主從關系中關聯兩個實體中的屬性。例如，在Cars和Colors實體(其順序很重要)之間的關系中， Cars實體中的許多記錄都有相同的Color。許多汽車可能有相同的Color ID屬性，這表示它們有相同的顏色。在這個關系中，Colors實體是主實體或查找實體，而Cars實體是從實體。在Cars和Colors實體之間的關系中，一個Color可以關聯多個Car。所以，是一對多還是多對一關系僅考慮觀察方向，它完全取決于觀察該關系的方向。鳥足記號法表示的其他關系說明，一輛車可以買賣多次，這就是Cars和Transactions實體之間是一對多關系的原因，一個客戶可以進行多次交易(例如買賣許多車)。

參照完整性和外鍵

這些關系引入了參照完整性的概念，該概念會確保關系中“一”端的實體中的屬性A必定唯一，而“多”端實體中的屬性B的值必定是屬性A描述的唯一值集合中的一個，從而保證數據的一致性和完整性。屬性B稱為外鍵，因為它對屬性A具有參照依賴性。考慮基于Color ID屬性的Colors - Cars關系。參照完整性確保，Cars實體中每個元組的Color ID屬性值都必須與Colors實體中的一個Color ID屬性實例相同。這個保證對關系建模非常重要，因為根據Color ID屬性連接Colors 和Cars實體，允許Colors.Color(這是句點記號)屬性匹配Cars實體中的一個相關元組。Cars實體中的Color ID屬性是與唯一鍵關聯的外鍵，而唯一鍵就是Colors實體中的Color ID屬性，Color ID屬性正巧是主鍵。實體中的外鍵常常基于關聯實體的主鍵，但這并不是什么硬性規則。

圖7-4中的邏輯模型一般會演化為關系模型，其中帶有更多的數據類型細節、更清晰的主鍵和外鍵，如圖7-5所示。

關系模型可以設計為一個物理模型，其中創建了實際的表和其他數據庫結構(參見本章后面)。圖7-3中的樣本數據傳入物理模型中，而物理模型是從前面描述的關系模型中創建的，這些樣本數據生成了4個數據集，如圖7-6所示。

Transactions數據集中的前兩行數據可以解釋為：

● 與TX ID 100相關的交易描述了，Sid的汽車代理公司于2013年6月1日以10 000美元的價格從Customer ID為2的客戶手里購買了Car ID為1的汽車。查找Customer ID為2的客戶，會發現他從SSN為12345的私人賣家那里買了這輛車。而查找Car ID為1的汽車，會發現它是一輛2001-A160梅塞德斯，其Color ID為1，該顏色進一步解析為銀色。

● 與TX ID 101相關的交易描述了，Car ID為1的汽車于2013年8月1日以12 000美元的價格賣給了Customer ID為4的客戶，該客戶可解析為Wags Auto中SSN為12347的Wags。

根據前面在單實體設計中提供的描述，把樣本數據組織到4實體設計中，就不會丟失任何信息。但是，還可以得到更多好處。數據沒有重復，簡潔、精確，在買賣新汽車、新客戶與Sid的汽車代理公司進行交易時，更便于數據的維護。

7.2.3 行和表

關系范例把數據建模為二維表。表由許多行組成，每一行又包含一組列。在表中，所有的行都具有相同的列結構，但在一些行中，一些列可以沒有值。表的一個例子是員工列表，每個員工都用一行表示。行中的列可能是員工號、姓名和該員工所在的部門代碼。當前沒有分配給任何部門的員工，其部門代碼列為空。再用另一個表表示部門，每個部門占據一行，其列是部門代碼和部門名。

關系表遵循限制和定義數據的規則。在列的級別上，每一列都必須是某種數據類型，例如數字、日期時間或字符。字符數據類型是最常用的，因為它可以接受任何類型的數據。在行的級別上，每一行通常都必須有唯一標識特性。它可以是一列的值，例如前面例子中的員工號和部門號，該列值在不同的行上不能重復。還有定義表之間鏈接的規則，例如每個員工都必須分配一個部門代碼，以匹配部門表中的一行。表7-1～表7-4是表格數據定義的例子(該數據和結構子集取自Oracle提供的樣本模式SCOTT)。

看看表7-1和表7-2中DEPT和EMP表的布局，其二維結構非常清楚。每一行都有固定的長度，每一列也都有固定的長度(在必要時用空格填滿)，行用換行符界定。表7-3顯示了DEPT表中按DEPTNO排列的行，但這是可選的，而不是設計出來的：關系表對其行的順序沒有特別的要求。表7-4顯示，部門號10有一個員工，部門號40沒有員工。用關系模型修改數據通常非常高效。新員工可以追加到員工表中，修改行中的DEPTNO值，員工就可以從一個部門調到另一個部門。

考慮另一種結構，其中數據根據層次結構來存儲。因技術的原因，層次模型在關系模型之前就開發出來了。在計算時代的早期，存儲設備缺乏維護許多獨立文件的能力，而許多關系表都需要這個能力。注意在Oracle數據庫中，這個問題通過從邏輯存儲(表)中抽象出物理存儲(文件)的方式避免了。表和文件之間沒有直接的連接，肯定不是一對一的映射。實際上，許多表可以存儲在幾個文件中。

層次結構在一個單元中存儲所有相關的數據。例如，部門記錄可能包含該部門的所有員工。層次范例可以非常快速、空間的利用非常高效。一次文件訪問，就可以檢索出滿足查詢所需的所有數據。前面列出的員工和部門可以按如下方式進行層次化的存儲：

在這個布局例子中，行和列的長度都是可變的。列用逗號界定，行用換行符界定。如果查詢沿著層次結構導航，數據檢索通常非常高效。如果知道某員工的部門，就可以很快找到該員工。如果不知道，檢索可能會比較慢。如果對數據的修改需要移動數據，就可能出問題。例如，要把員工7566, JONES從RESEARCH移動到SALES，就需要在數據庫上付出極大的努力，因為要完成數據的移動，需要刪除一行中的數據，再把它插入另一行。注意在這個例子中，部門可以沒有員工(OPERATIONS部門)，但員工絕對不能沒有部門。如果一個業務規則指定，所有員工必須在一個部門里，這就很好；但如果沒有這個規則，就不那么妙了。

對于許多類型的數據，關系范例在許多方面的效率都很高，但它不適合所有應用程序。一般規則是，在給系統建模時，關系分析應是第一個采用的方法。只有證明關系分析不適合，才應轉而采用非關系結構。關系模型非常高效的應用程序包括幾乎所有OLTP和DSS系統。關系范例可以應對其硬件需求和開發應用程序所需的技能，但如果數據合適，這就是最通用的模型。例如，維護索引(索引維護表之間的鏈接)的要求可能會有問題，在索引內部和列所在的表中維護索引數據的多個副本所需的空間也可能有問題。盡管如此，關系設計在大多數情況下，仍是最優模型。

許多軟件發布商都發布了遵循關系范例的數據庫管理系統(但精確程度各不相同), Oracle不是唯一的一個。 IBM也許是第一家為其提供主要資源的公司，但其產品(后來集成到DB2中)多年來都沒有移植到非IBM平臺上。Microsoft SQL Server是另一個受運行平臺限制的關系數據庫。而Oracle數據庫從其第1版開始，就總是可以移植到所有主流平臺上。因此，Oracle占據了關系數據庫管理系統(RDBMS)市場的領先位置。

注意一個術語：與習慣于使用Microsoft產品的人們討論關系數據庫時，可能會出現混淆。SQL是一種語言，SQL Server是一個數據庫，但在Microsoft里，術語SQL常常用于指代上述兩者。

7.2.4 創建演示模式

整本書中有許多SQL代碼的示例。這些示例使用Oracle提供的兩個演示模式：HR模式(這是模擬簡單人力資源應用程序的樣本數據)和OE模式(它模擬一個更復雜的訂單錄入應用程序)。

當創建數據庫時可以創建這些模式；這是數據庫配置助手(Database Configuration Assistant)提供的選項。如果它們不存在，稍后可以通過運行數據庫Oracle Home中的某些腳本來創建它們。

7.2.5 用戶和模式

在Oracle中，數據庫用戶是可以登錄數據庫的人。數據庫模式是數據庫中一個用戶擁有的所有對象。這兩個術語常常互換使用，因為用戶和模式之間是一對一關系。注意盡管仍有CREATE SCHEMA命令，但它實際上并沒有創建模式——只是創建模式中的對象的快捷方式。用CREATE USER命令創建用戶時，模式最初創建為空。

模式用于存儲對象。它們可以是數據對象，例如表，也可以是編程對象，例如PL/SQL存儲過程。用戶登錄用于連接數據庫，訪問這些對象。默認情況下，用戶可以訪問其模式下的對象，不能訪問其他用戶的對象，但大多數應用程序修改了這個規則。一般情況下，一個模式用于存儲其他用戶訪問的數據(即使這些用戶不擁有這些數據，但有使用權即可訪問)。實際上，很少有用戶在自己的模式下擁有對象，也沒有創建它們的權限。他們擁有的訪問權(受到嚴格控制)，僅能訪問另一個模式下的對象。這些對象由運行應用程序的所有用戶使用，應用程序的數據存儲在該模式中。相反，擁有數據存儲模式的用戶實際上永遠不能登錄，其模式的唯一作用是保存其他用戶使用的數據。

數據對象不能獨立于模式而存在。換言之，所有表都必須有一個擁有者。擁有者是表所在模式的用戶。表的唯一標識符(或其他模式對象)是用戶名，后跟對象名。兩個同名(結構或內容可能不同)的表不能存在于一個模式中，但可以存在于不同的模式中。如果對象沒有存在于它自己的模式中，要訪問它，用戶就必須用它所在的模式名限定其名稱。例如， HR.EMPLOYEES是用戶HR的模式中的表EMPLOYEES。除非有同義詞，否則一個連接為HR的用戶只能引用沒有模式名限定的EMPLOYEES來訪問它。同義詞是一個結構，使對象能由其他用戶訪問，而無須把其模式名作為前綴。

7.2.6 HR和WEBSTORE模式

HR演示模式由7個表組成，通過主鍵到外鍵的關系鏈接在一起。圖7-7以實體-關系圖的形式顯示了這些表之間的關系。

圖7-7中顯示的兩種關系可能不是很好理解。首先，從EMPLOYEES到EMPLOYEES有多對一的關系。這就是所謂的自引用外鍵(self-referencing foreign key)。這意味著多名員工可能連接到一名員工，因為多名員工可能有一名經理，但經理也是一名員工。這種關系由列manager_id實現(作為employee_id的外鍵)，而employee_id是表的主鍵。

需要解釋的第二種關系是DEPARTMENTS和EMPLOYEES之間的關系，它是雙向的。一個部門對多名員工的關系說明在一個部門中可能有許多員工，EMPLOYEES的department_id列是相對于DEPARTMENTS的主鍵department_id列的外鍵。一名員工對多個部門的關系表示一名員工可能是幾個部門的經理，由DEPARTMENTS中的manager_id列實現，它是相對于EMPLOYEES中的主鍵employee_id列的外鍵。

表7-5顯示了HR模式中各表的列，使用前面講述的符號來表示主鍵(#)、外鍵(\)以及列是可選的(o)還是強制的(*)。

這些表分別如下所示：

● REGIONS包含表示主要地區的行。

● COUNTRIES包含表示每個國家的行，可以將一個國家分配給一個地區。

● LOCATIONS包含單個地址，可將一個地址分配給一個國家。

● DEPARTMENTS包含表示各部門的行，可將一個部門分配給一個地址和一位經理(必須作為員工存在)。

● EMPLOYEES包含表示各員工的行，必須為每個員工分配一項工作，并且可將他分配給一個部門和一位經理，經理本身必須是員工。

● JOBS列出組織中所有可能的工作。多名員工可能有相同的工作。

● JOB_HISTORY列出員工以前從事的工作，由employee_id和start_date唯一標識；一名員工不可能同時從事兩份工作。每個工作歷史記錄都針對一名員工，當時他只有一份工作，是一個部門中的成員。

該HR模式將在本書各章的許多練習和示例中使用，要求必須是可用的。

OE模式比HR模式復雜得多。表結構也復雜許多；它們包含的列定義為嵌套表、用戶定義的數據類型，和XML數據類型。所使用的對象在使用它們時解釋。

7.2.7 演示模式的創建

如果使用的數據庫專門用于研究SQL考試，就應該已經創建了演示模式。在創建數據庫時，DBCA把它們顯示為選項。創建數據庫后，模式可能需要解鎖，設置其密碼。默認情況下，賬戶是鎖定的，這表示無法登錄它。下面的命令在SQL *Plus或SQL Developer上執行，可以使用密碼hr和oe登錄為用戶HR和OE：

        alter user hr account unlock identified by hr;
        alter user oe account unlock identified by oe;

只有用具有數據庫管理員權限的用戶身份(例如SYSTEM)連接數據庫，才能執行這些alter user命令。

如果在創建數據庫時沒有創建模式，那么通過運行安裝在數據庫Oracle Home中的腳本就可以創建它們。如果這些腳本不存在，就可以從Oracle 上下載并安裝Oracle Database Examples軟件。必須以具有SYSDBA特權的用戶身份從SQL*Plus或者SQL Developer運行這些腳本。在運行時，腳本會提示需要某些值。例如，在Linux系統上，先從操作系統提示符下啟動SQL*Plus：

        sqlplus / as sysdba

這一連接有許多可選參數，但如果數據庫與SQL*Plus 在同一臺計算機上運行，上面的語句通常就會起作用。然后從SQL>提示符調用腳本：

        SQL> @? /demo/schema/human_resources/hr_main.sql

“? ”字符是一個變量，SQL*Plus會將其擴展成Oracle Home目錄的路徑。腳本會提示需要輸入HR口令、默認表空間、臨時表空間、SYS口令和腳本運行的日志文件的目標。默認表空間和臨時表空間的典型值是USERS和TEMP，但必須已經創建這些值。腳本運行之后，就可以作為新的HR用戶連接到數據庫。要驗證這一點，請運行下面的語句：

        SQL> show user;

你會發現當前用戶作為HR連接到數據庫；然后運行：

        SQL> select table_name from user_tables;

你會看到HR模式中7個表的列表。

要創建OE模式，可使用相同的過程，指定腳本：

        ?/demo/schema/order_entry/oe_main.sql

在Windows上創建模式的過程是相同的，只有路徑界定符不同——大多數操作系統使用斜杠，而Windows使用反斜杠。所以Windows HR創建腳本的路徑是：

        @? \demo\schema\human_resources\hr_main.sql

注意運行這些模式創建腳本，會先刪除已有的模式。刪除模式意味著，刪除其中的所有項，再刪除用戶。這不應出問題，除非模式用于某些需要保留的開發工作。