正規形
データベース設計
Lis Sulmont
Curriculum Manager
正規化
データ内に繰り返し存在するグループを識別し、新しいテーブルを作成する
形式的な定義:
正規化の目的:
- リレーショナルスキーマの冗長性レベルを把握する
- 冗長性を除去するため、スキーマ変換のメカニズムを提供する
1 エイドリエン・ワット著、『データベース設計第2版』
正規形（NF）
正規化の程度が低い順:
- 第1正規形（1NF）
- 第2正規形（2NF）
- 第3正規形（3NF）
- 基本キー正規形（EKNF）
- ボイス・コッド正規形（BCNF）
$$
- 第4正規形（4NF）
- 必須タプル正規形（ETNF）
- 第5正規形（5NF）
- ドメインキー正規形（DKNF）
- 第6正規形（6NF）
1 https://en.wikipedia.org/wiki/Database_normalization
1NFのルール
- 各レコードは一意 - 重複行なし
- 各セルには1つの値のみが入る
最初のデータ
| 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 AND を満たす
- 主キーが1列の場合
- その後、自動的に2NFを満たす
- 複合主キーの場合
- 非キー列がすべての複合主キーに依存している必要がある
- 主キーが1列の場合
最初のデータ
| 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のメールアドレスを更新するには:
- 複数のレコードを更新しないと、不整合が生じる
- ユーザーが冗長性について把握する必要あり
挿入異常
属性が欠けているためレコードを追加できない
| 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 |
Student 230 を削除すると、Cleaning Data in R のデータはどうなるか?
データ異常
正規化が不十分な場合のリスクは？
1. 更新異常
2. 挿入異常
3. 削除異常
データベースが正規化されているほど、異常は起こりにくくなる
前回の動画で学んだ正規化の欠点も忘れずに
練習しましょう！
データベース設計
