令和3年度秋期 データベーススペシャリスト 午後Ⅰ 問1

業務ルールから対応関係を読み取る

データベース設計では、図や関係スキーマを埋める前に、問題に示された業務上の条件を一つずつ拾い、どの事柄とどの事柄が何対何で結び付くのか、何によって一件が識別されるのかを自分の言葉で整理しておくことが、後続の検討全体の土台になります。

この問題で問われていること

確認すべき観点は一つ、「業務上の条件と、モデル上の対応関係・識別子の対応付け」です。正規化や集計処理の課題を考える場面でも、記述された条件がどの属性やどの関係に反映されているかを照合する姿勢が共通して役立ちます。

視点

概念データモデルは、現実の業務で扱う事柄と、事柄同士の関係を表したものです。関係スキーマは、それを表の構造として具体化したものです。両者を行き来する際は、条件の一文ごとに「これはどの事柄の性質か」「どの事柄同士の結び付きか」「一件を区別する手掛かりは何か」と問い直します。そのうえで、自分の整理とモデル上の表現が一致しているかを照らし合わせます。正規化や処理上の課題を考えるときも、同じ照合を土台にすると、根拠のある判断をしやすくなります。

確認ポイント

• 問題に示された業務上の条件を一文ずつ拾い、それぞれがどの事柄に関する記述かを整理したか
• 事柄同士の結び付きが何対何かを、条件の記述を根拠にして自分で説明できるか
• 一件を区別する手掛かりと各属性の置き場所が、拾い出した条件と矛盾していないか照合したか

解説

共通ポイントサービスを題材に、業務要件から関係スキーマの属性（主キー・外部キーの区別を含む）と概念データモデルのリレーションシップを完成させ、関係“加盟企業商品”について候補キー、部分・推移的関数従属性、正規形と第3正規形への分解を答えます。さらに、支払日時で抽出する日次バッチではポイントの後付け分が加算から漏れることを指摘し、その対策として関係“支払”に追加する属性の役割を説明する問題です。

問題の全体像

B社は複数の加盟企業向けに共通ポイントサービスを運営しており、その基盤となるポイントシステムを再構築するためにデータベース設計を行っています。本文には、会員・加盟企業・店舗・加盟企業商品・横断分析用商品情報、支払ごとの利用ポイントと付与ポイント（購入商品ごと・支払方法ごと）、日次バッチによるポイント残高への加算、ポイントの後付け、クーポンの設定・配布・利用といった要件が書かれています。出題趣旨では、業務要件をデータモデルに反映する能力、関数従属性や正規化理論などの基礎知識でデータモデルを分析する能力、データモデルの問題点を識別する能力を問うとされています。設問1は図2の空欄の属性と図1のリレーションシップ、設問2は関係“加盟企業商品”の候補キー・関数従属性・正規化、設問3は日次バッチの問題点と対策を扱います。採点講評では、全体の正答率は平均的だったと述べられています。

設問1(1)(a)

公式解答例では、aは「加盟企業コード」で、外部キーを表す破線の下線が付いています（bとは順不同）。関係“支払”には、どの店舗で支払をしたかを記録する必要があります。クーポンは企画した加盟企業の店舗だけで利用でき、クーポン配布の抽出条件には「ある期間に特定の店舗を利用した」があるためです。店舗は加盟企業コードと店舗コードで識別されるので、支払は{加盟企業コード、 店舗コード}の組で関係“店舗”を参照します。加盟企業コードはこの複合外部キーの一部ですが、支払の主キーはレシート番号だけなので、主キーの実線ではなく外部キーの破線を付けます。

設問1(1)(b)

公式解答例では、bは「店舗コード」で、外部キーを表す破線の下線が付いています（aとは順不同）。aの加盟企業コードと組になって関係“店舗”の主キー{加盟企業コード、 店舗コード}を参照する複合外部キーを構成します。店舗コードだけでは店舗を一意に識別できないため、加盟企業コードとの組で持つ必要があります。支払は1回ごとにレシート番号で識別されるので、店舗コードは主キーに含まれず、破線の下線だけになります。

設問1(1)(c)

公式解答例では、cは「支払金額」で、下線はありません。本文の〔ポイントの概要〕3.(3)②には、支払方法ごとの付与ポイントは「各支払方法での支払金額に、ポイント付与率を乗じて計算する」とあります。関係“支払方法明細”は{レシート番号、 支払方法コード}で識別される明細なので、その支払方法で支払った金額を属性として持つ必要があります。また、クーポン配布の抽出条件「特定の支払方法で一定以上の金額を支払った」を判定するときにもこの金額を使います。主キーでも外部キーでもない非キー属性なので、下線は付けません。

設問1(1)(d)

公式解答例では、dは「購入数」で、下線はありません。本文には「商品の購入金額（購入数×商品単価）にポイント付与率を乗じて計算する」とあります。関係“購入商品明細”には商品単価と付与ポイントが既にありますが、購入金額を求めるための購入数がありません。クーポン配布の抽出条件「特定の商品を一定以上の金額分購入した」の判定にも、購入数×商品単価が必要です。購入数は非キー属性なので、下線は付けません。

設問1(1)(e)

公式解答例では、eは「ポイント設定コード」で、外部キーを表す破線の下線が付いています。本文には「ポイントを付与する支払方法にポイント設定を対応付ける。同じポイント設定を、複数の支払方法に対応付けることがある」とあります。1つのポイント設定に複数の支払方法が対応する1対多の関係なので、多側の関係“支払方法”が関係“ポイント設定”の主キーを外部キーとして持ちます。支払方法の主キーは支払方法コードなので、ポイント設定コードは破線の下線になります。

設問1(1)(f)

公式解答例では、fは「ポイント付与率」で、下線はありません。本文には「ポイント設定はポイント設定コードで識別し、ポイント付与率、適用期間をもつ」とあります。図2の関係“ポイント設定”には、適用期間に当たる適用開始日・適用終了日は既にあるので、残る属性はポイント付与率です。非キー属性なので下線は付けません。

設問1(1)(g)

公式解答例では、gは「配布上限数」で、下線はありません。〔クーポンの配布方法〕(5)に「クーポンには、配布上限数と配布期間を設定している」とあります。関係“クーポン設定”には、配布期間は配布開始日・配布終了日として、利用期間は利用開始日・利用終了日として、クーポンに設定されたポイント付与率はポイント付与率として既にあります。まだ表現されていない要件は配布上限数です。非キー属性なので下線は付けません。

設問1(1)(h)

公式解答例では、hは「クーポンコード」で、主キーを表す実線の下線が付いています（iとは順不同）。関係“クーポン配布”は、B社が抽出した会員を登録するクーポン配布リストに当たります。本文には「同じ会員に同じクーポンを2回発行することはない」とあるので、クーポンと会員の組で1件が決まります。主キーは{クーポンコード、 会員コード}の複合主キーで、クーポンコードはその一部です。クーポンコードは関係“クーポン設定”を参照する外部キーの役割も持ちますが、公式解答例では主キー構成属性として実線の下線だけが付いています。答案でも実線の下線を付けることが要点です。

設問1(1)(i)

公式解答例では、iは「会員コード」で、主キーを表す実線の下線が付いています（hとは順不同）。hのクーポンコードと組み合わせた複合主キー{クーポンコード、 会員コード}を構成し、「どの会員にどのクーポンを配布するか（配布済フラグ・配布日時）」を1行で表します。会員コードは関係“会員”を参照する外部キーの役割も持ちますが、主キーの一部なので、公式解答例では破線ではなく実線の下線だけが付いています。この2つの外部キーによって、クーポン設定と会員の多対多の関係が、関係“クーポン配布”を介した2つの1対多に分解されます。

設問1(1)(j)

公式解答例では、jは「クーポンコード」で、主キーを表す実線の下線が付いています（kとは順不同）。本文には「会員は、クーポンコードが異なる複数のクーポンを1回の支払で利用できる」「クーポンがどの支払で利用されたか分かるように記録する」とあります。1回の支払で複数のクーポンが使われ、1つのクーポンは多くの支払で使われるので、関係“クーポン利用”は支払とクーポン設定の多対多を解消する連関エンティティです。主キーは{クーポンコード、 レシート番号}の複合主キーで、クーポンコードはその一部です。関係“クーポン設定”を参照する外部キーの役割も持ちますが、公式解答例では実線の下線だけが付いています。

設問1(1)(k)

公式解答例では、kは「レシート番号」で、主キーを表す実線の下線が付いています（jとは順不同）。1回の支払はレシート番号で識別されるので、クーポンがどの支払で利用されたかを表すには、支払のレシート番号を持つ必要があります。レシート番号はjのクーポンコードとともに複合主キー{クーポンコード、 レシート番号}を構成します。関係“支払”を参照する外部キーの役割も持ちますが、主キーの一部なので、公式解答例では実線の下線だけが付いています。なお、同じ会員に同じクーポンを2回発行することはないので、この組で1件が一意に決まります。

設問1(2)

図1に最初から描かれているのは、会員→支払、支払→支払方法明細、支払→購入商品明細、支払方法→支払方法明細の4本です。公式解答例では、次の8本のリレーションシップ（いずれも1対多で、矢印の先が多側）を追加しています。

①店舗→クーポン設定対象店舗：関係“クーポン設定対象店舗”が外部キー{加盟企業コード、 店舗コード}で店舗を参照するため。

②クーポン設定→クーポン設定対象店舗：店舗を限定したクーポンでは1つのクーポン設定に複数の対象店舗があり、関係“クーポン設定対象店舗”がクーポンコードでクーポン設定を参照するため。この関連を描き忘れると、クーポンと対象店舗の対応が表現できません。

③クーポン設定→クーポン配布：関係“クーポン配布”がクーポンコードでクーポン設定を参照するため。

④会員→クーポン配布：関係“クーポン配布”が会員コードで会員を参照するため。

⑤クーポン設定→クーポン利用：関係“クーポン利用”がクーポンコードでクーポン設定を参照するため。

⑥支払→クーポン利用：関係“クーポン利用”がレシート番号で支払を参照するため。

⑦店舗→支払：設問1(1)のa・bのとおり、支払が{加盟企業コード、 店舗コード}で店舗を参照するため。

⑧ポイント設定→支払方法：設問1(1)のeのとおり、支払方法がポイント設定コードでポイント設定を参照するため。

関係“加盟企業”やクーポン設定対象商品などは図1に表示されていないので、考慮しません。採点講評では、正答率はやや高かったものの、店舗と支払のリレーションシップを記入できていない解答が散見されたと指摘されています。会員の抽出条件「ある期間に特定の店舗を利用した」から、支払がどの店舗のものかを記録する必要があることを読み取れていないと考えられます。リレーションシップの根拠が複数の業務要件に分散していても読み取れるように、との注意も述べられています。

設問2(1)

公式解答例では、候補キーは{加盟企業コード、 加盟企業商品コード}と{加盟企業コード、 横断分析用商品コード}の2つです。1つ目は、本文に「加盟企業商品は、加盟企業コードと加盟企業商品コードで識別する」とあることから決まります。2つ目は、B社が同じ商品と判断したものに同じ横断分析用商品コードを設定するので、1つの加盟企業の中では横断分析用商品コードによって加盟企業商品が一意に決まることから導かれます。複数の加盟企業が同じ横断分析用商品コードを持つので、横断分析用商品コード単独は候補キーになりません。JANコードは再利用され、設定されない商品もあるので候補キーにはなりません。部分関数従属性は「あり」で、公式解答例では「加盟企業コード→加盟企業名」「加盟企業コード→契約開始日」「加盟企業コード→契約終了日」「横断分析用商品コード→横断分析用商品名」が例として挙げられています（答案にはこのうち1つを書きます）。推移的関数従属性も「あり」で、公式解答例の具体例は{加盟企業コード、 加盟企業商品コード}→横断分析用商品コード→横断分析用商品名です。採点講評では、候補キーを「横断分析用商品コード」単独で答える誤りや、推移的関数従属性で「横断分析用商品コード→横断分析用商品名」の部分しか書けていない誤りが多かったと指摘されています。属性3に当たる候補キーから書き始めることが要点です。

設問2(2)

公式解答例では、主キーとして採用できない候補キーは{加盟企業コード、 横断分析用商品コード}です。理由は「横断分析用商品コードは加盟企業商品が登録された後に設定される場合があるから」です。根拠は本文3.(2)③の「横断分析用商品コードの設定には、加盟企業商品の登録から数日を要する場合がある」という記述です。登録時点では横断分析用商品コードがNULLになり得るので、NULLを許さない主キーには使えません。45字以内の答案では、「横断分析用商品コード」「加盟企業商品の登録後に設定される（登録時は未設定の場合がある）」という2つの要素を押さえます。

設問2(3)

公式解答例では、正規形は「第1正規形」です。設問2(1)のとおり、候補キーの一部である加盟企業コードに加盟企業名・契約開始日・契約終了日が従属し、横断分析用商品コードに横断分析用商品名が従属する部分関数従属性があるので、第2正規形を満たしません。公式解答例の第3正規形への分解は次の3つで、関係名には本文中の用語を使っています。

加盟企業（加盟企業コード〔実線〕、 加盟企業名、 契約開始日、 契約終了日）

加盟企業商品（加盟企業コード〔実線〕、 加盟企業商品コード〔実線〕、 横断分析用商品コード〔破線〕、 加盟企業商品名、 JANコード）

横断分析用商品情報（横断分析用商品コード〔実線〕、 横断分析用商品名）

加盟企業商品の主キーは、設問2(2)で主キーに採用できないとした候補キーではなく、{加盟企業コード、 加盟企業商品コード}です。横断分析用商品コードは横断分析用商品情報を参照する外部キーなので破線の下線になります。加盟企業コードは加盟企業を参照する外部キーでもありますが、主キーの一部なので、公式解答例では実線の下線だけが付いています。

設問3(1)

公式解答例は「購入の翌日以降にポイントの後付けをしたとき」です。日次バッチは午前0時を過ぎると、「支払日時が前日の分」だけを抽出して加算します。一方、ポイントの後付けは1か月以内なら可能で、その付与ポイントの記録は「レシートが発行された日時の記録となる」とされています。購入の翌日以降に後付けをすると、記録を作成した時点で支払日時はすでに前日より前になっています。そのため、その後のどの日次バッチでも抽出対象にならず、ポイント残高に加算されません。30字以内の答案では、本文中の用語「ポイントの後付け」を使い、「購入（支払）の翌日以降」という時期の条件を入れることが要点です。

設問3(2)

公式解答例では、追加属性の役割として「ポイント残高に加算済みかどうかを判別する」「ポイント残高への加算処理日が分かるようにする」「付与ポイントの記録を作成した日で抽出できるようにする」の3つが示されています。問題の原因は、抽出条件が支払日時だけであることです。そこで、加算済みフラグや加算処理日を持たせて未加算の記録を抽出する方法、または記録の作成日を持たせて前日に作成された記録を抽出する方法をとれば、後付け分も漏れなく加算できます。30字以内の答案では、追加する属性が日次バッチで未加算の付与ポイントを正しく抽出するために何を判別・記録するのかを1つ明確に書きます。

学習のポイント

「識別する」「対応付ける」などの記述から主キー・外部キーとリレーションシップを導く読解力、候補キーと関数従属性に基づく正規化の手順、NULLになり得る属性は主キーにできないという原則、バッチ処理の抽出条件の抜けを見抜く観点を重点的に学習してください。