データベースのおさらい
ここでは、SQLではなく、「データベース」の概念をおさらいする。理由は「SQLを書けても、リレーショナルモデルは知らないではいけない…」と思うきっかけがあったので、おさらいすることにした。下記を参考にしている。
ちなみに、この本はすごくわかりやすいので、絶対買ったほうがいい。真面目にデータベースの勉強するなら。
リレーショナルモデル
リレーショナルモデルは、データをどのように表現するかの考え方ということ。Key-Value Storeとかもデータをどのように表現するかの1つの考え方。そもそもリレーショナルモデルの「リレーション」とはなにか。
「リレーション」とは、SQLで対応させて考えると「テーブル」のこと。リレーションは「テーブル同士の関係性」ではなく、「テーブル」自体のことをリレーションと考える。ER図はテーブル同士の関係性を可視化しているものであって、リレーショナルモデルというわけではない。
| リレーショナルモデル | SQL |
|---|---|
| リレーション | Table |
| タプル | Row |
| 属性 | Column |
Wikipediaのリレーションal_modelの図のようなイメージ。HeadingとBodyで構成され、「Headingはn個の属性の集合」で「Bodyは属性とvalueの集合=タプルの集合」。タプルが1行の集合であると考えると、リレーショナルモデルが行志向の考え方であることがわかる。
Source | relational_model_model
リレーショナルモデルの演算
リレーショナルモデルでは、データをリレーションとして考えており、集合がベースならば演算ができるはず。それがクエリと呼ばれるもので主な演算は下記の通り。
- Restrict(制限)：リレーションのうち、特定の条件にあうタプルを返す。
- Projection(射影)：特定の属性だけを含んだリレーションを返す。
- Extend(拡張)：Projectionとは反対に属性を増やす、属性を拡張する。
- Rename(属性名変更)：属性の名前を変更すること。
- Union(和)：2つのリレーションにおける重複しないタプルをすべて含むリレーションを返す。
- Intersect(積)：2つのリレーションにおけるタプルの共通しているタブルをすべて含むリレーションを返す。
- difference(差)：2つのリレーションのうち、片方のリレーションにのみ含むタプルのリレーションを返す。
- Product(直積)：2つのリレーションのタプルを組み合わせたリレーションを返す。
- Join(結合)：2つのリレーションの共通属性をもつリレーションをその共通の属性の値が同じもの組み合わせたリレーションを返す。
これらの演算の原理を考えて作られたのがクエリであって、クエリを使うことで情報を抽出できるようにしているのが、リレーショナルデータベース(RDS)。
正規化
リレーショナルデータベースの正規化についてまとめておく。実務では第3正規化までされていれば問題ないとか言われるけど、せっかくなので、第5正規化までおさらいする。達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへの例をお借りする。キーは{会社コード、社員ID}の2つ。
| 会社コード__ | 会社名 | 社員ID__ | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C001 | A商事 | 000A | 加藤 | 40 | D01 | 開発 |
| C001 | A商事 | 000B | 藤本 | 32 | D02 | 人事 |
| C001 | A商事 | 001F | 三島 | 50 | D03 | 営業 |
| C002 | B化学 | 000A | 斎藤 | 47 | D03 | 営業 |
| C002 | B化学 | 009F | 田島 | 25 | D01 | 開発 |
| C002 | B化学 | 010A | 渋谷 | 33 | D04 | 総務 |
第1正規化
第1正規化は、単純に「1つのセルに1つの値が格納されていること」。つまり、Excelでいうセル結合的な状態ではないこと。この状態で何も問題はない。
| 会社コード__ | 会社名 | 社員ID__ | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C001 | A商事 | 000A | 加藤 | 40 | D01 | 開発 |
| C001 | A商事 | 000B | 藤本 | 32 | D02 | 人事 |
| C001 | A商事 | 001F | 三島 | 50 | D03 | 営業 |
| C002 | B化学 | 000A | 斎藤 | 47 | D03 | 営業 |
| C002 | B化学 | 009F | 田島 | 25 | D01 | 開発 |
| C002 | B化学 | 010A | 渋谷 | 33 | D04 | 総務 |
第2正規化
第2正規化は部分従属性がないことを目標とする正規化。部分従属性というのは、主キーのうちいずれかのキーで関数従属性があることをいうので、今回であれば、{会社コード、社員ID}なので、{会社コード}または{社員ID} で関数従属性がないこと。
| 会社コード__ | 会社名 | 社員ID__ | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C001 | A商事 | 000A | 加藤 | 40 | D01 | 開発 |
| C001 | A商事 | 000B | 藤本 | 32 | D02 | 人事 |
| C001 | A商事 | 001F | 三島 | 50 | D03 | 営業 |
| C002 | B化学 | 000A | 斎藤 | 47 | D03 | 営業 |
| C002 | B化学 | 009F | 田島 | 25 | D01 | 開発 |
| C002 | B化学 | 010A | 渋谷 | 33 | D04 | 総務 |
現状はこの状態であるが、{会社コード}→{会社名}で関数従属性がある。つまり、C001はA商事、C002はB化学と決まるので、関数従属性がある。なので、この部分のテーブルを「社員テーブル」「会社テーブル」に分離する。
- 社員テーブル
| 会社コード__ | 社員ID__ | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C001 | 000A | 加藤 | 40 | D01 | 開発 |
| C001 | 000B | 藤本 | 32 | D02 | 人事 |
| C001 | 001F | 三島 | 50 | D03 | 営業 |
| C002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C002 | 009F | 田島 | 25 | D01 | 開発 |
| C002 | 010A | 渋谷 | 33 | D04 | 総務 |
- 会社テーブル
| 会社コード__ | 会社名 |
|---|---|
| C001 | A商事 |
| C002 | B化学 |
無損失分解なので、社員テーブルをプライマリテーブルにして、会社テーブルをLEFTJOINすればもとに戻るし、こうしておけば、会社テーブルに様々な会社コードと会社名を登録できる。こうなっていないと、元のテーブルに社員情報をNULLにして、会社を登録しないといけなくなって、管理上良くない。
第3正規化
第3正規化は、推移関数従属性がないことを目標とする。{部署コード}でソートして、{会社コード、社員ID}をconcatしている。このテーブルであれば、{会社コード、社員ID}→{部署コード}→{部署名}というように関数従属が発生している。{会社コード、社員ID}が決まれば、{部署コード}が決まり、{部署コード}が決まれば{部署名}が決まる、という流れである。
- 社員テーブル
| 会社コード&社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|
| C001&000A | 加藤 | 40 | D01 | 開発 |
| C002&009F | 田島 | 25 | D01 | 開発 |
| C001&000B | 藤本 | 32 | D02 | 人事 |
| C001&001F | 三島 | 50 | D03 | 営業 |
| C002&000A | 斎藤 | 47 | D03 | 営業 |
| C002&010A | 渋谷 | 33 | D04 | 総務 |
- 部署テーブル
| 部署コード__ | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 営業 |
| D04 | 総務 |
- 会社テーブル
| 会社コード__ | 会社名 |
|---|---|
| C001 | A商事 |
| C002 | B化学 |
先程と同じではあるが、このように管理しておけば、部署テーブルに社員が属していない部署を登録しておくことができるので、管理上都合が良い。
第3.5正規化~ボイスコッド正規化~
ボイスコッド正規化というのは、非キーからの関数従属性がないことを目標としている。下記のようなテーブルを考える。
| 社員ID__ | チームコード__ | チーム補佐 |
|---|---|---|
| 000A | 001 | 123W |
| 000B | 001 | 456Z |
| 000B | 002 | 003O |
| 001F | 001 | 123W |
| 001F | 002 | 003O |
| 003O | 002 | 999Y |
{社員ID、チームコード}→{チーム補佐}で関数従属性があるが、最小のテーブル構成なので、テーブルを分離する必要はない。しかし、実は{チーム補佐}→{チームコード}という逆向きの関数従属性も発生している。非キーからの関数従属性がない状態にするには、これもテーブルを分解する。
分解する際には関数従属性を残したまま、テーブルを分解する。単純に{社員ID、チームコード}、{チーム補佐、チームコード}と分解すると多vs.多の結合になるので、損失分解が発生する。
- 社員ID・チーム補佐テーブル
| 社員ID__ | チーム補佐__ |
|---|---|
| 000A | 123W |
| 000B | 456Z |
| 000B | 003O |
| 001F | 123W |
| 001F | 003O |
| 003O | 999Y |
- チーム補佐・チームコードテーブル
| チーム補佐__ | チームコード |
|---|---|
| 123W | 001 |
| 456Z | 001 |
| 003O | 002 |
| 999Y | 002 |
第4正規化
下記のように2つの列をキーに使うことで、主キーが作られるような場合、部分関数従属性、推移関数従属性などは発生していません…が、多値従属性が発生している。多値従属性とは、キーと集合の対応のことで、1つの値に複数の値が紐づくような場合のこと。
| 社員ID__ | チームコード__ | 製品コード |
|---|---|---|
| 000A | 001 | P1 |
| 000A | 001 | P2 |
| 000B | 001 | P1 |
| 000B | 002 | P1 |
| 001F | 001 | P2 |
| 001F | 002 | P2 |
| 003O | 002 | P2 |
| 003O | 002 | P3 |
分解してみると、「000Bは、001と002」に紐づき、「001Fは、001と002」に紐づきく。なので、{社員ID}→→{チームコード}となる。多値従属性は矢印が2つで表される。{社員ID}→→{製品コード}もそう。なので、これも「社員・チームテーブル」と「社員・製品テーブル」に分割。このような多値従属性は値が集合に紐づくので、その関係性をそのままテーブルしたものが関連エンティティである。
- 社員ID・チームコードテーブル
| 社員ID__ | チームコード__ |
|---|---|
| 000A | 001 |
| 000B | 001 |
| 000B | 002 |
| 001F | 001 |
| 001F | 002 |
| 003O | 002 |
- 社員ID・製品コードテーブル
| 社員ID__ | 製品コード |
|---|---|
| 000A | P1 |
| 000B | P1 |
| 001F | P2 |
| 003O | P2 |
| 003O | P3 |
なんですが、これ・・・うん。書籍(9刷、p112)のテーブルのままなんですが、誤りでは？もとに戻せないので、000Aの社員はもう1行いるのではないだろうか。正誤表にものってないけど・・・。
- 社員ID・製品コードテーブル
| 社員ID__ | 製品コード |
|---|---|
| 000A | P1 |
| 000A | P2 |
| 000B | P1 |
| 001F | P2 |
| 003O | P2 |
| 003O | P3 |
第5正規化
さきほども使ったこのテーブルは{社員ID}→→{チームコード}、{社員ID}→→{製品コード}という多値従属性があったが、{チームコード}→→{製品}という多値従属性も、もしかすると存在する場合にどうすればよいのか。そこを解消するのが第5正規化。
| 社員ID__ | チームコード__ | 製品コード |
|---|---|---|
| 000A | 001 | P1 |
| 000A | 001 | P2 |
| 000B | 001 | P1 |
| 000B | 002 | P1 |
| 001F | 001 | P2 |
| 001F | 002 | P2 |
| 003O | 002 | P2 |
| 003O | 002 | P3 |
これは「チームコード」と「製品コード」の関連エンティティを表すテーブルを追加で作成することで解消。これが、第5正規化で、つまりは関連エンティティをテーブルで作ること。なのだが、戻す時に頭使わず、ER図とか確認せず、紐づくからって、「社員・チームコードテーブル」に「チームコード・製品コードテーブル」を「チームコード」でJOINすると爆死する。
- 社員ID・チームコードテーブル
| 社員ID__ | チームコード__ |
|---|---|
| 000A | 001 |
| 000B | 001 |
| 000B | 002 |
| 001F | 001 |
| 001F | 002 |
| 003O | 002 |
- 社員ID・製品コードテーブル
| 社員ID__ | 製品コード__ |
|---|---|
| 000A | P1 |
| 000A | P2 |
| 000B | P1 |
| 001F | P2 |
| 003O | P2 |
| 003O | P3 |
- チームコード・製品コードテーブル
| チームコード__ | 製品コード__ |
|---|---|
| 001 | P1 |
| 001 | P2 |
| 002 | P1 |
| 002 | P2 |
| 002 | P3 |
第4正規化と第5正規化の違いは、第4正規化は多値従属性があるなら関連エンティティを作成することが目標となるが、第5正規化は多値従属性がある可能性があるなら関連エンティティを作成することが目標となる。難しくいうと、「第5正規形を満たす関係は、その関係が第4正規形であり、さらにその関係に含まれる結合従属性の決定項が候補キーのみである場合、かつその場合だけである」らしい。
ER図
ER図はテーブル同士の関係を管理しているダイアグラム。
関連パターン
ここでは、さきほど作ったテーブルを参考にする。
- 社員テーブル
| 会社コード&社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|
| C001&000A | 加藤 | 40 | D01 | 開発 |
| C002&009F | 田島 | 25 | D01 | 開発 |
| C001&000B | 藤本 | 32 | D02 | 人事 |
| C001&001F | 三島 | 50 | D03 | 営業 |
| C002&000A | 斎藤 | 47 | D03 | 営業 |
| C002&010A | 渋谷 | 33 | D04 | 総務 |
- 部署テーブル
| 部署コード__ | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 営業 |
| D04 | 総務 |
- 会社テーブル
| 会社コード__ | 会社名 |
|---|---|
| C001 | A商事 |
| C002 | B化学 |
テーブルの関係性で考慮することは、「1 vs. 1」「1 vs. N」「N vs. N」のどの関係なのかを把握すること。基本的には「1 vs. 1」は見かけないことが多いので、ここでは扱わない。
「1 vs. N」は、1つのテーブルの値が、他のテーブルの複数の値と紐づく場合のことで、「会社と社員」「部署と社員」のテーブルはこの関係にある。
「N vs. N」は、1つのテーブルの値が複数ひも付き、一方で、もう1つのテーブルの値も複数紐づくような場合のこと。こんなテーブルを考え人の立場からみると、「AはR、Python、Julia」「BはR」「CはPython、Julia」に紐づく。一方で、言語の立場からみると、「RはA、B」「PythonはA、C」「JuliaはA、C」と紐づく。
| ユーザーID | 言語コード | 使用ツール名 |
|---|---|---|
| A | 001 | R |
| A | 002 | Python |
| A | 003 | Julia |
| B | 001 | R |
| C | 002 | Python |
| C | 003 | Julia |
この状態だと何が都合が悪いかというと、未登録の学生や、未登録の言語は追加できない状態だということ。NULLで突っ込むのは良くない。
なので中間の組み合わせを保持したテーブルを作る。つまり、関連実体テーブルを作成する。そうすれば、学生や言語が増えても問題なく管理できる。
- ユーザーテーブル
| ユーザーID |
|---|
| A |
| B |
| C |
- 言語テーブル
| 言語コード | 使用ツール名 |
|---|---|
| 001 | R |
| 002 | Python |
| 003 | Julia |
- 使用言語テーブル
| ユーザーID | 言語コード |
|---|---|
| A | 001 |
| A | 002 |
| A | 003 |
| B | 001 |
| C | 002 |
| C | 003 |
IE表記のER図
先程の社員情報に関するIE表記のER図をここでは作成する。
まずは「会社テーブルと社員テーブル」の関係について。会社テーブルの横線が1つあるが、これはカーディナリティを表す。そこから○と「/|\」の部分は「○」が「0」を表し、「/|\」が「複数」を表すので「0以上の複数」という関連を表現している。つまり、「会社テーブルと社員テーブル」の関係は、「会社コードをキーに、会社テーブルの1つの会社コードが、社員テーブルの0以上の複数に紐づく」ということになる。
次に「部署テーブルと社員テーブル」の関係について。部署テーブルの横線は1つで、そこから横線と鳥の足になるので、「1以上の複数」という関連を表現している。つまり、「部署テーブルと社員テーブル」の関係は、「部署コードをキーに、会社テーブルの1つの部署コードが、社員テーブルの1以上の複数に紐づくので、社員が属さない部署はない」ということになる。
