資料函式庫設計正規化實踐

– 9 分鐘閱讀

關聯式資料函式庫設計的核心概念是正規化，它能有效減少資料冗餘、提升資料一致性並增強資料函式庫效能。正規化過程包含多個階段，每個階段都根據前一階段，逐步完善資料函式庫結構。本文將著重於第一正規化 (1NF) 和第二正規化 (2NF)，並以學生課程資料表為例，逐步說明如何應用這些正規化原則。首先，我們會分析未正規化的資料表，找出其設計缺陷，例如欄位包含多個值，違反原子性原則。接著，我們會逐步調整表格結構，使其符合 1NF 的要求，並使用 SQL 程式碼展示如何建立符合 1NF 的資料表。最後，我們將進一步最佳化設計，將學生資訊和課程資訊分離至獨立的資料表，並建立關聯表，以符合 2NF 的要求，同時確保資料函式庫的完整性和一致性。

資料函式庫設計的堅實基礎

在資料函式庫設計中，建立堅實的基礎至關重要。這不僅涉及瞭解資料模型和關鍵術語，還需要探討如何在現實世界中應用這些原理，以提升資料函式庫的效能、安全性和可擴充套件性。

詳細理解關聯模型

現代資料函式庫系統的核心是關聯模型，這是一種由E.F. Codd於1970年提出的資料組織和管理方法。該模型已成為資料函式庫設計和管理的根本，提供了一種結構化的方式來表示、儲存和檢索資訊。

表格結構

關聯模型使用表格（稱為關聯）來封裝資料，使其邏輯清晰且易於理解。表格由行和列組成。行代表個別例項或記錄，每個記錄包含一組獨特的屬性。列則包含描述實體的特定資料點。

例如，在名為「客戶」的表格中，每一行可能對應一個特定的客戶，而列則包含客戶ID、姓名、電子郵件和電話號碼等資訊。

主鍵和外部索引鍵

主鍵和外部索引鍵是關聯模型的關鍵要素。主鍵用於唯一標識表格中的每一行，確保資料的唯一性和完整性。外部索引鍵則透過參照另一個表格的主鍵，在表格之間建立關係，從而建立一個相互關聯的資訊網路。

雖然外部索引鍵是關聯模型的一部分，但在使用時需要謹慎。與主鍵不同，外部索引鍵在大型實施中可能會帶來操作上的問題。

注意

外部索引鍵的操作複雜性在Shlomi Noach的部落格中有詳細解釋，網址為https://code.openark.org/blog/mysql/the-problem-with-mysql-foreign-key-constraints-in-online-schema-changes。

資料正規化

正規化是關聯模型中的重要實踐，旨在透過將複雜的表格分解為更小、更專注的表格來減少資料冗餘和不一致性。正規化透過遵循特定的規則（稱為正規形式）來實作，這不僅提高了資料完整性，還減少了因冗餘或衝突資訊而導致的異常。

資料完整性

資料完整性是任何資料函式庫系統中的首要關注點，關聯模型透過應用主鍵、外部索引鍵和其他約束來自然地維護資料完整性。這有助於防止錯誤和不一致，從而確保決策和資料分析的準確性。

結構化查詢語言（SQL）

在實踐中，SQL是關聯模型的通用語言。SQL提供了一種標準化的語法，用於建立、修改和查詢資料函式庫。它讓使用者能夠定義表格、插入資料、檢索特定資訊，甚至執行複雜的操作，如連線多個表格以提取有價值的見解。

辨識實體、屬性和關係

在設計資料函式庫時，初始步驟之一是辨識實體、屬性和關係。這構成了資料函式庫結構的基礎，即實體關係圖（ERD）。

  • 實體：這些是您想要儲存資訊的主要物件或概念，通常會成為關聯資料函式庫中的表格。例如，在學校資料函式庫中，實體可能包括學生、課程和教師。
  • 屬性：這些是描述或提供有關實體的更多資訊的個別資料片段，成為表格中的列（或欄位）。例如，對於學生實體，屬性可能包括學生ID、名字、姓氏、出生日期和地址等。
  • 關係：這些定義了實體之間的相互關係，決定了表格如何在關聯資料函式庫中連線。關係可以是一對一、一對多或多對多。

辨識實體和關係的步驟

在設計資料函式庫模型之前，需要經過幾個步驟來辨識實體及其關係。這些步驟對於定義應用程式和業務需求的發現階段至關重要。

  1. 收集需求：與利益相關者會面，瞭解系統的範圍和需求。
  2. 辨識實體：在需求中尋找名詞。例如，如果需求指出學生需要註冊課程，那麼「學生」和「課程」就是潛在的實體。

圖表說明

圖 2.7 – 一對一實體關係

此圖示展示了一對一的實體關係，例如一個人與其社會安全號碼之間的關係。

圖 2.8 – 一對多實體關係

此圖示展示了一對多的實體關係，例如一位教師可以教授多門課程，但每門課程只由一位教師教授。

圖 2.9 – 多對多實體關係

此圖示展示了多對多的實體關係，例如學生和課程之間的關係，因為一名學生可以註冊多門課程，而每門課程也可以有多名學生。這種關係通常透過一個聯結表格或關聯實體來表示，例如「學生課程」表格，其中包含學生ID和課程ID等屬性。

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    DateOfBirth DATE,
    Address VARCHAR(255)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(255),
    Credits INT
);

CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

內容解密：

  • 上述SQL程式碼建立了三個表格：Students、Courses和StudentCourses。
  • Students表格儲存學生的資訊，主鍵是StudentID。
  • Courses表格儲存課程的資訊，主鍵是CourseID。
  • StudentCourses是一個聯結表格，用於表示學生和課程之間的多對多關係。它包含兩個外部索引鍵：StudentID和CourseID，分別參考Students和Courses表格的主鍵。這種設計確保了資料的一致性和完整性，並且便於查詢學生註冊的課程以及每門課程的註冊學生。

建立資料函式庫設計的堅實基礎

在開發高效能且穩健的資料函式庫系統時，良好的資料函式庫設計是至關重要的。本篇文章將探討資料函式庫設計的基本原則，並介紹如何使用實體關係圖（ERD）來視覺化資料函式庫結構。我們還將討論先進的資料函式庫設計技術，包括正規化，以減少資料冗餘和提高資料完整性。

瞭解資料函式庫設計的基本步驟

  1. 識別實體：首先，確定系統中需要儲存資訊的主要物件或實體，例如學生、課程和教師。
  2. 定義屬性：對於每個實體，列出描述該實體的屬性或資料點。
  3. 識別關係：確定實體之間的關係，例如學生註冊課程或教師教授學生。
  4. 建立初步ERD：根據上述資訊，建立ERD以視覺化實體、屬性和關係。
  5. 審查和改進：與相關人員驗證ERD，並根據需要進行調整。
  6. 正規化資料函式庫：透過正規化過程，系統地分解表格以最小化資料冗餘並防止異常。

建立ER圖

建立ER圖是資料函式庫設計中的關鍵步驟。雖然有多種工具可以幫助建立ER圖，但瞭解其背後的原理至關重要。MySQL Workbench是一個功能強大的工具，可以用於建立和管理MySQL資料函式庫的ER圖。

使用MySQL Workbench建立ER圖

MySQL Workbench提供了一套完整的工具，用於資料函式庫設計、開發和管理。利用其ER圖功能，可以直觀地設計和修改資料函式庫結構。

先進的資料函式庫設計

在滿足業務需求的基礎上，好的資料函式庫設計需要在簡單性和效率之間取得平衡。正規化是實作這一目標的關鍵技術。

正規化：減少冗餘和提高資料完整性

正規化的目標是消除表格中的冗餘資料，從而使資料更加乾淨和一致。這不僅節省了儲存空間，還提高了查詢效率。

達成正規化形式：1NF、2NF和3NF
  1. 第一正規化形式（1NF）：
    • 每行必須是唯一的，主鍵是必要的。
    • 表格中只包含原子值，個別欄位不應包含集合、陣列或列表。
CREATE SCHEMA IF NOT EXISTS `DesignAndModeling`
DEFAULT CHARACTER SET utf8mb4;

CREATE TABLE `StudentCourses` (
  `StudentID` int unsigned NOT NULL AUTO_INCREMENT,
  `StudentName` varchar(45) DEFAULT NULL,
  `Courses` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`StudentID`)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

內容解密：

  • CREATE SCHEMA陳述式用於建立一個新的資料函式庫綱要（schema），並指定預設的字元集。
  • CREATE TABLE陳述式定義了一個名為StudentCourses的表格，用於儲存學生的課程資訊。
  • StudentID欄位被設定為主鍵，且自動遞增，確保每行資料的唯一性。
  • StudentName和Courses欄位用於儲存學生的姓名和課程資訊。

然而，上述表格設計存在問題，因為Courses欄位包含多個值，這違反了1NF。更好的設計應該是將課程資訊拆分到另一個表格中，以實作正規化。

資料函式庫設計的基礎：正規化處理

在資料函式庫設計中，正規化是一個至關重要的過程，它確保資料函式庫的結構能夠有效地儲存資料、減少資料冗餘並提高資料完整性。本文將探討第一正規化（1NF）和第二正規化（2NF）的概念及其在資料函式庫設計中的應用。

第一正規化（1NF）

第一正規化要求資料表中的每個欄位都是原子性的，也就是說每個欄位都不能再被分割。此外，資料表中的每一列都必須是唯一的。

1NF 的問題與解決方案

考慮以下未經正規化的 StudentCourses 資料表：

| StudentID | StudentName | Courses | |

|

–|

| | 101 | John | Math, English| | 102 | Emily | History | | 103 | Mike | Math, Science, History|

這個資料表違反了 1NF，因為 Courses 欄位包含了多個值。為瞭解決這個問題，我們需要將 Courses 欄位拆分成多個獨立的列，每個列代表一個學生和一門課程的組合。

建立 1NF 資料表

CREATE TABLE IF NOT EXISTS `DesignAndModeling`.`StudentCourses`
(
  `EnrollmentID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `StudentID` INT NOT NULL,
  `StudentName` VARCHAR(45) NULL,
  `Course` VARCHAR(45) NULL,
  PRIMARY KEY (`EnrollmentID`)
)
ENGINE = InnoDB;

轉換後的 StudentCourses_in_1NF 資料表如下：

| EnrollmentID | StudentID | StudentName | Course | |

|

|

–|

–| | 1 | 101 | John | Math | | 2 | 101 | John | English | | 3 | 102 | Emily | History | | 4 | 103 | Mike | Math | | 5 | 103 | Mike | Science | | 6 | 103 | Mike | History |

#### 內容解密：

  • EnrollmentID 作為主鍵，確保每一行的唯一性。
  • 將原本的 Courses 欄位拆分，確保了原子性。
  • StudentID 和 Course 的組合也可以作為複合主鍵，但加入 EnrollmentID 簡化了這個過程。

最佳化資料函式庫設計

為了進一步最佳化資料函式庫設計，我們應該將學生資訊和課程資訊分開儲存，分別建立 Student、Course 和 StudentCourses 三個資料表，以確保資料的獨立性和完整性。

建立獨立的資料表

-- 建立 Student 資料表
CREATE TABLE Student (
  StudentID BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  StudentName VARCHAR(100),
  DateOfBirth DATE,
  Major VARCHAR(100),
  ContactNumber VARCHAR(15)
);

-- 建立 Course 資料表
CREATE TABLE Course (
  CourseID VARCHAR(10) PRIMARY KEY,
  CourseName VARCHAR(100)
);

-- 建立 StudentCourses 資料表
CREATE TABLE StudentCourses (
  EnrollmentID BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  StudentID BIGINT UNSIGNED,
  CourseID VARCHAR(10),
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
  FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

#### 內容解密：

  • 將學生資訊和課程資訊分開儲存，提高了資料的獨立性。
  • StudentCourses 資料表使用外部索引鍵參考 Student 和 Course 資料表的主鍵，確保了參照完整性。
  • 這種設計支援了靈活性和可擴充套件性，同時減少了資料冗餘。

第二正規化（2NF）

第二正規化要求資料表必須滿足以下條件：

  1. 資料表已經達到 1NF。
  2. 資料表中沒有部分相依性，所有非主鍵屬性都必須完全功能相依於主鍵。

#### 內容解密：

  • 如果主鍵是複合主鍵，則非主鍵欄位不能只相依於主鍵的一部分。
  • 上述最佳化後的資料函式庫設計已經滿足了 2NF 的要求，因為每個非主鍵屬性都完全相依於各自資料表的主鍵。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。