Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
83
Help us understand the problem. What is going on with this article?
@naoqoo2

【テーブル正規化】候補キーから第3正規化までをマスターする

More than 1 year has passed since last update.

はじめに

今週末はデータベーススペシャリスト試験ですね。
DBの正規化について、毎回忘れて勉強し直すのでまとめておこうと思います。

試験でも実務でも第3正規化まで出来れば大概OKですね。
下記順序で進めるのがポイントです。

  1. 非正規形
  2. 第1正規形にする
  3. 候補キーを探す ←ここがミソ!
  4. 主キーを探す ←ここもミソ!
  5. 第2正規形にする
  6. 第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

候補キーを探す

次に主キーを決めたいのですが、主キーの前に候補キーを探す必要があります。
候補キーとは主キーの候補になるキーのことです。そのままですね。
テーブルの中で1つのレコード(ユニーク)を特定できるカラム。またはカラムの組み合わせが候補キーです。

  • 『記事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

これで第3正規化までできました!

おまけ:主キーの種類

ナチュラルキー(自然キー, natural key)

主キーが、ユーザーにとって意味のあるカラム。またはカラムの組み合わせ。

サロゲートキー(代替キー、surrogate key)

主キーが、連番などのユニーク性を確保するためだけに用意した(ユーザーにとって意味のない)カラム。
そのため複合キー(カラムの組み合わせ。)となることはない。(よほど変な設計しない限り)

※ちなみに代理キーと呼ばれることもあるが、本来代理キーは候補キーのうち主キーとして選ばれなかったキーのことを言うらしい。まぁサロゲートキーって呼ぼう。
本来の代理キー

参考サイト

83
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
naoqoo2
myhm
住宅に関わるすべての人にオドロキとヨロコビを

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
83
Help us understand the problem. What is going on with this article?