データベーススペシャリスト 2021年 午後1 問01

解答の導き方

1. (a),(b)（支払の店舗識別子）
   • 本文に「店舗は、加盟企業コードと店舗コードで識別する。」とあります。店舗の識別子がこの2属性の組であるため、支払がどの店舗で行われたかを記録するには同じ2属性を支払に持たせる必要があります。
   • また本文に「1回の支払はレシート番号で識別する。」とあるので、支払の主キーはレシート番号であり、加盟企業コード・店舗コードは店舗を参照する外部キーとなります。
   • 多重度は「加盟企業は複数の店舗をもつ」かつ「1回の支払は1店舗で行われる」ので、店舗:支払 = 1:N（支払側から見て多）です。したがって支払に外部キーを置くのは正規化（第3正規形）を損なわない設計です。
   • よって (a),(b) = 加盟企業コード（外部キー）, 店舗コード（外部キー）。
2. (c)（支払方法明細の金額）
   • 本文に「支払方法ごとの付与ポイントは、各支払方法での支払金額に、ポイント付与率を乗じて計算する。」とあります。支払方法ごとの付与ポイントを計算・記録するために、その支払方法で支払われた金額が明細に要ります。
   • よって (c) = 支払金額。
3. (d)（購入商品明細の個数）
   • 本文に「商品の購入金額 (購入数×商品単価) にポイント付与率を乗じて計算する。」とあるため、購入商品明細には購入数が必要です。商品単価は既にスキーマにあるので、購入金額は購入数×商品単価から求められます。
   • よって (d) = 購入数。
4. (e)（支払方法とポイント設定の対応）
   • 本文に「ポイント設定はポイント設定コードで識別し、ポイント付与率、適用期間をもつ。ポイントを付与する支払方法にポイント設定を対応付ける。」と明記されています。つまり支払方法はどのポイント設定を使うかを参照する必要があります。
   • よって (e) = ポイント設定コード（外部キー、支払方法からポイント設定を参照）。
5. (f)（ポイント設定の付与率）
   • 本文に「ポイント設定は…ポイント付与率、適用期間をもつ。」とあるため、ポイント設定テーブルにポイント付与率を置きます。
   • よって (f) = ポイント付与率。
6. (g)（クーポン設定の配布上限）
   • 本文に「クーポンには、配布上限数と配布期間を設定している。」とあり、クーポン設定に配布上限数の列が必要です。
   • よって (g) = 配布上限数。
7. (h),(i)（クーポン配布：配布リストの主キー）
   • 本文に「B社は、条件に合う会員を抽出し、クーポン配布リストとして登録する。」かつ「同じ会員に同じクーポンを2回発行することはない。」とあるため、クーポン配布は「クーポンコード」と「会員コード」の組合せが一意（主キー）になります。
   • 同時にそれぞれはクーポン設定および会員の参照なので外部キーでもあります（試験上は主キー表示を優先することが多いですが、意味としては主キーかつ外部キーです）。
   • よって (h),(i) = クーポンコード（主キーの一部／外部キー）, 会員コード（主キーの一部／外部キー）。
8. (j),(k)（クーポン利用：どの支払で使われたかの記録）
   • 本文に「クーポンがどの支払で利用されたか分かるように記録する。」とあり、さらに「会員は、クーポンコードが異なる複数のクーポンを1回の支払で利用できる。」とあるため、クーポン利用は (クーポンコード, レシート番号) の組で一意に記録すれば足ります。会員コードは冗長で、レシート番号から支払の会員が分かります。
   • よって (j),(k) = クーポンコード（主キーの一部／外部キー）, レシート番号（主キーの一部／外部キー）。

• a：加盟企業コード（外部キー、支払 → 店舗 の参照）
• b：店舗コード（外部キー、支払 → 店舗 の参照）
• c：支払金額
• d：購入数
• e：ポイント設定コード（外部キー、支払方法 → ポイント設定 の参照）
• f：ポイント付与率
• g：配布上限数
• h：クーポンコード（主キーの一部、かつクーポン設定への外部キー）
• i：会員コード（主キーの一部、かつ会員への外部キー）
• j：クーポンコード（主キーの一部、かつクーポン設定への外部キー）
• k：レシート番号（主キーの一部、かつ支払への外部キー）

誤りやすいポイント

• 支払 ⇄ 店舗の多重度を1:1と誤解する
  • 本文の「加盟企業は複数の店舗をもつ」と「1回の支払はレシート番号で識別する」から、店舗:支払 = 1:N（支払側多）であると判断します。支払に店舗の識別子を外部キーとして持たせるのは正しい慣行で、正規化（第3正規形）を崩しません。
• クーポン利用に会員コードを冗長に入れてしまう
  • 「クーポンがどの支払で利用されたか分かるように記録する。」かつ支払に会員コードがあるため、クーポン利用は支払（レシート番号）を参照すれば会員は導出できます。会員コードを追加して重複管理の手間を作るとデータ不整合の原因になります。
• ポイント設定の参照先を誤る（支払全体に付与するのか支払方法ごとに付与するのか）
  • 本文に「ポイントを付与する支払方法にポイント設定を対応付ける。」とあり、ポイント設定は支払方法に割り当てる設計が求められます。支払側に直接ポイント設定コードを置くのは要件に合いません。
• 支払方法明細に支払金額を入れ忘れる
  • 支払方法別の付与ポイントは「各支払方法での支払金額」に基づくため、明細行に支払金額を確実に持たせる必要があります。
• 付与ポイントの小数桁扱いを見落とす
  • 本文に「付与ポイントは、小数第3位まで記録する。」とあるため、付与ポイント列は小数第3位まで保持できる型とすること。日次バッチで加算後に小数以下切り捨てが行われる点も意識しておきます。

FAQ

解答の導き方

1. クーポン設定 と クーポン設定対象店舗／店舗

• 根拠（問題文）："クーポンには、適用対象となる店舗を限定したクーポン、…"
• 理由・考え方：クーポンが「適用対象店舗を限定できる」ため、クーポン企画（クーポン設定）と店舗を結ぶ中間的な実体が必要です。図2にクーポン設定対象店舗（クーポンコード、加盟企業コード、店舗コード）がある点から、これはクーポン設定と店舗を結ぶ結合テーブルであると判断できます。
• 結論（参照の向き）：参照側（外部キーを持つ側）: クーポン設定対象店舗
  参照先: クーポン設定、店舗

1. クーポン配布 と クーポン設定／会員

• 根拠（問題文）："B社は、条件に合う会員を抽出し、クーポン配布リストとして登録する。"／"クーポン配布リストに登録されている会員が、全加盟企業のいずれかの店舗を利用した場合に、クーポンを発行する。 同じ会員に同じクーポンを2回発行することはない。"
• 理由・考え方：配布リストは「どのクーポン（クーポン設定）」を「どの会員（会員）」に配布するかを記録するため、クーポン配布エンティティはクーポン設定と会員の両方を参照する必要があります。図2のクーポン配布（(h)、(i)、配布済フラグ、配布日時）から (h),(i) がそれぞれクーポンコード／会員コードと推定できます。
• 結論（参照の向き）：参照側: クーポン配布
  参照先: クーポン設定、会員

1. クーポン利用 と 支払／クーポン設定

• 根拠（問題文）："クーポンの効果を測るために、 クーポンがどの支払で利用されたか分かるように記録する。"／"会員が支払時にクーポンを提示すると、クーポンに設定されたポイント付与率を適用する。"
• 理由・考え方：クーポン利用は「どの支払（＝どのレシート）」でどのクーポンが使われたかを記録する実体です。図2のクーポン利用（(j)、(k)）は (j)=レシート番号、(k)=クーポンコード と推定できるため、クーポン利用は支払とクーポン設定を参照します。
• 結論（参照の向き）：参照側: クーポン利用
  参照先: 支払、クーポン設定

1. 支払 と 会員／店舗

• 根拠（問題文）："1回の支払はレシート番号で識別する。"／"店舗は、 加盟企業コードと店舗コードで識別する。"／図2の支払（レシート番号、会員コード、(a)、(b)、支払日時、利用ポイント）
• 理由・考え方：支払には会員コードがある（図2に明示）ため支払は会員を参照します。図2の支払に (a),(b) があり、店舗が加盟企業コード＋店舗コードで識別されることから、支払の (a),(b) は店舗特定の外部キー（加盟企業コード、店舗コード）と推定され、支払は店舗も参照します。
• 結論（参照の向き）：参照側: 支払
  参照先: 会員、店舗

1. 購入商品明細 と 支払

• 根拠（問題文）："利用ポイントを支払ごとに記録する。 1回の支払はレシート番号で識別する。"／図2の購入商品明細（レシート番号、加盟企業コード、加盟企業商品コード、(d)、商品単価、付与ポイント）
• 理由・考え方：購入商品明細は「どの支払（レシート）」に紐づく明細かを示す必要があるため、レシート番号で支払を参照します（図2にレシート番号が存在）。
• 結論（参照の向き）：参照側: 購入商品明細
  参照先: 支払

1. 支払方法明細 と 支払／支払方法

• 根拠（問題文）："支払においては、 現金、 ポイント利用、 電子マネー利用など、 1回の支払で複数の支払方法を併用できる。"／図2の支払方法明細（レシート番号、支払方法コード、(c)、付与ポイント）と支払方法（支払方法コード、支払方法名、(e)）
• 理由・考え方：1回の支払で複数の支払方法を併用できるため、支払方法明細は「どの支払（レシート）」で「どの支払方法（支払方法コード）」が使われたかを記録する。したがって支払方法明細は支払と支払方法の両方を参照します。
• 結論（参照の向き）：参照側: 支払方法明細
  参照先: 支払、支払方法

1. 支払方法 と ポイント設定

• 根拠（問題文）："ポイント設定はポイント設定コードで識別し、 ポイント付与率、 適用期間をもつ。ポイントを付与する支払方法にポイント設定を対応付ける。 同じポイント設定を、複数の支払方法に対応付けることがある。"
• 理由・考え方：同じポイント設定を複数の支払方法に対応付けることがあるため、ポイント設定が親（1つのポイント設定）で支払方法が子（各支払方法がポイント設定コードを持つ）となるのが自然です。つまり支払方法テーブルがポイント設定コードを外部キーとして持ちます。
• 結論（参照の向き）：参照側: 支払方法
  参照先: ポイント設定

• クーポン設定対象店舗（参照側） → クーポン設定、店舗（参照先）
• クーポン配布（参照側） → クーポン設定、会員（参照先）
• クーポン利用（参照側） → 支払、クーポン設定（参照先）
• 支払（参照側） → 会員、店舗（参照先）
• 購入商品明細（参照側） → 支払（参照先）
• 支払方法明細（参照側） → 支払、支払方法（参照先）
• 支払方法（参照側） → ポイント設定（参照先）

誤りやすいポイント

• 外部キーの向きを混同する
  • 「参照側」がどちらかを必ず明示する癖をつけてください。例：要件に「同じポイント設定を複数の支払方法に対応付ける」とあるとき、支払方法にポイント設定の外部キーを置くのが正解で、逆にポイント設定に支払方法のキーを置くのは誤りです。
• クーポン配布とクーポン利用を混同する
  • クーポン配布は「会員への発行（配布リスト）」を記録する実体、クーポン利用は「どの支払でクーポンが使われたか」を記録する実体です。用途が異なるため、参照先も配布→会員＋クーポン、利用→支払＋クーポン と分けて設計します。
• 店舗の参照を抜かす（または購入明細だけで店を特定する設計にする）
  • 支払そのものがどの店舗で行われたかを示す必要があるため、支払側に店舗（加盟企業コード＋店舗コード）を参照させるべきです。購入商品明細だけに店舗情報を持たせると、支払レコード単位で店舗を特定できない可能性があります。
• 多対多をそのまま描いてしまう
  • 問題文は「エンティティタイプ間のリレーションシップとして “多対多” のリレーションシップを用いないこと」と明示しています。多対多の関係は結合テーブル（例：クーポン設定対象店舗）で解消してください。
• 図2の空欄属性を無視する
  • 図2の (a),(b),(c)… といった空欄は図1でのリレーションヒントです。これを読み取ってどの外部キーが必要かを判断してください。

FAQ

解答の導き方

1. 関係「加盟企業商品」の明示的な識別の記述を読む。本文に「加盟企業商品は、加盟企業コードと加盟企業商品コードで識別する。」とあるので、まず明らかな候補キーは { 加盟企業コード、加盟企業商品コード } である。これは「識別する」と明示されているため、論理的に一意性を満たす。
2. 次に横断商品コードの扱いを確認する。本文に「横断分析用商品情報には、横断分析用商品コードと横断分析用商品名を設定し、横断分析用商品コードで識別する。」とある点と、「B社は、加盟企業商品が追加される都度、既に同じ商品の横断分析用商品が登録済みかどうかを確認し、登録済みと判断すればその横断分析用商品コードを加盟企業商品に設定する。」という記述を合わせて読むと、加盟企業の商品には横断分析用商品コードが割り当てられ、そのコードは「同一の商品」を表す共通の識別子であることがわかる。したがって「ある加盟企業が扱う横断分析用商品（＝その会社が取り扱うある共通商品）」を表すには加盟企業コードと横断分析用商品コードの組合せで一意に定まると解釈でき、もう一つの候補キーは { 加盟企業コード、横断分析用商品コード } とできる。補足として本文に「横断分析用商品コードの設定には、加盟企業商品の登録から数日を要する場合がある。」とあるため、新規登録時には一時的に横断分析用商品コードが未設定（NULL）となることがある点に注意する。ただし設計上の候補キーは、最終的に割り当てられる実データの一意性を想定して決めるのが一般的であり、本問の想定解は上記の2つの組合せを候補キーとする。
3. 部分関数従属性の判定：
   • 候補キー { 加盟企業コード、加盟企業商品コード } を見ると、その部分集合である加盟企業コードだけで決まる属性がある。本文に「加盟企業は…加盟企業コードで識別する。」および「同じ加盟企業と複数回の契約をすることはない。」とあるため、加盟企業コードは加盟企業に関する情報（加盟企業名、契約開始日、契約終了日）を決定する。したがって部分関数従属性は存在する。具体例：
     • 加盟企業コード → 加盟企業名
     • 加盟企業コード → 契約開始日
     • 加盟企業コード → 契約終了日
   • また別の候補キー { 加盟企業コード、横断分析用商品コード } に対しては、横断分析用商品コードだけで横断分析用商品名が決まる（本文「横断分析用商品コードで識別する」より）。したがって
     • 横断分析用商品コード → 横断分析用商品名 も部分関数従属性の具体例となる。
4. 推移的関数従属性の判定：
   • 候補キー { 加盟企業コード、加盟企業商品コード } はリレーション内のすべての属性を決定するが、その結果として横断分析用商品コードが決まる（加盟企業商品に横断分析用商品コードが登録されるため）。さらに横断分析用商品コードから横断分析用商品名が決まるため、次のような推移的関数従属性が成立する： { 加盟企業コード、加盟企業商品コード } → 横断分析用商品コード → 横断分析用商品名
   • したがって推移的従属性も存在する。

• 候補キー：
  • { 加盟企業コード、加盟企業商品コード }
  • { 加盟企業コード、横断分析用商品コード }
• 部分関数従属性：
  • 有無：あり
  • 具体例：
    • 加盟企業コード → 加盟企業名
    • 加盟企業コード → 契約開始日
    • 加盟企業コード → 契約終了日
    • 横断分析用商品コード → 横断分析用商品名
• 推移的関数従属性：
  • 有無：あり
  • 具体例：
    • { 加盟企業コード、加盟企業商品コード } → 横断分析用商品コード → 横断分析用商品名

誤りやすいポイント

• 横断分析用商品コードだけを候補キーとして挙げる誤り
  → 横断分析用商品コードは横断商品情報を識別するコードであり、同じコードが複数の加盟企業にまたがって使われ得るため、加盟企業間の区別ができない。したがって単独では加盟企業商品を一意に識別できない。
• 部分関数従属性と推移的関数従属性の取り違え
  → 「加盟企業コード → 加盟企業名」は候補キーの一部（加盟企業コード）による決定であり部分従属性。これを誤って推移的とする受験者が多い。推移的は「キー → X → Y」の形で、Xが非キー属性である場合に成立する。
• JANコードや加盟企業商品名をキーと見なす誤り
  → 本文に「加盟企業商品コードは再利用されないが、加盟企業商品名とJANコードは再利用されることがある。 また、 JANコードが設定されない商品もある。」とあるため、これらをキーにすると一意性や存在性（NULL）の問題が生じる。
• 横断分析用商品コードの「未設定（設定の遅れ）」を見落とす誤り
  → 設計上は候補キーにできると判断されていても、実運用では新規登録時に横断分析用商品コードが数日遅れて設定される点を考慮しないと実装上の制約に気づかない。

FAQ

• { 加盟企業コード、加盟企業商品コード } に対しては、加盟企業コード → 加盟企業名 等が部分従属性の例です。
• { 加盟企業コード、横断分析用商品コード } に対しては、横断分析用商品コード → 横断分析用商品名 が部分従属性の例になります。

解答の論理構成

1. 候補キー候補の洗い出し
   関係 “加盟企業商品” は【問題文】で
   “加盟企業商品は、加盟企業コードと加盟企業商品コードで識別する。”
   と規定されています。また
   “②…横断分析用商品コードを加盟企業商品に設定する。”
   から { 加盟企業コード、横断分析用商品コード } も一意性を満たすため候補キーになり得ます。
2. 主キー要件の確認
   主キー列は「常に NOT NULL」である必要があります（エンティティ整合性制約）。
3. NULL発生の有無
   同じく【問題文】の
   “③ 横断分析用商品コードの設定には、加盟企業商品の登録から数日を要する場合がある。”
   より、行登録時点では 横断分析用商品コード が未設定になるケースが明示されています。
4. 結論
   よって { 加盟企業コード、横断分析用商品コード } は主キー条件を満たさず「採用できない候補キー」となります。

誤りやすいポイント

• 「一意になれば主キーにできる」と思い込み、入力タイミングやNULL可能性を無視する。
• 横断分析用商品コードが後日必ず埋まるから問題ないと考え、初期NULLが許されない点を見落とす。
• 候補キーと主キーの区別（候補キーは複数可、主キーはその中で選定される1つ）を混同する。

FAQ

解答の論理構成

1. 主キーの確認
   【問題文】「加盟企業商品は、加盟企業コードと加盟企業商品コードで識別する。」
   ⇒ 主キー：{加盟企業コード、加盟企業商品コード}
2. 第1正規形の判定
   ・繰返し属性や多値属性は無い。よって第1正規形。
3. 第2正規形の判定
   ・「加盟企業名、契約開始日、契約終了日」は“加盟企業コード”だけで決まる。
   ・主キーの一部にのみ従属する部分関数従属性が存在 → 第2正規形を満たさない。
4. 第3正規形の判定
   ・「横断分析用商品名」は“横断分析用商品コード”で決まる。
   ・非キー → 非キーの推移的関数従属性が存在 → 第3正規形も満たさない。
5. 分解手順
   ① “加盟企業コード”の従属性を独立
   加盟企業(加盟企業コード、加盟企業名、契約開始日、契約終了日)
   ② “横断分析用商品コード”の従属性を独立
   横断分析用商品情報(横断分析用商品コード、横断分析用商品名)
   ③ 残りを再構成
   加盟企業商品(加盟企業コード、加盟企業商品コード、JANコード、加盟企業商品名、横断分析用商品コード)
   以上3関係はいずれも主キーしか決定因子を持たず、第3正規形を満たします。

誤りやすいポイント

• 「加盟企業商品コードは再利用されない」→ だからといって単独キーにしない
  （同一加盟企業を識別する“加盟企業コード”との複合が必要）。
• 「JANコードが設定されない商品もある」→ NULL可属性はキーにならない。
• 「横断分析用商品名」をそのまま残してしまい推移従属性を見落とす。
• 分解後に外部キーを付け忘れ、参照整合性を示せていない。

FAQ

解答の論理構成

1. 日次バッチの対象
   • 「毎日午前0時を過ぎると、支払ごとの付与ポイントの記録から、支払日時が前日の分を日次バッチで抽出し、集計して会員のポイント残高に加算する。」
     ⇒バッチは“前日”の支払日時を条件に抽出。
2. 後付けレコードのタイムスタンプ
   • 「付与ポイントの記録は、レシートが発行された日時の記録となる。」
     ⇒後付けしても“支払日時”は購入時(レシート発行時)のまま。
3. 時系列のずれ
   • 後付けが「購入の翌日以降」に行われると、 バッチは既に対象日(購入日)を処理済み → 抽出されない。
4. 結果
   • よって後付けポイントは残高に加算されず、設問の答えは「購入の翌日以降にポイントの後付けをしたとき」となる。

誤りやすいポイント

• 「後付けした日の翌日バッチで入る」と誤解し、支払日時と登録日時を混同する。
• 付与ポイントを即時加算と勘違いし、日次バッチの存在を見落とす。
• 「当日中の後付け」も漏れると決めつけるが、当日23:59までに登録されれば前日判定にかからないため集計対象になる。

FAQ

解答の論理構成

1. 日次バッチの仕様
   【問題文】「支払ごとの付与ポイントの記録から、支払日時が前日の分を…会員のポイント残高に加算する」。
2. 問題点
   • 処理が失敗して再実行すると、同じ支払レコードが再度抽出され二重加算の恐れ。
   • 将来“当日分を即時加算”など仕様変更があった場合も誤加算リスクが残る。
3. 解決策
   • 支払レコードに「加算済」を示す情報を付与。
   • バッチは “未加算かつ対象日付” で抽出し、成功後にフラグを更新。
4. したがって追加属性の役割
   「ポイント残高に加算済みかどうかを判別する。」（30字以内）

誤りやすいポイント

• 抽出条件に「支払日時が前日」を使えば重複防止できると誤解する。
• 支払テーブルではなくポイント残高テーブル側にフラグを持たせようとする。
• 「加算済フラグ」と「ポイント残高更新日時」の違いを区別しない。

FAQ