スタートページWeb教材一覧データベース

データの正規化

学習のポイント

ファイルの概念」で,マスタファイルに分解するメリットを学習しましたが,そこでは,どのような基準で設定するかは説明しませんでした。その基準にデータの正規化ERモデルがありますが,ここでは,データ正規化の手順を理解します。

データの正規化とは,データの重複をなくすことにより,データの管理を容易にしたり,データを多様な目的に用いるのに有効な方法で,データベースの構築の基本になる技法です。ここでは,正規化されていないファイルを,第1正規化,第2正規化,第3正規化する手順を理解します。

キーワード

正規化,非正規形,第1正規化,第2正規化,第3正規化

参考動画:メディアリンク「ビジネスパーソンIT道場」

過去問題:「データの正規化」( db-seikika.html


正規化の手順

非正規形

ある売上伝票は,次のように1枚の伝票に3つの商品を連記することができるとします。

売上伝票の図

これをそのままファイルにすると,図の「非正規形」のようになります(ここでは,図を単純にするかめに,商品区分や単価を省略しています)。伝票に記載された商品数が少ないときは,レコードの右側に空白がならび無駄になってしまいます。

第1正規化

第1正規化とは,繰り返しの部分を複数のレコードにして,繰り返しを排除する操作です。第1正規化をした結果を第1正規形といいます。

第1正規形のファイルが次のようであるとします。

    売上ファイル1=年月日
           +得意先コード+得意先名
           +商品コード+商品名
           +商品区分コード+商品区分名
           +数量+単価

関数従属性

このとき,年月日,得意先コード,商品コードのすべてが定まると,そのレコードが決まります。そのとき,これらを主キーといいます。得意先コードが定まると得意先名が定まるというように,ある属性(項目)が定まると他の属性が定まるという関係を関数従属といいます。そして,数量は年月日,得意先コード,商品コードのすべての主キーが定まりますが,このように主キー全体に従属することを完全関数従属といい,得意先コードと得意先名の関係のように,主キーの一部に従属することを部分関数従属といいます。また,商品コードから商品区分コードがわかり,それにより商品区分名がわかるというように,間接的に従属することを推移関数従属といいます。

第2正規化

第2正規化とは,部分関数従属する項目を分離することです。その結果のファイルを第2正規形といいます。主キーは年月日,得意先コード,商品コードですが,他の項目とは次の関係があります。

それで,
    得意先マスタ=得意先コード+得意先名
    商品マスタ=商品コード+商品名+商品区分コード+商品区分名+単価
が分離されます。残った項目と分離したファイルのユニークキーからなるファイルは,
    売上ファイル=年月日+得意先コード+商品コード+数量
となります(ファイル名は任意)。

第3正規化

第3正規化は,主キー以外のキーに関数従属する項目を分離します。その結果を第3正規形といいます。

得意先マスタは,得意先名は主キーである得意先コードに従属しているから完全関数従属であり,売上ファイルでは,数量がすべての主キーに完全関数従属しているので,これ以上は分割できません。

ここでは,商品マスタが対象になります。
    商品マスタ=商品コード+商品名+商品区分コード+商品区分名+単価
において,主キー以外のキーとは商品区分コードであり,商品区分コードが決まれば商品区分名が決まるので,
    商品区分マスタ=商品区分コード+商品区分名
が分離できます。それで残った項目(分離ファイルの主キーを含む)で,
    商品マスタ=商品コード+商品名+商品区分コード+単価
の2ファイルになります。

すなわち,第3正規形のファイル群は,次の4ファイルになります。
    売上ファイル=年月日+得意先コード+商品コード+数量
    得意先マスタ=得意先コード+得意先名
    商品マスタ=商品コード+商品名+商品区分コード+単価
    商品区分マスタ=商品区分コード+商品区分名
これは,「ファイルの概念」での結果と同じになります。

なお,データの内容は次のようになります。

主キーと外部キー

得意先表では得意先コードにより、商品表では商品コードにより、商品区分表では商品区分コードにより行が特定します。このように表の行を特定する項目を主キーといいます。通常は、主キーの値はすべて異なります。同じ値をもたない主キーをユニークキーということもあります。
 売上表では、主キーに相当する項目がありません。同一日に同一得意先に同一商品の売上はないと仮定すれば「年月日+得意先コード+商品コード」をまとめた項目(複合キーという)が主キーになります。

商品表の商品コードは商品表の主キーです。売上表の得意先コードと商品コードは、それぞれ得意先表、商品表の主キーです。このように、他表の主キーである項目を外部キーといいます。外部キーは二つの表を結合するときの項目になります。

整合性制約

整合性制約とは、データベースを常に正しい状態に保つための規則のことです。整合性制約に適合したデータベースにすることも正規化を行う目的の一つです。また、整合性制約を理解することにより、適切な正規化ができます。

定義域制約
属性項目は、これ以上細分化できないのが望ましい。例えば、「年月日」を単に日付として用いるのではなく、「年」「月」「日」により操作することが多いならば、それぞれを独立した項目にするべきだということです。
主キーの制約
主キー(複合キーも含む)には次の制約があります。
NOT NULL制約:主キーの値はNULLであってはなりません。
UNIQUE制約(一意性制約):主キーはユニークでなけらばならないという制約です。例えば、得意先名を主キーにすると同名が存在することがあります。それでユニークとなる得意先コードを設定して主キーとし、得意先名は属性にするのです。
参照制約
外部キーの必要性に関する制約です。売上ファイルには、得意先マスタや商品マスタとの結合が必要になります。そのためには、売上ファイルには、得意先コードと商品コードを外部キーとして持たなければなりません。

正規化の練習

正規化の手順を習得するために,いくつかの練習問題をやってみましょう。次の第1正規形のファイルをベースにします(簡単にするために,商品区分を省略しました)。単価は商品により決まり,全得意先で同一とします。

売上ファイル=年月日
+得意先コード+得意先名
+商品コード+商品名
+数量+単価

主キーは,年月日,得意先コード,商品コードです。
 このファイルの第3正規形は次のようになります。
   得意先マスタ=得意先コード+得意先名
   商品マスタ=商品コード+商品名+単価
   売上ファイル=年月日+得意先コード+商品コード+数量

図示をすると,次のようになります。

● 練習問題1

上の例題では,単価はどの得意先についても同一商品同一価格であると仮定していました。それを得意先ごとに単価が違う(同一得意先ではいつも同じ)としたら,第3正規化された結果はどうなりますか。

解答

第2正規化では,主キーのどれかが決まると決まる項目を調べます。

それで,次のものが分離されます。

残ったものは,
    売上ファイル=年月日+得意先コード+商品コード+数量
となります。

これらのファイルには,主キー以外のキー項目がないので,すでに3次正規化されています。

● 練習問題2

単価は全得意先で共通とすします。各得意先が複数の店舗を持っており,請求書とは別に店舗別に納入明細書を必要とするとき,次の第1正規形のファイルになります。これを第3正規化しなさい。

   売上ファイル=年月日
+得意先コード+得意先名
         +店舗コード+店舗名
         +商品コード+商品名
+数量+単価

解答

得意先と店舗の関係は,「得意先はいくつかの店舗を持つ」「各店舗はそれぞれ唯一の得意先に属する」関係があります。このとき,得意先と店舗には1:Nの関連があるといい,得意先を親(上位),店舗を子(下位)といいます。得意先よりも店舗のほうが細かいのですから,このファイルの主キーは,年月日,店舗コード,商品コードになります。

第2正規化:部分関数従属の分離

それに従い次の3つのファイルに分解できます。

商品マスタと売上ファイルがこれ以上分解できませんが,店舗マスタはさらに第3正規化により分解できます。
店舗マスタでは,主キーの店舗コード以外にキーとなる項目である得意先コードがあり,それに得意先名が従属していますので,次のように分解できます。


正規化の目的等

非正規形あるいは第1正規形のファイルを第3正規形のテーブル群にすることにより、どのような利点や欠点が生じるかを列挙します。

利点:データの重複・冗長性の排除

得意先マスタを例にします。得意先は、販売システム以外にも、流通システム、会計システムなど多くのシステムで使われます。正規化されていないと、売上ファイル、請求書ファイル、出荷ファイル、売掛金ファイル、債権ファイルなど夥しい数のファイルが得意先情報をもつことになります。しかも、それらのファイルには、得意先コードだけでなく、得意先名、住所、電話番号、担当者名など多様な項目が重複して記録されています。
 正規化することにより、それらのファイルには得意先コードだけがあればよく、得意先に関する項目は得意先マスタだけにあればよいことになります。
 ファイル数が多く、レコード数が多いのが通常です。正規化することにより、ファイル全体の記憶容量が非常に少なくできます。しかし、ディスクやメモリが大容量化低廉化したことにより、この利点はあまり重要視されなくなりました。

利点:データ管理の容易化

例えば得意先の住所が変更になったとき、正規化されていないと、その住所があるファイルを調べて修整することになりますが、その作業量が大きいだけでなく、該当するファイルを見落す危険性があります。
 正規化してあれば、得意先マスタだけを修正すればよいので、データ管理が容易になります。更新時におけるデータの不整合の発生を防止することができ、データの一貫性・整合性(インテグリティ)を高めることができます。これが正規化をする最大の目的です。

欠点:結合による処理効率の低下

得意先別商品別の売上一覧表作成を例にします。売上ファイルに得意先名や商品名があれば一つのファイルから作成できます。ところが正規化されているときは、それらを得意先マスタや商品マスタから取り出すために、売上ファイルとそれらのマスタを照合する操作が必要になります。
 処理速度を上げるためには、高性能のコンピュータや記憶装置が必要になり、コスト高になります。昔は、正規化の利点は認識されていたのに、ハードウェアの性能の壁で採用されないこともありました。現在は、ハードウェアの性能向上、照合処理のアルゴリズムの進歩により、この欠点は重視されなくなってきました。

欠点:処理記述が複雑

売上一覧表を作成するのに、「どのテーブルをどのように結合するのか」の知識が必要になります。

欠点:過去データの扱い

得意先住所が変更になる以前のデータを使う場合、非正規形であれば変更前の住所が入っていますが、正規化したとき、すべて現在の住所になってしまいます。得意先マスタに旧住所と変更日付の項目をもつ方法がありますが、複雑になってしまいます。


理解度チェック

第1問

第1正規化,第2正規化,第3正規化とは,どのような操作をするものであるか。説明しなさい。

第2問

単価が各取引ごとに異なる(金額だけが必要で単価は必要としない)としたら,正規化したファイル群はどうなるでしょうか。

第3問

次の第1正規形のファイルがあります。これを第3正規化しなさい。

    履修ファイル=学生番号+学生名
          +科目番号+科目名+科目区分+単位
          +教員コード+教員名
          +成績
  1. 学生はいくつかの科目を履修する。その結果が成績であり,5段階評価である。
  2. 科目区分は必修/選択の区分であり,単位はその科目の単位数である。
  3. 科目は一人の教員が担当する。一人の教員は複数の科目を担当することがある。

本シリーズの目次へ