セブンイレブンのレシートから第三正規形を求める

##ことの発端
近くのコンビニに立ち寄り色々買ったあと、寒空の下レシートを見ながら歩いていたら、レシートからわかる情報で正規化してみたいと思い実践したので、その時のことを記事にします。

正規化とは

正規化とは、データの整合性を保ったまま冗長性をなくし、データを効率的に管理するためにテーブルを分離していくことです。
簡単に言えば、データを使いやすいように整理したり変形したり分割したりすることです。
より具体的に述べると、RDBを利用する際にオリジナルのテーブルの関連性を失わないように項目を整理して、複数のテーブルに分離することになります。
正規化をすることで、データの更新や削除が非常に容易になるだけでなく、スキーマの理解がしやすくなり、データを効率的に管理できるようになります。

正規化の手順

正規化には第一正規化から第五正規化までの5段階の手順があります。
しかし、一般的に利用されるのは第三正規化までになります。
正規化されていない状態を非正規形と呼び、第一正規化された状態を第一正規形、第二正規化された状態を第二正規形、第三正規化された状態を第三正規形と呼びます。
一連の流れを横軸にすると以下のような状態になります。

それぞれの手順でどのようなことを行うかを記載すると、それぞれ以下の様な状態を目指します。
第一正規化→繰り返し項目を分離して独立した行にする。
第二正規化→主キーが決まればひととおり決まる項目を取り出し、表を分離。
第三正規化→主キー以外の項目間に従属関係があれば、それらを取り出して表を分離。

##使用するレシート
今回使用するレシートは以下のものを使用します。

##項目の確認
上記のレシートを見ると以下の様な情報があると思います。（全項目については今回は対象としません）

• 一枚のレシートの中で繰り返し登場する項目は(繰り返し)と記載しました。

• 店舗名

• 住所

• 電話番号

• 日付

• 商品名 (繰り返し)

• 単価 (繰り返し)

• 数量 (繰り返し)

• 合計金額

このレシートから読み取れる訳ではないのですが、今後必ず必要となりそうなものを先に用意しておこうと思います。
必要となりそうな項目は以下の様なものがあると予測します。(あくまで予測ですが、必要なはずです)

• レシートID
  • 受注番号の様に一度の買い物を一括りとできる識別番号
• 店舗コード
  • 店舗名や住所を紐づける為の識別番号
    • これがないと店舗名が変わった場合に、変更対象の該当するレコード(レシート)の分だけ店舗名を変更するUPDATEをかけなければいけなくなります。
• 商品コード
  • 商品を紐づける識別番号
    • これが必要な理由は店舗コードと同様の理由です。

##上記の項目をエクセルにまとめる
レシートのままだとどうにもやりにくいので一旦スプレッドシートにまとめました。

レシートに記載されている項目を一つの枠としてそのまま以下の様に記入しました。

オレンジ色になっているところは今回私が適当に当てはめたものになります。
ここからはスプレッドシートを使ってデータを扱っていきます。

##非正規形
上記に示した状態が正規化を行う前の状態、つまり非正規形の状態です。
一つのレシートに記載されている内容がそのまま一つのレコードとして保持されています。
今のままだと以下の問題点があります。

1. データが冗長的すぎる
   • 例えばレッドブルの単価が変わったとき、レッドブルが含まれている注文データ全てを更新する必要がある。
2. データの整合性が保たれていない
   • もし新たにレッドブル2という商品が追加されたとしても、レッドブル2が購入されない限り、レッドブル2はデータベースに保存されないので存在しないことになってしまいます。
   • また、購入されたレシートが1件しかない場合、データが削除されてしまうと、その商品の存在が消えることになってしまいます。

上記の問題は一つのレコードに対して、いくつもの事実が記録されていることが問題になっています。

##第一正規化
非正規形の問題点は上記にてお伝えした通りです。その問題点を解消するために正規化を実施していきましょう。

まず正規化の第一ステップとして第一正規化を行います。
第一正規形は1レコード1事実にします。

どのようなことをするかと言うと、繰り返し項目をなくすことを目標とします。

そのために行うこととして、繰り返し項目を含まないようにレコードを分割し、主キーを見つけます。
主キーとは、行に対する一意の値をもった属性です。
主キーがわかれば他の項目も紐づくようにわかるものが主キーとして設定されます。

今回の場合でレコードを分割し、それぞれ主キーを決定すると以下の様になると思います。

背景が赤色の項目を主キーとしました。
上記の例だとレシートIDと商品コードが主キーとなります。
繰り返し項目となっていた商品コード、商品名、単価、数量を別レコードとして分割しました。

繰り返し項目を排除したことで、第一正規化は完了です。
上記によって求められた形が第一正規形です。

##第二正規化
続いて第二正規化を実行していきます。
第二正規化は部分関数従属の排除をします。

つまり、主キーが複合キーになっていて、その部分キーに関数従属がある場合、これを別テーブルに分離します。

複合キーとは、購入詳細テーブルのレシートIDと商品コードのように、行の内容を決める一意の値が複数ある場合にそれらの総称です。

また、上述したような部分キーに関数従属がある様な状態のことを、部分関数従属と言います。

つまり第二正規化では複合キーがあるテーブルにおいて、いずれかの部分キーによって関数従属しているものを別テーブルとして切り出すということです。

今回の場合だと、まず第一正規形から複合キーに関数従属する要素をテーブルに切り出して、そのテーブルを購入詳細テーブルとし以下の様なテーブルにします。

テーブルを分割する際には、リレーションを保持するために他のテーブルと共通する項目(今回であればレシートID)がそれぞれのテーブルにも必要となることです。
このようなキーを外部キーといいます。

そして、切り出された購入詳細テーブルのうち部分キーの商品コードが商品名と単価の関数従属しているので、こちらを別テーブルとして切り出すことになります。

この様に部分関数従属性をもった要素を別テーブルに切り出す第二正規化を行うと以下のような第二正規形を生成することができます。

##第三正規化
第3正規化では主キー以外の項目によって一意に決まる項目を別表に移します。
今回のポイントは推移的関数従属になります。
第三正規化は、推移的関数従属している項目を別テーブルに切り出します。
推移的関数従属とは、Aの値が決まるとBの値が決まり、Bの値が決まるとCの値が決まるという関係で、CはAに推移的に関数従属していると言えます。
今回の例で言うと、購入テーブルのレシートIDと店舗コードが該当します。

レシートIDが決まることで店舗コードが決まり、店舗コードがわかることで店舗名や住所が決まるといった具合です。

つまり、店舗コードにより関数従属している項目が第三正規化の対象となります。
また今回の正規化では、導出項目を削除します。
導出項目とは、計算によって求められるもののことを言います。今回の例で言うと、合計金額が導出項目に当たります。

以上の第三正規化によって推移的関数従属性を別テーブルに切り出し、導出項目を削除した第三正規形は以下のようになります。

これにより正規化を終了することができました。
この後にも第五正規形が続いていくのですが、一般的には第三正規形までを求めることになると思うので、今回はここで正規化を終了します。

##終わりに
思いつきではありましたが、無事にセブンイレブンのレシートから正規化を実行することができました。正規化について勉強し直すきっかけにもなりましたので、非常に有意義でした。
理論として学ぶことのおおい正規化ですが、実際のデータを使って実践的に学ぶのは非常に面白かったです。

間違い等ございましたら、ぜひご指摘いただけるとありがたいです。
ありがとうございました。
(コンビニで買いすぎていることをかなり反省します)

####謝辞
第一正規化についてご指摘いただいた@mamu_taさん,
ありがとうございました！