■正規化の概念
リレーショナルデータベース（RDB）の設計で、できるだけ単純かつ一元管理できるようにテーブルを分割する手法のことを正規化といいます。
エクセルを簡易データベースとして利用することを考えた場合、この正規化の手法を全く無視するわけにはいきません。
複数のワークシートに作られた表をテーブルに見立て、SQLステートメントでレコードセットを取得する方法などについてこれまで述べてきました。
※ エクセルのワークシートにADOで接続しよう ADOレコードセットの操作1
2
ならば、テーブル設計の技法である正規化についても学ぶべきだと考えます。
どのような形でデータを管理していくことが適切なのかを、この正規化の手法から学ぶことができるからです。
■正規化の手順
正規化の目的はデータの冗長性をなくし、関連性の強い項目群をまとめること、つまり「一事実一箇所」（1 fact in 1 place）を目指すことにあるといわれます。
その正規化の手法はいくつかの規則があります。規則を適用した状態を「正規形」と呼び、適用している規則の数で第1正規形〜第3正規形、ボイスコッド正規形、第4、第5正規形に分かれます。
それぞれの正規形は、その前段階の正規形ルールが適用されていることが前提になります。例えば第3正規形を適用するためには、その前段階である第1、第2正規形ルールが適用されていることが条件になります。
現在、多くのデータベースシステムで採用されている正規形ルールは第3正規形までで、ボイスコッド正規形や第4、第5正規形まで採用されることはめったにありません。
本講座で解説を行うのは、この第3正規形に正規化するまでの手順です。
■非正規形から第1正規形への正規化
購入履歴一覧表を考えてみましょう。
まずこのような表があった場合、とても困ります。A4コピー用紙はA文具店とB文具店の両方から購入しているのですが、このままではA文具店のみのデータ抽出や集計を行いたいときなど、困難で大変使いづらい表です。
仕方がないので、購入先項目を2つに分けました。
一見よさそうですが、この表をもとにエクセルのシステムを構築していった場合、購入先は1つの購入品に対して最大2つまでしかもてません。
3つ目の購入先が登場した場合、購入先3 購入単価3 購入数3を追加すればよいのですが、その場合購入先を参照しているシステム全ての見直しが必要になります。また、この設計では購入先の数が動的に変化するケースには対応できないでしょう。
第1正規形への正規化では、この購入先のような繰り返しグループ（反復群）を排除する作業を行います。
繰り返しグループを除去し、第1正規形へと正規化が行われました。これにより購入先の数が動的に変化した場合にも対応ができるようになりました。
第2正規形への正規化の前に関数従属（関数従属性）について説明します。
関数従属とは、一方の値が決まることでもう一方の値が決まる関係のことをいいます。
関数従属はさらに完全従属と部分従属、推移従属などに分類されます。
| 完全従属（完全的関数従属） |
複数の項目を主キーとしてもつレコードにおいて、その全体によって値が決定される関係を完全従属と呼びます。
（例：購入日,購入品番号,購入先によって購入数が決定される） |
| 部分従属（部分的関数従属） |
主キー項目の一部で値が決定する関係を部分従属と呼びます。
（例：購入品番号によって購入品名が決定される） |
| 推移従属（推移的関数従属） |
主キーに属さない値をキーにして従属関係がある場合、主キーから見て間接的（推移的）に値が決定される関係を推移従属と呼びます。
（例：購入品番号によって購入区分が決定し、購入区分によって購入区分名が決定される） |
※主キー（プライマリキー）は1つまたは複数の項目によって、レコードを一意に識別できる値の集合を指します。テーブルの中で、主キーとして使用できる項目群を候補キーと呼びます。候補キーの中で主キーでないものを代理キーと呼びます。
（例：購入品履歴一覧表は購入履歴番号を設定した場合、購入履歴番号または｛購入日,購入品番号,購入先｝が主キーになり得るので候補キーとなります。購入履歴番号が主キーになった場合、｛購入日,購入品番号,購入先｝は代理キーとなります）
■第1正規形から第2正規形への正規化
先ほど説明した関数従属のなかで、部分従属の関係にある項目（フィールド）を分離します。
この場合、候補キーの一つによってもう一方の値が決定されます。
第1正規化された購入履歴一覧表をもとに考えてみましょう。
このとき主キーになっているのが購入日、購入品番号、購入先です。
これらの項目と他の項目にある関係を考えてみましょう。
・購入日から決定される項目は何もない。
・購入品番号が決まれば購入品名、購入区分は決定される。
・購入品番号と購入先が決定されれば購入単価が決定される。
・購入数は主キー全体によって決定される（完全従属）
そこでテーブルを分割します。
購入履歴テーブル
購入品マスタ
購入単価マスタ
このように部分従属されている項目を分離します。これによって第2正規化への作業が完了しました。
■第2正規形から第3正規形への正規化
最後に、主キー以外のキーに関数従属する項目（フィールド）を分離します。
購入品マスタには購入区分と購入区分名が含まれていますが、購入区分名は購入区分によって決定されるので、購入品マスタから分離できます。
購入品マスタを次のように分割してみましょう。
購入品マスタ
購入区分マスタ
これに先ほどの2テーブルがあるため、最終的に4テーブルに分割されました。
購入履歴テーブル
購入単価マスタ
これにより推移的に関数従属していた項目が分離され、第3正規化への作業が完了しました。
以上、非正規形から第3正規形に正規化するまでの各手順を解説しました。
■エクセルで正規化することの意味
エクセルは表計算ソフトであってリレーショナルデータベースシステムではないのだから、正規化の手法は意味がない。そう考える方もたくさんみえるでしょう。
しかし、エクセルをデータベース的に利用することを考えた場合、やはり最低限の正規化に対する知識が必要になってくると考えられます。
それは、さまざまなデータが重複した表や従属性を無視した表の集合は、長期間にわたってメンテナンスを行い、効率的に運用を行う上で、様々な障害を引き起こす可能性があると考えられるからです。
様々なQ&Aサイトで行われている質問・回答の多くは、表の設計をきちんと行っていさえすれば発生しなかったはずのトラブルです。
エクセルは、かなり器用なことをワークシート関数やVBAによって行うことができるため、肝心の表の設計部分はあまり注意が払われません。
本来、表の設計さえきちんと行っていれば発生しなかった多くの問題を、ワークシート関数やVBAで解決させることは本末転倒ではないでしょうか？
具体的には、今回行った正規化の手法を用いて、ワークシートごとに各テーブル、マスタを作成します。メインの表からは、VLOOKUP関数などで表引きして利用する形になります。
これなら、データを一元管理しているので、参照元の表を修正すれば、メインの表も自動的に修正されます。
今回は特に触れませんでしたが、正規化の手法の中に導出フィールドの削除というものがあります。
これは、単価ｘ数量=金額のように計算によって値を算出できるフィールドをテーブル内から削除していくというものです。
これなどはエクセルの場合、自然に行っています。まさか単価と数量の項目があるのに金額の項目を手入力される方はいないでしょう。
さらに、VBAからADOなどを利用してワークシートに接続した場合、SQLステートメントによって簡単にデータの結合や抽出等のクエリを発行することも可能です。
※ エクセルのワークシートにADOで接続しよう ADOレコードセットの操作1
2
正規化の手法については知識としてだけでも、知っておいて決して損はないと思います。
|
