正規化范式
資料庫設計
Lis Sulmont
Curriculum Manager
正規化
找出重複的資料群，並為它們建立新資料表。
更正式的定義：
正規化的目標是：
- 能描述關聯式綱要中的冗餘程度。
- 提供轉換綱要以移除冗餘的機制。
1 Database Design, 2nd Edition by Adrienne Watt
正規化范式（NF）
由低到高的正規化等級：
- 第一正規化（1NF）
- 第二正規化（2NF）
- 第三正規化（3NF）
- 基本鍵正規化（EKNF）
- Boyce-Codd 正規化（BCNF）
$$
- 第四正規化（4NF）
- 基本元組正規化（ETNF）
- 第五正規化（5NF）
- Domain-key 正規化（DKNF）
- 第六正規化（6NF）
1 https://en.wikipedia.org/wiki/Database_normalization
1NF 規則
- 每筆紀錄必須唯一，不得有重複列。
- 每個儲存格只能有一個值。
初始資料
| Student_id | Student_Email | Courses_Completed |
|------------|-----------------|----------------------------------------------------------|
| 235 | [email protected] | Introduction to Python, Intermediate Python |
| 455 | [email protected] | Cleaning Data in R |
| 767 | [email protected] | Machine Learning Toolbox, Deep Learning in Python |
轉為 1NF 後
| Student_id | Student_Email |
|------------|-----------------|
| 235 | [email protected] |
| 455 | [email protected] |
| 767 | [email protected] |
| Student_id | Completed |
|------------|--------------------------|
| 235 | Introduction to Python |
| 235 | Intermediate Python |
| 455 | Cleaning Data in R |
| 767 | Machine Learning Toolbox |
| 767 | Deep Learning in Python |
2NF
- 必須滿足 1NF，且：
- 若主鍵只有一欄，
- 則自動滿足 2NF。
- 若為複合主鍵，
- 則每個非鍵欄位必須依賴於所有鍵。
- 若主鍵只有一欄，
初始資料
| Student_id (PK) | Course_id (PK) | Instructor_id | Instructor | Progress |
|-----------------|----------------|---------------|---------------|----------|
| 235 | 2001 | 560 | Nick Carchedi | .55 |
| 455 | 2345 | 658 | Ginger Grant | .10 |
| 767 | 6584 | 999 | Chester Ismay | 1.00 |
轉為 2NF 後
| Student_id (PK) | Course_id (PK) | Percent_Completed |
|-----------------|----------------|-------------------|
| 235 | 2001 | .55 |
| 455 | 2345 | .10 |
| 767 | 6584 | 1.00 |
| Course_id (PK) | Instructor_id | Instructor |
|----------------|---------------|---------------|
| 2001 | 560 | Nick Carchedi |
| 2345 | 658 | Ginger Grant |
| 6584 | 999 | Chester Ismay |
3NF
- 已滿足 2NF。
- 無「傳遞相依」：非鍵欄位不能依賴其他非鍵欄位。
初始資料
| Course_id (PK) | Instructor_id | Instructor | Tech |
|----------------|---------------|---------------|--------|
| 2001 | 560 | Nick Carchedi | Python |
| 2345 | 658 | Ginger Grant | SQL |
| 6584 | 999 | Chester Ismay | R |
轉為 3NF 後
| Course_id (PK) | Instructor | Tech |
|----------------|---------------|--------|
| 2001 | Nick Carchedi | Python |
| 2345 | Ginger Grant | SQL |
| 6584 | Chester Ismay | R |
| Instructor_id | Instructor |
|---------------|---------------|
| 560 | Nick Carchedi |
| 658 | Ginger Grant |
| 999 | Chester Ismay |
資料異常
若正規化不足，風險是什麼？
1. 更新異常
2. 插入異常
3. 刪除異常
更新異常
由冗餘造成更新時的資料不一致
| Student_ID | Student_Email | Enrolled_in | Taught_by |
|------------|-----------------|-------------------------|---------------------|
| 230 | [email protected] | Cleaning Data in R | Maggie Matsui |
| 367 | [email protected] | Data Visualization in R | Ronald Pearson |
| 520 | [email protected] | Introduction to Python | Hugo Bowne-Anderson |
| 520 | [email protected] | Arima Models in R | David Stoffer |
要更新學生 520 的 email：
- 需要更新多筆紀錄，否則會產生不一致。
- 進行更新的人必須知道有冗餘存在。
插入異常
因缺少屬性而無法新增紀錄
| Student_ID | Student_Email | Enrolled_in | Taught_by |
|------------|-----------------|-------------------------|---------------------|
| 230 | [email protected] | Cleaning Data in R | Maggie Matsui |
| 367 | [email protected] | Data Visualization in R | Ronald Pearson |
| 520 | [email protected] | Introduction to Python | Hugo Bowne-Anderson |
| 520 | [email protected] | Arima Models in R | David Stoffer |
無法插入已註冊但尚未選任何課程的學生。
刪除異常
刪除紀錄時意外遺失其他資料
| Student_ID | Student_Email | Enrolled_in | Taught_by |
|------------|-----------------|-------------------------|---------------------|
| 230 | [email protected] | Cleaning Data in R | Maggie Matsui |
| 367 | [email protected] | Data Visualization in R | Ronald Pearson |
| 520 | [email protected] | Introduction to Python | Hugo Bowne-Anderson |
| 520 | [email protected] | Arima Models in R | David Stoffer |
如果刪除學生 230，關於 Cleaning Data in R 的資料會怎樣？
資料異常
若正規化不足，風險是什麼？
1. 更新異常
2. 插入異常
3. 刪除異常
正規化程度越高，越不易發生資料異常。
別忘了前一支影片提到的正規化缺點。
一起來練習吧！
資料庫設計
