データベースの正規化の練習｜データベーススペシャリスト過去問を解きながら練習方法解説

はじめに

データベースの正規化練習をデータベーススペシャリストの過去問で行いました。

過去問を解く過程で、

どのように第３正規化にしていくのかについて

情報の整理や分析のやり方についてまとめています。

この記事を読む前に

そもそも第３正規形とは？？を理解していることが前提となるので、

必要な方はこちらご覧ください。

データベースの正規化の目的とは？？｜非正規形〜第３正規形の正規化のやり方

データベースの正規化練習の題材確認

まずは正規化対象の題材を確認していきましょう。

今回は、こちらを題材にしています。

＞＞データベーススペシャリスト過去問(2021年(令和3年度)午後I

題材の内容で、

ポイントとなる箇所をピックアップして見ていきます。

正規化対象のテーブルの関係スキーマはこんな感じ

加盟企業商品(加盟企業コード,加盟企業商品コード, JAN コード, 加盟企業名
,契約開始日,契約終了日,加盟企業商品名,横断分析用商品コード,横断分析用商品名)

出典：令和３年度　秋季　テータベーススペシャリスト午後I問題

このテーブルを第３正規形にしていきます。

(パッと見た感じ、加盟企業商品コードと横断分析用商品コードの

商品コードっぽいのが２つあるのが違和感、、、)

１つ目の設問はこんな感じ

(1) 関係“加盟企業商品”の候補キーを全て答えよ。また,部分関数従属性,
推移的関数従属性の有無を,答案用紙のあり・なしのいずれかを○で囲んで
示せ。“あり”の場合は,次の表記法に従って,その関数従属性の具体例を一
つ示せ。

出典：令和３年度　秋季　テータベーススペシャリスト午後I問題

第３正規形にしていく前段として、

まず部分関数従属性、推移的関数従属性がそれぞれあるか？？

を整理する必要があります。

この設問ではまず正規化前の前段のステップについての話です。

部分関数従属性、推移的関数従属性については、

以下の記事にまとめているので、

必要な方はご覧ください。

関数従属性と関数従属性の推論則

２つ目の設問はこんな感じ

(2) 関係“加盟企業商品”の候補キーのうち,主キーとして採用できないもの
はどれか答えよ。また,その理由を 45 字以内で具体的に述べよ。

出典：令和３年度　秋季　テータベーススペシャリスト午後I問題

候補キーにはなれるけど主キーになれない代表としては、

候補キーの項目にnullが入る可能性があるケースです。

(一意に特定できるkey情報だけど値が入らないこともあるよって項目があるはず・・・)

候補キーについては以下の記事にまとめているので、

必要な方はご覧ください。

主キー、候補キー、外部キー、スーパーキーなどのいろんなキーについて｜データベースの基礎

３つ目の質問はこんな感じ

(3) 関係“加盟企業商品”は第1 正規形,第2正規形,第3正規形のうち,どこ まで正規化されているか答えよ。
第3正規形でない場合は,第3正規形に分解 し,関係スキーマを示せ。ここで,分解後の関係の関係名には、
本文中の用語を用いること。
なお,主キーを構成する属性の場合は実線の下線を,外部キーを構成する属性の場合は破線の下線を付けること。

出典：令和３年度　秋季　テータベーススペシャリスト午後I問題

この設問でいよいよ加盟店企業商品テーブルを第３正規形にしていきます。

正規化対象のテーブルに関する情報

2. 加盟企業
(1) 加盟企業は,B 社と共通ポイントサービス加盟の契約をした企業であり,加盟
企業コードで識別する。コンビニエンスストア, レストランチェーンなど様々
な業種の企業がある。同じ加盟企業と複数回の契約をすることはない。
(2) 加盟企業は複数の店舗をもつ。店舗は,加盟企業コードと店舗コードで識別
する。
3. 加盟企業商品と横断分析用商品情報
(1) 加盟企業商品
① 加盟企業が販売する商品を,B社から見て加盟企業商品と呼ぶ。
② 加盟企業は,商品をポイントシステムに登録するときに,当該加盟企業の
商品コード(以下,加盟企業商品コードという),商品名(以下,加盟企業商
品名という), JAN コードを登録する。加盟企業商品は,加盟企業コードと加
盟企業商品コードで識別する。
③ 加盟企業商品コードは再利用されないが,加盟企業商品名と JAN コードは
再利用されることがある。また, JAN コードが設定されない商品もある。
(2) 横断分析用商品情報
① 横断分析用商品情報は,複数の加盟企業が同じ商品を扱っている場合に同
一商品であると認識できるようにするものである。横断分析用商品情報には、
横断分析用商品コードと横断分析用商品名を設定し,横断分析用商品コード
で識別する。横断分析用商品名は一意になるとは限らない。
② B社は,加盟企業商品が追加される都度,既に同じ商品の横断分析用商品が
登録済みかどうかを確認し,登録済みと判断すればその横断分析用商品コー
ドを,登録済みでないと判断すれば新たな横断分析用商品コードを加盟企業
商品に設定する。
③ 横断分析用商品コードの設定には,加盟企業商品の登録から数日を要する
場合がある。

出典：令和３年度　秋季　テータベーススペシャリスト午後I問題

長い、、、

ざっくりと説明文書のポイントをまとめるとこんな感じです。

これら分析する際、

「一意に特定」「一旦空で登録」「〇〇毎に」

この辺りのキーワードは重要になります。

現場では、今回の題材のように丁寧に文章でまとめられていることはないでしょうが、

この辺りに注意してビジネス側の人にヒアリングする必要があります。

現状が第○正規形か見極めるには？？

ここから加盟企業商品テーブルを第３正規形に指定きたいのですが、

その前に現状加盟企業商品テーブルは

第１正規形なのか？？第２正規形なのか？？それ以外なのか？？

について見ていきます。

まず、候補キーについて考えます。

加盟企業商品を一意に特定できるキーは・・・

{加盟企業コード、加盟企業商品コード}、

{加盟企業コード、横断分析商品コード}

(1の解答)

が挙げられます。

横断分析商品コードは、

企業を横断した際には重複する可能性がありますが、

加盟企業との組み合わせで考えると一意に特定できます。

ただ、、、

空の状態でデータが登録される可能性があるので主キーにはなれません(2の解答)

さらに

部分関数従属性がある場合は現状第１正規形

部分関数従属性はないが、推移的関数従属性がある場合は第２正規形

なので、部分関数従属性があるか見ていくと・・・

候補キーの一部の項目である加盟企業コードが決まれば、

加盟企業名が決まるので部分関数従属性有りということで

現状は第１正規形です。(1、3の解答)

さらに、

推移的関数従属性があるかついでに見ると・・・

{加盟企業コード、加盟企業商品コード}が決まると、

横断分析商品コードが決まり、横断分析商品コードが決まると、

横断分析商品名が決まる。ので推移的関数従属性もあります。(1の解答)

テーブルの情報が復元できるに分解する｜第１正規形から第３正規形にする過程

現状第１正規形である加盟企業商品テーブルを、

第3正規形に分解していきます。

テーブルを分解してもキー同士を紐付けて、テーブルを結合することで

今の第１正規形の状態に戻せなければなりません。

(情報が欠落するようにテーブルを分解してはダメです)

最終的にはこんな感じに分解できます。

(3の回答)

(本当なら部分関数従属のみ排除した第２正規形の状態を見せたかったですが、、、

この例だと推移的関数従属性だけの形が残せない。。。)

慣れないうちはテーブルを分解する際は、

テーブル同士を外部キーを利用して結合して問題なく元の状態を再現できるか

確認しながら第３正規化するのがいいと思います。

まとめ

第３正規化されていないテーブルを

候補キーを見つけて、

第１正規形の状態から部分関数従属性を取り除いて、

推移的関数従属性を取り除いて第３正規形にしていく流れを具体例で確認しました。

基本的にはテーブル設計はこの第３正規形になるように設計することが多いです。

(テーブルを結合する際の性能劣化を考慮したり、

登録した当時の断面を残したりするためにあえて非正規化したりすることもありますが)

手を動かして第３正規化する流れを実際に体験することが、

個人的には一番理解度を深める方法だと思います。

データベーススペシャリストの過去問である程度感覚をつかめたら、

次のステップとして、

情報をどう集めると正しく分析できて良い設計ができるのか？？

ということを考え、

必要な情報をビジネスサイドから漏れなく集めるヒアリングをする

訓練もしてみるといいと思います。

今回の課題のように丁寧に事前に情報がまとめられていて

そこからテーブル設計する現場はないと思うので、

ヒアリングのスキルは重要です。

おまけ

僕が９年間現場でSE・PGとして働く中で、

重要だと考えるデータベース周りの知識をまとめた記事を作りました。

是非そちらもご覧ください。

初めてデータベースを触る方に向けて〜新人プログラマー時代の自分に伝えたいこと〜