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

模範解答

解説

解答の論理構成

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

誤りやすいポイント

• “施設ID は一意だからキーは施設ID だけで良い”と早合点し、駐車場との多対多を失念する。
• “経度＋緯度が一意”の記述を読み飛ばし第２の候補キーを落とす。
• “カテゴリ名”の依存関係を部分従属と誤答（正しくは推移従属）。
• 部分従属の表記で { 施設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正規形が達成されます。

誤りやすいポイント

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

FAQ

模範解答

解説

解答の論理構成

1. (a) の決定
   • 引用: “駐車場及び施設は,いずれか一つのエリアに属する。”
   • 駐車場が属するエリアを示す列が必要。表1に “エリアコード” が提示されているため、駐車場表には “駐車場エリアコード” を追加し外部キーにするのが自然。
2. (b)(c) の決定
   • 引用: “契約期間を定め…契約は、会員がC社に契約書類を送付して…初めて成立する。”
   • 契約は “月極駐車場”（=駐車場）と “会員” の組合せで一意。よって “駐車場ID” と “会員ID” を外部キーとして格納。
3. (d) の決定
   • 引用: “ポイントが付与されると, ポイント付与の基となった支払データに対して, ポイント付与済みであることが記録される。”
   • 月極駐車場利用には支払額があるため、当該行に “ポイント付与フラグ” を置き、付与済み状態を保持。
4. (e) の決定
   • 引用: “会員は,毎月の支払額(時間貸駐車場で会員カードを提示して支払った支払額+… )”
   • 時間貸しの場合、利用レコードは非会員利用も含むため、会員である場合のみ関連付けが必要。橋渡し表 “会員時間貸駐車場利用” に “会員ID” を持たせ、元の利用連番と組み合わせて主キー／外部キーとする。

誤りやすいポイント

• 駐車場表に “エリアコード” でなく “エリア名” を入れてしまう（正規化違反）。
• (b)(c) の順序を固定と誤認し、片方だけ下線を忘れる。
• “ポイント付与フラグ” をポイント付与表へ入れてしまい、支払レコード側との紐付けが取れなくなる。
• “会員時間貸駐車場利用” に “会員ID” が既にあると勘違いし、別の属性を探してしまう。

FAQ

模範解答

解説

解答の論理構成

1. 契約主体の特定
   【問題文】月極駐車場では「契約は、会員がC社に…成立する」とあり、契約と会員は一対多です。関係スキーマでも「月極駐車場契約（契約番号, (b)）」の(b)が“会員ID”であることから、リレーションシップ①「会員―月極駐車場契約」が確定します。
2. 月極関連の流れ
   月極駐車場契約は駐車場側に帰属するため、図4の外部キー(c)＝“駐車場ID”より②「月極駐車場―月極駐車場契約」。さらに支払い明細を表す「月極駐車場利用」が契約番号を持つので③「月極駐車場契約―月極駐車場利用」が派生します。
3. 駐車場のIS-A
   【問題文】「駐車場は, 月極駐車場と時間貸駐車場のいずれかに分類される。」とあり、図3でも三角記号で表されているため④「駐車場⇢月極駐車場」「駐車場⇢時間貸駐車場」のIS-A関係を採用します。
4. 時間貸し利用のパス
   時間貸し精算は駐車場単位で行われるので、外部キー「時間貸駐車場利用（駐車場ID, 利用連番, …）」より⑤「時間貸駐車場―時間貸駐車場利用」。
5. 会員による時間貸し利用の枝分かれ
   「会員でなくても利用することができる。」が前提ですが、会員カード提示時のみ別途ポイント管理を行うため、図3では時間貸駐車場利用を汎化／特化して「会員時間貸駐車場利用」を派生させています。したがって⑥「時間貸駐車場利用⇢会員時間貸駐車場利用」。あわせて会員IDを保持するので⑦「会員―会員時間貸駐車場利用」。
6. 料金パターンと曜日
   【問題文】「料金は, “月〜金 8:00〜22:00 100 円/20分” のように, 曜日ごと時間帯ごとに定められている。」図4では「時間帯別料金パターン」と「時間帯別料金設定曜日」に分け、前者が駐車場IDの外部キー、後者が（駐車場ID, パターン番号）の外部キーを持つため⑧「時間貸駐車場―時間帯別料金パターン」、⑨「時間帯別料金パターン―時間帯別料金設定曜日」で完結します。

誤りやすいポイント

• 「会員時間貸駐車場利用」を独立した利用実績と誤解し、二重に駐車場を参照させる
• 駐車場から月極／時間貸を単なる１対多と考え、IS-Aを描かない
• 「時間帯別料金設定曜日」→「時間帯別料金パターン」の従属方向を逆にしてしまう
• 月極契約と月極利用の順序（契約→利用）を逆に描く

FAQ

模範解答

解説

解答の論理構成

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

誤りやすいポイント

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

FAQ

模範解答

解説

解答の論理構成

1. 不具合の確認
   【問題文】「付与年月が古いポイントから順に消費される」ため、1 回の交換で複数の “付与年月” が関与する。現行 “ポイント交換（会員ID, 交換年月日, 付与年月, 商品コード, 数量）” では “交換年月日” が重複し更新・削除時異常が発生。
2. 削除すべき属性
   重複の原因は “付与年月” なので (a) は “付与年月”。
3. 新関係の構築
   交換と付与を N:M で結び付ける橋渡し表として “ポイント消費” を追加。
   ・交換側 FK： (会員ID, 交換年月日) ― “ポイント交換”
   ・付与側 FK： (会員ID, 付与年月) ― “ポイント付与”
   ・消費量を保持： “消費ポイント”
   ・一意性確保： (付与年月, 交換年月日, 消費ポイント) を主キーとし、会員ID は外部キーのみに留める。
4. 具体データ展開
   【図1】【図2】より各交換での消費内訳を計算。
   ・2015-06-10 に 300pt 消費 → 2015-04 から 250pt、2015-05 から 50pt
   ・2015-07-20 に 100pt 消費 → 2015-05 から 10pt、2015-06 から 90pt
   これを “ポイント消費” 4 行に分解。

誤りやすいポイント

• “消費ポイント” を “ポイント交換” に残してしまい、依然として重複行が発生する。
• 主キーに “会員ID” を含め忘れ、外部キー制約と整合しない設計にしてしまう。
• 「古いポイントから消費」というビジネスルールを ER 設計の必須要件と取り違え、物理的に順序属性を追加してしまう。

FAQ