テーブル設計と論理設計

今回は学習中のテーブル設計と論理設計についてまとめてみます。

覚えておくべきワード

データベース設計：データベースは3層のスキーマ（枠組）からなる。
・外部スキーマ（ユーザーから見たDB、ビュー）
・概念スキーマ（開発者から見たDB、テーブル）
・内部スキーマ（DBMSから見たDB、データの物理的配置）

それぞれのスキーマは
「外部スキーマと概念スキーマ」
「概念スキーマと内部スキーマ」
の間で関連性を持ち、「外部スキーマと内部スキーマ」は直接関わることは無い。
外部スキーマと概念スキーマ：テーブルからセレクト文でデータを取り出す
概念スキーマと内部スキーマ：データファイルから人間にとっても分かりやすいテーブルという形で扱えるよう設定。

今回は主に概念スキーマについてのまとめになります。

主キー（プライマリーキー)
その値を指定すれば必ず1行のレコードを特定できるような列（の組み合わせ）。
テーブルにおいて必ず1つ存在する必要があり、かつ1つしか存在しない。（2つ以上のカラムで1つの主キーを構成することもある）

外部キー
2つのテーブル間の列同士の関連性を設定するもの

論理設計と物理設計
・論理設計（概念スキーマの設計）。要はテーブル設計。
・物理設計（内部スキーマの設計）。

正規形について

実在する事象をテーブル化していく時、正規化を行いながら矛盾のないテーブルを作成していく。
正規化は第1〜第5まであるが第3までやればほぼ第5正規化を行った形と同じものになるので第3まで解説、演習してみます。
第1正規形
「1つのフィールドには1つの値しか含まない」仮に1つのフィールドに複数の値があると主キーが特定の値を一意に定めることができなくなる。
第2正規形
「部分関数従属が解消されていて完全関数従属のみのテーブルとなっている状態」
といっても何言ってんだ？って感じだけど例えば
という場合、主キーに会社IDと社員IDを置いているが会社名は会社IDのみの従属関係となるので部分関数従属となる。
これを解消する為に会社IDと会社名を別テーブルに切り出して完全関数従属のみのテーブルにすることが第2正規形である。
第3正規形
「推移的関数従属が解消されている状態」
これは何かというと複数の主キーから導き出されるカラムがあり、それに更に従属するカラムが存在している状態を解消しようということ。
第2正規形で提示したテーブルでも推移的関数従属が発生している。
会社IDと社員IDという2つの主キーから部署IDが導き出されるがそれに従属する形で部署名が同じテーブルにある。これが「推移的従属がある」ということ。
これを解消する為に部署ID、部署名は別テーブルに切り出してやる必要がある。

論理設計について

論理設計は以下の検討方法で行う。
[1]その事象が何をするためのシステムか？を検討する。これにより中心となるエンティティ（テーブル）が予測できる
[2]5W1Hに当てはめる「いつ、誰に、何を、どこで、なぜ、どうやって」
[3]インターフェース上の項目をカラムとして抽出、エンティティに当てはめる
[4]主キーと外部キーの設定（主キーとして適切なカラムがあるか探してみる。なければ自分で追加する）
[5]テーブルにおとしこみ正規形の条件が満たされているか確認する

これを踏まえて私が前職で使っていた「有償工事注文書」を用いて実際に論理設計をやってみる

[1]何をするためのシステムか？
契約した工事の内容を管理するシステム

[2]5W1Hに当てはめる。
今回は「いつ（日付）」「誰が（注文者）」「誰が2（担当者）」「何を注文（注文内容）」が大事だと思われるので、それぞれ「契約日テーブル」「注文者テーブル」「注文内容テーブル」「担当者テーブル」という形で中心となるエンティティ（テーブルを抽出）

[3]インターフェース上の項目をカラムとして抽出、エンティティに当てはめる。
今回のインターフェースからは
・日付
・注文者住所
・氏名
・電話
・担当者
・工事場所
・注文内容
・工期
・注文金額
が抽出される。それぞれエンティティに当てはめると

というようになる。

[4]主キーと外部キーの設定
まずは主キーに適切なカラムがあるか確認するが、どのカラムも一意でなく多様な値が入りそうなので適さない。その為、自分で主キーを追加する
主キーを追加したものが下記

次に外部キーを設定するが、その目的はテーブル間の関係を持たせることなので、どのテーブル間でリレーション（関係）を持たせるべきか検討してみると
・注文内容にはいつ契約したか？の情報を含める必要がある
・注文内容にはどのお客様が契約したか？の情報を含める必要がある
・注文内容にはそれがどの担当者が担当するのか？の情報を含める必要がある

これらを踏まえると以下となる