SQLの勉強中ですが、自分の脳内整理や備忘録用に作成。
今回は、データベースの設計のアレコレ。
データベースの設計
データベース設計の流れ
①要件聴取：システムを作るにあたって、どのような要件があるのかを聞き出す。
②概念設計：管理すべき情報はどのようなものかを整理する。DBやシステムのことは考えず、要件に登場する情報だけを把握する。また、情報間で関連がある場合、どのような関係なのかも併せて整理する。
③論理設計：概念設計の各情報について、RDBを使う前提で構造を整理して詳しく具体化していく。「どのようなテーブルを作り、各テーブルにどのような列を作るか」まで明らかになれば十分。型や制約などの付随的な部分は考えない。
④物理設計：特定のDBMSを使う前提で、論理設計で明らかになった各テーブルについて、その内容を詳しく具体化していく。全てのテーブルの全ての列について、型、インデックス、制約、デフォルト値など、テーブル作成に必要な全ての要素を確定させる。
●概念設計ですること
・要件を実現するため、抽象的な概念としてどのような「情報の塊」を管理しなければならないかを明確にする。
この「情報の塊」のことをエンティティといいます。エンティティには通常、複数の属性(Attribute)を持っている。また、エンティティ同士にどのような関係があるかも明らかにする。
・概念設計の成果は、ER図という図にまとめる。ER図を使うことで、エンティティ、属性、リレーションシップを俯瞰で見ることができる。
エンティティの導き方
STEP1：候補となる用語を洗い出す
・要件の中から「名詞」を抜き出す。
・要件が実現されている姿を仮定し、そこに登場する「人」「物」「事実」「行為」などの用語を書き出す。
STEP2：不要な用語を捨てる
・他の用語の具体例でしかないものを捨てる。
・計算または集計をすれば算出可能な値は捨てる。
STEP3：関連がありそうなものをまとめる
・同じ用語に関連するものを捨てる。
STEP4：エンティティ名と属性名にわける
・STEP３でまとめたグループの中で、「～の～」という日本語が成り立つ場合、前者がエンティティ名、後者が属性名になる。
●論理設計ですること
・概念設計で作成したER図のエンティティを、リレーショナルデータモデルで取り扱いやすい形のテーブルに変形する。
論理設計の流れ
①多対多の分解：ER図の多対多の関係性がある場合、RDBでは「多対多」の関係を美味く扱うことができないため、2つのエンティティの対応を格納した中間テーブルを追加する。
②キーの整理：主キーにおいて、3つの特性(非NULL性、一意性、不変性)を満たしているかをチェックする。
③正規化：矛盾したデータを格納できないよう、テーブルを複数に分割していく。(※詳細は以下)
●正規化の段階
正規化は1段階～5段階まで存在する。ただし、通常のシステム開発が目的である場合、業務で求められるのは、第３正規形まで理解していれば問題ない。
◎第１正規形への変形
第１正規形の達成条件：テーブルの全ての行の全ての列に、１つずつ値が入っている姿。「繰り返しの列」「セルの結合」が現れてはいけない。
STEP①：繰り返しの列の部分を別の表に切り出す
・元テーブルから「繰り返しの列」の部分を別テーブルとして切り出し、切り出したテーブルに名前をつける。
STEP②：切り出したテーブルの仮の主キーを決定する
・切り出したテーブルの主キーとなる列を決定する。
STEP③：主キー列をコピーして複合主キーを構成する
・元テーブルの主キー列を、切り出したテーブルにも加え、STEP２の仮の主キーとあわせて複合主キーを構成する。
【用語】
○関数従属性とは・・・「ある列Aの値が決まれば、自ずと列Bの値も決まる」という関係。このとき、「列Bは列Aに関係従属している」という。
○テーブルにおける理想的な関数従属
・全ての非キー列は、主キーにきれいに関数従属しているべきである。
◎第２正規形への変形
第２正規形の達成条件：複合主キーを持つテーブルにて、非キー列は、複合主キーの全体に関数従属するべきである。よって、「複合主キーの一部の列に対してのみ関数従属する列」が含まれてはならない。
【用語】
○部分関数従属とは・・・全ての非キー列が「複合主キーの全体」に関数従属することを【きれいな関数従属】と表現するが、複合主キーの一部の列にしか関数従属していないことを【きたない関数従属】と表現する。このきたない関数従属の状態を部分関数従属という。
STEP①：複合主キーの一部に関数従属する列(部分関数従属する列)を切り出す
・複合主キーの一部の列に関数従属している列を別テーブルとして切り出して、名前をつける。
STEP②：部分関数従属先だった列をコピーする
・切り出した列が関数従属していた列を、STEP１で作ったテーブルにコピーし、主キーとする。
◎第３正規形への変形
第３正規形の達成条件：テーブルの非キー列は、主キーに直接関数従属するべきである。よって、「主キーに関数従属する列にさらに関数従属する列」は存在してはならない。
【用語】
○推移関数従属とは・・・「主キーに対する間接的な関数従属」をきたない関数従属とみなし、それを排除する。間接的に関数従属することを推移関数従属という。
STEP①：間接的に主キーに関数従属する列を切り出す
・間接的に主キーに関数従属している列を、別テーブルとして切り出して名前をつける。
STEP②：直接関数従属先だった列をコピーする
・切り出して列が関数従属していた列を、切り出したテーブルにコピーして主キーとする。
正規化を覚えるコツ
・正規化は、それぞれの形態に変化させる毎に「排除」を行っている。その排除内容を覚えるとラクになる。
○３つの正規化で排除しようとするもの
・第１正規形への変形：繰り返し列
・第２正規形への変形：複合主キーの一部への関数従属(部分関数従属)
・第３正規形への変形：間接的な関数従属(推移関数従属)
●物理設計ですること
物理設計の流れ
①最終的なテーブル名、列名(物理名)の決定：最終的にDB内にテーブルが作られる際のテーブル名や列名(物理名)を決める。
②列の型を決定する：各列に対して指定する型を決定する。
③制約、デフォルト値を決定する：各テーブル、各列に対して設定する制約やデフォルト値を決定する。
④インデックスの決定：どの列にインデックスを設定するかを決める。総合的に考慮して決定する。
⑤その他：利便性を考慮しVIEWを作成したり、性能のためあえて正規化を崩したりする。
※上記のような過程を経て決定した物理設計の成果は、ER図とは別に「テーブル設計仕様書」などの名称で呼ばれる別文書にとりまとめられる。
※⑤その他で、正規化を崩す手段(非正規化にする)は、データの整合性が崩れやすくなるため、あくまで最後の手段であると考えておく。