2024年01月19日玄貓（BlackCat）

資料函式庫建模邏輯實體與維度設計

本文探討資料函式庫建模的邏輯和實體階段，涵蓋概念、邏輯和實體階段的設計重點，並以書籍資料函式庫為例說明如何將概念模型轉換為物理模型。同時，文章也詳細解釋了資料正規化的步驟和重要性，以及 OLTP 和分析系統的差異。此外，本文也介紹了維度建模的概念、星型架構的設計方法，以及與之相關的資料倉儲和商業智慧應用。

資料函式庫系統設計

資料函式庫建模正規化 ERD 星型架構維度建模 OLTP

資料函式庫設計的流程通常分為概念、邏輯和實體三個階段。概念階段著重於定義實體、屬性和關係，而邏輯階段則關注資料正規化，以減少冗餘並提升資料完整性。實體階段則將邏輯模型轉換為特定資料函式庫系統的物理設計，包含定義儲存結構、索引和資料型別。以書籍資料函式庫為例，書籍、作者和類別是主要的實體，它們之間的關係透過關聯式模型表示，並在實體階段轉換為 SQL 表格和欄位。正規化過程旨在消除資料冗餘和異常，例如，將書籍表格拆分為書籍、作者和型別等多個表格，以避免資料不一致。實體設計階段則需根據所選用的資料函式庫系統（如 MySQL）調整表格結構和資料型別，並定義主鍵和外部索引鍵約束，以確保資料函式庫的最佳化設計。

資料函式庫建模的邏輯與實體階段

在資料函式庫設計中，實體關係圖（ERD）是概念建模的基礎。實體以矩形框表示，代表真實世界的物件或概念，例如書籍或作者。關係以菱形表示，說明實體之間的關聯。屬性則以陰影框表示，描述實體的特徵或特性，如名稱或出版日期。

概念階段的重點

在概念階段，我們關注的是如何正確地表示實體、屬性和關係。這包括識別關鍵屬性（帶下劃線的陰影框）和非關鍵屬性（不帶下劃線的陰影框）。此外，還需要考慮基數和參與約束，以定義關係中的例項數量。

邏輯階段的重點

在邏輯階段，我們關注的是資料的正規化，以消除冗餘、提高資料完整性並最佳化查詢效能。這導致了一個正規化的邏輯模型，能夠準確反映實體之間的關係和依賴。

邏輯階段的步驟

重構實體關係架構：根據特定的標準最佳化架構，不依賴於特定的邏輯模型。
轉換為邏輯模型：將最佳化的 ERD 轉換為特定的邏輯模型，如關聯式資料函式庫模型。

在關聯式資料函式庫模型中，每個實體被表示為一個表格，實體的屬性成為表格的欄位。主要鍵約束被標示為每個表格的主要鍵欄位。此外，多對多關係透過單獨的聯接表格來表示，該表格包含參考對應實體的外部索引鍵。

轉換規則

實體轉換：實體 E 被轉換為表格 T，E 的名稱成為 T 的名稱，E 的主要鍵成為 T 的主要鍵，E 的簡單屬性成為 T 的簡單屬性。
關係轉換：
- N:1 關係：在表格 T1 中定義一個外部索引鍵，參考表格 T2 的主要鍵，以建立兩個表格之間的連線。
- N:N 關係：建立一個交叉參考表格來表示關係 REL，REL 的主要鍵被定義為兩個表格 T1 和 T2 的主要鍵的組合，這些主要鍵在交叉參考表格中作為外部索引鍵。

實際案例

假設我們有一個書籍資料函式庫的概念模型，如圖 2-2 所示。

圖 2-2：書籍資料函式庫的邏輯 ERD 範例

此圖示說明瞭書籍、作者和類別之間的關係。

圖表翻譯： 此圖表呈現了書籍資料函式庫的邏輯結構，包括書籍、作者和類別三個主要實體，以及它們之間的關係。其中，書籍和作者之間存在多對多的關係，因此需要一個交叉參考表格來表示這種關係。

邏輯模型的建立

根據轉換規則，我們將實體對映到表格，並建立必要的關係。例如，作者、書籍和類別被直接對映到相應的表格。書籍和類別之間的 1:N 關係透過在書籍表格中新增一個外部索引鍵來參考類別表格。書籍和作者之間的 N:N 關係則透過建立一個新的交叉參考表格（釋出表格）來表示，該表格包含書籍 ID 和作者 ID 的組合作為主要鍵。

實體階段的重點

在實體階段，我們將正規化的邏輯模型轉換為特定的資料函式倉管理系統（DBMS）中的物理資料函式庫設計。這包括定義儲存結構、索引策略和資料型別，以確保高效的資料儲存和檢索。

MySQL 中的物理模型範例

CREATE TABLE category (
  category_id INT PRIMARY KEY,
  category_name VARCHAR(255)
);

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  ISBN VARCHAR(13),
  title VARCHAR(50),
  summary VARCHAR(255),
  category_id INT,
  FOREIGN KEY (category_id) REFERENCES category(category_id)
);

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(255),
  date_birth DATETIME
);

CREATE TABLE publishes (
  book_id INT,
  author_id INT,
  publish_date DATE,
  planned_publish_date DATE,
  FOREIGN KEY (book_id) REFERENCES books(book_id),
  FOREIGN KEY (author_id) REFERENCES authors(author_id),
  PRIMARY KEY (book_id, author_id)
);

程式碼解密：

上述 SQL 陳述式定義了書籍資料函式庫的物理模型，包括四個表格：類別、書籍、作者和釋出。

category 表格儲存類別資訊，主要鍵為 category_id。
books 表格儲存書籍資訊，主要鍵為 book_id，並包含一個外部索引鍵 category_id 以參考 category 表格。
authors 表格儲存作者資訊，主要鍵為 author_id。
publishes 表格是一個交叉參考表格，用於表示書籍和作者之間的 N:N 關係，其主要鍵是 book_id 和 author_id 的組合，並包含兩個外部索引鍵以參考 books 和 authors 表格。

這個物理模型是根據前面的邏輯模型建立的，能夠有效地儲存和管理書籍資料函式庫中的資料。透過這種結構化的表示方法，我們能夠清晰地理解不同實體之間的關係，並對資料函式庫進行高效的操作。

資料函式庫設計與資料正規化

在範例2-1中，我們建立了四個表格：category、books、authors和publishes。實體設計階段會根據MySQL資料函式庫系統對表格結構、資料型態和約束進行微調，以確保資料函式庫的最佳化設計。

實體設計的重點

在category表格中，category_id欄位可以定義為INT型別，並設定為主鍵，以確保資料的唯一性。同樣地，category_name欄位可以定義為VARCHAR(255)，以容納不同長度的類別名稱。

在books表格中，可以為各個欄位分配適當的資料型態和長度，例如book_id（INT）、ISBN（VARCHAR(13)）、title（VARCHAR(50)）和summary（VARCHAR(255)）。此外，category_id欄位可以設定為外部索引鍵，參照category表格中的category_id欄位。值得注意的是，每個ISBN程式碼都是由13個字元組成的字串，因此不需要使用更長的字串型別。

同樣地，在authors表格中，可以為各個欄位定義適當的資料型態，例如author_id（INT）、author_name（VARCHAR(255)）和date_birth（DATETIME）。

在publishes表格中，我們定義了外部索引鍵約束，以建立books表格中的book_id欄位和authors表格中的author_id欄位之間的關係。同時，外部索引鍵由兩個表格的主鍵組成。

資料正規化的過程

資料正規化技術包含多個步驟，旨在將資料組織成邏輯且高效的結構。範例2-2展示了包含幾個相關屬性的 books 表格。

範例2-2：待正規化的 `books` 表格

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  publication_year INT,
  genre VARCHAR(50)
);

第一步：第一正規化（1NF）

第一正規化要求消除重複群組，將資料分解為更小的原子單位。我們將建立一個名為 authors 的表格，包含作者ID和作者姓名。 books 表格現在參照作者ID，而不是重複儲存完整的作者姓名，如範例2-3所示。

範例2-3：第一正規化後的 `books` 表格

-- Authors 表格
CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(100)
);

-- Books 表格
CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  publication_year INT,
  genre VARCHAR(50),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

第二步：第二正規化（2NF）

第二正規化檢查資料內部的依賴關係。我們觀察到出版年份功能上依賴於書籍ID，而型別則依賴於作者ID。為了符合2NF的要求，我們將 books 表格拆分為三個表格：

books：包含書籍ID和標題
authors：包含作者ID和姓名
bookDetails：儲存書籍ID、出版年份和型別

這樣可以確保每個欄位僅依賴於主鍵，如範例2-4所示。

範例2-4：第二正規化後的 `books` 表格

-- Authors 表格
CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(100)
);

-- Books 表格
CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100)
);

-- Book details 表格
CREATE TABLE bookDetails (
  book_id INT PRIMARY KEY,
  author_id INT,
  genre VARCHAR(50),
  publication_year INT,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

第三步：第三正規化（3NF）

第三正規化關注消除傳遞依賴。我們意識到，可以透過 bookDetails 表格從書籍ID推匯出型別。為瞭解決這個問題，我們建立了一個名為 genres 的新表格，包含型別ID和型別名稱，而 bookDetails 表格現在參照型別ID，而不是直接儲存型別名稱，如範例2-5所示。

範例2-5：第三正規化後的 `books` 表格

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(100)
);

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100)
);

CREATE TABLE genres (
  genre_id INT PRIMARY KEY,
  genre_name VARCHAR(50)
);

CREATE TABLE bookDetails (
  book_id INT PRIMARY KEY,
  author_id INT,
  genre_id INT,
  publication_year INT,
  FOREIGN KEY (author_id) REFERENCES authors(author_id),
  FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

#### 內容解密：

此範例展示瞭如何透過正規化過程最佳化資料函式庫設計。首先，我們將資料分解為更小的原子單位，以消除重複群組。接著，我們檢查資料內部的依賴關係，並將表格拆分為多個相關的表格，以確保每個欄位僅依賴於主鍵。最後，我們消除了傳遞依賴，以進一步提高資料的完整性和一致性。

資料正規化的重要性

資料正規化對於實作高效的交易處理和儲存至關重要。線上上交易處理系統（OLTP）中，正規化的資料函式庫設計可以提高效能、減少資料冗餘並改善資料完整性。然而，在分析專案或系統中，使用者通常希望能夠快速檢索所需的資料，而不需要進行大量的連線操作。因此，在分析資料函式庫設計中，可能需要採用不同的策略來最佳化查詢效能。

OLTP與分析系統的差異

OLTP系統旨在高效處理和儲存交易，並檢索交易資料，如客戶訂單、銀行交易或薪水。而分析系統則需要包含歷史資料，並提供跨多個OLTP系統的整合檢視。因此，在分析資料函式庫設計中，需要考慮不同的需求和最佳化策略。

#### 內容解密：

OLTP系統與分析系統有不同的設計目標和最佳化策略。OLTP系統注重交易的即時處理和儲存，而分析系統則需要包含歷史資料，並提供跨多個OLTP系統的整合檢視。因此，在設計分析資料函式庫時，需要考慮查詢效能、資料儲存和資料整合等多個因素。

資料建模與維度建模的基礎

在探討資料建模的世界之前，我們必須先了解其基礎型別。資料建模是設計和組織資料函式庫以有效儲存和管理資料的基礎工作。我們的旅程將從探索這種基礎的資料建模型別開始，這構成了線上交易處理（OLTP）系統的基礎。接著，我們將轉向討論針對線上分析處理（OLAP）環境最佳化的資料建模方法。透過這種區分，我們旨在提供對資料建模兩個方面的全面理解，為後續章節中探討分析工程方法及其應用奠定基礎。

維度資料建模

資料建模是設計和組織資料函式庫以有效儲存和管理資料的基本方面。如前所述，它涉及定義系統中資料實體的結構、關係和屬性。

維度建模是一種流行的資料建模方法，專注於支援分析和報告需求的資料建模。維度建模特別適合用於資料倉儲和商業智慧（BI）應用。它強調建立由事實表和維度表組成的維度模型，其中事實表代表可衡量的資料，而維度表提供描述性背景。透過使用維度建模技術，如星型架構和雪花型架構，資料可以被組織成簡化複雜查詢並實作高效資料分析的形式。

資料建模和維度建模之間的關係在於它們的互補性質。資料建模為捕捉和結構化資料提供了基礎，而維度建模則提供了一種專門的技術來支援分析和報告需求。共同地，這些方法使組織能夠設計出強健且靈活的資料函式庫，以促進交易處理和深入的資料分析。

要了解維度建模，我們首先應該向被視為資料倉儲和維度建模之父的兩個人物：Bill Inmon 和 Ralph Kimball 致敬。他們被公認為是企業範圍內資訊收集、管理以及用於決策支援的分析領域的先驅。

他們對資料倉儲主題提出了重要的爭論，每個人都主張不同的哲學和方法。Inmon 提出建立一個涵蓋整個企業的集中式資料倉儲，旨在生成一個全面的 BI 系統。另一方面，Kimball 建議建立多個針對特定部門的小型資料集市，以實作部門級別的分析和報告。他們不同的觀點導致了在資料倉儲設計技術和實施策略上的差異。

除了他們不同的做法之外，Inmon 和 Kimball 還提出了在資料倉儲背景下構建資料的不同方法。Inmon 主張在企業資料倉儲中使用關聯式（ERD）模型，特別是第三正規化形式（3NF）。相反，Kimball 的方法在維度資料倉儲中採用多維模型，利用星型架構和雪花型架構。

Inmon 認為，以關聯式模型構建資料可以確保企業範圍內的一致性。這種一致性促進了在維度模型中建立資料集市的相對容易。另一方面，Kimball 認為，以維度模型組織資料可以促進資訊匯流排，讓使用者能夠更有效地理解、分析、聚合和探索資料不一致性。此外，Kimball 的方法允許直接從分析系統存取資料。相比之下，Inmon 的方法限制了分析系統僅從企業資料倉儲存取資料，需要與資料集市互動以進行檢索。

資料集市

資料集市是資料倉儲的一個特定部分，旨在滿足特定部門或業務單位的獨特需求。

在接下來的章節中，我們將探討三種建模技術：星型架構、雪花型建模以及新興的 Data Vault。Data Vault 由 Dan Linstedt 在 2000 年提出，近年來逐漸受到關注。它遵循一種更正規化的結構，這與 Inmon 的方法並非完全一致，但相似。

星型架構建模

星型架構是一種在關聯式資料倉儲中廣泛使用的建模方法，特別適用於分析和報告目的。它涉及將表分類別為維度表或事實表，以有效地組織和表示業務單位及相關觀察或事件。

維度表用於描述要建模的業務實體。這些實體可以包括各種方面，如產品、人員、地點和概念，包括時間。在星型架構中，您通常會找到一個日期維度表，提供了一套完整的日期用於分析。維度表通常由一個或多個關鍵列組成，這些列作為每個實體的唯一識別碼，以及提供有關實體的其他描述性列。

事實表則儲存業務中發生的觀察或事件。這包括銷售訂單、庫存水平、匯率、溫度和其他可衡量的資料。事實表包含維度鍵列，這些列參考了維度表，以及數字測量列。維度鍵列決定了事實表的維度，並指定了哪些維度是相關的。

-- 建立日期維度表示例
CREATE TABLE DateDimension (
    DateKey DATE PRIMARY KEY,
    FullDate DATE,
    Year INT,
    Quarter INT,
    Month INT,
    Day INT
);

-- 建立銷售事實表示例
CREATE TABLE SalesFact (
    SaleID INT PRIMARY KEY,
    DateKey DATE,
    ProductID INT,
    CustomerID INT,
    SalesAmount DECIMAL(10, 2),
    FOREIGN KEY (DateKey) REFERENCES DateDimension(DateKey)
);

內容解密：

上述 SQL 程式碼展示瞭如何在星型架構中建立日期維度表和銷售事實表。首先，建立了一個名為 DateDimension 的日期維度表，其中包含了日期鍵 DateKey 作為主鍵，以及其他描述日期的屬性如年、季度、月、日等。這使得分析人員能夠輕鬆地根據日期進行分析。

接著，建立了一個名為 SalesFact 的銷售事實表，其中包含了銷售相關的測量值，如 SalesAmount。DateKey 列作為外部索引鍵參考了 DateDimension 表中的 DateKey，從而將銷售資料與日期維度相關聯。這種設計允許使用者透過日期和其他相關維度來查詢和分析銷售資料。

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 內容解密：

rectangle "DateKey" as node1
rectangle "ProductID" as node2
rectangle "CustomerID" as node3

node1 --> node2
node2 --> node3

@enduml

圖表翻譯： 此圖示展示了星型架構中事實表與維度表之間的關係。SalesFact 事實表透過外部索引鍵（如 DateKey、ProductID 和 CustomerID）與 DateDimension、ProductDimension 和 CustomerDimension 維度表相連。這種結構使得查詢和分析能夠高效地進行，因為事實表的每一行資料都與相關的維度資訊相聯絡，從而簡化了複雜查詢並提高了資料分析的效率。

資料函式庫建模的邏輯與實體階段

概念階段的重點

邏輯階段的重點

邏輯階段的步驟

轉換規則

實際案例

圖 2-2：書籍資料函式庫的邏輯 ERD 範例

邏輯模型的建立

實體階段的重點

MySQL 中的物理模型範例

程式碼解密：

資料函式庫設計與資料正規化

實體設計的重點

資料正規化的過程

範例2-2：待正規化的 books 表格

第一步：第一正規化（1NF）

範例2-3：第一正規化後的 books 表格

第二步：第二正規化（2NF）

範例2-4：第二正規化後的 books 表格

第三步：第三正規化（3NF）

範例2-5：第三正規化後的 books 表格

#### 內容解密：

資料正規化的重要性

OLTP與分析系統的差異

#### 內容解密：

資料建模與維度建模的基礎

維度資料建模

資料集市

星型架構建模

內容解密：

範例2-2：待正規化的 `books` 表格

範例2-3：第一正規化後的 `books` 表格

範例2-4：第二正規化後的 `books` 表格

範例2-5：第三正規化後的 `books` 表格