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

解答の論理構成

1. 周辺施設関係の構造把握
   • 施設⇔駐車場が多対多で結合し、関係に “所要時間” などの関係属性を持つ。
2. 施設側の一意性
   • 【表1】より「施設緯度、施設経度…経度及び緯度を組み合わせた位置上に、駐車場又は施設が複数存在することはない。」
   • したがって (施設経度、施設緯度) または 施設IDが施設の候補キー。
3. 周辺施設レコードの一意化
   • 【問題文】①より同一施設が複数の駐車場にひも付く。施設識別子だけでは関係行が重複するため、駐車場IDを加える必要がある。
   • ゆえに候補キーは
     { 施設ID, 駐車場ID } と { 施設経度、施設緯度、駐車場ID }。
4. 部分関数従属性の判定
   • 候補キーが複合で、その一部 施設IDにのみ従う属性がある。例：
     施設ID → 施設名、施設ID → カテゴリコード、施設ID → 施設エリアコード
   • よって“あり”。
5. 推移的関数従属性の判定
   • さらに 施設ID → カテゴリコード かつ カテゴリコード → カテゴリ名 が成り立つ。
   • これは 施設ID → カテゴリコード → カテゴリ名 の推移従属であり“あり”。
6. 以上より模範解答の内容を導出できる。

誤りやすいポイント

• “施設IDは一意だからキーは施設IDだけで良い”と早合点し、駐車場との多対多を失念する。
• “経度＋緯度が一意”の記述を読み飛ばし第2の候補キーを落とす。
• “カテゴリ名”の依存関係を部分従属と誤答（正しくは推移従属）。
• 部分従属の表記で { 施設ID, 駐車場ID } → 施設名 と書き、部分従属であることを示せていない。

FAQ

解答の論理構成

1. 主キーの把握
   【問題文】の関係 “周辺施設” には “駐車場ID, 施設ID, 所要時間” が含まれます。駐車場と施設の組み合わせごとに “所要時間” が一意に決まるため、 主キー候補＝“駐車場ID＋施設ID” です。
2. 部分関数従属の確認（2NF判定）
   “施設ID → 施設名、施設緯度、施設経度、施設エリアコード、カテゴリコード” が成り立ち、これらは主キーの一部（施設ID）のみに従属します。
   よって 第2正規形を満たさない。
3. 推移関数従属の確認（3NF判定）
   さらに “カテゴリコード → カテゴリ名” があり、非キー属性間での推移従属が発生します。従って 第3正規形も未達 となります。
4. 分解手順
   (i) “施設ID” が決める属性を切り離し、関係 施設 を生成
   (ii) “カテゴリコード → カテゴリ名” を分離し、関係 カテゴリ を生成
   (iii) 残りを “周辺施設” とし、主キーを “駐車場ID＋施設ID” に保持
5. 外部キー定義
   ・施設.カテゴリコード は カテゴリ.カテゴリコード への外部キー
   ・施設.施設エリアコード は エリア.エリアコード への外部キー（【問題文】“駐車場及び施設は、いずれか一つのエリアに属する。”）
   ・周辺施設.施設IDは 施設.施設IDへの外部キー
   ・周辺施設.駐車場IDは 駐車場.駐車場IDへの外部キー
   以上で全属性が主キーに完全従属し、非キー属性間の推移従属が排除され、第3正規形が達成されます。
6. 正規形の結論
   分解前の “周辺施設” は、全ての属性が単一値をとるので第1正規形ですが、手順2の部分関数従属があるので第2正規形ではありません。したがって解答は「第1正規形」です。

誤りやすいポイント

• “カテゴリ名” を独立させず 施設 の列に残してしまい推移従属を温存する
• 主キーを誤って “駐車場ID＋施設ID＋カテゴリコード” としてしまい、部分従属を見落とす
• 所要時間 が “駐車場ID＋施設ID” に従属することを忘れ、別表に分けてしまう
• 外部キーを明示せず減点される

FAQ

解答の導き方

• 図4の駐車場には (a) の空欄があります。問題文には「駐車場及び施設は、いずれか一つのエリアに属する」とあり、エリアを一意に識別するのは「エリアコード」です。
• したがって、駐車場表はエリアを参照する属性を持つ必要があります。表の属性名として明示的に「駐車場」と結びつく名前にするのが分かりやすいため、(a) を 駐車場エリアコード とします。
• この属性はエリア表の主キー（エリアコード）を参照する外部キーなので、外部キーとして破線の下線を付けます。
• 回答：(a) 駐車場エリアコード（外部キー）

• 図4の月極駐車場契約には (b),(c) の空欄があります。問題文の月極駐車場の説明に「利用できるのは、契約した会員だけである」や「契約は、会員がC社に契約書類を送付して初回の月額利用料金を振り込み、C社がそれらの内容を確認して、初めて成立する」とあり、契約は「どの駐車場」と「どの会員」を結びつける情報であることが分かります。
• 図4に既に契約を一意に識別する属性として「契約番号」があるため、(b),(c) は駐車場と会員を参照する外部キーでよいです。属性名は図4で使われている名称をそのまま使います。
• 回答（順不同）：(b) 駐車場ID（外部キー）、(c) 会員ID（外部キー）

• 図4の月極駐車場利用の最後の属性が (d) です。問題文に「ポイントが付与されると、ポイント付与の基となった支払データに対して、ポイント付与済みであることが記録される」とあり、支払データにポイント付与済みかどうかを示す属性が必要であることが明示されています。表1にも「ポイント付与フラグ」が意味・制約としてあります。
• したがって (d) は ポイント付与フラグ です（通常の属性で、ここでは主キー・外部キーには該当しません）。
• 回答：(d) ポイント付与フラグ

• 図4に「時間貸駐車場利用（駐車場ID, 利用連番, …）」と「会員時間貸駐車場利用（駐車場ID, 利用連番, (e), ポイント付与フラグ）」が並んでいます。時間貸駐車場利用は会員でない利用者の記録も含むため、利用の一意識別子は 駐車場ID＋利用連番 であり、この組が時間貸駐車場利用の主キーです。問題文にも「時間貸駐車場: 会員でなくても利用することができる」とあります。
• 会員として利用した場合にのみ追加情報を持つのが会員時間貸駐車場利用なので、会員の識別子である 会員IDを (e) に入れ、会員表の主キーを参照する外部キーとします。ただし会員時間貸駐車場利用の主キーは元の利用を一意に示す 駐車場ID＋利用連番 のままにしておき、会員IDは外部キーにとどめます（会員IDを主キーに含めると時間貸駐車場利用の参照整合性が保てなくなるため不適切）。
• 回答：(e) 会員ID（外部キー）
• 重要な明記：会員時間貸駐車場利用の主キーは 駐車場ID＋利用連番 のままにする（会員IDは外部キーのみ）。

• a：駐車場エリアコード（外部キー）
• b：駐車場ID（外部キー）
• c：会員ID（外部キー）
• d：ポイント付与フラグ
• e：会員ID（外部キー）

誤りやすいポイント

• 会員時間貸駐車場利用で会員IDを主キーに含める誤り
  • 時間貸駐車場利用の主キーは駐車場ID＋利用連番であり、会員時間貸駐車場利用はその利用に付随する情報を持つため、会員IDは外部キーで十分です。会員IDを主キーに含めると時間貸駐車場利用との参照が不整合になります。
• 駐車場とエリアの結びつきを忘れて (a) を空欄のままにする、あるいは不適切な名前（例：ただの「エリア名」）を入れる誤り
  • 図4に「エリア（エリアコード, エリア名）」があるので、駐車場側にはエリアコードを参照する属性が必要です。属性名は設計方針で変えてよいが、参照先がエリアコードであることを明確にすること。
• ポイント付与の扱いを誤る：ポイント集計表だけで支払データへのフラグを省略する誤り
  • 問題文は「ポイントが付与されると、 ポイント付与の基となった支払データに対して、 ポイント付与済みであることが記録される」と書いているので、支払データ側（月極駐車場利用や会員時間貸駐車場利用）にフラグが必要です。

FAQ

解答の論理構成

1. 会員と月極駐車場契約
   • 【問題文】では「② 契約は、会員がC社に契約書類を送付して…」とあり、契約が必ず会員に紐付くことが示されています。よって「会員―月極駐車場契約」のリレーションシップを設定します。
2. 会員と会員時間貸駐車場利用
   • 時間貸駐車場は「① 会員でなくても利用することができる」と記載されています。会員が利用した場合のみポイント付与判定を行うため、会員をキーにした「会員時間貸駐車場利用」エンティティが必要です。したがって「会員―会員時間貸駐車場利用」で接続します。
3. 駐車場と月極駐車場／時間貸駐車場
   • 駐車場は「月極駐車場と時間貸駐車場のいずれかに分類される」と明記されており、スーパタイプ／サブタイプ構造を組みます。「駐車場―月極駐車場」「駐車場―時間貸駐車場」の２本を張ります。
4. 月極駐車場と月極駐車場契約
   • 契約は特定の月極駐車場に対して締結される（空き車室数の管理が必要）ため「月極駐車場―月極駐車場契約」のリレーションが生じます。
5. 月極駐車場契約と月極駐車場利用
   • 利用実績は契約単位で記録されるので「月極駐車場契約―月極駐車場利用」で接続します。
6. 時間貸駐車場と時間貸駐車場利用
   • 【問題文】「② 利用者は、空いている車室に入庫し…」から各利用は駐車場単位で管理されるため「時間貸駐車場―時間貸駐車場利用」が必要です。
7. 時間貸駐車場利用と会員時間貸駐車場利用
   • 会員利用分だけを切り出してポイント処理を行う設計なので、全利用（親）と会員利用（子）の間で「時間貸駐車場利用―会員時間貸駐車場利用」を関連付けます。
8. 時間貸駐車場と時間帯別料金パターン
   • 【問題文】「③ 料金は…曜日ごと時間帯ごとに定められている」とあり、駐車場ごとに複数パターンを持つため「時間貸駐車場―時間帯別料金パターン」とします。
9. 時間帯別料金パターンと時間帯別料金設定曜日
   • 曜日区分はパターンを細分化する属性群で、「時間帯別料金パターン―時間帯別料金設定曜日」を結びます。

• 会員―月極駐車場契約
• 会員―会員時間貸駐車場利用
• 駐車場―月極駐車場
• 駐車場―時間貸駐車場
• 月極駐車場―月極駐車場契約
• 月極駐車場契約―月極駐車場利用
• 時間貸駐車場―時間貸駐車場利用
• 時間貸駐車場利用―会員時間貸駐車場利用
• 時間貸駐車場―時間帯別料金パターン
• 時間帯別料金パターン―時間帯別料金設定曜日

誤りやすいポイント

• 「会員時間貸駐車場利用」を独立エンティティではなく単なる属性拡張と見なしてしまい、会員とのリレーションを欠落させる。
• 月極契約と月極利用の関係を「駐車場」と直接結び付けてしまい、契約単位の履歴管理ができなくなる。
• 「曜日区分」を時間帯別料金の単なる属性と誤認し、専用エンティティを設けない。
• 駐車場とサブタイプ（月極／時間貸）の全体―部分構造を書き忘れ、整合性制約（排他制御）が弱まる。

FAQ

解答の論理構成

1. 業務要件の確認
   • 【問題文】「交換を行うと、付与年月が古いポイントから順に消費され、その内容が記録される。」
     ⇒ 交換1件で複数の “付与年月” が対象になり得る。
2. 現在の関係スキーマ
   • “ポイント付与（会員ID, 付与年月、付与ポイント）”
   • “ポイント交換（会員ID, 交換年月日、付与年月、商品コード、数量）”
     ⇒ 交換1件につき “付与年月” を1つしか保持できない。
3. 発生する不具合
   • 例）300pt消費で “2015-04”250ptと “2015-05”50ptを使うケース
     • “商品コード” と “数量” は1行にまとめたい。
     • しかし “付与年月” を2つ登録できず、どちらかが欠落。
   • 結果
     • “ポイント付与” 残高が正しく減算できない。
     • 画面例の「残ポイント」計算も不正確になる。
4. したがって
   「複数の付与年月のポイントを合算してポイント交換を行う場合、ポイントを消費したことを記録できない」ことが不具合である。

誤りやすいポイント

• 商品コードや数量があるから消費ポイントは算出可能と考え、付与年月の多重性を見落とす。
• 残ポイント列を“動的計算すれば良い”と誤解し、月別消費履歴の欠如を見逃す。
• “付与年月”をNULL可にすれば解決できると思い込むが、多対多構造の欠陥は残る。

FAQ

解答の導き方

• （a）関係“ポイント交換”から削除する属性は「付与年月」です。
• （b）関係“ポイント消費”の属性は 会員ID、付与年月、交換年月日、消費ポイント で、主キーは（会員ID、付与年月、交換年月日）です。
• （c）図1・図2の表示に対応する具体例は表の通りです。

1. 何を記録する必要があるかを本文から読み取る
   本文には「交換を行うと、付与年月が古いポイントから順に消費され、その内容が記録される」とあります。これは1回のポイント交換（交換年月日）で複数の「付与年月」にまたがってポイントが消費され得ることを意味します。従って、どの付与年月のポイントがどの交換でいくら消費されたかを、交換とは別に記録できる構造が必要です。
2. なぜ「付与年月」をポイント交換から削除するか（＝（a）の理由）
   図4には「ポイント交換（会員ID, 交換年月日, 付与年月, 商品コード, 数量）」とあり、現状ではポイント交換の行に「付与年月」が含まれています。しかし1回の交換が複数の付与年月を消費する場合、同じ交換（同一会員・同一交換年月日）の商品情報（商品コード・数量など）を複数行に複製する必要が出てきます。これはデータの冗長・更新異常を招くため、「付与年月」を移して別関係で消費の明細を持つ設計にする必要があります。したがって（a）は「付与年月」です。
3. 新関係“ポイント消費”の属性と主キーの決め方（＝（b）の理由）
   • 記録すべき内容は「どの会員の、どの付与年月のポイントが、どの交換（交換年月日）でいくら消費されたか」です。したがって属性は 会員ID、付与年月、交換年月日、消費ポイント となります。
   • 主キーに会員IDを含める理由：付与年月や交換年月日は会員ごとに意味を持つ日付であり、同じ日付が異なる会員に対して重複する可能性があるため、会員IDを含めて一意に識別する必要があります（ポイント付与の関係が「会員ID, 付与年月」で表されていることも根拠になります）。
   • 主キーに消費ポイントを含めない理由：消費ポイントは測定値（量）であり、識別子としての安定性や最小性を満たしません。主キーは最小かつ変更されにくい属性の組合せであるべきで、量的属性を含めるのは不適切です。
   • よって主キーは（会員ID、付与年月、交換年月日）とします。なお参照整合性としては、(会員ID, 付与年月) は ポイント付与 を参照し、(会員ID, 交換年月日) は ポイント交換 を参照することになります。

• 図1・図2より会員はT1234567、交換は2015-06-10に300ポイント、2015-07-20に100ポイント消費しています。本文にあるように「付与年月が古いポイントから順に消費される」ため、各交換で古い付与から順に引き落とします。
  2015-06-10の300ポイント消費の割当て：
  • 2015-04の付与250を全消費 → 消費250
  • 残り50を2015-05の付与60から消費 → 消費50（2015-05の残は10）
    2015-07-20の100ポイント消費の割当て：
  • まず古い残（2015-05の残10）を消費 → 消費10（2015-05の残0）
  • 残り90を2015-06の付与250から消費 → 消費90（2015-06の残160）

誤りやすいポイント

• 主キーに「消費ポイント」を含めてしまうケース：消費量は識別子にならないため誤りです。主キーは事象の識別に用いる属性で、量的属性を含めるべきではありません。
• 会員IDを主キーに含めない設計：付与年月や交換年月日は会員ごとに意味があるため、会員IDを外すと別会員の同日付データと衝突する可能性があります。
• 「付与年月」をポイント交換に残したままにして多行で表現する：同一交換の繰返し（商品コード・数量の重複）で冗長や更新異常を招きます。
• FIFOルールを適用せず最新付与から消費する誤配分：本文の「付与年月が古いポイントから順に消費される」を正確に実装すること。
• 交換を日付だけで識別してしまう：同一日付に同会員が複数回交換する可能性がある場合は、交換を一意に識別する追加情報（時刻や連番）を考慮する必要があります。

FAQ