平成28年度春期 データベーススペシャリスト 午後Ⅰ 問1

業務ルールと従属関係を照合する視点

データモデルの設計を検討するときは、業務の記述に書かれた決まりごとを一つずつ取り出し、それが属性やエンティティ間の結び付きにどう反映されているかを照合する姿勢が役立ちます。記述と構造のずれに注目しましょう。

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

確認すべき観点は一つ、「業務上の決まりごとと、ある項目の値が何によって一意に決まるかという対応」です。記述から読み取れる決まりを根拠に、構造の妥当性を自分で判断する練習をしてください。

視点

設計の良し悪しは、構造だけを眺めても判断しにくいものです。業務の記述にある「何が決まれば何が一つに定まるか」という決まりごとを手掛かりにすると、項目の置き場所や結び付きの妥当性を落ち着いて検討できます。思い込みや一般常識ではなく、記述に書かれている内容を根拠にすることが大切です。

確認ポイント

• 業務の記述から「何が決まれば何が一つに定まるか」を表す決まりごとを抜き出したか
• 抜き出した決まりごとが、検討対象の構造で矛盾なく表現できるかを自分で照合したか
• 判断の根拠が一般常識ではなく、業務の記述の中の文言に基づいているかを確かめたか

解説

C社の駐車場管理システムを題材に、関係“周辺施設”の候補キー・関数従属性・正規化、図4の空欄の属性と主キー・外部キー、図3の概念データモデルに引くリレーションシップ（二つのスーパータイプ／サブタイプを含む）、ポイント交換で複数の付与年月のポイントを消費する場合に記録できない不具合と、その解決策となる関係“ポイント消費”の設計を問う問題です。

問題の全体像

C社は月極駐車場と時間貸駐車場を運営しており、駐車場管理システムの再構築にあたってN君がデータベースを設計しています。問題文には、駐車場の区分、会員とポイントの付与・交換、Webサイトの施設・エリア・カテゴリ・周辺施設が説明されています。そのうえで、未完成の概念データモデル（図3）、関係スキーマ（図4）、属性の意味・制約（表1）が示されています。出題趣旨では、業務要件を分析する能力と、関数従属性・正規化理論などの基礎知識を用いてデータモデルを分析する能力を問うとしています。採点講評では、関係スキーマや属性名を答える設問で、主キーの実線の下線や外部キーの破線の下線を付けていない解答が目立ったと指摘されています。下線の付け方まで正確に答えることが大切です。

設問1(1)

関係“周辺施設”は、駐車場と施設の多対多の対応を表す関係です。〔Webサイトの概要〕(4)によると、一つの駐車場には複数の周辺施設があり、一つの施設が複数の駐車場の周辺施設になることもあります。そのため、施設IDと駐車場IDの組で一つの組が一意に決まります。さらに表1には「経度及び緯度を組み合わせた位置上に、駐車場又は施設が複数存在することはない」とあるので、施設経度と施設緯度の組合せでも施設が一意に決まります。したがって、公式解答例では候補キーを{施設ID，駐車場ID}と{施設経度，施設緯度，駐車場ID}の二つとしています。採点講評によると、前者だけを挙げた解答が多かったとのことです。部分関数従属性は「あり」です。施設IDだけで施設名、カテゴリコード、施設エリアコードが決まり、公式解答例では例えば「施設ID→施設名」を挙げています。推移的関数従属性も「あり」です。施設IDからカテゴリコードが決まり、そのカテゴリコードからカテゴリ名が決まるので、公式解答例では「施設ID→カテゴリコード→カテゴリ名」を例にしています。

設問1(2)

周辺施設には、候補キーの一部である施設IDに対する部分関数従属性があるため、第2正規形を満たしません。公式解答例では、正規形を「第1正規形」としています。第3正規形に分解した関係スキーマは、公式解答例では次の三つです。①カテゴリ（カテゴリコード，カテゴリ名）：主キーはカテゴリコード（実線の下線）です。②施設（施設ID，施設名，施設経度，施設緯度，カテゴリコード，施設エリアコード）：主キーは施設ID（実線の下線）です。カテゴリコードはカテゴリを参照する外部キー、施設エリアコードはエリアを参照する外部キーで、どちらも破線の下線を付けます。③周辺施設（駐車場ID，施設ID，所要時間）：主キーは{駐車場ID，施設ID}で、両方に実線の下線を付けます。所要時間は駐車場から周辺施設までの徒歩の時間なので、駐車場と施設の組によって決まり、この関係に残ります。部分関数従属は施設への分離で、推移的関数従属（カテゴリコード→カテゴリ名）はカテゴリへの分離で解消されます。

設問2(1)

公式解答例の内容を空欄ごとに整理します。a（関係“駐車場”）は「駐車場エリアコード」で、破線の下線（外部キー）を付けます。問題文に「駐車場及び施設は、いずれか一つのエリアに属する」とあるので、エリアを参照する外部キーが必要です。名前は周辺施設の「施設エリアコード」にそろえています。b・c（関係“月極駐車場契約”）は「駐車場ID」と「会員ID」で、どちらも破線の下線（外部キー）を付けます。公式解答例では順不同です。契約は契約番号で一意に識別されるため主キーには含まれず、どの月極駐車場の契約か、どの会員の契約かを表す参照用の属性になります。d（関係“月極駐車場利用”）は「ポイント付与フラグ」で、下線は付けません。問題文には、ポイントを付与すると基となった支払データに付与済みであることが記録されるとあります。表1のポイント付与フラグは「支払額に対してポイント付与済みかどうかを識別するフラグ」で、会員時間貸駐車場利用には既にあるのに、月極駐車場利用にはないため、ここに入ります。主キーでも外部キーでもありません。e（関係“会員時間貸駐車場利用”）は「会員ID」で、破線の下線（外部キー）を付けます。主キーは{駐車場ID，利用連番}で決まっているので、会員IDは、会員カードを提示して支払った会員を表す、主キーに含まれない外部キーです。

設問2(2)

公式解答例のリレーションシップは次のとおりです。〔1対多〕①会員―月極駐車場契約、②月極駐車場―月極駐車場契約、③月極駐車場契約―月極駐車場利用、④会員―会員時間貸駐車場利用、⑤時間貸駐車場―時間貸駐車場利用、⑥時間貸駐車場―時間帯別料金パターン、⑦時間帯別料金パターン―時間帯別料金設定曜日。〔スーパータイプ／サブタイプ〕⑧駐車場をスーパータイプ、月極駐車場と時間貸駐車場をサブタイプとする関係、⑨時間貸駐車場利用をスーパータイプ、会員時間貸駐車場利用をサブタイプとする関係。①②④は、図4の外部キー（b・c・e）が参照する向きから読み取れます。⑥は時間帯別料金パターンの主キーに駐車場IDが含まれていること、⑦は時間帯別料金設定曜日の主キーが{駐車場ID，パターン番号，曜日区分}で、パターン側の主キーを含んでいることから導けます。時間帯別料金設定曜日は、駐車場と直接ではなく、時間帯別料金パターンを経由して結び付く点に注意してください。⑧は駐車場区分と、月極駐車場・時間貸駐車場の主キーが駐車場IDと同じであることから、⑨は会員時間貸駐車場利用の主キー{駐車場ID，利用連番}が時間貸駐車場利用と同じで、会員が利用した場合だけ存在することから判断します。採点講評でも、⑨の時間貸駐車場利用と会員時間貸駐車場利用のリレーションシップを誤った解答が目立ったと指摘されています。

設問3(1)

図4の関係“ポイント交換”（会員ID，交換年月日，付与年月，商品コード，数量）では、付与年月が一つの交換につき一つしか持てません。一方、問題文には、交換すると付与年月が古いポイントから順に消費されるとあります。図1・図2の例では、2015-06-10に300ポイントを交換した際、2015-04の250ポイントと2015-05の50ポイントを合わせて消費しています。公式解答例の要点は「複数の付与年月のポイントを合算してポイント交換を行う場合、ポイントを消費したことを記録できない」ことです。50字以内に収めるには、①複数の付与年月にまたがって合算して交換する場合、②付与年月ごとの消費を記録できない、の2点を押さえます。

設問3(2)(a)

公式解答例では、削除する属性を「付与年月」としています。ポイント交換と付与年月の対応は1対多になりうるため、ポイント交換の単一の属性としては持てません。この対応は、新しく追加する関係“ポイント消費”に移します。

設問3(2)(b)

公式解答例では、関係“ポイント消費”の属性を「会員ID、付与年月、交換年月日、消費ポイント」の4列としています。会員ID・付与年月・交換年月日の3属性すべてに実線の下線を付け、{会員ID，付与年月，交換年月日}を主キーとします。消費ポイントには下線を付けません。この関係は、どの交換（会員ID＋交換年月日）で、どの付与分（会員ID＋付与年月）のポイントを何ポイント消費したかを表します。{会員ID，付与年月}はポイント付与を、{会員ID，交換年月日}はポイント交換を参照する役割を持ちますが、どちらも主キーを構成する属性なので、公式解答例では実線の下線で示されています。表2は5列ありますが、公式解答例で使っているのは4列です（問題文にも、全て埋まるとは限らないとあります）。消費ポイントは、図2の注記の用語「消費ポイント」をそのまま使っています。

設問3(2)(c)

図1・図2の値を、付与年月の古い順に消費する規則に当てはめます。2015-06-10の交換（300ポイント）では、2015-04の250ポイントをすべて使い、残り50ポイントを2015-05から使います。2015-07-20の交換（100ポイント）では、2015-05に残っている10ポイントを使い、残り90ポイントを2015-06から使います。公式解答例では、次の4行としています（会員IDはすべてT1234567）。2015-04／2015-06-10／250、2015-05／2015-06-10／50、2015-05／2015-07-20／10、2015-06／2015-07-20／90。検算すると、2015-05は60−50−10＝0、2015-06は250−90＝160となり、図1の残ポイントと一致します。採点講評でも、具体的な値と照らし合わせて確認するよう求めています。

学習のポイント

表1の一意性に関する制約から候補キーを漏れなく見つけること、主キーと外部キーを下線で正確に書き分けること、主キーが同じ関係のあいだのスーパータイプ／サブタイプを見抜くこと、そして1対多の対応を具体的な値で確かめることが重点です。