経緯
初心者である私が２週間データベースを学習し、学んだ知識をより確実に自分に定着させるために、アウトプット（本記事）するもの。
データベースとは
データベースとは、大量の情報を保存し、コンピュータから効率よくアクセスできるように加工したデータのあつまり。
由来は第二次世界大戦後の米軍が、情報のアクセスを効率化するために点在していた資料をひとつの基地にすべて集めたことが起源。
⇒ これを情報（Data）の基地（Base）と呼んだ。
また、データベースを管理するシステムをDBMS(data base management system)と呼ぶ。
リレーショナルデータベース（RDB）
数あるデータベースの中で、最も使用されているものがリレーショナルデータベース（relational database）である。RDBとはデータベース同士の関係を設定し関連付けたもの。ちなみに、relationalは、「関係性のある」や「相互関係に基づく」と訳されていた。
また、RDBはデータを二次元表の形式で管理している。これを、テーブルと呼ぶ。
テーブルは、共通点をもったレコードの寄せ集め。よって、テーブル名は複数形、複数名詞でよく表現される。
| 顧客番号 | 顧客名 | 性別 |
|---|---|---|
| 001 | 佐藤 | 男 |
| 002 | 中村 | 女 |
データベースの構造
データベースとは、3層のスキーマから成り立っている。設計する上で重要な概念。
- 外部スキーマ
- 概念スキーマ
- 内部スキーマ
外部スキーマとは
ユーザー側からみた、データベースのことであり、見たい情報が表示される。これを、ビューと呼ぶ。
概念スキーマとは
開発者側からみた、データベースのことであり、データの要素やデータ同士の関係を定義したもの。いわば、テーブルである。
内部スキーマとは
DBMS側からみた、データベースのことであり、データを具体的に格納するかを定義したもの。
なぜ中間（概念）スキーマが必要か
⇒データの独立性を確保するため。もし、概念スキーマが適切に設計されていれば、
外部スキーマを変更しても、内部スキーマを変更する必要がない。（論理的データ独立性と呼ぶ。）逆も同様。（物理的的データ独立性と呼ぶ。）
概念スキーマは、他のスキーマの変更を吸収する緩衝材の役割をもっている。言い換えると、隠ぺいしていると考えることもできる。
データベース設計とは
システム開発のひとつのプロセスで、データの保持に関する設計のこと。なお、以降はRDBを対象に記載する。
論理設計
概念スキーマの設計のこと。いわば、データを管理するためのテーブルの設計。
論理設計のステップ
以下の４つのステップからなる。
①エンティティの抽出
②エンティティの定義
③正規化
④ER図の作成
エンティティの抽出
どんなエンティティ（テーブル）が必要か？を明確にする。
エンティティ（Entity）は日本語で「実体」という意味だが、注文・予約というような概念としてしか存在しないものもある。
例えば、「動画の予約管理システム」に必要なエンティティは、顧客・動画・予約・などが挙げられる。
エンティティの定義
各エンティティ（テーブル）ごとにどんな属性が必要かを明確にする。
例えば、予約というエンティティであれば、予約日、予約者、予約した動画などがあげられる。
また、特に重要なのが、「キー（key）」という列を決定すること。
| id | name | tel | address |
|---|---|---|---|
| 1 | 佐藤 | 000-0000-0000 | 東京都 |
| 2 | 中村 | 000-0000-0000 | 大阪府 |
主キー(primary key）
その値を指定すれば必ず一行のレコードを特定できるような列。テーブルにおいて必ず一つ存在する必要がある。
外部キー(foreign key)
2つのテーブル間の列同士の関連性を設定するもの。関係性を設定することにより、他方にないデータは存在することができない。これを、参照整合性制約と呼ぶ。
正規化
テーブルを分割することでデータの冗長性（ムダ・重複）をなくす。データを扱いやすくするための設計。
なぜ必要か。
⇒データの重複（冗長性）があると、無駄なデータ領域と面倒な更新処理を発生させてしまう。
また、正規化を行う上で、「関数従属性」が重要な概念となっている。関数従属性とは、ある属性Xの値を決定すると、他の属性Yの値が一意に決まる関係のことをいう。
例：｛X｝ → ｛Y｝
正規化にあてはめると、テーブルの全ての列が一意の値（関数従属性）になるよう編集していくことである。
例： ｛主キー｝ → ｛カラム｝
第1正規形
「1つのカラムには1つの値しか含まない」という原則が守られた形。
なぜ必要か
⇒複数の値を許すと、主キーが各列の値を一意に定めることができなくなる。
第2正規形
部分関数従属が解消され、完全関数従属のみのテーブルになっている形。
部分関数従属とは、複数列からなる主キーの一部の列にのみ従属する列がある状態。
完全関数従属とは、複数列からなる主キーの全てに対して従属する列がある状態。
なぜ必要か
⇒複数の値を許すと、主キーが各列の値を一意に定めることができなくなる。
第3正規形
推移的関数従属が解消されている形。推移的関数従属とは、2段階の関数従属がある状態。
なぜ必要か
⇒推移的な関係がないカラム値は、テーブルに表示することができない。優れた論理設計は、テーブルを見ただけでどういうビジネスモデルなのか判別することができる。
ボイコット正規形
第三正規化の次にあるのが、ボイスコッド正規化。
主キー以外のカラムが全て主キーに完全従属であり、それ以外の従属関係があれば表を分離する必要がある。いわば、キーから主キーへの関数従属を排除する。
ER図の作成
実際のシステムは、テーブル数が何百もの数となる。そのため、視覚的にテーブル間でどのような関係（リレーション）があるのか表したER図というもの。
また、リレーションの種類は大きく分けて3種類ある。
1対1
あるテーブルのカラム値が他のテーブルの１つのカラム値に対応する場合のことをいう。
1対1の関係のテーブル同士であれば一つのテーブルで表現できるが、機密度合いが高い情報に関しては、あえてテーブルを分けて、アクセス権を絞るなりして管理の仕方を変える方法もある。
1対多
あるテーブルのカラム値が他のテーブルの複数のカラム値に対応する場合のことをいう。大半がこれに該当する。
多対多
複数のテーブルのレコードが互いに複数のレコードに対応する場合のことをいう。
この場合は、中間テーブルを作成するのが便利。NULLを防ぐことができる、テーブルの役割が明瞭となる等のメリットがある。
論理設計まとめ
以上のステップを非常にわかりやすくまとまった概念図が下記の通りである。
物理設計
内部スキーマの設計のこと。論理設計で決まったデータを格納するための物理的な格納方法を決める。
CREATE
データベースやテーブルを作成する。
CREATE TABLE テーブル名
(カラム1 データ型 制約,
カラム2 データ型 制約,
：
テーブルの制約1, テーブルの制約2, ...);
データ型の種類と特徴は下記の通り。
INTEGER型
整数を指定。少数は不可能。
CHAR型
固定長文字列を指定。CHARACTER（文字数）のように文字数を指定する。
文字数が指定した数を超過するとエラーが表示され、余った場合は空白で表示される。
VARCHAR型
可変長文字列を指定。VARCHARACTER（文字数）のように文字数を指定する。
文字数が余った場合も空欄は表示されない。
VARCHAR型の欠点は、データの更新によって容量が増えた場合、ディスクに収まらなくなってしまうことがあり、一つのレコードを異なるディスクに割り振ることになるので、検索時のパフォーマンスが低下してしまうというものがある。逆に言えばその程度なので、迷ったらVARCHAR型を採用してしまって良い。
主キー制約
主キー制約はテーブルに絶対に一つ必要であり、一つしか設定できない。
主キーが複数のカラムからなる場合は、以下のように書く。
PRIMARY KEY (カラム1, カラム2, ...)
外部キー制約
外部キーを設定することのできる制約、以下のように書く。
FOREIGN KEY (外部キーはどれか)
REFERENCES 参照テーブル (参照カラム));
DROP
テーブルを削除する。一度削除した場合は復旧できないため注意が必要。
DROP TABLE テーブル名;
ALTER
定義済みテーブルを変更する。
テーブル中の列を削除する
ALTER TABLE テーブル名DROP COLUMN カラム名;
テーブル名を変更する
ALTER TABLE 変更前テーブル名RENAME TO 変更テーブル名;
最後に
以上がデータベース設計における大まかな一連の流れである。
データベースの存在感はあらゆる分野でますます増してきていると思う。データベースの奥深い世界を探求したい。
なお、分析関数についての記事も作成しているため、見て頂けたら幸甚です。