テーブル構造を考える②（正規化編）

前回の記事では、データモデルについて説明しました。本記事では、その後の正規化について説明します。ただし、第四正規形以降に関しては説明しません。

正規化の目的

正規化をする目的は以下の通りです。

1. データの重複を防ぐ
   同じデータを何度も保存するのを避けます。例えば、顧客の名前を何度も書くのではなく、顧客情報を別の場所にまとめて保存することで、データが重ならないようにします。

2. データのミスを減らす
   1ヶ所にしか保存されていないデータなら、変更が必要なときにその場所だけを直せばいいです。重複データがあると、複数箇所を直す必要があり、ミスが起こりやすくなります。

3. データの整理が簡単になる
   データが整理されていると、後で検索したり、追加したり、削除したりするときも簡単になります。

候補キーと主キー

正規化を行う時には、関係の主キーを決定する必要があります。主キーを決定する前に候補キーを明確にします。
候補キーは、行を一意に識別できる単独の属性または必要最小限の属性の集合です。候補キーの値を決定すると、関係から1行だけが選択されます（もしくは何も選択されません）。候補キーは、関係に複数存在する場合もあります。
候補キーの中の1つを主キーとして設定します。ただし、主キーには主キー制約（ユニーク制約とNOT NULL制約）があり、NULLを含む属性を構成要素に持つ候補キーは、主キーとして設定できません。
候補キーと主キーについて、具体的に説明します。図書の貸出履歴を管理する関係「貸出」を考えます。関係「貸出」の属性は、書籍番号、会員番号、貸出年月日、返却予定年月日、返却年月日とします。書籍番号は、図書館が保持している書籍を一意に識別する番号です。会員番号は、図書館の会員を一意に識別する番号です。図書の貸出条件は、「貸出当日に返却できない」です。
同じ書籍を何度も貸し出すことがあるので、単独の書籍番号は候補キーになりません。同じ会員が何度も書籍を借りるので、単独の会員番号も候補キーになりません。貸出年月日、返却予定年月日、返却年月日についても、様々な書籍が同じ日に貸し出されたり、同じ返却予定年月日とされたり、同じ日に複数の書籍が返却されたりするので、貸出年月日、返却予定年月日、返却年月日の単独の属性を候補キーとすることはできません。

関係「貸出」

書籍番号	会員番号	貸出年月日	返却予定年月日	返却年月日
S0001	M001	2023/06/01	2023/06/05	2023/06/03
S0001	M025	2023/06/04	2023/06/20	2023/06/08
S0001	M070	2023/06/10	2023/06/20	(NULL)
S0055	M070	2023/06/15	2023/06/20	2023/06/18
S0079	M147	2023/06/15	2023/06/25	2023/06/18

単独の属性では、候補キーにならないため、複数の属性を組み合わせて候補キーを検討します。図書の貸出条件を基に考えると、同じ書籍を同じ日に貸し出したり、同じ書籍を同じ日に返却したりすることはできないので、書籍番号と貸出年月日を決めると、関係「貸出」の1行が決まります。同様に、書籍番号と返却年月日を決めても、関係「貸出」の1行が定まります。したがって、{書籍番号, 貸出年月日}と、{書籍番号, 返却年月日}が候補キーとなります。
主キーとしては、{書籍番号, 貸出年月日}、{書籍番号, 返却年月日}のいずれかを選択します。貸出年月日は書籍を貸し出したときに必ず値が設定されます。しかし、返却年月日は書籍を貸し出したときには、値が設定されず、NULLとなります。貸出が行われた時点で関係「貸出」に行を追加する必要があることから、{書籍番号, 返却年月日}は主キーのNOT NULL制約に反するので、主キーにすることはできません。

非キー属性

非キー属性とは、いずれの候補キーにも含まれない属性のことです。例えば、関係Rが{A, B, C, D, E}の5属性から構成されていて、候補キーが{A, B}、{A, C}であるとき、非キー属性はDとEになります。

関数従属

正規化を行う時には、関数従属という性質に着目します。関数従属は属性間の性質で、ある属性（もしくは属性の集合）Xの値を決定すれば、別の属性（もしくは属性の集合）Yの値がただ1つ決まる場合、「属性Yは属性Xに関数従属する」と言います。例えば、都道府県コードでは47都道府県に都道府県コードとして番号が割り当ててあります。都道府県コードが決まれば都道府県名が1つ決まるので、都道府県名は都道府県コードに関数従属します。
本記事では、属性（もしくは属性の集合）Yが属性（もしくは属性の集合）Xに関数従属することを「X→Y」と表現します。
X→Yで、かつ、Xの任意の真部分集合で構成される属性X'について、X'→Yを満たさない場合、YはXに完全関数従属する、と言います。Xの真部分集合とは、Xの属性のうち（すべてではない）いくつかの属性から構成される集合です。例えば、属性Xが{A, B, C}であるとすると、真部分集合として{A}、{B}、{C}、{A, B}、{A, C}、{B, C}が考えられます。数学的には空集合（Φ）も真部分集合となります。

数式的な関数従属と完全関数従属（おまけ）

1. 関数従属
   関数従属は、ある属性の集合Xの値が、別の属性の集合Yの値を一意に決定する関係です。
   形式的には、次のように表されます：
   $$ X \rightarrow Y $$
   任意の2つの行$ t_1 $と$ t_2 $が、同じXの値を持つ場合、必ず同じYの値を持つ。
   $$ t_1[X] = t_2[X] \Rightarrow t_1[Y] = t_2[Y] $$

2. 完全関数従属
   完全関数従属は、ある属性集合Xが別の属性集合Yを一意に決定するが、Xの任意の部分集合ではYを決定できない関係です。
   形式的には、次のように表されます：

• $ X \rightarrow Y $が成立し、かつ
• $ Z\subset X $であるすべての$ Z $に対して、$ Z \nrightarrow Y $も成立する。

非正規形

関係のドメインが単純でない関係を、非正規形（非第一正規形）と言います。具体的には、繰り返し項目が存在したり、複数の値を含む属性から構成されたりするような関係です。
以下のような非正規形である関係「受注」を考えます。ここでは、受注番号、顧客コード、商品コードは一意（ユニーク）とし、商品名も一意とします。

関係「受注」

受注番号	受注年月日	顧客コード	顧客名	顧客住所	顧客電話番号	商品コード	単価	数量
J001	2023/06/01	K01	OO食堂	東京都港区	03-3333-XXXX	S01（マグカップ）	1200	3
						S40（銀スプーン）	5000	5
						S63（絵付大皿）	1200	5
J002	2023/06/01	K33	キッチンAA	京都市上京区	075-755-XXXX	S01（マグカップ）	1200	2
						S83（ワイングラス）	1800	1
J003	2023/06/05	K71	CCフーズ	大阪市中央区	06-6666-XXXX	S63（絵付大皿）	1200	2
						S83（ワイングラス）	1800	3

第一正規形

非正規形に対して、関係のドメインが単純になるように変換したものを第一正規形と言います。先ほどの図に示した繰り返し項目を解消し、複数の値を持つ属性を分割することによって、第一正規形に変換した結果です。

関係「受注」

受注番号	受注年月日	顧客コード	顧客名	顧客住所	顧客電話番号	商品コード	商品名	単価	数量
J001	2023/06/01	K01	OO食堂	東京都港区	03-3333-XXXX	S01	マグカップ	1200	3
J001	2023/06/01	K01	OO食堂	東京都港区	03-3333-XXXX	S40	銀スプーン	5000	5
J001	2023/06/01	K01	OO食堂	東京都港区	03-3333-XXXX	S63	絵付大皿	1200	5
J002	2023/06/01	K33	キッチンAA	京都市上京区	075-755-XXXX	S01	マグカップ	1200	2
J002	2023/06/01	K33	キッチンAA	京都市上京区	075-755-XXXX	S83ワイングラス	ワイングラス	1800	1
J003	2023/06/05	K71	CCフーズ	大阪市中央区	06-6666-XXXX	S63	絵付大皿	1200	2
J003	2023/06/05	K71	CCフーズ	大阪市中央区	06-6666-XXXX	S83	ワイングラス	1800	3

次に候補キーを明確にします。関係「受注」では、受注番号の「J001」、受注年月日の「2023/06/01」のように、すべての属性において重複するデータが含まれていますので、単独の属性では候補キーにならないことが分かります。
単独の属性では候補キーにならないので、属性の集合を考えます。同じ商品であっても、どの受注に含まれている商品であるかを決定すれば、関係から、1行を特定できます。商品コードも商品名も一意なので、候補キーとしては、{受注番号, 商品コード}と{受注番号, 商品名}が考えられます。主キーを構成する属性としては、名称よりコードの方が適切と考えられるため、{受注番号, 商品コード}を主キーとして選択することとします。

第二正規形

第二正規形の定義は、

• 第一正規形であって、かつ
• 関係中のすべての非キー属性が候補キーに完全関数従属

であることです。関係「受注」の非キー属性は、受注年月日、顧客コード、顧客名、顧客住所、顧客電話番号、単価、数量です。商品名は候補キーに含まれているので非キー属性ではありません。
それぞれの非キー属性が候補キーに完全関数従属するかどうかを確認します。受注年月日、顧客コード、顧客名、顧客住所、顧客電話番号の5つの属性は、受注番号のみに関数従属します。また、単価は、商品コードまたは商品名に関数従属します。この例では、候補キーに完全関数従属しない非キー属性があるため、第二正規形ではありません。
候補キーの一部に関数従属することを部分関数従属と言います。第二正規形に変形するためには、部分関数従属する部分を別の関係に分割します。分割した結果は、次の通りとなり、上から関係「受注明細」、「商品」、「受注」となります。

関係「受注明細」

受注番号	商品コード	商品名	数量
J001	S01	マグカップ	3
J001	S40	銀スプーン	5
J001	S63	絵付大皿	5
J002	S01	マグカップ	2
J002	S83	ワイングラス	1
J003	S63	絵付大皿	2
J003	S83	ワイングラス	3

関係「商品」

商品コード	単価
S01	1200
S40	5000
S63	1200
S83	1800

関係「受注」

受注番号	受注年月日	顧客コード	顧客名	顧客住所	顧客電話番号
J001	2023/06/01	K01	OO食堂	東京都港区	03-3333-XXXX
J002	2023/06/01	K33	キッチンAA	京都市上京区	075-755-XXXX
J003	2023/06/05	K71	CCフーズ	大阪市中央区	06-6666-XXXX

第三正規形

第三正規形の定義は、

• 第二正規形であって、かつ
• すべての非キー属性がどの候補キーに対しても推移的に関数従属していない

ということです。属性X（候補キー）、Y（候補キーでない属性、または候補キーでない属性の集合）、Z（非キー属性）の間に、「X→Y」、「Y→Z」、「Y→Xでない」のすべてが成り立つ時、ZはXに対して推移関数従属すると言います。
関係「受注」の候補キー（主キー）は受注番号ですから、受注番号以外の属性（受注年月日、顧客コード、顧客名、顧客住所、顧客電話番号）はすべて非キー属性となります。受注番号→顧客コード、顧客コード→{顧客名, 顧客住所, 顧客電話番号}という関数従属があり、顧客コードから受注番号への関数従属はないので、{顧客名, 顧客住所, 顧客電話番号}は受注番号に推移関数従属します。関係「受注明細」の候補キーは{受注番号, 商品コード}と{受注番号, 商品名}、非キー属性は数量で、推移関数従属はありません。関係「商品」についても、候補キーが商品コード、非キー属性が単価で、推移関数従属はありません。
よって、第三正規形に変形するためには、推移関数従属する部分を別の関係に分割します。今回の例では、関係「受注」を関係「受注」と関係「顧客」に分けます。

関係「受注」

受注番号	受注年月日	顧客コード
J001	2023/06/01	K01
J002	2023/06/01	K33
J003	2023/06/05	K71

関係「顧客」

顧客コード	顧客名	顧客住所	顧客電話番号
K01	OO食堂	東京都港区	03-3333-XXXX
K33	キッチンAA	京都市上京区	075-755-XXXX
K71	CCフーズ	大阪市中央区	06-6666-XXXX

導出属性（例：単価×数量=金額という関係がある場合の金額）は、基本的にデータベースに保存する必要がありません。導出属性は第三正規形への変形において削除します。

数式的な推移関数従属（おまけ）

3つの集合$ A, B, C $に対して、
$$ A \rightarrow B \quad and \quad B \rightarrow C \Rightarrow A \rightarrow C $$
が成り立つ時、A→Cは推移関数従属と言います。

ボイスコッド正規形

最初に第三正規形について再確認します。関係「受注明細」には、商品コードと商品名について同じデータが保持されています。

関係「受注明細」

受注番号	商品コード	商品名	数量
J001	S01	マグカップ	3
J001	S40	銀スプーン	5
J001	S63	絵付大皿	5
J002	S01	マグカップ	2
J002	S83	ワイングラス	1
J003	S63	絵付大皿	2
J003	S83	ワイングラス	3

同じデータが保持されている原因は、第二正規形を検討したときの「関係中のすべての非キー属性が候補キーに完全関数従属であること」というルールにあります。商品コードも商品名も候補キーに含まれている属性なので、非キー属性ではありません。同じデータが保持されているのは、商品コード→商品名、商品名→商品コードという関数従属が存在しているのにもかかわらず、第二正規形の定義に則って非キー属性にだけ着目して部分関数従属を分割したためです。
ボイスコッド正規形の定義は、「関数Rのすべての候補キーについて、候補キーをとし、Xに含まれていない関係Rの属性をYとしたとき、YはXに完全関数従属する」となります。
関係「受注明細」について、ていぎに照らし合わせてボイスコッド正規形になっているか否かを確認します。候補キーは{受注番号, 商品コード}と{受注番号, 商品名}です。候補キーの{受注番号, 商品コード}に着目すれば、候補キー以外の属性は{商品名, 数量}になります。数量は候補キー{受注番号, 商品コード}に完全関数従属しますが、商品名は候補キーの一部である商品コードに関数従属します。候補キーの{受注番号, 商品名}に着目すれば、候補キー以外の属性は{商品コード, 数量}になります。数量は、{受注番号, 商品名}に完全関数従属しますが、商品コードは候補キーの一部である商品名に関数従属します。したがって、関係「受注明細」はボイスコッド正規形ではありません。
ボイスコッド正規形へ変形するためには、完全関数従属していない部分を別の関係に分割します。ここで、既に関係「商品」があり、分割部分はこの関係に統合できるため、関係「受注明細」から商品名を削除すれば済むことになります。
他の関係は既にボイスコッド正規形であります。よって、すべての関係は以下のようになります。

関係「受注明細」

受注番号	商品コード	数量
J001	S01	3
J001	S40	5
J001	S63	5
J002	S01	2
J002	S83	1
J003	S63	2
J003	S83	3

関係「受注」

受注番号	受注年月日	顧客コード
J001	2023/06/01	K01
J002	2023/06/01	K33
J003	2023/06/05	K71

関係「商品」

商品コード	商品名	単価
S01	マグカップ	1200
S40	銀スプーン	5000
S63	絵付大皿	1200
S83	ワイングラス	1800

関係「顧客」

顧客コード	顧客名	顧客住所	顧客電話番号
K01	OO食堂	東京都港区	03-3333-XXXX
K33	キッチンAA	京都市上京区	075-755-XXXX
K71	CCフーズ	大阪市中央区	06-6666-XXXX

最後に

ここまで読んでいただき、ありがとうございました。
テーブル設計をする場合、毎回このようなことを考えなければいけないのかといわれると、そういう訳ではありません。例えば、正規化するデータによってはボイスコッド正規形への変形により関数従属性が失われてしまう場合があります。アプリケーションの規模感やデータの関係性によっては、ボイスコッド正規形以上の正規化をすべきですが、実際は第三正規形まで考えられれば十分だと言われています。