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

話題提供

■演習（1/2）

（概要） 操作方法の復習と次の演習(2/2)の準備

XAMPPのphpMyAdminを使用して以下のデータベースを作成する。

要件：

楽曲情報を登録

データベース利用者を登録

楽曲に対する利用者からの評価を登録

データベース名 btunes3123222（学籍番号を名前に含める）

テーブル名 songs

テーブル名 users

テーブル名 recommends

（概略手順）

・データベースを作成

・以下の画像のテーブルを３つ、外部キー制約を２つ作成

テーブル作成：前々回

外部キー制約設定：前回

・テーブルのフィールド名　データ型　データ長　デフォルト値　主キー 

デザイナ画像の　テーブル設定を参照

主キー（鍵アイコン）に注意

recommends は複合キーを設定

song_id と user_id でアンダーバー（ 「_」記号）を使う

演習1/2のデータベースのデザイナ画面

songsテーブルの作成

usersテーブルの作成

recommendsテーブルの作成

song_idとuser_idを両方ともPRIMARY にして複合キーを設定する

保存する　を押して複合キーを設定する。（以下の画像参照）

外部キー制約（参照制約）の設定

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

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

※外部キー制約（参照制約）の設定でエラーとなる場合は制約を設定するテーブル間のレコードの値を確認する。

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

以下の順に選択してリレーションビューの画面から設定する

recommendsテーブル → 構造 → リレーションビュー

■提出

課題　問１

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

■演習（2/2）

動作確認用のサンプルデータを入力する

楽曲レコードを数件

ユーザーを数件

楽曲評価を数件　楽曲の評価値 は １～５ の数値を入力

次に、楽曲の評価をSQLで集計して表示する

評価値（１～５）の楽曲ごとの最大、最小、平均を求める

集計結果を確認できるように工夫して評価をテーブルに追加しておく（不足ならここでさらに追加する）

SQLの集計関数の実験

phpMyAdminの 

SQL 実行タブ

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

SQLの文法の詳細はPaizaラーニングの教材（予習・自習用）と講義第6回～第8回のSQLZOOで練習する。

楽曲の最高点と最低点と平均点を求めて表示する例）

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コマンドの処理内容：

selectコマンド　テーブルを検索

from句　検索対象のテーブルは２つ　songs as s, recommends as r　でそれぞれ s と r と略記

表示内容は　s.id, s.title, max(r.stars), min(r.stars), avg(r.stars)　の順にフィールドや集計値を表示

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

where句　条件設定

sとrを idが一致する条件で結合する

group by句　グループ化

sのidが同じものを集計範囲としてまとめる

ようするに曲ごとにお勧め（☆）の数の最大と最小と平均を集計して表示する。

実験結果の記録

SQLによる集計結果が表示されているテーブルのスクリーンショットを撮る

追加点の課題

応用1：

次の様にSQLコマンドを修正して色々試す

表示する項目の入れ替えや削除　（s.id, s.title, max(r.stars), min(r.stars), avg(r.stars) から適当に削る）

グループ化なしで集計する　（group by s.id　を削除。全ての曲について集計）

今年発表された楽曲だけで集計する　（where s.id = r.song_id に続けて　and year = 2023　の様な条件を追加）

評価順に並び替える　（並べ替えで使用する項目にoと名前を付け　max(r.stars) o,　SQLの末尾に order by o　を追加）

応用２：

btunes データベースを設計変更してアーティストを登録できるようにする

songs テーブルを修正する。修正内容は　artistフィールド　の削除

artists　テーブルを追加する 。フィールドは主キーのid と name など。幾つか増やしてもよい。

artist_song テーブルを追加する。

song_id から songsテーブルに外部キー制約を設定する。制約名 song_artist_key

※ 制約名 song_key やuser_keyは演習1/2で使用済みのため使用できない

artist_idからartistsテーブルに外部キー制約を設定する。制約名 artist_key

■提出

課題　問2

SQLの実験結果のスクリーンショットを撮る。

＋

応用１　SQLの実行結果をSQLと出力（テーブル）が分かるようにスクリーンショットして、アップロード用画像に加える。

＋

応用２　テーブルを設計変更してからデザインビュー画面のスクリーンショットを撮り、アップロード用画像に加える。

Webclassにアップロードする。

■課題　問3　Webclassに回答（採点は次週以降に実施）

応用問題

ChatGPTやGrokなどのAIを利用して、今回の演習で用いた楽曲データベース　btunes の改善点を見つける。

改善の方向性としてはテーブルやフィールドを追加してより利用者にとって便利な楽曲データベースにすること。

AIの提案した改善案の中から受講生が魅力的・有意義と感じたものを1つ選んでレポートする。

レポート項目

　　「btunesについて受講生がAIに説明した内容」

　　「受講生が選んだ機能拡張の説明」

「拡張機能の実装に必要なSQLコマンド」

正規化の必要性とメリット

前回の第4回課題 問３で以下の様な「受験者名簿」テーブルの正規化について考えた。

このテーブルで受験者の情報を記録するには幾つかの問題が発生することを確認しよう。

テーブル「受験者名簿」

〇受験番号

判定結果

（〇出願者番号、〇入試区分、〇学科）　複合キー

受験料

学費

出身校

氏名

生年月日

受験者の年齢

受験者の十二支の干支

郵便番号

都道府県

市町村

番地など

次の表のレコードはchatGPT4で生成して調整した。郵便番号は正しいものではない。

この表の段階で第一正規形であるが、第二正規形ではない。

（補足）

生年月日フィールドには　年　月　日　の3項目が入っているが一纏めにして日付型の単一フィールととして扱う。日付型の値から年月日を導出できる。

郵便番号は数字7文字のVARCHAR型で記録して、表示する際に3桁と4桁に分けて表示すればよい。

氏名、番地も姓や丁目だけを扱うことは無いので1つのフィールに記録する。

複数の高校に在籍していた場合は出願時に在籍している高校を記載するとする。
在籍した高校をすべて登録するとすると出身校のフィールドが繰り返し項目となり第一正規ではなくなる。

正規化前

レコード挿入に関する問題

受験者を記録する度に出願者情報、学費などを繰返し記入することになる

新規の出願者情報を登録する際に、受験方式と学科を決めないといけない

新規の学科の追加は受験者を記録するときにしかできない（予め学科名や学費は決まっているのに）

レコード更新に関する問題

出願者の情報を訂正する際に、その出願者の全ての受験記録の出願者情報を修正することになる（複数回受験している場合）

生年月日を訂正すると、年齢と干支の訂正が発生する（訂正しないと生年月日と不整合になる）

レコード削除に関する問題

ある学科の受験者をすべて削除すると、学科名と学費の記録も消える

正規化によるテーブルの設計変更

郵便番号テーブル　○郵便番号　都道府県　市町村

出願者テーブル　○出願者番号　氏名　出身校　生年月日　※郵便番号　番地など

年齢と干支は導出可能なので削除

学科テーブル　○学科名　学費

入試区分テーブル　○入試区分　受験料

受験者名簿　○受験番号　※出願者番号　（以下省略）

正規化後

出願者の情報の訂正は出願者テーブルのレコードを修正するだけで済む。

受験番号を決める前の時点で、出願者の情報、学科と学費、入試区分と受験料、を予め登録しておける

受験者が０名だったり、受験情報を削除しても学科の情報が消えない（学科テーブルに残る）

テーブルを正しく正規化できたか確認するには、正規化後のテーブルにレコードの追加修正削除が正常に行えるか確認するとよい。

ボイス・コッド正規形（BCNF）

Wikipedia 関係の正規化 から：

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

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

いいかえると

・非候補キーも候補キー（複合キーの各属性）も（つまりすべての属性）、主キーに完全従属しなければならない

第３正規形では非候補キーから他の非候補キーへの関数従属性がある場合は分解して正規化するが、非候補キーから候補キーへの関数従属があっても放置している。

非候補キーから候補キーへの関数従属性を持つ属性があるなら別の表に分離してBCNFに正規化する。

・第３正規形まではテーブルの正規化では元のテーブルから分離可能な部分を取り除いて新しい表を１つ用意した。正規化を進めるとテーブルは２つに分割された。

BCNF以降のテーブルの正規化では１つのテーブルを３つ以上に同時に分割する必要がある場合がある。

＠ITアジャイル/DevOpsDatabase Expert「特殊な正規形」を理解する　https://www.atmarkit.co.jp/ait/articles/1703/01/news183.html

qiita/ ボイスコッド正規化  https://qiita.com/gooddoog/items/f40a7f0602bbe6afa1cf

BCNFである例

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

　　次回の診療日時を記録する。

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

ここでは候補キーは主キー（○担当医　○日時）のほかに、 （○日時　○患者）や （○患者　○担当医）がある。

各候補キーに対応する非属性キーは以下の通り。

○担当医　○日時　　→　患者

○日時　　○患者　　→　担当医

○患者　　○担当医　→　日時

　例１の３つの属性、担当医　日時　患者　の全てが候補キーに完全従属していることが確認できたのでボイス・コッド正規形であると言える。

（確認）

ボイス・コッド正規形なら自動的に第2正規形でありさらに第三正規形でもある。

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

→ 候補キーに部分従属する属性はない → よって全ての属性は候補キーに完全従属

BCNFでない例

　地区巡業（　○担当者　○地区　宿泊地　滞在日数　）

　○担当者　○地区　→　宿泊地

　○担当者　○地区　→　滞在日数

で　担当者　地区　が候補キー。さらに、担当者は一度訪問した地区には再び滞在しないものとする。

　ここで

宿泊地　→　○地区

のように非候補キー属性　宿泊地　からの関数従属が存在するので　地区巡業　はBCNFではない。

※非候補キー属性　宿泊地　と　滞在日数　には候補キーから推移的従属は存在しない。つまり第3正規形である。

確認

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

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

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

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

・誤　複合キーがない場合、第３正規形である。

・正　複合キーがない場合、第２正規形である。

第4正規形

Wikipedia 関係の正規化 から：

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

2NF、3NF、BCNFでは関数従属性を持つ属性の問題を解消したが、4NFでは多値従属性を持つ属性の問題を解消する。

候補キーではない属性への多値従属性を持つ属性があるなら別の表に移して4NFにする。

関数従属性：　 Aが１つ決まると対応して Bが１つ決まる性質のこと

 表記は　 A→B　でBがAに関数従属することを示す

多値従属性：　テーブルの属性　A　B　C　があって、

Aを1つ決めるとBが一式セットで決まる（集合が求まる）

Aを1つ決めるとCも一式セットで決まる

BとCの間には従属性は無い

この状況を以下の様に表記する

　　　A →→ B | C

BはAに多値従属するという

AとCが具体的に (a1 c1) (a1 c2)のときに(a1 b1) (a1 b2)とすると

(a1 b1 c1)
(a1 b2 c1)
(a1 b1 c2)
(a1 b2 c2)
の様にc1でもc2でも無関係にa1に対しては b1 b2が対応する状況を示している

このとき、
(a1 b1 c1)
(a1 b1 c2)
(a1 b2 c1)
(a1 b2 c2)
の様にb1でもb2でも無関係にa1に対してはc1 c2が対応する状況になるので、

A →→ C | B 　でCはAに多値従属　が成立する

確認：

・テーブルに属性が３つ以上ないなら多値従属性は無い。

・多値従属性を持つ属性をテーブルに記録するには3属性以上の複合キーが必要である。

・複合キーを持たないテーブルには多値従属性は無い。ただし単一キーのテーブルでも関数従属性が存在する場合がある（3NFが必要な場合）。

４NFでない例

このテーブルは 教科・参考書・教員 の３属性が複合キーで非候補キーは存在しない。よって３NFである。

（○科目　○参考書　○教員）

応用情報技術者　基本情報試験対策　教員１

応用情報技術者　応用情報試験対策　教員１

応用情報技術者　基本情報試験対策　教員２

応用情報技術者　応用情報試験対策　教員２

基本情報技術者　基本情報試験対策　教員１

このテーブルには

科目　→→　参考書　｜　教員

の多値従属性がある。つまり科目 から 複数の参考書籍が決まる が それは 教員 とは無関係に決まる。

多値従属性がある場合は対応する多値従属性がある。

科目　→→　教員　｜　参考書

で、科目から複数の教員が決まるがそれは参考書とは無関係である。

この2つの多値従属性を別々のテーブルに分解して4NFにできる。

教書リスト（○科目　○参考書）

応用情報技術者　基本情報試験対策

応用情報技術者　応用情報試験対策

基本情報技術者　基本情報試験対策

教科担当リスト（○科目　○教員）

応用情報技術者　教員１

応用情報技術者　教員２

基本情報技術者　教員１

例2）

　（○学生　○連絡方法　○教員）

　教員グループと学生の間の連絡方法を登録する

　学生は　連絡方法を　電話　電子メール　SNS　などをいくつか選んで登録する

　教員は　すべての連絡方法に対応できるものとする（教員個別の連絡方法を指定しない）

連絡方法と教員は独立の場合（教員個別の連絡方法を指定しない）：

多値従属性1 学生→→連絡方法　どの連絡方法でも、どの教員にも連絡可能

多値従属性２ 学生→→教員 どの教員にも、どの連絡方法でも連絡可能

連絡方法と教員は独立でない場合（教員個別の連絡方法を指定する）：

この場合は4NFによる正規化は行えない（不必要）

SNSを連絡に使わない場合など、教員ごとに利用可能な連絡手段を登録することになる。つまり学生を定めても連絡方法が決まらない（連絡先の教員の都合で変わる）

他の例）

（○巡礼ツアー企画名　○巡礼地　旅行会社）　※企画名で巡礼地が決まるとする

（○一挙上映企画名　○映画タイトル　映画館）　※企画名で映画のセットリストが決まるとする

（○キャラクター　○装備品　作品） ※キャラクターが決まると装備一式（剣・盾・鎧のような感じで）が決まるとする

第5正規形

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

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

↑※文字化けしているので別のページを紹介（2023/5/10確認）５７ページ目

Wikipedia 関係の正規化 から：

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

５NFとして分解できる例

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

（Ａ Ｂ） と （Ｂ Ｃ） と （Ｃ Ａ）

の３つのテーブルに情報無損失分解ができたとする（BCNF以降の正規化で現れるパターン）。

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

テーブル（Ａ Ｂ） と （B Ｃ） と （Ｃ Ａ） の主キーは、 ABとBCとCA。

元のテーブル（Ａ Ｂ Ｃ）の主キーは、ABC で、ABやBCやCAとは異なっている。

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

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

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

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

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

具体例

　（○学生　○連絡方法　○教員）

学生はいくつかの連絡方法を持っている

教員もいくつかの連絡方法を持っている

連絡方法のパターンは学生と教員で同じとは限らない。

学生と教員の間で共通の連絡手段をすべて登録するものとする。

↑この条件が冗長性を生じている。情報無損失分解可能。つまり5NFではない。

この条件が無ければこの複合キーは分解不能でこのテーブルは5NFである。

この表には多値従属性は無いので4NFである。

（確認）

学生が定める連絡方法のセットは教員に依存する（多値従属であるには独立の必要がある）

教員が定める連絡方法のセットは学生に依存する（多値従属であるには独立の必要がある）

テーブルを以下の3つに分割して5NFにできる。

（○教員　○連絡方法）

（○学生　○連絡方法）

（○教員　○学生）

このテーブルを全て結合すると元のテーブルを再構成できる。

■課題　問4　Webclassに回答（採点は次週以降に実施）

以下の5NFのテーブルを結合して元のテーブルに戻すことを考える。

教員リスト（○教員　○連絡方法　アドレスまたは番号）

学生リスト（○学生　○連絡方法）

担当リスト（○※教員　○※学生　担当期間）

結合リスト（元のテーブル）

（○教員　○連絡方法　アドレスまたは番号　○学生　担当期間）

回答内容：

・教員2名～、学生2名～、連絡方法2種類～を架空で構わないので3つのテーブルのレコードとして例示する。

・3つのテーブルを結合したテーブルのレコードを示す

・教員と学生で連絡方法が異なる場合は結合結果のテーブルには含まれないものとする。または、連絡方法が食い違わない様にレコードを工夫する。

・結合結果の元のテーブルにはレコードが2件以上含まれるものとする

回答書式：

教員リスト

　レコード１・・・・

　レコード２・・・・

学生リスト

　レコード１・・・・

　レコード２・・・・

　レコード３・・・・

担当リスト

　レコード１・・・・

　レコード２・・・・

　レコード３・・・・

　レコード４・・・・

結合リスト

　レコード１・・・・

　レコード２・・・・

　レコード３・・・・

　レコード４・・・・

　レコード５・・・・

　レコード６・・・・

（回答のヒント）

学生と教員とで共通の連絡手段がない場合は、結合したテーブルにも連絡手段での組み合わせは出現しない。

■提出

Webclassの　課題　問4　に記入して回答する。

補足：2023/5/10

担当期間の表記方法は自由に考えてOK

フレッシュマンゼミ

基礎ゼミ

専門ゼミ　２年間

学生プロジェクト　３年間

２０２０

２０２２－２０２３

２０２３フレゼミ

など

■予習

来週から　SQLZOO　に3回連続で取り組みます。
Paizaラーニングの講座　新・SQL入門編　の　新・SQL入門編3: WHERE句を理解しよう　まで進めておいてください。
SQLの文法については講義中でも触れますが、基本的にPaizaの講座によるアクティブラーニングとします。