SQL　データベース設計

SQLの勉強中ですが、自分の脳内整理や備忘録用に作成。
今回は、データベースの設計のアレコレ。

<h2>データベースの設計</h2>

<h4>データベース設計の流れ</h4>

　　<b>①要件聴取：</b>システムを作るにあたって、どのような要件があるのかを聞き出す。

　　<b>②概念設計：</b><b>管理すべき情報はどのようなものかを整理する。</b>DBやシステムのことは考えず、要件に登場する情報だけを把握する。また、情報間で関連がある場合、どのような関係なのかも併せて整理する。

　　<b>③論理設計：</b>概念設計の各情報について、<b>RDBを使う前提で構造を整理して詳しく具体化していく。</b>「どのようなテーブルを作り、各テーブルにどのような列を作るか」まで明らかになれば十分。型や制約などの付随的な部分は<b>考えない。</b>

　　<b>④物理設計：</b>特定のDBMSを使う前提で、論理設計で明らかになった各テーブルについて、その内容を詳しく具体化していく。<b>全てのテーブルの全ての列について、型、インデックス、制約、デフォルト値など、テーブル作成に必要な全ての要素を確定させる。</b>

---
<h3>●概念設計ですること</h3>
・要件を実現するため、抽象的な概念としてどのような「情報の塊」を管理しなければならないかを明確にする。
<b>この「情報の塊」のことをエンティティといいます。</b>エンティティには通常、複数の<b>属性(Attribute)</b>を持っている。また、エンティティ同士にどのような関係があるかも明らかにする。

・概念設計の成果は、<b>ER図</b>という図にまとめる。ER図を使うことで、エンティティ、属性、リレーションシップを俯瞰で見ることができる。

<h4>エンティティの導き方</h4>

<b>STEP1：候補となる用語を洗い出す</b>
　　・要件の中から<b>「名詞」</b>を抜き出す。
　　・要件が実現されている姿を仮定し、そこに登場する<b>「人」「物」「事実」「行為」</b>などの用語を書き出す。

<b>STEP2：不要な用語を捨てる</b>
　　・他の用語の具体例でしかないものを捨てる。
　　・計算または集計をすれば算出可能な値は捨てる。

<b>STEP3：関連がありそうなものをまとめる</b>
　　・同じ用語に関連するものを捨てる。

<b>STEP4：エンティティ名と属性名にわける</b>
　　・STEP３でまとめたグループの中で、「～の～」という日本語が成り立つ場合、前者がエンティティ名、後者が属性名になる。

---
<h3>●論理設計ですること</h3>
・概念設計で作成したER図のエンティティを、<b>リレーショナルデータモデルで取り扱いやすい形のテーブルに変形する。</b>


<h4>論理設計の流れ</h4>
　　<b>①多対多の分解：ER図の多対多の関係性がある場合、RDBでは「多対多」の関係を美味く扱うことができないため、2つのエンティティの対応を格納した中間テーブルを追加する。
　　②キーの整理：主キーにおいて、3つの特性(非NULL性、一意性、不変性)を満たしているかをチェックする。
　　③正規化：矛盾したデータを格納できないよう、テーブルを複数に分割していく。(※詳細は以下)</b>

---
<h3>●正規化の段階</h3>
正規化は1段階～5段階まで存在する。ただし、通常のシステム開発が目的である場合、<b>業務で求められるのは、第３正規形</b>まで理解していれば問題ない。

<b>◎第１正規形への変形</b>
<b>第１正規形の達成条件：</b>テーブルの全ての行の全ての列に、１つずつ値が入っている姿。「繰り返しの列」「セルの結合」が現れてはいけない。

<b>STEP①：繰り返しの列の部分を別の表に切り出す</b>
　　・元テーブルから<b>「繰り返しの列」</b>の部分を別テーブルとして切り出し、切り出したテーブルに名前をつける。

<b>STEP②：切り出したテーブルの仮の主キーを決定する</b>
　　・切り出したテーブルの<b>主キーとなる列</b>を決定する。

<b>STEP③：主キー列をコピーして複合主キーを構成する</b>
　　・元テーブルの主キー列を、切り出したテーブルにも加え、STEP２の仮の主キーとあわせて<b>複合主キー</b>を構成する。

<b>【用語】
○関数従属性</b>とは・・・「ある列Aの値が決まれば、自ずと列Bの値も決まる」という関係。このとき、<b>「列Bは列Aに関係従属している」</b>という。

<b>○テーブルにおける理想的な関数従属</b>
　　・全ての非キー列は、主キーにきれいに関数従属しているべきである。

---
<b>◎第２正規形への変形
第２正規形の達成条件：</b>複合主キーを持つテーブルにて、非キー列は、複合主キーの全体に関数従属するべきである。よって、<b>「複合主キーの一部の列に対してのみ関数従属する列」が含まれてはならない。</b>

<b>【用語】
○部分関数従属</b>とは・・・全ての非キー列が「複合主キーの全体」に関数従属することを【きれいな関数従属】と表現するが、複合主キーの一部の列にしか関数従属していないことを【きたない関数従属】と表現する。このきたない関数従属の状態を<b>部分関数従属</b>という。

<b>STEP①：複合主キーの一部に関数従属する列(部分関数従属する列)を切り出す</b>
　　・複合主キーの一部の列に関数従属している列を別テーブルとして切り出して、名前をつける。

<b>STEP②：部分関数従属先だった列をコピーする</b>
　　・切り出した列が関数従属していた列を、STEP１で作ったテーブルにコピーし、主キーとする。

---

<b>◎第３正規形への変形</b>
<b>第３正規形の達成条件：</b>テーブルの非キー列は、主キーに直接関数従属するべきである。よって、<b>「主キーに関数従属する列にさらに関数従属する列」は存在してはならない。</b>

<b>【用語】
○推移関数従属</b>とは・・・「主キーに対する間接的な関数従属」をきたない関数従属とみなし、それを排除する。間接的に関数従属することを<b>推移関数従属</b>という。

<b>STEP①：間接的に主キーに関数従属する列を切り出す</b>
　　・間接的に主キーに関数従属している列を、別テーブルとして切り出して名前をつける。

<b>STEP②：直接関数従属先だった列をコピーする</b>
　　・切り出して列が関数従属していた列を、切り出したテーブルにコピーして主キーとする。

---
<h3>正規化を覚えるコツ</h3>
・正規化は、それぞれの形態に変化させる毎に「排除」を行っている。その排除内容を覚えるとラクになる。

<b>○３つの正規化で排除しようとするもの</b>
　　・第１正規形への変形：<b>繰り返し列</b>
　　・第２正規形への変形：<b>複合主キーの一部への関数従属(部分関数従属)</b>
　　・第３正規形への変形：<b>間接的な関数従属(推移関数従属)</b>

---

<h3>●物理設計ですること</h3>
<b>
物理設計の流れ
　　①最終的なテーブル名、列名(物理名)の決定：最終的にDB内にテーブルが作られる際のテーブル名や列名(物理名)を決める。
　　②列の型を決定する：各列に対して指定する型を決定する。
　　③制約、デフォルト値を決定する：各テーブル、各列に対して設定する制約やデフォルト値を決定する。
　　④インデックスの決定：どの列にインデックスを設定するかを決める。総合的に考慮して決定する。
　　⑤その他：利便性を考慮しVIEWを作成したり、性能のためあえて正規化を崩したりする。</b>

　　※上記のような過程を経て決定した物理設計の成果は、<b>ER図とは別に「テーブル設計仕様書」などの名称で呼ばれる別文書にとりまとめられる。</b>

　　※⑤その他で、正規化を崩す手段(非正規化にする)は、データの整合性が崩れやすくなるため、<b>あくまで最後の手段</b>であると考えておく。