【開発SE】DB設計の『第５正規形』をダブルチーズバーガーを使って簡単に説明する

記事の概要

実務ではあまり聞かないと思われるが、高度試験やDB設計の学習に出てくる『第５正規形』について、イメージがしやすいように身近なものを使って説明する。

尚、この記事は続編となっているため、まずは前回の記事を読んで頂くことをオススメする。
【開発SE】DB設計の『第４正規形』をダブルチーズバーガーを使って簡単に説明する

本題に入る前に

前回は、ぜひ事前にダブルチーズバーガーを買って食べて欲しいと言った気がするが、今回はマクドナルドで夜マックの倍ダブルチーズバーガーを食べて欲しいかもしれないｗ

また、前回同様に定義の詳細は省略し簡易的な説明とする。
とにかくイメージを掴みやすくするのが目的である。

第５正規形について

データベーススペシャリストの勉強やDB設計の書籍を読むと、正規化の話で非正規形～第５正規形までのキーワードに遭遇する。

筆者は正直、パッと見た感じでは『第４正規形』と『第５正規形』は何が違うのか分からなかった。
よく考えれば理解できる内容ではあるが、ややこしい理論に抵抗がある人はなかなか覚えられないことは無いだろうか？

前回の対応（第４正規形）の何が問題なのか？

おさらい（パティとチーズの組合せ）

前回、ダブルチーズバーガーに使われている『パティとチーズの組合せ』を『第４正規形』で表現した。
その際のテーブル定義は下記の通りである。

◆パティテーブル

メニュー（主キー）	使用パティ（主キー）
ダブルチーズバーガー	上段アメリカ産ビーフ
ダブルチーズバーガー	下段アメリカ産ビーフ

◆チーズテーブル

メニュー（主キー）	使用チーズ（主キー）
ダブルチーズバーガー	上段チェダー
ダブルチーズバーガー	下段チェダー

これらを組み合わせた結果が下記である。

メニュー（主キー）	使用パティ（主キー）	使用チーズ（主キー）
ダブルチーズバーガー	上段アメリカ産ビーフ	上段チェダー
ダブルチーズバーガー	上段アメリカ産ビーフ	下段チェダー
ダブルチーズバーガー	下段アメリカ産ビーフ	上段チェダー
ダブルチーズバーガー	下段アメリカ産ビーフ	下段チェダー

結合イメージ

使用したSQL文

-- ◆第４正規形の定義をする
-- メニューで使用するパティのテーブルを作成
CREATE TABLE パティテーブル
(
    メニュー VARCHAR(50) ,
    使用パティ VARCHAR(50),
    PRIMARY KEY (メニュー ,使用パティ) -- 全項目の組合せが主キー
)
;
-- メニューで使用するチーズのテーブルを作成
CREATE TABLE チーズテーブル
(
    メニュー VARCHAR(50) ,
    使用チーズ VARCHAR(50),
    PRIMARY KEY (メニュー ,使用チーズ) -- 全項目の組合せが主キー
)
;
-- メニューで使用するパティを登録
INSERT INTO パティテーブル
VALUES ('ダブルチーズバーガー','上段アメリカ産ビーフ')
      ,('ダブルチーズバーガー','下段アメリカ産ビーフ')
;
-- メニューで使用するチーズを登録
INSERT INTO チーズテーブル
VALUES ('ダブルチーズバーガー','上段チェダー')
      ,('ダブルチーズバーガー','下段チェダー')
;
-- ◆メニューに必要な具材の組合せを取得（第４正規形）
-- メニューで使用するパティとチーズの組合せを確認
SELECT P.メニュー,P.使用パティ,C.使用チーズ
FROM パティテーブル P
    INNER JOIN チーズテーブル C           -- 内部結合
              ON P.メニュー = C.メニュー  -- メニューで結合
ORDER BY P.使用パティ,C.使用チーズ        -- 表示順も元通りにする
;

問題点１：具材の位置

各テーブルの主キーの一部に共通する『メニュー』で結合することにより、ダブルチーズバーガーに使用されるパティとチーズが、上段と下段にそれぞれ必要なことは表現できた。
しかし、考え方次第では下記のルールが存在するかもしれない。

• 上段の具材は上段同士でなければならない。
• 下段の具材は下段同士でなければならない。

つまり、上段の具材と下段の具材を同じレコードで持つことを禁止するというルールである。
それを表現した結果が下記である。

メニュー（主キー）	使用パティ（主キー）	使用チーズ（主キー）
ダブルチーズバーガー	上段アメリカ産ビーフ	上段チェダー
ダブルチーズバーガー	下段アメリカ産ビーフ	下段チェダー

問題点２：実は『倍ダブチ』になっている？

これは『見た目』というか『捉え方』次第ではあるのだが、いかがだろうか？
つまり、第４正規形のテーブルから表現した結果、１種類のメニュー『ダブルチーズバーガー』に対して『パティ』と『チーズ』が上段下段で各々２レコードずつになっている点だ。

※画像：マクドナルドHPより引用

ただし、正確には『倍ダブチ』はパティは２倍だがチーズが１セットのみなので、厳密には異なる。
なので、第４正規形から表現した結果は『倍ダブチ＋チーズ２枚トッピング』となるｗ

問題点を解消する（第５正規形）

２つの問題点は、テーブル定義の追加とSQLの修正で解消できる。
つまり、『第４正規形』→『第５正規形』に正規化することになる。

自明でない結合従属性

第４正規形では、テーブルはいずれも『自明な多値従属性』になり、下記の通り表現されている。

• メニュー→→使用パティ
• メニュー→→使用チーズ

しかし、これらを組み合わせたダブルチーズバーガーは『自明でない結合従属性』となる。

• *{{メニュー,使用パティ},{メニュー,使用チーズ},{使用パティ,使用チーズ}}

これだけでは理解するのは難しいと思うので、ひとまず今回はイメージだけを覚えて頂きたい。
理屈はイメージ出来た後に改めて勉強した方が、頭にスッと入りやすくなるだろう。

テーブル定義の追加

第４正規形に無いテーブル、つまり上記の {使用パティ,使用チーズ} を追加すれば良い。
上段同士、下段同士を組み合わせた連関エンティティを作る。
◆配置テーブル

使用パティ（主キー）	使用チーズ（主キー）
上段アメリカ産ビーフ	上段チェダー
下段アメリカ産ビーフ	下段チェダー

SQL文

-- ◆第５正規形にするための定義を追加する
-- パティとチーズの配置テーブルを作成
CREATE TABLE 配置テーブル
(
    使用パティ VARCHAR(50),
    使用チーズ VARCHAR(50),
    PRIMARY KEY (使用パティ ,使用チーズ) -- 全項目の組合せが主キー
)
;
-- 配置テーブルを登録
INSERT INTO 配置テーブル
VALUES ('上段アメリカ産ビーフ','上段チェダー')
      ,('下段アメリカ産ビーフ','下段チェダー')
;

いざ、結合！

さて、新たなテーブルが出来たがこれで問題は解消できるのか？結合して検証する。
第４正規形から表現する際に使用したSQLに、新たなテーブルとの結合条件を追加する。
SQL文

-- ◆本当にあるべき組合せの状態を取得（第５正規形）
-- メニューで使用するパティとチーズの組合せを確認（第４正規形の場合と同じ）
SELECT P.メニュー,P.使用パティ,C.使用チーズ
FROM パティテーブル P
    INNER JOIN チーズテーブル C           -- 内部結合
              ON P.メニュー = C.メニュー  -- メニューで結合
-- 上記（第４正規形からの復元）に更に下記を追加する
    INNER JOIN 配置テーブル H                      -- 内部結合
              ON P.使用パティ = H.使用パティ        -- 使用パティで結合
              AND  C.使用チーズ = H.使用チーズ      -- 使用チーズで結合
-- ソートは変更なし
ORDER BY P.使用パティ,C.使用チーズ         -- 表示順も元通りにする
;

実行結果が下記である。

メニュー（主キー）	使用パティ（主キー）	使用チーズ（主キー）
ダブルチーズバーガー	上段アメリカ産ビーフ	上段チェダー
ダブルチーズバーガー	下段アメリカ産ビーフ	下段チェダー

第４正規形で表現した結果よりも、更に本物に近づいた印象である。

※画像：マクドナルドHPより引用

ダイアグラムだと、ちょっと変かもしれないが、こんな感じだろう。

尚、配置テーブルを追加したので『自明な結合従属性』が成立している。

• *{メニュー,使用パティ}
• *{メニュー,使用チーズ}
• *{使用パティ,使用チーズ}

問題は解消できたか？

問題点１：具材の位置

配置テーブルにより『上段は上段』『下段は下段』の組合せになるよう登録することで制御が出来ている。

問題点２：実は『倍ダブチ』になっている？

実行結果とマクドナルドからの画像を一緒に見て頂ければ、なんとなく通常のダブルチーズバーガーのイメージに近づいたことが分かるだろう。

ただし、そもそもメニュー列に『ダブルチーズバーガー』が２件あると突っ込まれると、何も言えないのだがｗ
突き詰めれば、いくらでもテーブル定義を変える必要が出てくるので、あくまで今回のテーマのために用意されたものであることをご理解頂き、ご容赦頂きたい。

最後に

前回の第４正規形を解説した時は、この第５正規形は解説するつもりは無かった。
ただ、マクドナルドのメニューを見て『倍ダブルチーズバーガー』を発見した時「しまった！！」と思ってしまったｗ
第４正規形からの実行結果のイメージが『倍ダブチ』にそっくりだと思ったからだ。

それはともかく、こうしてアウトプットしながら知識を整理したり、見直したりする機会は大事であることをまた実感した。

前回も書いたが、私はダブルチーズバーガーよりフィレオフィッシュが好きであることを、しつこく付け加えておくｗ

ここまで読んで頂き、多謝！ m(_ _)m