Database - RDBモデリング

■内容

■関係データ・モデル

• 関係データベースの原理となる関係データ・モデルは E. F. Codd 博士によって 1970 年に発表されました。

• この論文の中で、関係データ・モデルの要件は以下のように定義されています。
  • フラットな表構造である
  • 表の間の関係付けは値の一致による
  • 正規化条件を満たす
  • リレーショナル代数が用意されている

■フラットな表構造

• 関係データ・モデルの概念はリレーション（関係）です。関係データ・モデルではデータをつぎの表のように行（Row）と列（Column）から構成される表（Table）で表現します。

• この表には以下の特性があります。
  • 表中のデータは値で表現する（空白値を許す）
  • 1 行の各列の項目は単一の値を持つ
  • ひとつの表に同じ行は許さない
  • ひとつの表に同じ列名は許さない
  • 行の順序は任意である
  • 行の列の順序は任意である

■値の一致による表の間の関係付け

• 関係データ・モデルでは実際に同じ値のデータがある場合に結合処理することができます。

  
  図　セールス・データベース

• この表同士を結合する際に関連してくる列のことをキー（Key）とよびます。

• キーには、つぎの種類があります。

  • 基本キー(主キー): 表内の行(基本キー(主キー))を一意に特定してくれるキーのことを基本キーとよびます。社員表における「社員番号」や部門表における「部門番号」は、それぞれの表における基本キーです。

  • 外部キー: 他の表の基本キーと結合させることのできるキーを外部キーとよびます。社員表の「部門番号」は部門表と結合させるための外部キーです。

  • 複数の列の組み合わせによってキーの役割を果たすものを複合キーとよびます。

■正規化条件

• 正規化条件は以下の条件が基本となっています。
  • 各フィールドに配列が含まれてはならない
  • 各フィールドにグループ値があってはならない

• 「配列」は行の繰り返しを表します。

• 「グループ値」はひとつのフィールド内に複数のフィールドが「入れ子」になっている状態のことを指します。

■リレーショナル代数

• つぎのような代数が使えます。

  選択（select）	表から行を抜き出して表を作る
  射影（project）	表から列を抜き出して表を作る
  直積（product）	2 つの表の行を組み合わせ、可能な全ての組み合せで表を作る
  合併（和, union）	2 つの表の行からなる表を作る
  共通（intersect）	2 つの表の両方に含まれる行からなる表を作る
  差（difference）	一方の表に含まれているが、他方の表には含まれていない行からなる表を作る
  結合（join）	「値の一致による表の間の関係付け」参照

■概念モデリング（ER モデルの作成）

• 複雑でわかりにくい仕様を、図形などを使って表現することにより、簡潔でわかりやすいモデルを作成できます。このような抽象的なモデルを作成して要件を分析し、リレーショナル・データ・モデルに適した構造を定義していく段階を概念モデリングとよびます。

• データ要件の概念モデリングにおいて、必要となるデータ情報を記述する方法として ER（エンティティ・リレーションシップ）モデルを作成するのが一般的です。一貫したルールを持つ ER モデルをたたき台にして設計することにより、間違いや誤解を最小限に押さえることができます。

• 設計者によって、もしくはそれぞれのケースによって方法はさまざまですが、インタビュー内容から名詞を抜き出してエンティティ候補とする場合もあれば、伝票や適用業務画面イメージを基に正規化をしていく方法もあります。

■ER モデリング

• ER モデリングは、つぎのような特徴を持っています。
  • インプリメントではなく、業務をモデリングする
  • 十分に完成された技法
  • 堅牢な構文
  • 読みやすいダイアグラムになる

• ER モデリングの目標
  • 必要とされるすべての情報を盛り込む
    • モデルは完結していなければなりません。インプリメントを開始する前に要件を理解し、因果関係が明確になっていなければなりません。

  • 情報は１度だけ現れる
    • システムが特定の情報を 2 度格納すると、その情報が 2 箇所で同じでなくなる可能性があります。
    • また、理想的なシステムには導出項目が含まれない、ということも意味します。

  • すでにモデリングされている別の情報から引き出せる情報は、モデリングしない
    • 情報は予測可能で論理的な位置に存在する
    • 適切な ER モデルを作成することで、論理的に一貫性のある一連の表を作成します。

■ER モデルの記述

• 概念モデリングで使用するモデルは、あいまいさを省き、コミュニケーションをしやすくするための規則や標準に基づいたものでなくてはなりません。

• データ要件の概念モデリングでは一般的に、設計者は ER モデルを描くことによって概念モデリングをしていきます。

■ER モデルの基本要素

• ER モデルを作成する際に必要となる基本要素は以下のものです。
  • エンティティ
  • リレーションシップ
  • 属性
  • 一意識別子

• 実際にはこの 4 つ以外にも要素は存在します。

• ER 図には、つぎのようにいくつかの表記法あります。

  名前	説明
  IE (Infomation Engineering)	J.マーチン が策定。
  IDEF1X (アイデフワンエックス, ICAM definition 1 extended)	アメリカ空軍の標準化手法の開発プロジェクトで策定。IDEFn とあり、その中の IDEF1X がデータモデルの手法。

■エンティティ

■エンティティとインスタンス

• エンティティとは人やもの、具体的または抽象的なものを含めて、情報を蓄積する対象となるものすべてを指しています。これは、情報（インスタンスとよぶ）とは異なります。

• エンティティは数多くあります。たとえば、以下のようなものがあります。
  • 人や製品のような実体のあるもの
  • 技術レベルのような実体のないもの
  • 選挙のようなできごと

    人                アブドゥラ・ムーミン
    製品              2.5ｘ35mm銅くぎ
    製品の種類        くぎ
    雇用契約          派遣
    仕事              SE
    技術レベル        2 級
    チケット予約      今夜のトペン・ダンス
    購入              ケチャックの CD
    選挙              夏の参院選
    プリンターの種類  ．．．
    ドキュメント      ．．．
  

• ひとつのエンティティのインスタンスは、それ自体がエンティティであることがあります。

• 「仕事」のインスタンス「SE」は、「佐藤」や「鈴木」のようなインスタンスを持つ別のエンティティの名前であることもあります。

• データベースの世界では、物理設計の段階では「エンティティ」は「表」候補となります。

■クイズ

1. つぎの中から、エンティティとインスタンスをペアでピックアップしてください。

     バス      所在地    山田一郎         神戸
     経理部    商品      ワンマン・バス   仕事
     顧客      チーム    よど号           部門
     東京      観光バス  阪神タイガース   乗り物
   

■エンティティの記述方法

• シノニム名とは別名のことです。設計段階では、エンティティ候補を洗い出す際に同じ「社員」エンティティのことを人によっては「従業員」や「EMPLOYEE」、「EMP」などと表現するかもしれません。

  

• 名前に関して議論するのは時間の無駄です。したがって、ここでは複数の名前が出てきたときには、本当にそれらの言葉が同じ意味のデータを指しているかどうかを確認するだけにします。

• 表名の候補となる言葉はシノニム名としてあげておきます。

■リレーションシップ

• リレーションシップは、エンティティ間の関連を表現します。

  

  • すべての従業員は部門に属す
  • 2 つ以上の部門に属している従業員はいない
  • すべての部門に従業員がいるわけではない
  • いくつかの部門は 2 人以上の従業員が属している

■リレーションシップの表現

(IE)

(IDEF1X)

(クラス図の流用)

• 最後の図は、UML のクラス図を流用しています。

■リレーションショップの記述方法

• リレーションシップは、エンティティ間の関連を表現します。リレーションシップにおいて、重要となる要素は 3 つありあす。

  

• 名前
  • リレーションシップに、名前をつけることによって、エンティティ間の関連性をわかりやすくします。
  • この図で名前は、ロール名として表現しています。

• 結合度（最大結合度）
  • 関連するインスタンスの発生頻度を表します。
  • 上図の例では、ある部門には複数の従業員が属しているので、部門エンティティのインスタンスひとつに対して多で結合します。
  • それに対し、従業員 1 人は必ずひとつの部門にしか属すことができないとすると、従業員エンティティのインスタンスひとつに対して 1 の結合度で結合します。

• 任意性（最小結合度）
  • 関連するインスタンスの必要性を表します。
  • 上図の例では、従業員は必ずどこかの部門に属していないといけない場合、従業員エンティティからは最小結合度 1 になります。
  • 一方、業務変更などによりあたらしい部門が作られ、まだ従業員が誰もその部門に属していないような場合があれば、部門からは任意となり、最小結合度 0 になります。

• 任意性(最小結合度)と結合度(最大結合度)の 2 つをあわせて、多重度ともよびます。書き方は以下のとおりです。

    最小結合度..最大結合度
  

  □IE
  

  □IDEF1X
  

■実習

1. 従業員エンティティと部門エンティティでは、"部門を管理する" という関係も考えられます。この関係の結合度(最小、最大)を考えてください。

2. 顧客エンティティと商品エンティティとの間にある関係と、そのときの結合度(最小、最大)を考えてください。

■属性

• 属性によって、エンティティの識別、記述、分類、定量化、状態などを表現することができます。

  

• 従業員エンティティに必要となる属性は「ID」、「名前」、「役職」、「給与」、「入社日」などがあげられます。

• 属性を決める際に、ひとつのインスタンスに対して複数の値を持つ可能性があるかどうか検証してください。もし、そのような場合は別のエンティティとして定義する必要があるかもしれません。 （例：「従業員」エンティティ内の扶養家族情報。「扶養家族人数」は属性となりうるが、扶養家族の「名前」や「年齢」は属性ではなく、別のエンティティ候補となる。）

■属性の例

  エンティティ  属性
  ------------- ------------------------------------------
  従業員        姓，年齢，靴のサイズ，現住所，email, ...
  車            モデル，重量，価格，...
  注文          注文日，発送日，...
  仕事          タイトル，説明，...
  取引          量，取引日，...
  雇用契約      開始日，給与，...

• 従業員の属性「現住所」は、変更される確率が高く、それでもある一時点ではひとつの値だけを持つという属性です。

• 属性「靴のサイズ」は重要ではないように見えますが、それは業務によって変わります。会社が従業員に作業服を支給するのであれば、これは必要な属性です。

• いくつかの属性は不安定です（永続性がありません）。つねに安定した、永続性のある属性を探します。

• たとえば、属性「年齢」です。この場合は、「年齢」でなく、「生年月日」を使用します。

■一意識別子

• 属性の中でエンティティを一意に「識別」する属性のことを一意識別子とよびます。一意識別子の属性は、属性名に下線を引きます。

• 一意識別子となる属性は、値が一意であり必須です。

• また、複数の属性の組み合わせによってエンティティを一意に識別する場合もあります。この定義は、将来的に複合基本キーとなります。

■外部キー列に変換される属性

• ER モデルにおける属性は、将来的に表の中の列に変換されます。ただし、列の定義と異なるのは「外部キー列に変換される属性は記述しない」ということです。

• ER モデルでは、エンティティ間の関連性をリレーションシップによって表現しているため、外部キー列になるであろう属性は定義しません。設計者は、物理設計への変換の際に、リレーションシップの定義を基に外部キー列を追加します。

• エンティティの一意識別子が、外部キー列に変換される属性だった場合は、下図のように、相手のロール名に "/" 記号をつけてあらわします。

  

• ER モデルの記述は概念モデリングの段階であり、あくまでもシンプルにすることも目的のひとつです。

• 他の手法では、外部キーとなる属性を記述するものもあります。

■ER モデルの例(セールス・データベース)

• とくに、社員表と従業員表に焦点

  □IE
  

  □IDEF1X
  

  図　社員と従業員

  
  図　セールス・データベース

■データの正規化

• 利用部門が使用する適用業務の画面レイアウトがあらかじめ決められている場合や、既存の表定義を再検討する場合など、概念モデリングをしていく上での基本概念として正規化の手順を踏みます。

• 正規化の目的は、関係データ･モデルの条件にあったデータ構造を定義することです。

• つまり、各フィールドから配列やグループ値をなくし、似た属性をまとめてそれぞれの表に分類します。その結果、データの重複が最小限となり、データ構造が安定し、状況の変化やシステムの拡張に柔軟に対応できるようになります。
  
  

  第 1 正規形	表の各行-列の位置には、値の集合ではなく、ひとつの値が存在します。
  第 2 正規形	キーの一部ではない各列は、キーに従属しています。
  第 3 正規形	それぞれの非キー列は、他の非キー列からは独立しており、キーにのみ従属しています。

■適用業務画面からの正規化

• まず、適用業務画面ないし伝票上で、他の複数の項目から計算などにより導出される項目（導出項目）を除去します。正規化の考え方では、導出項目は適用業務ロジックで導出すべきであり、データベース内に格納すべきではありません。

  
  社員資格表 = 社員番号 + 社員名 + 内線 + 課コード + 課名 + 課長名 + 資格手当合計
                                        + {資格コード + 資格名 + 取得年 + 資格手当}
  
  社員番号 社員名 内線 課コード 課名  課長名 資格手当合計 資格コード 資格名  取得年 資格手当
  -------- ------ ---- -------- ----- ------ ------------ ---------- ------- ------ --------
   930001   山田  1234    10   経理課  松本     20,000       001     英検1級   94    10,000
                                                             002     簿記1級   95    10,000
   931010   鈴木  1235    10   経理課  松本     30,000       003     珠算1級   94     5,000
                                                             002     簿記1級   95    10,000
                                                             004      第2種    96    15,000
   940210   田中  2345    20   秘書課  中山     12,000       005    秘書検定   95    12,000
  
  

■非正規形(UNF)

• 導出項目を除去した状態を非正規形(UNF, Un-Normal Form)とよび、正規化はこの形からスタートします。

  
  社員資格表 = 社員番号 + 社員名 + 内線 + 課コード + 課名 + 課長名 + 資格手当合計
                                        + {資格コード + 資格名 + 取得年 + 資格手当}
  
  社員番号 社員名 内線 課コード 課名  課長名 資格手当合計 資格コード 資格名  取得年 資格手当
  -------- ------ ---- -------- ----- ------ ------------ ---------- ------- ------ --------
   930001   山田  1234    10   経理課  松本     20,000       001     英検1級   94    10,000
                                                             002     簿記1級   95    10,000
   931010   鈴木  1235    10   経理課  松本     30,000       003     珠算1級   94     5,000
                                                             002     簿記1級   95    10,000
                                                             004      第2種    96    15,000
   940210   田中  2345    20   秘書課  中山     12,000       005    秘書検定   95    12,000
  
  

■非正規形の問題点

• この状態では、繰り返し項目が存在しています。

• ひとつの社員番号に対して、複数の資格が含まれています。これは関係データ･モデルの理論「正規化条件を満たす」ことに反しています。

• したがって、「社員資格台帳」を「社員」表と、「資格取得」表に分割したほうが適した形になります。

■第 1 正規化(1NF)

• 第 1 正規化(1NF, 1st Normal Form)は、繰り返し項目をなくすため、データを分割します。
  
  

  □非正規形
  
  社員資格表 = 社員番号 + 社員名 + 内線 + 課コード + 課名 + 課長名 
                                        + {資格コード + 資格名 + 取得年 + 資格手当}
  
  社員番号 社員名 内線 課コード 課名  課長名 資格コード 資格名  取得年 資格手当
  -------- ------ ---- -------- ----- ------ ---------- ------- ------ --------
   930001   山田  1234    10   経理課  松本     001     英検1級   94    10,000
                                                002     簿記1級   95    10,000
   931010   鈴木  1235    10   経理課  松本     003     珠算1級   94     5,000
                                                002     簿記1級   95    10,000
                                                004      第2種    96    15,000
   940210   田中  2345    20   秘書課  中山     005    秘書検定   95    12,000
  
  

  ↓

  □社員表 社員表 = 社員番号 + 社員名 + 内線 + 課コード ~~~~~~~~ + 課名 + 課長名 社員番号 社員名 内線 課コード 課名 課長名 -------- ------ ---- -------- ----- ------ 930001 山田 1234 10 経理課 松本 931010 鈴木 1235 10 経理課 松本 940210 田中 2345 20 秘書課 中山

  □資格取得表 資格取得表 = 社員番号 + 資格コード + 資格名 ~~~~~~~~~~~~~~~~~~~~~ + 取得年 + 資格手当 社員番号 資格コード 資格名 取得年 資格手当 -------- ---------- ------- ------ -------- 930001 001 英検1級 94 10,000 930001 002 簿記1級 95 10,000 931010 003 珠算1級 94 5,000 931010 002 簿記1級 95 10,000 931010 004 第2種 96 15,000 940210 005 秘書検定 95 12,000

• 上図の例では、社員表と資格取得表に分割しました。しかし、ただ分割しただけでは、資格取得表の基本キーがありません。また、それぞれの資格データは社員表のどの資格データに関連づいているのかわかりません。

• そこで、資格取得表に、「社員番号」列を追加します。これにより、「社員番号」列が社員表と関連を持つ外部キーとなります。また、資格取得表のデータを一意に特定させるために、「社員番号」列と「資格コード」列を組みあわせた複合基本キーを定義します。

■第 1 正規形の問題点

• 複合基本キーが存在している表を検討します。複合基本キーは、複数の属性の組み合わせですが、その中の一部の属性のみに依存しているほかの属性を探してみます。

• データ更新時の矛盾性
  • 上図の例では、社員番号が93001と931010とで同じ資格を取得しています。
  • それぞれの資格データには「資格名」や「資格手当」も含まれています。もし「簿記1級」の資格手当が値上がりし10,000円から12,000円に更新された場合、複数のデータがあればすべて更新する必要があるのですが、片方の更新を忘れてしまう危険性があります。

• マスター・基本キー(主キー)が存在しない
  • 資格取得表内のあるデータ（行）を削除したとき、もしほかに同じ資格を取得していなかった場合は、その資格の情報はまったくなくなってしまいます。
  • 上図では、資格コード001の資格の資格名が「英検1級」であることも、資格手当が10,000円であることも、すべてデータベース内からなくなります。
  • また、取得されてない資格データはどこにも存在しませんので、別に資格に関するマスター表を作成する必要があります。

■第 2 正規化(2NF)

• 第 2 正規化(2NF, 2nd Normal Form)は、複合基本キーの一部の属性にのみ依存している属性を独立させて、表を分割します。ただ分割しただけでは2つの表の関連性がなくなってしまうため、もとの表には新しい表の基本キーに対応する外部キーを残します。
  
  □資格取得表

  
  資格取得表 = 社員番号 + 資格コード + 資格名 + 取得年 + 資格手当
               ~~~~~~~~~~~~~~~~~~~~~
  社員番号 資格コード 資格名  取得年 資格手当
  -------- ---------- ------- ------ --------
   930001     001     英検1級   94    10,000
   930001     002     簿記1級   95    10,000
   931010     003     珠算1級   94     5,000
   931010     002     簿記1級   95    10,000
   931010     004      第2種    96    15,000
   940210     005    秘書検定   95    12,000
  
  

  ↓

  □資格取得表 資格取得表 = 社員番号 + 資格コード + 取得年 ~~~~~~~~~~~~~~~~~~~~~ 社員番号 資格コード 取得年 -------- ---------- ------ 930001 001 94 930001 002 95 931010 003 94 931010 002 95 931010 004 96 940210 005 95

  □資格表 資格表 = 資格コード + 資格名 + 資格手当 ~~~~~~~~~~ 資格コード 資格名 資格手当 ---------- ------- -------- 001 英検1級 10,000 002 簿記1級 10,000 003 珠算1級 5,000 004 第2種 15,000 005 秘書検定 12,000

  
  
  
• 第 2 正規化によってデータ更新時の矛盾性やマスター・データの消去を回避させることができます。
  
  
• 上図では、資格取得情報と資格情報を独立させたため資格取得表の行(基本キー(主キー))を資格表の1ヶ所のみ変更すればすみます。

■第 2 正規形の問題点

• 第 1 正規形では、複合基本キーを含む表に関して検討しましたので、今回はその他の表について検討します。

• 基本キーではない属性にのみ依存している他の属性があるかどうかチェックします。

• 上図では「課名」と「課長名」の2つの属性は、「課コード」にのみ依存しています。ここでの問題点は第１正規形における問題点と同様です。

■第 3 正規化(3NF)

• 第 3 正規化(3NF, 3rd Normal Form)は、基本キーではない属性にのみ依存している属性を独立させて、表を分割します。
  
  □社員表

  
  社員表 = 社員番号 + 社員名 + 内線 + 課コード + 課名 + 課長名 
           ~~~~~~~~
  社員番号 社員名 内線 課コード 課名  課長名 
  -------- ------ ---- -------- ----- ------ 
   930001   山田  1234    10   経理課  松本  
   931010   鈴木  1235    10   経理課  松本  
   940210   田中  2345    20   秘書課  中山  
  

  ↓

  □社員表 社員表 = 社員番号 + 社員名 + 内線 + 課コード ~~~~~~~~ 社員番号 社員名 内線 課コード -------- ------ ---- -------- 930001 山田 1234 10 931010 鈴木 1235 10 940210 田中 2345 20

  □課表 課表 = 課コード + 課名 + 課長名 ~~~~~~~~ 課コード 課名 課長名 -------- ----- ------ 10 経理課 松本 20 秘書課 中山

  
  
  
• 上図では、「課コード」と、それに依存している「課名」と「課長名」を独立させて課表を作成します。

• 課表の基本キーは「課コード」であり、社員表の外部キーも同じく「課コード」です。

• これによって、同じ社員が複数の資格を取得をしていた場合でも整合性は保たれますし、課情報の変更は課表を1ヶ所変更するだけですみます。

■正規化の結果

• 第１正規化から第 3 正規化までした結果、もともとひとつのデータ構造が4つの表に分割されました。
  
  

  □非正規形
  
  社員番号 社員名 内線 課コード 課名  課長名 資格コード 資格名  取得年 資格手当
  -------- ------ ---- -------- ----- ------ ---------- ------- ------ --------
   930001   山田  1234    10   経理課  松本     001     英検1級   94    10,000
                                                002     簿記1級   95    10,000
   931010   鈴木  1235    10   経理課  松本     003     珠算1級   94     5,000
                                                002     簿記1級   95    10,000
                                                004      第2種    96    15,000
   940210   田中  2345    20   秘書課  中山     005    秘書検定   95    12,000
  
  

  ↓

  □社員表 社員番号 社員名 内線 課コード -------- ------ ---- -------- 930001 山田 1234 10 931010 鈴木 1235 10 940210 田中 2345 20	□課表 課コード 課名 課長名 -------- ----- ------ 10 経理課 松本 20 秘書課 中山
  □資格取得表 社員番号 資格コード 取得年 -------- ---------- ------ 930001 001 94 930001 002 95 931010 003 94 931010 002 95 931010 004 96 940210 005 95	□資格表 資格コード 資格名 資格手当 ---------- ------- -------- 001 英検1級 10,000 002 簿記1級 10,000 003 珠算1級 5,000 004 第2種 15,000 005 秘書検定 12,000

• 一見初めの状態のほうがシンプルであり、正規化することによってわかりにくくなったように感じるかもしれませんが、関係データベースに格納する形としては、このほうがより適しています。

• 正規化は、一般的に第 3 正規化までが適当とされています。それ以上の正規化もありますが、より細かく分けてしまうと結合処理が頻繁に発生する結果となり、関係データベースには適さない形になってしまうこともあります。

• また、パフォーマンスを考慮して正規化を崩す場合もあります。これを非正規化とよびます。

■実習