正規化
正規化とは、データの冗長性を排除し、1つの事実を1箇所に保存する（"1 fact in 1 place"）ために、テーブルを適切に分割することです。
「データの定義」で定義されたデータをエクセルで確認し、タプル（行）が増える様子を想像してください。
例えば、「問題データ」の区分に「データベーススペシャリスト」を登録し、その問題が複数登録された後に「データベーススペシャリスト」が「データサイエンティストスペシャリスト」のように名称変更された場合、一部のデータのみを更新してしまうと、区分には「データベーススペシャリスト」と「データサイエンティストスペシャリスト」の両方が存在してしまい、1つの事実が複数箇所に存在することになります。
また、「問題回答ログ」では、ユーザーのニックネームが複数存在する場合に、ニックネーム変更時に全てを正しく更新できないと、同じ問題が発生します。これも1つの事実が1箇所に保存されない状態です。
さらに、回答1～4という設計では、回答が4つまでしか登録できません。将来的に回答が5つや6つ必要になった際に対応できず、逆に回答を2つや3つに減らす場合は不要な項目が生まれます。過去の同じ問題が複数登録されることがあり、誤字などの入力ミスによって異なる問題として扱われることもあります。
正規化は、このように発生しうる問題を事前に考慮し、データの一貫性を保つために行います。ただし、闇雲にテーブルを分解すると、データが損失し、復元できなくなることがあります。正規化の基本は、テーブル同士を結合することで元の状態に戻せる（情報損失のない分解）ことを前提に進めることです。
正規化には、第1正規形から第5正規形、およびボイス・コッド正規形があります。以下に各正規形について説明します。
非正規形
非正規形とは、正規化されていない状態で単一でない値の属性が含まれていることです。
エクセルで例えるとセル結合がそれに辺り、以下のようなイメージです。
同じセルであれば結合してしまうようなエクセルの表は非正規形になります。
- 分類
- IPA
- 区分
- データベーススペシャリスト
- 実施年度
- 2020
- 問題番号
- 1
- 2
- 問題内容
- 図のデータベース1,2は互いのデータの複製をもつ冗長構成である。クライアントからの更新・参照要求を受けたデータベースサーバ(以下、サーバという)は直下のデータベースを更新・参照し、他方のサーバにデータ更新を通知する。通知を受けたサーバは直下のデータベースに更新を反映する。サーバ1,2間のネットワークが分断し、データ更新を通知できなくなったとき、CAP定理で重視する特性(C,A,P)に対するサーバの挙動のうち、適正な組み合わせはどれか。
- Base特性を満たし、次の特徴をもつNoSQLデータベースシステムに関する記述のうち、適切なものはどれか[NoSQLデータベースシステムの特徴]・ネットワーク上に分散した複数のノードから構成される。・一つのノードでデータを更新した後、他の全てのノードにその更新を反映する。
- 図
- /ipa/db/2020/1.jpg
- /ipa/db/2020/2.jpg
- 回答ア
- ア
- ア
- 回答イ
- イ
- イ
- 回答ウ
- ウ
- ウ
- 回答エ
- エ
- エ
- 回答
- イ
- イ
- 解説
- 冗長構成だから
- 冗長構成だから
- 作成日
- 2021/5/29 16:00
- 2021/5/29 16:00
- 更新日
- 2021/5/29 16:00
- 2021/5/29 16:00
第1正規形
第1正規形とは、全ての属性がアトミックになっていることです。
アトミックとは、それ以上分解ができない単一のことです。
アトミックでないとはエクセルで例えるとセル結合がなく、以下のようなイメージです。
- 分類
- IPA
- IPA
- 区分
- データベーススペシャリスト
- データベーススペシャリスト
- 問題実施
- 2020
- 2020
- 問題番号
- 1
- 2
- 問題内容
- 図のデータベース1,2は互いのデータの複製をもつ冗長構成である。クライアントからの更新・参照要求を受けたデータベースサーバ(以下、サーバという)は直下のデータベースを更新・参照し、他方のサーバにデータ更新を通知する。通知を受けたサーバは直下のデータベースに更新を反映する。サーバ1,2間のネットワークが分断し、データ更新を通知できなくなったとき、CAP定理で重視する特性(C,A,P)に対するサーバの挙動のうち、適正な組み合わせはどれか。
- Base特性を満たし、次の特徴をもつNoSQLデータベースシステムに関する記述のうち、適切なものはどれか[NoSQLデータベースシステムの特徴]・ネットワーク上に分散した複数のノードから構成される。・一つのノードでデータを更新した後、他の全てのノードにその更新を反映する。
- 図
- /ipa/db/2020/1.jpg
- /ipa/db/2020/2.jpg
- 回答ア
- ア
- ア
- 回答イ
- イ
- イ
- 回答ウ
- ウ
- ウ
- 回答エ
- エ
- エ
- 回答
- イ
- イ
- 解説
- 冗長構成だから
- 冗長構成だから
- 作成日
- 2021/5/29 16:00
- 2021/5/29 16:00
- 更新日
- 2021/5/29 16:00
- 2021/5/29 16:00
しかし、このイメージでは特定のレコードを取得する際、主キーが文字列になるとシステム管理が複雑になる可能性があります。
主キーとは、複数のデータからレコード（問題）を特定するために、一意である必要があるキーのことです。
例えば、「区分がデータベーススペシャリストで、実施年度が2020年、問題番号が1」という条件に該当するデータが1つしかないなら、これが一意なデータとなります。
IPAのデータベーススペシャリスト試験で2020年の1問目を特定する場合、分類・区分・実施年度・問題番号の4つの項目が必要です。
しかし、これらを文字列としてシステムで扱うのは複雑なので、管理しやすい代用キーを使い、以下のような形に変換します。
- 分類コード
- 1
- 1
- 分類
- IPA
- IPA
- 区分
- 1
- 1
- 区分名
- データベーススペシャリスト
- データベーススペシャリスト
- 実施年度
- 2020
- 2020
- 問題番号
- 1
- 2
- 問題内容
- 図のデータベース1,2は互いのデータの複製をもつ冗長構成である。クライアントからの更新・参照要求を受けたデータベースサーバ(以下、サーバという)は直下のデータベースを更新・参照し、他方のサーバにデータ更新を通知する。通知を受けたサーバは直下のデータベースに更新を反映する。サーバ1,2間のネットワークが分断し、データ更新を通知できなくなったとき、CAP定理で重視する特性(C,A,P)に対するサーバの挙動のうち、適正な組み合わせはどれか。
- Base特性を満たし、次の特徴をもつNoSQLデータベースシステムに関する記述のうち、適切なものはどれか[NoSQLデータベースシステムの特徴]・ネットワーク上に分散した複数のノードから構成される。・一つのノードでデータを更新した後、他の全てのノードにその更新を反映する。
- 図
- /ipa/db/2020/1.jpg
- /ipa/db/2020/2.jpg
- 回答ア
- ア
- ア
- 回答イ
- イ
- イ
- 回答ウ
- ウ
- ウ
- 回答エ
- エ
- エ
- 回答
- イ
- イ
- 解説
- 冗長構成だから
- 冗長構成だから
- 作成日
- 2021/5/29 16:00
- 2021/5/29 16:00
- 更新日
- 2021/5/29 16:00
- 2021/5/29 16:00
分類、区分、回答に代用キーを使い、番号で管理できるようになりました。
第2正規形
第2正規形とは、第1正規形を満たし、、すべての非キー属性が関数従属していることです。
関数従属とは、X(候補キー・スーパーキー)が決まるとY(非キー属性)が特定できることです。
「X → Y」のように表します。
例えば、ユーザIDが決まればニックネームは特定できます。
非キー属性とは、候補キー以外の属性を指します。
前述しているかもしれませんが、候補キーはレコードを一意(1行)にすることができるカラムの組合せです。
問題内容や回答番号でもレコードは特定できるのではと考えてしまうこともありますが、同じ問題内容や回答が別実施年度で発生することがありますので、レコードが増加すると特定はできなくなります。
実際に第2正規形にすると以下のようなイメージになります。
[分類テーブル]
- 分類コード
- 1
- 1
- 分類
- IPA
- IPA
[区分テーブル]
- 区分
- 1
- 1
- 区分名
- データベーススペシャリスト
- データベーススペシャリスト
[問題テーブル]
- 分類コード
- 1
- 1
- 区分
- 1
- 1
- 実施年度
- 2020
- 2020
- 問題番号
- 1
- 2
- 問題内容
- 図のデータベース1,2は互いのデータの複製をもつ冗長構成である。クライアントからの更新・参照要求を受けたデータベースサーバ(以下、サーバという)は直下のデータベースを更新・参照し、他方のサーバにデータ更新を通知する。通知を受けたサーバは直下のデータベースに更新を反映する。サーバ1,2間のネットワークが分断し、データ更新を通知できなくなったとき、CAP定理で重視する特性(C,A,P)に対するサーバの挙動のうち、適正な組み合わせはどれか。
- Base特性を満たし、次の特徴をもつNoSQLデータベースシステムに関する記述のうち、適切なものはどれか[NoSQLデータベースシステムの特徴]・ネットワーク上に分散した複数のノードから構成される。・一つのノードでデータを更新した後、他の全てのノードにその更新を反映する。
- 図
- /ipa/db/2020/1.jpg
- /ipa/db/2020/2.jpg
- 回答ア
- ア
- ア
- 回答イ
- イ
- イ
- 回答ウ
- ウ
- ウ
- 回答エ
- エ
- エ
- 回答
- イ
- イ
- 解説
- 冗長構成だから
- 冗長構成だから
- 作成日
- 2021/5/29 16:00
- 2021/5/29 16:00
- 更新日
- 2021/5/29 16:00
- 2021/5/29 16:00
分類コードが決まれば、分類は特定できますので分類の候補キーは分類コードです。
区分が決まれば、区分名が特定できますので区分名の候補キーは区分です。
分類コード・区分・実施年度・問題番号が決まれば、問題・回答などが特定できます。
第3正規形
第3正規形とは、第2正規形を満たし、、全ての非キー属性が推移的関数従属をしていないことです。
推移的関数従属とは、X(候補キー)が決まるとY(非キー属性)が特定でき、さらにそのY(非キー属性)が特定すると別のZ(別の非キー属性)も特定できることです。
今回の表で考えてみますと、
分類コード・区分・実施年度・問題番号が決まると問題が特定できて、それが特定できると回答番号・回答内容も特定されます。
これは、Z(別の非キー属性)がX(候補キー)を特定できないのが条件になります。
回答番号から分類コード・区分・実施年度・問題番号が特定できてしまう場合は、推移的関数従属にはなりません。
例として、2020年の問1と2018年の問2の問題が同じであり、回答番号がその問題に用意されてユニークになる場合です。
2020年の問1の回答番号「202001」で2018年の問2の回答番号が「201802」のようになり、問題が特定できてしまう場合は推移的関数従属にはなりません。
2020年の問1の回答番号も2018年の問2の回答番号も同じ番号であれば、推移的関数従属になります。
実際に第3正規形にすると以下のようなイメージになります。
[分類テーブル]
- 分類コード
- 1
- 1
- 分類
- IPA
- IPA
[区分テーブル]
- 区分
- 1
- 1
- 区分名
- データベーススペシャリスト
- データベーススペシャリスト
[問題テーブル]
- 分類コード
- 1
- 1
- 区分
- 1
- 1
- 実施年度
- 2020
- 2020
- 問題番号
- 1
- 2
- 問題内容
- 図のデータベース1,2は互いのデータの複製をもつ冗長構成である。クライアントからの更新・参照要求を受けたデータベースサーバ(以下、サーバという)は直下のデータベースを更新・参照し、他方のサーバにデータ更新を通知する。通知を受けたサーバは直下のデータベースに更新を反映する。サーバ1,2間のネットワークが分断し、データ更新を通知できなくなったとき、CAP定理で重視する特性(C,A,P)に対するサーバの挙動のうち、適正な組み合わせはどれか。
- Base特性を満たし、次の特徴をもつNoSQLデータベースシステムに関する記述のうち、適切なものはどれか[NoSQLデータベースシステムの特徴]・ネットワーク上に分散した複数のノードから構成される。・一つのノードでデータを更新した後、他の全てのノードにその更新を反映する。
- 図
- /ipa/db/2020/1.jpg
- /ipa/db/2020/2.jpg
- 回答番号
- 1
- 2
[回答テーブル]
- 回答番号
- 1
- 2
- 回答ア
- ア
- ア
- 回答イ
- イ
- イ
- 回答ウ
- ウ
- ウ
- 回答エ
- エ
- エ
- 回答
- イ
- イ
- 解説
- 冗長構成だから
- 冗長構成だから
- 作成日
- 2021/5/29 16:00
- 2021/5/29 16:00
- 更新日
- 2021/5/29 16:00
- 2021/5/29 16:00
ボイス・コッド正規形
ボイス・コッド正規形（BCNF）とは、第3正規形を満たし、すべての関数従属性の左側がスーパーキーである状態を指します。
それは、完全な推移的関数従属がないことを表します。
BCNFは、第3正規形と第4正規形の間に位置しており、「3.5正規形」とも呼ばれることがあります。
また、ボイス・コッド正規形以降の正規化は、高次正規形と呼ばれます。
以下のようなユーザ実施テーブルがあり、IPA問題が決まると問題番号が特定できるような表があったとします。
[ユーザ実施テーブル]
- ユーザ
- IPA問題
- 問題番号
- 100001
- 2020Q1
- 1
- 100001
- 2020Q2
- 2
- 100002
- 2020Q1
- 1
- 100003
- 2020Q1
- 1
- 100004
- 2020Q1
- 1
実際にボイス・コッド正規形にすると以下の2つのテーブルに分解するようなイメージになります。
ユーザ「10001」に2018Q4のIPA問題があるとユーザとIPA問題がスーパーキーになるため分解します。
[ユーザ実施テーブル]
- ユーザ
- 問題番号
- 100001
- 1
- 100001
- 2
- 100002
- 1
- 100003
- 1
- 100004
- 1
[問題テーブル]
- IPA問題
- 問題番号
- 2020Q1
- 1
- 2020Q2
- 2
- 2020Q1
- 1
- 2020Q1
- 1
- 2020Q1
- 1
第4正規形
第4正規形とは、第3正規形を満たし、多値従属性をもった属性がないことです。
多値従属性とは、X(スーパーキー)が決まると複数のY(非キー属性の集合)が特定できることです。
「X →→ Y」のように表します。
ユーザが取得資格・お気に入り登録した分類のテーブル以下のようなイメージがあったとします。
[ユーザ実施テーブル]
- ユーザID
- 1
- 1
- 1
- 1
- お気に入り分類
- IPA
- IPA
- LPI Japan
- LPI Japan
- 取得資格
- データベーススペシャリスト
- ネットワークスペシャリスト
- OSS-DB Silver
- LinuC 101
ユーザIDの1が決まれば、お気に入りに登録している「IPA」と「LPI
Japan」は特定できます。
同じく取得資格である「データベーススペシャリスト」と「OSS-DB」も特定できます。
このようなときにお気に入りと取得資格を別々のテーブルにして、多値従属性を持たなくした以下のようなイメージが第4正規形です。
[お気に入りテーブル]
- ユーザID
- 1
- 1
- お気に入り分類
- IPA
- LPI Japan
[取得資格テーブル]
- ユーザID
- 1
- 1
- 1
- 1
- 取得資格
- データベーススペシャリスト
- ネットワークスペシャリスト
- OSS-DB Silver
- LinuC 101
第5正規形
第5正規形は、第4正規形を満たし、結合従属性の決定が候補キーのみであることです。
結合従属性とは、正規化はテーブルを分解していきますが、その分解したテーブル同士をを結合して基の状態に戻せることをいいます。
第5正規形にすると以下のようなイメージになります。
[お気に入りテーブル]
- ユーザID
- 1
- 1
- お気に入り分類
- IPA
- LPI Japan
[取得資格テーブル]
- ユーザID
- 1
- 1
- 1
- 1
- 取得資格
- データベーススペシャリスト
- ネットワークスペシャリスト
- OSS-DB Silver
- LinuC 101
[取得資格テーブル]
- お気に入り分類
- IPA
- IPA
- LPI Japan
- LPI Japan
- 取得資格
- データベーススペシャリスト
- ネットワークスペシャリスト
- OSS-DB Silver
- LinuC 101