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

関係データベースにおけるテーブルの設計理論を2回連続で解説する。

テーブルの正規化（1/2）今回：第１正規形、第２正規形、第３正規形（正規形 = Normal Form、1NF、2NF、3NFと表記）を扱う。

テーブルの正規化（2/2）次回：ボイス・コッド正規形、第4正規形、第5正規形（BCNF、4NF、5NFと表記）を扱う。

第4回課題（1/3）XAMPPでテーブル間のリレーション設定

演習の概要
前回の演習で作成したテーブルの構造を修正する

1/3 テーブルのフィールドに外部キー制約を設定

2/3 テーブルにレコードを追加
外部キーの値は「外部キーの参照先のテーブルのレコード」から選んで入力できるようになる

例）

studentsテーブルに「s_id: 1 , s_name: こばし」

groupsテーブルに「g_id:3, g_name: データベース」

があって

student_groupテーブルに「s_id: 1, g_id: 3」を記録すると、こばしがデータベースのメンバーになるとする

このとき

student_groupテーブルの 　s_id 　と　g_id　フィールドは外部キーで、その値は

students の s_id とgroupsのg_idフィールドから値を選ぶ

ように外部キー制約で設定する

状況確認：
・前回の続きの演習です。前回と同じパソコンを利用してください。

・student_group  の各レコードの値がgroupsとstudentsのidと対応するか確認する

・対応しないデータがある場合

studentsやgroupsにレコードを追加する

または

student_group の対応しないレコードの値を修正する

リレーションの設定手順：

・テーブル student_group をクリックして選択する

・テーブルの構造タブをクリックして表示する

リレーションビューボタンを押す。

下記の作業例の画像を参考にして　 外部キー制約　を２つ設定する

リレーション設定作業の例）

保存する を押す。

・データベース「mytest3122333」をクリックする
デザイナでテーブル間のリレーションを確認（スクリーンショットを撮りWebclassに提出）

下の例の画像参照

外部キー制約の動作確認

・外部キー制約を設定したテーブルにデータを挿入・削除・修正して動作を確認する

提出方法：

 phpMyAdmin の デザイナー 機能を利用してテーブルのリレーションを確認する。

 → スクリーンショットを第４回課題 問1としてWebClassに提出

スクリーンショットの例）

第4回課題（2/3）テーブルの設計変更と追加

演習用データベースmytest3123888に次の作業を行う。

(1/6) student_group テーブルのフィールドを1つ以上追加

どのようなフィール名と型を設定するかも自分で考えてよい。

追加方法：　構造→　カラムを追加→　実行

例）グループに所属した時点で決まる項目を考える
入会年月日
役職
担当

(2/6) テーブルを新規作成（1つ以上追加する）
他のテーブル（例えばstudents）から外部キーとして参照する主キー(例えばid)をフィールドに加えること。

テーブル名とフィールド名（2個以上）と型は自分で企画して考える。

追加方法：　データベース名「mytest3123888」をクリックー>テーブルを作成

(3/6) テーブルにレコードを挿入

新規作成しテーブル　→　挿入

必要な件数追加しておく。idフィールドの値はテーブルのA_I設定で自動的に連番で入寮されるのに任せる

例　英語でも日本語でもOK
id 1 female
id 2 male
id 3 none

(4/6) 元からあるテーブル（例えばstudents）に外部キーを設定

例）

studentsテーブルに外部キーを追加する

追加方法：　構造→　カラムを追加→　実行

追加したカラムの「タイプ（データ型）」は　INT を選ぶ（外部キー制約として他のテーブルの主キーに接続するため）

(5/6) studentsに既にあるレコードの追加したフィールドの値を修正

studentsテーブルの外部キーの値を新規追加したテーブルに存在するidの値に修正する必要がある

studentsの既存のレコードの追加フィールドの値はデフォルト値（０など）なので修正する

(6/6) 追加した外部キーにリレーション（外部キー制約）を設定

設定方法：　studentsテーブル　→　構造　→　リレーション

提出方法

課題（２/３）と同様にデザイナのスクリーンショットを保存

 →第４回課題 問２としてWebClassに提出

デザイナでリレーションを表示した例）

テーブルの正規化

キーワード： 正規化　主キー　外部キー

関係データベース このWikipediaの記事のテーブルの例で住所１、住所２や商品１、商品２、商品３のフィールド名は敢えて正規化前の非正規形で説明してある（と推測）。

関係モデルをもとに設計したデータベース このWikipediaの記事の「データベース例」のテーブルの例で主キーについて確認するとよい。

関係データベース管理システム(RDBMS) 

演習で使用する開発環境の名称はXAMPPで、そのデータベース管理システム（DBMS）の種類は関係データベース（RDBMS）でその名称はMariaDB

関係の正規化

関係データベースのテーブルの設計を適切なものにするための手法。

関係の正規化を以下のステップに分けて考える。各ステップにおける正規化されたテーブルは正規形になる。

第１正規形、第２正規形、第３正規形（今回扱う）

ボイス・コッド正規形、第４正規形、第５正規形（次回扱う）

正規形の定義の要点：

第1正規形 (first normal form; 1NF) 関係がスカラ値のみを持つ

第2正規形 (second normal form; 2NF) 関係が第1正規形で　かつ　すべての非キー属性がすべての候補キーに対して完全従属する

第3正規形 (third normal form; 3NF) 関係が第2正規形で　かつ　すべての非キー属性が候補キーに推移的従属していない

関係(リレーション)：テーブルのこと

スカラ値：単一の値。繰り返し並べて記録できない値。
キーの値を追記できない値。（表のセルを分割して記録するようなことができない）。

主キー：テーブルのレコードを一意に決定（特定）することができるキー。
テーブルで主キーの値は重複しない。なぜなら、重複したレコードの特定が不能になり主キーである条件に反する。

複合キー：複数のキーを組み合わせると主キーとして利用できることがある。これを複合キーという。

非キー属性：主キー以外のキーのこと。

候補キー: 主キーとして機能するキーが複数ある場合、それらを候補キーと呼ぶ。主キーも候補キーである。

完全従属：あるキーが候補キーの一部のキーだけで決まらない場合、完全従属するという。複合キーの一部のキーでも決まる場合は部分従属になる。
候補キー（主キー）が複合キーでないなら完全従属している。複合キーの場合は完全従属について確認する必要がある。

推移的従属： キー A から キー B が定まることをA→B と書く。

Aの他に非キー属性BとCがあるとき、Aは主キーなので当然ながらA→B であり A→C である。

この時、主キーA以外の非キー属性 B からB→C となるとき、

Cは、A→B さらに B→C と Bを介して間接的にAから決まる。これを推移的従属と呼ぶ。

主キーAに推移的に従属従属するキーCを、A→Cの関係としてテーブルに記載しておく必要はないことになる。

正規形について別の観点から確認

・複合キーが無いテーブルは第2正規形

・候補キー（主キー）以外のキーが候補キーだけで決まるテーブルは第3正規形

なぜ正規化が必要なのか？

非正規形のテーブルで不都合が生じる例：

レコードの更新の際に内容に矛盾が発生

新規レコード登録不能

レコードの削除の際に残しておきたい情報が消滅

など。

導出項目について： 

テーブルのキーAからキーBを導出できるならキーBを記録しておく必要は無くなる。

例１）生年月日 → 星座

例２）定価ｘ個数 →小計 →合計

導出項目はテーブルの設計上は記録は不要。ただし処理の効率化のために、あらかじめ導出した値を記録する場合もある（非正規化）。

第4回課題（3/3）

Webclassの問３に回答する。採点は次回講義日以降に行う。

以下に示す「受験者名簿」のテーブルについてデータベースのテーブルを「再設計」してください。

※再設計　企画することではない。元のテーブルの構造を修正する。

この課題ではテーブルを正規化して複数のテーブルに分解することになる。

「受験者名簿」では〇の付いたフィールドが主キー（と候補キー）である。このテーブルは第1正規形であるが第2正規形ではない（補足参照）

各フィールドについて考慮する条件：

受験料は入試区分で決まる

学費は学科で決まる

郵便番号から都道府県と市町村が決まる。番地など住所の詳細までは決まらない

年齢や干支は生年月日から決まる。テーブルにする必要があるか検討する。

同じ出願者番号で複数の入試区分と学科に出願できる

例）

出願者番号　1122　として　住所　氏名　出身校　などを登録した

出願者番号　1122　が　総合型選抜　受験番号　3344　を受験

出願者番号　1122　が　一般入試前期3科目型　受験番号　5566　で受験

テーブル「受験者名簿」

〇受験番号

判定結果

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

受験料

学費

出身校

氏名

生年月日

受験者の年齢

受験者の十二支の干支

郵便番号

都道府県

市町村

番地など

回答の注意点

導出項目は削除（特定の法則で決定できるのでテーブルに記載しないことにする）

第3正規形にする（必要に応じて別の表を追加してフィールドを整理する）

の2点を考慮して設計変更する。

設計を変更したテーブルと追加するテーブルを全て示すこと。

追加するテーブルの名称は適当に自分で考えてよい。

回答書式：

テーブル名に続けてフィールド名をカンマか「、」で区切って（）で括る

主キーのフィールド名の先頭に ○ （WIndows：まる　と入力して変換）を付ける

外部キーには先頭に※（WIndows：こめ　と入力して変換）を付ける

主キーかつ外部キーのフィールド名には　○※フィールド名　と両方の記号を付ける

例）

天候記録テーブル（〇ID、日時、※地区ID、※天候ID、気温）

天候テーブル（〇天候ID、名称、記号）

地区テーブル（〇地区ID、地区名）

（補足）

非候補キーの「入試区分」から決まるキー「受験料」があるので第2正規形ではない。（完全従属していない）

非候補キーの「学科」から決まるキー「学費」があるので第3正規形ではない。（推移的従属がある）

非候補キーの「生年月日」から決まるキー「受験時の年齢」や「受験者の十二支の干支」がある。（導出項目がある）

非候補キーの「郵便番号」から決まるキー「都道府県、市町村」があるので第3正規形ではない。（推移的従属がある）

予習・復習

Paizaラーニングの「新・SQL入門編１：SQLを始めよう」

Paizaラーニングの「新・SQL入門編２：SELECT文を理解しよう」

https://paiza.jp/works/sql/new-primer

まで取り組んでおくとよいでしょう。