スッキリわかるSQL入門 第12章テーブルの設計 学習備忘録

## はじめに

以下はスッキリわかるSQL入門第12章(テーブルの設計)の学習備忘録です。

引用されている個所は書籍の内容をそのまま書き、引用されていない箇所は私が調べて補足した内容を書いてあります。
`SQL`のタグが付いているコードブロックに関しては、書籍の引用と私が調べて補足したものを織り交ぜた内容になっています。

**RDMSについてはMySQLを想定しています。**

# 第12章 テーブル設計

## 12.1 システムとデータベース
<br>

**『要件』とは**
あるシステム、ソフトウェア、製品、サービス、プロジェクトなどが持つべき機能や性能、品質、制約条件、規格、規程、法律などの要素を定義したもの。つまり、あるシステムやプロジェクトに必要な条件や要素の集合体を要件と呼ぶ。
<br>

**データベースを用いたシステムを開発するには**
> SQLやDBMSの機能の知識だけではシステムは開発できない。要件をしっかりと理解し、その要件をデータベース設計に適切に落とし込むための方法論を活用しなければならない。
<br>

以下はデータベース設計の流れの概要を指す。

**1\. データベース構築のINPUTとOUTPUT**
> * INPUT: 要件の一覧表(お客様から聴取したもの)
> * OUTPUT: 一連のDDL表(実行すれば必要十分なテーブルが生成されるもの)
※OUTPUTに関しては、設計フェーズの物理設計で作成を行う。
<br>

**2\. データベース設計作業(概念設計、論理設計、物理設計)**
<br>

**概念設計の概要**
> 管理すべき情報を整理する。データベースやシステムに関する事は考えず、要件に登場する情報(エンティティ)をザックリと把握。
また、情報面で関連がある場合、どのような関係があるかも合わせて整理する。

**論理設計の概要**
> 概念設計で明らかになった各情報について、RDBを使う前提で構造を整理し詳しく具体化していく。
論理設計では『どのようなテーブルを作り、それぞれのテーブルにどのような列を作るか』までを明らかにすれば十分。型や制約など付随的な部分については考えない。

**物理設計の概要**
> 論理設計で明らかになった各テーブルついて、その内容を詳しく具体化していく。すべてのテーブルのすべての列について型、インデックス、制約、デフォルト値など、テーブル作成に必要なすべての要素を確定させる。
また、CREATE TABLE文などを含む一連のDDL文を作成する。

## 12.3 概念設計
<br>

**概念設計で出てくる用語**
> * **エンティティ**: 要件を実現するための抽象的な概念としての『情報の塊』のこと。テーブルの原石。
> * **属性**: エンティティの属性のこと。テーブルの列のようなもの。
> * **関連**: エンティティ同士の関連のこと。テーブル同士の関連における『リレーションシップ』のようなもの。
※書籍のように形があるものだけではなく、事実とか行為みたいな形のないようなものでもエンティティになる。
<br>

**ER図(ERD: entity-relation diagram)**
> 概念設計の成果はER図に纏めることが一般的。ER図を使うことで、エンティティ、属性、リレーションシップを俯瞰してみることできる。
また、ER図には2つの記述形式がある。1つは**IE(Information Engineering)**、2つめは**IDEF1X**。

**IE図**
![スクリーンショット (1184).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1220815/8a6de583-ed82-379c-16ae-d50682428780.png)

**カーディナリティ(多重度)**
> カーディナリティ(多重度)とはエンティティ同士の数量的な関係のこと。
![スクリーンショット (1185).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1220815/fa15c7ea-c5b3-4e61-20dc-c55a49a0d417.png)

※ER図はITSakuraさんの記事から、カーディナリティはdocomo businessさんの記事から拝借しました。

[ITSakuraさん ER図のサンプル](https://www.ntt.com/business/sdpf/knowledge/archive_50.html)
[docomo businessさん カーディナリティ](https://www.ntt.com/business/sdpf/knowledge/archive_50.html)
<br>

**エンティティを導き出す方法**
> ステップ1\. 候補となる用語を洗い出す。
ステップ2\. 不要な用語を捨てる。
ステップ3\. 関連がありそうなものをまとめる。
ステップ4\. エンティティ名と属性名に分ける。<br>
※ER図ではエンティティの中にエンティティを作ること(2重構造)はできないので外部に抽出し、外部に取りだしたエンティティと元のエンティティと関連付けれるように取り出したエンティティに元の**主キー**を属性として追加しておく。この属性を**外部キー**と呼ぶ。

## 12.4 論理設計
<br>

**論理設計の目的**
> 概念上のエンティティを『関係性のある複数の2次元表』として情報を取り扱うリレーショナルデータモデルで取り扱いやすい形のテーブルに変形する。
リレーショナルデータモデルでは『2重構造のテーブル』を格納することはできない。


※論理設計の流れとしては、多対多の分解⇒キーの整理⇒テーブルの正規化の流れで行う。
<br>

**1\. 多対多の分解**
> リレーショナルデータベースでは、『多対多』の関係を上手く扱うことはできない。そこで、2つのエンティティの対応を格納した中間テーブルを追加する事によって、『多対多』を『1対多』の関係に変換する必要がある。

※多対多をリレーショナルデータベースが上手く扱うことができない理由を説明した記事に関して、エスパーラボさんの解説記事を貼っておきます。とてもわかりやすかったです。
[エスパーラボさん リレーション考察 N:Nは使用するべきか](https://esp-labo.com/nnrelation/)
<br>

**2\. キーの整理**
> 主キーを持たないエンティティには、管理をしやすくするために人工的な主キー(人口キー)を追加する。
それ以外に、不適切な主キー(非NUL性、UNIQUE、不変性を満たさない)を持つエンティティがないか確認していく。

> **主キーが備えるべき3つの特性**
1\. **非NULL性**: 必ず何らかの値を持っている。NULLではない。
2\. **一貫性(UNIQUE)**: 他と重複しない。
3\. **不変性**: 一度決定されたら値が変化することはない(主キーは一貫して同じ1行を指し示す)
<br>

**3\.テーブルの正規化** 
> 正規化とは、矛盾したデータを格納できないようテーブルを複数に分割すること。
テーブル定義(項目定義)を整理していくこと。

> **テーブルを分解しない場合の懸念**
> * 内容に重複が多く、わかりにくい。
> * データ更新時には複数の関連個所を正確に更新しなければならず、更新を忘れたり間違えたりすると、データの整合性が損なわれる。

## 12.5 正規化の手順
<br>

**正規化の流れ**
> 手元にあるエンティティ構造(テーブル構造)を、非正規形から第3正規形まで順次変形していく。
<br>

**1\. 第1正規形の目指す姿と達成条件**
> テーブルのすべての行のすべての列に1つずつ値が入っているべきである。
よって、『繰り返しの列』や『セルの結合』が現れてはならない。
以下に、第1正規化の流れを記述する。<br>
> ステップ1\. **繰り返しの列の部分を別の表に切り出す**
> ステップ2\. **切り出したテーブルの仮の主キーを決める**
> ステップ3\. **主キー列をコピーして複合主キーを構成する**

:::note info
**関数従属性とは**
『ある列Aの値が決まれば、自ずと列Bの値も決まる』という関係。この時『列Bは列Aに関数従属している』と言う。
:::

:::note info
**テーブルにおける理想的な関数従属**
すべての非キー列は、主キーにきれいに関数従属しているべきである。
:::

**2\. 第2正規形の目指す姿と達成条件**
> 複合主キーを持つテーブルの場合、非キー列は、複合主キーの全体に関数従属するべきである(**完全関数従属**)。よって、『複合主キーの一部の列に対してのみ関数従属する列』が含まれてはならない(**部分関数従属**)。<br>
> ステップ1\. **複合主キーの一部に関数従属する列を切り出し、テーブルを作成**
> ステップ2\. **部分関数従属先だった列を、切り出したテーブルの主キー列としする**
<br>

**3\. 第3正規形の目指す姿と達成条件**
> テーブルの非キー列は、主キーに直接、関数従属するべきである。
よって、『主キーに関数従属する列にさらに関数従属する列』は存在してはならない(**推移関数従属**)<br>
> ステップ1\. **間接的に関数従属している列を切り出し、テーブルを作成**
> ステップ2\. **直接関数従属先だった列をコピーして、切り出したテーブルの主キー列とする**

:::note info
**3つの正規化で排除しようとするもの**
1\. 第1正規化への変形: **繰り返し列**
2\. 第2正規化への変形: **複合主キーの一部への関数従属(部分関数従属)**
3\. 第3正規化への変形: **間接的な関数従属(推移関数従属)**
:::

## 12.6 物理設計
<br>

**1\. 最終的なテーブル名、列名(物理名)を決定する**
> テーブル名や列名を作成する場合、アルファベットで名前を付けるのが一般的。予約語は使えない。
また、物理設計でのテーブル名や列名のことを**物理名**と呼び、論理設計までの段階で利用してきた名前を**論理名**と呼ぶ。

**2\. 列の型を決定する**
> DBMS製品によって、型の種類や数値の精度は異なるため、マニュアルを参照しながら最適な型を選ぶ。

**3\. 制約、デフォルト値を決定する**
> 各テーブルや各列に対して、設定する制約を決定する。

**4\. インデックスを決定する**
> どの列に対してインデックスを設定するのかも、物理設計で決める事柄。