テーブル設計入門!!第3正規化の練習|データベーススペシャリスト過去問(2021年(令和3年度)午後I_問1_設問2)を例に

はじめに

テーブル設計の基本練習として、

テーブルをどのように第3正規化にしていくのか??についてまとめました。

 

データベーススペシャリスト過去問(2021年(令和2年)午後I_問1_設問2)をお題に解説していきます!!

 

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

自身のない方はこちらを参考にしてみてください。

 

データベースの基礎【正規化(非正規形を第3正規形にする)】

 

データベーススペシャリストの過去問(2021年(令和2年)午後I 問1 設問2)の問題を理解する

まずは問題を理解していきましょう!!

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

 

設問の内容と、関係する問題文をピックアップして見ていきます。

 

第3正規形にしたいテーブルの関係スキーマ

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

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

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

(パッと見商品コードっぽいのが2つあるのが違和感ですね、、、)

 

設問

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

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

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

まず部分関数従属性、推移的関数従属性がそれぞれあるか??を整理する必要があります。

設問1ではまずそのステップを回答する感じです。

 

もし、関数従属性について自身がない方は、

先に以下を参照ください

データベースの基礎【関数従属性】

 

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

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

候補キーにはなれるけど主キーになれない代表としては、候補キーの項目にnullが入る可能性があるケースです。

(なので問題文に候補キーっぽいけど値が入らないこともあるよって書かれている項目があるはず・・・)

 

もし、候補キーについて自身がない方は、

先に以下を参照ください

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

 

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

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

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

 

第3正規形にするテーブルについて説明している箇所の抜粋

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

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

ちょっと長いですね、、、

データベーススペシャリストに合格するにはこれをテンポ良く効率良く読み進めていく必要があります。

 

第3正規化の練習が目的ならこんなの読まなくてもいいのですが・・・・

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

  • 加盟企業は加盟企業コードで一意に特定できる
  • 加盟企業商品は加盟企業コードと加盟企業商品コードで一意に特定できる
  • 加盟企業商品名とJANコードは使い回すので一意に特定できるキーではない
  • 横断分析用商品情報は全加盟企業で一意の商品コードとして登録できる情報(加盟企業間の商品を名寄せしたもののイメージ)
  • 横断分析用商品名は一意に特定できるキーではない
  • 横断分析商品コードは一旦空で登録されて遅れて値が設定されることがある

 

MEMO

加盟店企業間の商品を名寄せ??

加盟店企業Aの商品〇〇

加盟店企業Bの商品××

は名前が違うだけで分析するときには同じ商品■■として扱いたい。

みたいなイメージでいいと思います!!

 

第1正規形のテーブルを第3正規形にする!!そもそも今、第○正規化かを見極める方法

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

その前に現状加盟企業商品テーブルは第1正規形なのか??第2正規形なのか??それ以外なのか??について見ていきます。

 

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

加盟企業商品を一意に特定できればいいので・・・

 

{加盟企業コード、加盟企業商品コード}、{加盟企業コード、横断分析商品コード}(1の解答)

 

が挙げられます。

横断分析商品コードは、企業を横断した際には重複しますが、加盟企業との組み合わせで考えると一意に特定できます。

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

 

さらに

 

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

部分関数従属性がなく、推移的関数従属性がある場合は現状第2正規系

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

 

候補キーの一部の項目である加盟企業コードが決まれば、加盟企業名が決まるので部分関数従属性有りということで

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

 

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

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

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

横断分析商品名が決まる。という感じになります。(1の解答)

 

第1正規形から第3正規形へ!!テーブルを追跡できるように分解する

ここからは加盟企業商品テーブルを第3正規形に分解していきます。

分解してもキー同士を紐付けて今の第1正規形の状態に戻せないといけない点に注意しましょう!!

 

加盟企業(加盟企業コード、加盟企業名)

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

横断分析商品情報(横断分析商品コード、横断分析商品名)

(3の回答)

こんな感じです!!

 

(すみません、、本当なら第2正規化した状態を見せたかったですが、、、この例でだと推移的関数従属性だけ残せない。。。)

 

分解して第3正規形にする際は、

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

実施するのがいいと思います。

 

まとめ

候補キーを見つけて、

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

推移的関数従属性を取り除いて第3正規形になるところが確認できました。

 

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

(テーブルを結合する際の性能劣化を考慮したり、登録した当時の断面を残したりするためにあえて非正規化したりすることもありますが)

 

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

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

 

2022/03/21追記

現場で必要なデータベース周りの知識に関するまとめ記事を作ったので

是非そちらも参照ください!!

現場で必要なデータベースの基礎知識まとめ|正規化、ACID特性、SQLチューニングなど

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください