令和3年度 秋期 データベーススペシャリスト試験 午後Ⅰ 問1 共通ポイントサービスのデータベース設計と関係スキーマの正規化

関係「支払」は1回の支払を表し、問題文の〔ポイントの概要〕2.(3)に「1回の支払はレシート番号で識別する」とあるため、主キーは レシート番号 となっています。

また、クーポンなどの処理において支払が行われた店舗を特定する必要があるため、関係「支払」は店舗と結びつく必要があります。〔ポイントシステムの概要〕2.(2)より「店舗は，加盟企業コードと店舗コードで識別する」、〔クーポンの概要〕2.(2) の会員の抽出条件に「ある期間に特定の店舗を利用した」とされているため、関係「支払」には外部キーとして 加盟企業コード と 店舗コード をもたせる必要があります。空欄 a と b にはこれらが順不同で入ります。

高得点のポイント

• 図2の関係「店舗」の主キー（加盟企業コード、店舗コード）を参照するための外部キーであることを理解し、正確な属性名を導出できていること。
• 公式解答例では破線の下線（外部キー）が付く。解答では「加盟企業コード（外部キー）」のように注記できる（注記が無くても減点しない）。

関係「支払」は1回の支払を表し、問題文の〔ポイントの概要〕2.(3)に「1回の支払はレシート番号で識別する」とあるため、主キーは レシート番号 となっています。

また、クーポンなどの処理において支払が行われた店舗を特定する必要があるため、関係「支払」は店舗と結びつく必要があります。〔ポイントシステムの概要〕2.(2)より「店舗は，加盟企業コードと店舗コードで識別する」、〔クーポンの概要〕2.(2) の会員の抽出条件に「ある期間に特定の店舗を利用した」とされているため、関係「支払」には外部キーとして 加盟企業コード と 店舗コード をもたせる必要があります。空欄 a と b にはこれらが順不同で入ります。

高得点のポイント

• 図2の関係「店舗」の主キー（加盟企業コード、店舗コード）を参照するための外部キーであることを理解し、正確な属性名を導出できていること。
• 公式解答例では破線の下線（外部キー）が付く。解答では「店舗コード（外部キー）」のように注記できる（注記が無くても減点しない）。

関係「支払方法明細」は、1回の支払においてどの支払方法でいくら支払ったかを記録するテーブルです。〔ポイントの概要〕3.(3)②に「各支払方法での支払金額に，ポイント付与率を乗じて計算する」とあり、付与ポイントを計算する基となる金額情報を保持する必要があります。したがって、空欄 c には 支払金額 が入ります。

高得点のポイント

• 支払方法ごとの付与ポイント計算に必要な要素が「支払金額」であることを業務要件から正しく読み取れていること。

関係「購入商品明細」は、1回の支払で購入した商品の明細を記録します。〔ポイントの概要〕3.(3)①に「商品の購入金額（購入数×商品単価）にポイント付与率を乗じて計算する」とあります。関係スキーマにはすでに 商品単価 と 付与ポイント が存在するため、計算に必要な残りの属性である 購入数 が空欄 d に入ります。

高得点のポイント

• 購入商品ごとの付与ポイント計算に必要な要素が「商品単価」と「購入数」であることを業務要件から正しく特定できていること。

関係「支払方法」は、利用可能な支払方法のマスターデータを管理します。〔ポイントの概要〕3.(3)②に「ポイントを付与する支払方法にポイント設定を対応付ける」とあります。この対応付けを実現するため、関係「支払方法」に外部キーとして ポイント設定コード をもたせる必要があります。したがって、空欄 e には ポイント設定コード が入ります。

高得点のポイント

• 支払方法とポイント設定の対応付け要件から、関係「ポイント設定」の主キー（ポイント設定コード）を参照するための外部キーが必要であると理解できていること。
• 公式解答例では破線の下線（外部キー）が付く。解答では「ポイント設定コード（外部キー）」のように注記できる（注記が無くても減点しない）。

関係「ポイント設定」は、ポイント付与に関する設定値を保持します。〔ポイントの概要〕3.(3)②に「ポイント設定はポイント設定コードで識別し，ポイント付与率，適用期間をもつ」とあります。関係スキーマには適用期間を表す 適用開始日 と 適用終了日 がすでにあるため、残りの属性である ポイント付与率 が空欄 f に入ります。

高得点のポイント

• ポイント設定が保持すべき項目を業務要件の記述から漏れなく抽出し、正確な属性名を導出できていること。

関係「クーポン設定」は、クーポンの企画内容を保持します。〔クーポンの概要〕2.(5)に「クーポンには，配布上限数と配布期間を設定している」とあります。関係スキーマには配布期間を表す 配布開始日、配布終了日 がすでに存在するため、残りの 配布上限数 が空欄 g に入ります。

高得点のポイント

• クーポン設定が保持すべき項目を業務要件の記述から漏れなく抽出し、正確な属性名を導出できていること。

関係「クーポン配布」は、どの会員にどのクーポンを配布したかを管理します。〔クーポンの概要〕2.(4)に「クーポン配布リストに登録されている会員が，全加盟企業のいずれかの店舗を利用した場合に，クーポンを発行する。同じ会員に同じクーポンを2回発行することはない」とあります。1回の発行（配布）は会員とクーポンの組み合わせで一意になるため、主キーは クーポンコード と 会員コード になります。空欄 h, i にはこれらが順不同で入ります。

高得点のポイント

• クーポン配布を記録するためには、どのクーポンを誰（どの会員）に渡したかという2つの識別子が必要であることを理解できていること。
• 公式解答例では実線の下線（主キー）が付く。主キーの一部が外部キーを兼ねる場合は破線を付けない（表記ルール③）。

関係「クーポン配布」は、どの会員にどのクーポンを配布したかを管理します。〔クーポンの概要〕2.(4)に「クーポン配布リストに登録されている会員が，全加盟企業のいずれかの店舗を利用した場合に，クーポンを発行する。同じ会員に同じクーポンを2回発行することはない」とあります。1回の発行（配布）は会員とクーポンの組み合わせで一意になるため、主キーは クーポンコード と 会員コード になります。空欄 h, i にはこれらが順不同で入ります。

高得点のポイント

• クーポン配布を記録するためには、どのクーポンを誰（どの会員）に渡したかという2つの識別子が必要であることを理解できていること。
• 公式解答例では実線の下線（主キー）が付く。主キーの一部が外部キーを兼ねる場合は破線を付けない（表記ルール③）。

関係「クーポン利用」は、支払時にどのクーポンが使われたかを記録します。〔クーポンの概要〕1.(8)に「クーポンがどの支払で利用されたか分かるように記録する」とあります。1回の支払は レシート番号 で識別され、利用されるクーポンは クーポンコード で識別されます。〔クーポンの概要〕1.(7)に「会員は，クーポンコードが異なる複数のクーポンを1回の支払で利用できる」とあり、1回の支払で複数のクーポンが利用されるので、レシート番号だけでは識別できません。クーポンと支払の組み合わせで特定できるため、この関係の主キーは クーポンコード と レシート番号 の組み合わせになります。空欄 j, k にはこれらが順不同で入ります。

高得点のポイント

• クーポンの利用履歴を特定するためには、利用されたクーポンと、利用された支払機会（レシート）の両方の識別子が必要であることを理解できていること。
• 公式解答例では実線の下線（主キー）が付く。主キーの一部が外部キーを兼ねる場合は破線を付けない（表記ルール③）。

関係「クーポン利用」は、支払時にどのクーポンが使われたかを記録します。〔クーポンの概要〕1.(8)に「クーポンがどの支払で利用されたか分かるように記録する」とあります。1回の支払は レシート番号 で識別され、利用されるクーポンは クーポンコード で識別されます。〔クーポンの概要〕1.(7)に「会員は，クーポンコードが異なる複数のクーポンを1回の支払で利用できる」とあり、1回の支払で複数のクーポンが利用されるので、レシート番号だけでは識別できません。クーポンと支払の組み合わせで特定できるため、この関係の主キーは クーポンコード と レシート番号 の組み合わせになります。空欄 j, k にはこれらが順不同で入ります。

高得点のポイント

• クーポンの利用履歴を特定するためには、利用されたクーポンと、利用された支払機会（レシート）の両方の識別子が必要であることを理解できていること。
• 公式解答例では実線の下線（主キー）が付く。主キーの一部が外部キーを兼ねる場合は破線を付けない（表記ルール③）。

概念データモデル（図1）を完成させるためには、業務要件からエンティティ間の関係（リレーションシップ）を読み解き、親（1）から子（多）へ矢印を引く必要があります。
解答は「1 側 → 多 側」の形で、1 行に 1 本ずつ書きます。

追加すべき主なリレーションシップは以下の通りです。

• 店舗と支払: 支払は特定の店舗で行われ、1つの店舗で複数の支払が発生します。〔クーポンの概要〕2.(2)の会員の抽出条件に「ある期間に特定の店舗を利用した」とあり、支払がどの店舗で行われたかを記録する必要があるため、店舗 → 支払 の関係があります。
• クーポン設定とクーポン設定対象店舗: 1つのクーポンは複数の店舗を対象にできるため、クーポン設定 → クーポン設定対象店舗 の関係があります。
• 店舗とクーポン設定対象店舗: 対象となる各店舗は複数のクーポンに紐づく可能性があるため、店舗 → クーポン設定対象店舗 の関係があります。
• クーポン設定と配布/利用: 1つのクーポンは複数の会員に配布・利用されるため、クーポン設定 → クーポン配布、クーポン設定 → クーポン利用 の関係があります。
• 会員とクーポン配布: 1人の会員は複数のクーポンを受け取るため、会員 → クーポン配布 の関係があります。
• 支払とクーポン利用: 1回の支払で複数のクーポンが利用されることがあるため、支払 → クーポン利用 の関係があります。
• ポイント設定と支払方法: 〔ポイントの概要〕3.(3)②より、1つのポイント設定を複数の支払方法に対応付けることがあるため、ポイント設定 → 支払方法 の関係があります。

高得点のポイント

• 本文の複数の箇所に分散している業務要件を漏れなく拾い上げ、エンティティ間の依存関係を過不足なく特定できていること。
• 主キー・外部キーの構成を考慮し、「1対多」の関係において「1（親）」から「多（子）」へ正しく矢印を引いていること。

候補キーとは、関係（テーブル）の中の行を一意に識別できる属性（またはその最小の組み合わせ）のことです。

• 〔ポイントシステムの概要〕3.(1)②より「加盟企業商品は，加盟企業コードと加盟企業商品コードで識別する」とあるため、$\{加盟企業コード, 加盟企業商品コード\}$ が候補キーの1つとなります。
• さらに、3.(2)①より「横断分析用商品情報には，横断分析用商品コードと横断分析用商品名を設定し，横断分析用商品コードで識別する。横断分析用商品名は一意になるとは限らない」とあり、同じ加盟企業内において、横断分析用商品コードも商品を一意に識別できる値となります。したがって、$\{加盟企業コード, 横断分析用商品コード\}$ も候補キーとなります。

高得点のポイント

• 「加盟企業コードと加盟企業商品コード」の組み合わせがレコードを一意に特定できることを理解していること。
• 「横断分析用商品コード」の仕様から、加盟企業コードとの組み合わせで代替の識別子となることを導出できていること。
• 複数の属性からなる複合キーであることを示すため、指定通り { } でくくって列挙できていること。

部分関数従属性とは、複合キー（複数の属性からなる候補キー）の「一部」だけで、他の非キー属性が一意に決まる関係のことです。

関係「加盟企業商品」の候補キーは $\{加盟企業コード, 加盟企業商品コード\}$ や $\{加盟企業コード, 横断分析用商品コード\}$ です。このうち、候補キーの一部である 加盟企業コード だけで、その企業の属性である 加盟企業名、契約開始日、契約終了日 が一意に決まります。
このように、候補キーの「全体」ではなく「一部」に従属している属性が存在するため、部分関数従属性は「あり」となります。

各選択肢の解説

• イ（なし）: 候補キーの一部（加盟企業コードなど）によって一意に決まる属性群が存在するため誤りです。

関係「加盟企業商品」における候補キー $\{加盟企業コード, 加盟企業商品コード\}$ または $\{加盟企業コード, 横断分析用商品コード\}$ の真部分集合（一部）から関数従属する属性を見つけます。

• 加盟企業コード（候補キーの一部）から、加盟企業自体の属性である 加盟企業名, 契約開始日, 契約終了日 が決定されます。
• また、候補キー $\{加盟企業コード, 横断分析用商品コード\}$ の一部である 横断分析用商品コード から 横断分析用商品名 が決定されます。

これらを指定された表記法「属性 1 → 属性 2」に従って記述します。解答としてはこれらの中から1つを示せば正解となります。

高得点のポイント

• 複合キーの一部（加盟企業コード、あるいは横断分析用商品コード）から一意に決まる属性を正確に見つけ出せていること。
• 問題文の表にある「部分関数従属性」の表記法（属性 1 → 属性 2）に厳密に従って記述できていること。

推移的関数従属性とは、X → Y かつ Y → Z が成り立ち、Y → X は成り立たない（Y を経由して Z が決まる）関係のことです。

関係「加盟企業商品」において、候補キー $\{加盟企業コード, 加盟企業商品コード\}$ から、単独では候補キーにならない 横断分析用商品コード が決まり、その 横断分析用商品コード から 横断分析用商品名 が一意に決まります。
横断分析用商品コードを経由した関数従属性が存在するため、推移的関数従属性は「あり」となります。

各選択肢の解説

• イ（なし）: 属性の間に推移的な依存関係が存在するため誤りです。

設問2(1)〜(4)の考察の通り、候補キー $\{加盟企業コード, 加盟企業商品コード\}$ → 横断分析用商品コード → 横断分析用商品名（横断分析用商品コードは候補キーを決定しない）という関係を記述します。

具体的には、候補キー $\{加盟企業コード, 加盟企業商品コード\}$ から 横断分析用商品コード が決定し、さらに 横断分析用商品コード から 横断分析用商品名 が決定します。
指定された表記法「属性 3 → 属性 4 → 属性 5」に従い、複合属性は { } でくくって {加盟企業コード, 加盟企業商品コード} → 横断分析用商品コード → 横断分析用商品名 と記述します。

高得点のポイント

• 候補キーから横断分析用商品コードを経てさらに横断分析用商品名が決定されるという、3段階の関係性を正確に捉えられていること。
• 問題文の表にある「推移的関数従属性」の表記法（属性 3 → 属性 4 → 属性 5）に従い、複合属性を { } でくくって記述できていること。

関係「加盟企業商品」の候補キーには $\{加盟企業コード, 加盟企業商品コード\}$ と $\{加盟企業コード, 横断分析用商品コード\}$ の2つがあります。

主キーの絶対条件として「値が必ず存在し、NULLにならないこと（エンティティ完全性）」があります。本文中の要件から、登録時点で値が確定しない可能性がある属性を含む候補キーは主キーとして採用できません。よって、採用できない候補キーは $\{加盟企業コード, 横断分析用商品コード\}$ となります。

高得点のポイント

• 本文の記述（横断分析用商品コードの設定に遅れが生じる可能性）から、NULLになり得る属性を含んだ候補キーを特定できていること。
• 複合キーであることを示すため、指定通り { } でくくって解答できていること。

主キーを構成する属性には、原則として NULL を設定できません（エンティティ完全性制約）。

〔ポイントシステムの概要〕3.(2)③に「横断分析用商品コードの設定には，加盟企業商品の登録から数日を要する場合がある」と記載されています。つまり、加盟企業商品をシステムに登録する時点では 横断分析用商品コード が未定（NULL）になる可能性があり、主キーとしての条件を満たしません。この理由を字数制限内にまとめます。

高得点のポイント

• 「登録から数日を要する場合がある」という業務要件を、主キーの「NULL不可」の制約と結びつけて理解できていること。
• 指定された字数（45字以内）の範囲内で、要点を論理的かつ簡潔な文章で表現できていること。

関係「加盟企業商品」には、1つの行に複数の値が入るような繰り返し項目は存在しないため、「第1正規形」の条件は満たしています。

しかし、設問2(1)の解答の通り、候補キーの一部である 加盟企業コード だけで 加盟企業名 などが一意に決まる「部分関数従属性」が存在しています。部分関数従属性が残っている関係は、第2正規形の条件を満たしていません。
したがって、現在の関係は「第1正規形」まで正規化されている状態です。

高得点のポイント

• 正規形（第1、第2、第3）のそれぞれの定義を正しく理解し、現在の関係における関数従属性（部分関数従属性の存在）に基づいて正確な判定を下せていること。

第1正規形の関係を第3正規形にするためには、データの重複や更新時の矛盾を防ぐため、部分関数従属性と推移的関数従属性を排除してテーブルを分割します。

1. 部分関数従属性の排除（第2正規形へ）:
   加盟企業コード のみに従属する属性群を別テーブルにします。
   加盟企業（加盟企業コード（主キー）, 加盟企業名, 契約開始日, 契約終了日）

2. 推移的関数従属性の排除（第3正規形へ）:
   横断分析用商品コード に従属する属性群を別テーブルにします。
   横断分析用商品情報（横断分析用商品コード（主キー）, 横断分析用商品名）

3. 残りの属性（加盟企業商品の本体）:
   分離した属性を取り除き、外部キーを残して元のテーブルを再構成します。
   加盟企業商品（加盟企業コード（主キー）, 加盟企業商品コード（主キー）, 横断分析用商品コード（外部キー）, 加盟企業商品名, JANコード）
   （※順序は問いませんが、属性が不足なく含まれ、主キーと外部キーの指定が正しい必要があります。なお、加盟企業商品の加盟企業コードは主キーの一部が外部キーを兼ねるので破線の下線は付けません）

高得点のポイント

• 部分関数従属と推移的関数従属を正しく排除し、論理的な矛盾のない3つの関係に分割できていること。
• 分解後の関係名に「加盟企業」「横断分析用商品情報」等の本文中の用語を用いていること。
• 主キーに実線の下線、外部キーに破線の下線を付けるという指定を正確に守っていること。

〔ポイントの概要〕4.(1)において、日次バッチの抽出条件は「支払日時が前日の分」と規定されています。

一方、5.(1)(2)の「ポイントの後付け」手続きでは、会員が後日レシートを持参した際にポイント付与の記録が作成されますが、「付与ポイントの記録は，レシートが発行された日時の記録となる」とされています。
つまり、購入日の「翌日以降」にポイントの後付けが行われた場合、作成された記録の「支払日時」はバッチ処理の前日より過去の日付になってしまいます。その結果、「前日の分」という日次バッチの抽出条件に合致せず、抽出から漏れてしまい、ポイント残高に加算されなくなります。

高得点のポイント

• 日次バッチの抽出条件（前日分）と、ポイント後付け処理における日付記録ルール（過去日付での記録）の間の不整合に気づき、具体的な抽出漏れのケースを特定できていること。
• 「ポイントの後付け」という本文中の用語を必ず用い、30字以内で端的に状況を説明できていること。

設問3(1)で指摘したように、「支払日時」を基準にして日次バッチの抽出を行うと、過去の支払日時で記録されるポイント後付け分が抽出漏れを起こします。

この問題を解消し、全ての付与ポイント記録を確実に1回ずつ残高に加算するためには、ポイント残高に加算済みかどうかや加算処理日といった処理状態を持たせる方法と、付与ポイントの記録を作成した日を持たせる方法があります。
具体的には「加算済フラグ」や「加算処理日」などの属性を追加し、バッチ処理で未処理のものを漏れなく抽出して処理後にフラグを更新するか、記録作成日の属性を追加し、日次バッチは作成日が前日の記録を抽出するという仕組みに変更します。解答ではこの属性の役割を30字以内で記述します。

高得点のポイント

• 抽出漏れや重複加算を防ぐため、レコード単位で処理状態（加算済みかどうか）を管理するフラグや日付属性の必要性を理解できていること。
• 追加した属性がシステム上どのような目的で利用されるかを、30字以内で過不足なく表現できていること。