はじめに
今週末はデータベーススペシャリスト試験ですね。
DBの正規化について、毎回忘れて勉強し直すのでまとめておこうと思います。
試験でも実務でも第３正規化まで出来れば大概OKですね。
下記順序で進めるのがポイントです。
- 非正規形
- 第1正規形にする
- 候補キーを探す ←ここがミソ！
- 主キーを探す ←ここもミソ！
- 第2正規形にする
- 第3正規形にする
Qiitaなので記事テーブルを例に考えてみましょう。
記事テーブル（記事ID, 記事タイトル, カテゴリID, カテゴリ名, カテゴリ内での表示順, 作成者ユーザID, コメント番号, コメント本文, コメント投稿者ユーザID）
参考:2017年度春期試験の午後Ⅰ
電子会議投稿（電子会議番号, 議題, 分野番号, 分野名, 表示順, 作成者ユーザID, 投稿番号, 投稿本文, 投稿者ユーザID）
非正規形
人間の目でパッと見てわかりやすいのが非正規形です。
例では2つの記事がありますが、行の中に複数の行（ここではコメント）がある状態です。
これを『繰り返し項目が存在する』と言います。この状態が非正規形です。
テーブルを作れない状態と思っていいでしょう。
| 記事ID | 記事タイトル | カテゴリID | カテゴリ名 | カテゴリ内での表示順 | 作成者ユーザID | コメント番号 | コメント本文 | コメント投稿者ユーザID |
|---|---|---|---|---|---|---|---|---|
| 11111 | 候補キーと第3正規化までを理解する | cate1 | データベース | 99 | naoqoo2 | 1 | いい記事です | xxxx |
| 2 | とてもわかりやすかったです | yyyy | ||||||
| 3 | 大変勉強になりました | zzzz | ||||||
| 22222 | 主キーの種類 | cate1 | データベース | 98 | naoqoo2 | 1 | ステキな記事です | xxxx |
第1正規形にする
データが重複しているイケてないテーブルが第1正規形です。
非正規形から繰り返し項目をなくす（1行にしていく）と第1正規形になります。
例だと2→4レコードになりました。
これならテーブルが作れますね！でもイケてない。
| 記事ID | 記事タイトル | カテゴリID | カテゴリ名 | カテゴリ内での表示順 | 作成者ユーザID | コメント番号 | コメント本文 | コメント投稿者ユーザID |
|---|---|---|---|---|---|---|---|---|
| 11111 | 候補キーと第3正規化までを理解する | cate1 | データベース | 99 | naoqoo2 | 1 | いい記事です | xxxx |
| 11111 | 候補キーと第3正規化までを理解する | cate1 | データベース | 99 | naoqoo2 | 2 | とてもわかりやすかったです | yyyy |
| 11111 | 候補キーと第3正規化までを理解する | cate1 | データベース | 99 | naoqoo2 | 3 | 大変勉強になりました | zzzz |
| 22222 | 主キーの種類 | cate1 | データベース | 98 | naoqoo2 | 1 | ステキな記事です | xxxx |
候補キーを探す
次に主キーを決めたいのですが、主キーの前に候補キーを探す必要があります。
候補キーとは主キーの候補になるキーのことです。そのままですね。
テーブルの中で１つのレコード（ユニーク）を特定できるカラム。またはカラムの組み合わせが候補キーです。
- 『記事ID』と『コメント番号』
でレコードを特定できますね。
また、記事IDの代わりにカテゴリIDと表示順を使用してもレコードを特定できそうです。
- 『カテゴリID』と『カテゴリ内での表示順』と『コメント番号』
この2つが候補キーとなります。
主キーを探す
開発者が候補キーからただ一つ選択したキーが主キーです。
NULLは許されないという制約があります。
今回は2つの候補キーからどちらかを選ぶことになります。
- 記事ID, コメント番号
- カテゴリID, カテゴリ内での表示順, コメント番号
記事を投稿すれば必ず記事IDは入りそうですが、
カテゴリや表示順は決まっていない（NULL）可能性がありますよね？
そのためここでの主キーは
- 記事ID, コメント番号
となります。
第2正規形にする
主キーを構成する一部のカラムによって決まるもの（部分関数従属）を別テーブルにすると第2正規形となります。
例での部分関数従属は下記のようになります。
記事IDから決まるもの
- 記事タイトル
- カテゴリID
- カテゴリ名
- カテゴリ内での表示順
- 作成者ユーザID
コメント番号から決まるもの
- なし（コメント番号は記事に対して連番なのでコメント本文などを特定できない）
よって下記のようになります。
| 記事ID | 記事タイトル | カテゴリID | カテゴリ名 | カテゴリ内での表示順 | 作成者ユーザID |
|---|---|---|---|---|---|
| 11111 | 候補キーと第3正規化までを理解する | cate1 | データベース | 99 | naoqoo2 |
| 22222 | 主キーの種類 | cate1 | データベース | 98 | naoqoo2 |
| 記事ID | コメント番号 | コメント本文 | コメント投稿者ユーザID |
|---|---|---|---|
| 11111 | 1 | いい記事です | xxxx |
| 11111 | 2 | とてもわかりやすかったです | yyyy |
| 11111 | 3 | 大変勉強になりました | zzzz |
| 22222 | 1 | ステキな記事です | xxxx |
第3正規形にする
最後に第3正規形です。
主キー以外のカラム（非キー）によって決まるもの（推移的関数従属）を別テーブルにすると第3正規形となります。
例ではカテゴリ名がカテゴリIDによって決まるので推移的関数従属ですね。
記事ID→カテゴリID→カテゴリ名
テーブルは下記のようになります。
| 記事ID | 記事タイトル | カテゴリID | カテゴリ内での表示順 | 作成者ユーザID |
|---|---|---|---|---|
| 11111 | 候補キーと第3正規化までを理解する | cate1 | 99 | naoqoo2 |
| 22222 | 主キーの種類 | cate1 | 98 | naoqoo2 |
| カテゴリID | カテゴリ名 |
|---|---|
| cate1 | データベース |
| 記事ID | コメント番号 | コメント本文 | コメント投稿者ユーザID |
|---|---|---|---|
| 11111 | 1 | いい記事です | xxxx |
| 11111 | 2 | とてもわかりやすかったです | yyyy |
| 11111 | 3 | 大変勉強になりました | zzzz |
| 22222 | 1 | ステキな記事です | xxxx |
これで第３正規化までできました！
おまけ：主キーの種類
ナチュラルキー(自然キー, natural key)
主キーが、ユーザーにとって意味のあるカラム。またはカラムの組み合わせ。
サロゲートキー(代替キー、surrogate key)
主キーが、連番などのユニーク性を確保するためだけに用意した（ユーザーにとって意味のない）カラム。
そのため複合キー（カラムの組み合わせ。）となることはない。（よほど変な設計しない限り）
※ちなみに代理キーと呼ばれることもあるが、本来代理キーは候補キーのうち主キーとして選ばれなかったキーのことを言うらしい。まぁサロゲートキーって呼ぼう。
本来の代理キー