5.テーブルの正規化(2/2)

第4正規形、第5正規形、ボイスコッド正規形

先回、第1～第3正規形の基準と、非正規形のテーブルの正規化の手順について解説した。

あわせて、正規化によるメリット、レコードの追加・更新・削除などの操作におけるデータ更新の問題点とその解消について確認した。

先回の テーブル正規化ミニレポートについてのコメント：

非正規形 のテーブルを 正規化 すると、導出項目の除去や重複項目（非スカラー値）の除去、複数テーブルへの分解などが起きる。

正規化後のテーブルにおいても、レコードの追加修正などの操作は、正規化前のテーブルでの操作と同様に実行できなければならない。

正しく正規化できたかどうか判断するために、正規化後のテーブルにレコードの追加修正が正常に行えるか確認するとよい。

・ひとつのテーブルに複数のフィールドからなる主キー（複合キー）が発生することがある。

・自分の回答案のテーブルに対して、

新規顧客の追加、新規商品の追加、注文の発生 、注文の修正、 注文のキャンセル

が発生しても、記録内容に問題が起きないか確認する。

■今回の内容

テーブル設計：

第4正規形、第5正規形、ボイス・コッド正規形について学ぶ。

キーワード： 外部キー 参照制約 非正規化

Wikipedia リレーションの正規化 から：

ボイス・コッド正規形

・すべての属性が候補キーに完全従属する。

※候補キー 主キーにすることが可能な属性の集合

いいかえると

・候補キーを構成する属性であっても、主キーに完全従属しなければならない。

例１）

病院予約（ 担当医 日時 患者 ）

※予約は担当医ごとに次回予約のみ記録する。予約日時は、年月日診療時間まで記録する。

担当医 日時 患者 のフィールのうち、２つを組み合わせたものが 候補キー となる。

担当医 日時 → 患者

日時 患者 → 担当医

患者 担当医 → 日時

利用者だけで決まる項目も、担当医だけで決まる項目も、日時だけで決まる項目もない。

→ 候補キーに部分従属する属性はない → よって全ての属性は、候補キーに完全従属 → ボイス・コッド正規形

例２）

以下の時間割リレーションは、ボイスコッド正規形か？

時間割 （教室 曜日 時間 教員 科目）

※教員は同じ時間に異なる科目を担当しない。

※教室は同じ時間に複数の科目を開講しない。

※同じ曜日と時間で同じ科目は開講されない。

※教員は複数の科目を担当することがある。 →これを ない に変えた場合はどうか？

※ある科目を複数の教員が担当することがある。 →これを ない に変えた場合はどうか？

※教員は同じ科目を別の曜日や時間に開講することがある。 →これを ない に変えた場合はどうか？

主キーは？ 候補キーは（何パターンある）？ 非キー属性は？ 完全従属か？

例３）

以下の文章のそれぞれは、○か×か？

・ボイス・コッドの正規形は第３正規形からさらに正規化を進めたものである。

・ボイス・コッド正規形の条件を満たす場合、第３正規形になっている。

・非候補キー から 候補キー（かつ複合キー）の一部に関数従属性があっても、それは第3正規形である。

第3正規形では、非候補キー から 非候補キー への関数従属性を認めず除去するだけ。

ボイス・コッド正規形（ＢＣＮF）では、非候補キー から 候補キーに関数従属性がある場合、それを許さずに除去する。

【確認】

・複合キーがない場合、第３正規形である。 → 先回紹介した基本情報技術者の午後問題の設問1

・複合キーがない場合、ボイス・コッド正規形である。

・候補キーの一部として使用できない属性がない場合、ボイス・コッド正規形である。

・候補キーが１つの場合でも、ボイス・コッド正規形でない場合がある。

・第３正規形の非候補キーから候補キーの中の一部のキーに関数従属性がある場合、分解してボイス・コッド正規形にできる。

第4正規形

第4正規形 (fourth normal form; 4NF) では候補キーではない属性への多値従属性をもった属性があってはならない。

以下の第３正規形について考える。（主キーは、 教科・参考書・教員 の３つからなる複合キー、非候補キーはない）

例）

科目・参考書・教員 の表

応用情報技術者 参考書あ 教員１

応用情報技術者 参考書い 教員１

応用情報技術者 参考書あ 教員２

応用情報技術者 参考書い 教員２

基本情報技術者 参考書あ 教員１

上の例には、教科 から 参考書 へ と、 教科 から 教員への 多値従属性がある。

下の様に分解して、第４正規形にできる。

教書リスト

応用情報技術者 参考書あ

応用情報技術者 参考書い

基本情報技術者 参考書あ

教科担当リスト

応用情報技術者 教員１

基本情報技術者 教員１

応用情報技術者 教員２

例題）以下について検討

学生 連絡方法 教員

※どの教員からどの学生に何の手段で連絡可能かを記録する

※連絡方法には 電話 メール SNS など複数利用が可能で、学生によって単一～複数の連絡方法を登録

※教員は学生に対してどの連絡方法でも利用可能

第5正規形

テーブルを情報無損失分解できない、もしくは分解後のテーブルの主キーと分解前のテーブルの主キーが同一となり分解する利点がない状態。

情報を損失して分解した例 → http://design.first-database.com/lossless.html

第5正規形 (fifth normal form; 5NF) を満たすリレーションは、そのリレーションが第4正規形であり、さらにそのリレーションに含まれる結合従属性の決定項が候補キーのみである。

例１）

テーブル（ Ａ Ｂ Ｃ） で、 Ａが主キーのとき、 （Ａ Ｂ） と （Ａ Ｃ） の２つのテーブルに情報無損失分解できる。

結合従属性の表記は ＊｛（Ａ Ｂ） （Ａ Ｃ）｝

テーブル（Ａ Ｂ） と （Ａ Ｃ） の主キーは A。Aは元のテーブル（Ａ Ｂ Ｃ）の候補キーである。（主キーだから）

よって結合従属性の決定項は候補キーのみであるので、

（Ａ Ｂ Ｃ）は第5正規形である。

つまり、 （Ａ Ｂ Ｃ） を情報無損失分解して、（Ａ Ｂ） （Ａ Ｃ） に分解しても利点は無く、それ以上は意味のある情報無損失分解が存在しない状態になっている。

例２）

テーブル（ Ａ Ｂ Ｃ） で、 （Ａ Ｂ Ｃ）が主キーのとき、

（Ａ Ｂ） と （Ｂ Ｃ） と （Ｃ Ａ） の３つのテーブルに分解して、情報無損失分解ができる場合、

結合従属性の表記は ＊｛（Ａ Ｂ） （Ｂ Ｃ） （Ｃ Ａ）｝

テーブル（Ａ Ｂ） と （B Ｃ） と （Ｃ Ａ） の主キーは、 AとBとCの１つづつ。

テーブル（Ａ Ｂ Ｃ）の主キーは、ABC で、AやBやC単独では主キーにはならない。

よって結合従属性の決定項に候補キーではないものがあるので、

（A B C） は第5正規形では無い。

（Ａ Ｂ Ｃ） を情報無損失分解して、（Ａ Ｂ） （Ｂ Ｃ） （Ｃ Ａ）に分解することで、（Ｃ Ａ） にだけ新規レコードを追加可能になるなど利点が生まれる。

ただし、第5正規形になった3つの表を結合する際には、全ての表を用いて結合しないと、元の正しい状態の表を構成できない。

例）

（Ａ Ｂ） と （Ｂ Ｃ）を結合して、 （Ａ Ｂ Ｃ） を得ても、（Ｃ Ａ）の関係を満たさないレコードが発生する可能性がある。

例２）第５正規化の例

教員 学生 連絡方法

※どの教員からどの学生に何の手段で連絡可能かを記録する

※連絡方法には 電話 メール SNS など複数利用が可能で、学生によって単一～複数の連絡方法を登録

※教員が利用可能な連絡手段は複数あるが、どれを利用可能かは教員によって異なる。 ※この条件から先の例と異なり、多値従属は存在せず第４正規形である。

※学生の利用可能な連絡方法と教員が利用可能な連絡方法は、お互いに一致しないものがあってもよい。

※テーブルの記録は、教員と学生で共通の連絡手段が複数あれば、その正当な組み合わせを全て記録する ※第５正規形ではない要因

状況確認：

学生の知り合いAがある教員に伝言をする。

Aは複数の学生と知り合いでもよい。

Aの使う連絡方法がいくつかある。

Aが選択した連絡方法である教員に伝言するには、Aと関係のある学生が、＊その連絡方法を使える ＊その教員と連絡方法を共有する の２条件を満たす必要がある

Aと教員が共有する連絡方法を使って直接連絡は出来ない。Aと学生と教員の連絡方法は伝言の過程で変更しない。

第５正規形は元のテーブルを分解した （教員 連絡方法） （学生 連絡方法） （教員 学生） となり、この3テーブルを全て結合すると元のテーブルになる。

※「テーブルの記録は、教員と学生で共通の連絡手段が複数あれば、その正当な組み合わせを全て記録する」の条件がなければ、教員 学生 連絡方法は第５正規形。

共通の連絡方法全てを登録してもよいし、一部を選択して登録してもよい状態。

Wikipedia「関係の正規化」の例 精神科医・保険会社・病気 の第５正規化の説明と比較してみる。

精神科医→教員 保険会社→学生 病気→連絡方法

患者→学生の知り合い

患者が入っている保険会社→学生の知り合いが教員に伝言を依頼する学生

その他）以下のテーブルの第５正規化について考える。

キャラ 所属ギルド 所有スキル

ギルドに所属するにはスキルが必要。要スキルのクエストがある。クエストは幾つかのギルドから出される。キャラがクエストを行うには、クエストのギルドとスキルがマッチするかテーブルで調べる。

キャラのスキルとギルドのスキルでマッチしたものは全てテーブルに記録する。キャラは所有スキルを全てギルドに提供する。

プログラマー IDE 言語

開発案件でIDEと言語の指定がある。

ある案件にあるプログラマーが対応可能かテーブルでチェックする。

プログラマーはIDEが対応した言語について、自分が対応できる言語すべてに対応する。

今回もこちらのサイトの解説のほうが分かりやすい。

■＠ＩＴのデータベース正規化の解説記事が分り易くて詳しいので紹介 http://www.atmarkit.co.jp/ait/articles/0605/11/news124.html

■ http://www.dsl.gr.jp/~okuyama/lecture/2006/tut/tut-database-20060508.pdf 詳しく分かりやすい。

■演習１

XAMPP に以下の様なデータベースを作成する。

要件： 楽曲情報を登録 利用者を登録 楽曲の利用者からの評価を登録

データベース名 btunes

テーブル名 songs

テーブル名 users

テーブル名 recommends

作成方法：

・データベースやテーブルの設定は、前々回の演習内容を参照 ※ recommends は複合キーを設定すること（インデックスの設定画面から）

・外部キー制約の設定は、前回の演習補足を参照

・各テーブルの フィールド名 データ型 データ長 デフォルト値 主キー は↑の内容を設定

注意点： 主キー（インデックス）設定すること。recommneds は複合キーを設定する

・外部キー制約（参照制約）を設定 recommnes に対して。

※リレーションの設定内容は RISTRICTでよい。NO ACTIONと同義。参照記事： http://qiita.com/suin/items/21fe6c5a78c1505b19cb

主キー（song_id , user_id） に対して、参照制約として songs の id と users の id が 外部キーになる。

※外部キー制約（参照制約）は、設定するフィールドに記録済みの値に不整合がある場合、設定に失敗する。

recommends 側にsongs や usersに存在していない id が記録されていると設定できないので、そのような値は修正すること。

・サンプルデータを入力する。

仮の楽曲を複数、仮のユーザーを複数、仮の楽曲評価を複数、記録する。

楽曲の評価値 は １～５ の数値を指定すること。

SQLの実験：

phpMyAdminの SQL 実行タブで、以下のSQLコマンドを実行して、結果を確認する。

各楽曲ごとに評価の 最高max 最低min 平均avg の３つを表示する SQLの例）

select s.id, s.title, max(r.stars), min(r.stars), avg(r.stars) from songs as s, recommends as r where s.id = r.song_id group by s.id

※ 赤文字 SQLの予約語 オレンジ SQLの集計関数 青色 テーブルの省略名 黒 テーブル名

※ SQLコマンドの内容 2つのテーブル songs と recommends を省略名 s と r とする。sとrを idが一致する条件で結合し、sのidでグループ化する。

各グループごとに（つまり曲ごとに）評価値 stars の max min avg を集計する。

id と title と集計結果３つを順に並べて表示する。

上記の操作では、 結合 と 射影 をしている。選択はしていない。

応用： 表示する項目の入れ替え、削除。

応用： 評価順に並び替えるには？

応用： グループ化なしで集計するとどうなるか確認。

応用： 今年発表された楽曲だけで集計する。（選択条件を付ける）

余談）

非正規化の効用について： 頻繁に stars に関する集計を実行するような場合、集計結果を表に格納しておき、参照する。

集計結果表示時の、DBMSの負荷を下げ、レスポンスタイムを短くすることが出来る。

■提出

・ WebClassに、上記演習で作成したXAMPP のデザインビュー画面のスクリーンショットをアップロードする。

■課題

Ｗｅｂｃｌａｓｓに以下を回答

■問題２

プロフィール のテーブルに 名前 性別 生年月日 年齢 星座 血液型 連絡先 のフィールドがある。

このフィールドから 導出項目として削除可能なものを示せ。

■問題３

次のような、９つのフィールドを持つテーブルについて答えなさい。

注文ID 注文日 顧客ID 顧客名 届け先 商品ID 商品名 単価 個数

Step1 正規化して４つのテーブルに分解する。

エンティティ（テーブル、ER図では四角で囲む）は

注文リスト

注文詳細リスト

顧客リスト

商品リスト

の４つ。

Step2 ＥＲ図にまとめる。

リレーション（参照制約、ER図では菱形で囲む）

発注 ： 顧客 と 注文 のリレーション

注文内容： 注文 と 注文詳細 のリレーション

商品情報： 注文詳細 と 商品 のリレーション

ER図を画像ファイルにしてアップロードする。

PowerPoint や ワード や XAMPP のデザイン画面を利用してよい。

■問題４

個人時間割表の正規化

学生ポータルの時間割表を正規化する。

テーブルと属性 について検討し、正規化したテーブルの設計例を示せ。

・各自で時間割に必要と思われる項目について検討する。

・正規化されたテーブルのフィールドで主キーであるものについて、適当に示すこと。

・各フィールドの定義域（ドメイン）について検討し、データ型や数値の範囲も設計すること。

■問題５

コンビニ、飲食店、レンタルショップ、映画館など、実際に運用されている業務用データベースのレシートの記載項目を参考にして、

テーブルの設計例を示す。