データベーススペシャリスト 2013年 午後1 問02

解答の導き方

1. 問題文から分かることを整理する

• 本文には「“サービス” テーブルには、 セットサービス、 個別サービス及びオプションサービスの3種類のサービスが格納される」とあるので、サービスは3種類に分類され、それぞれ固有の属性を持ちうることが分かります。
• また「サービスは、サービスコードで一意に識別される」とあるため、サービス共通の主キーはサービスコードでよいことが分かります。
• 表1～表3の例を見ると、個別サービスは「サービス内容」「標準価格」「標準作業時間数」を持ち、セットサービスは「選択可能な個別サービス群」「選択可能数」「標準価格」を持ち、オプションサービスは「サービス内容」「適用可能な個別サービス」「標準価格」「標準作業時間数」を持つことが分かります（例えば本文に「個別サービスのサービス内容、標準価格、標準作業時間数は決まっている」「セットサービスに対して、選択可能な個別サービス群、選択可能数、標準価格は決まっている」「オプションサービスのサービス内容、適用可能な個別サービス、標準価格、標準作業時間数は決まっている」とある）。

1. 分割方針の論理的根拠

• 共通属性は一つのテーブルにまとめ、各種別固有の属性は別テーブルに分ける（正規化、冗長排除）。ここで共通属性として「サービスコード」「サービス区分」「サービス名」「標準価格」が妥当である。理由は各種別の例すべてに「サービス名」「標準価格」が登場し、サービスごとの識別子が「サービスコード」であるためです。
• 個別サービスとオプションサービスは双方で「サービス内容」「標準作業時間数」を持つが、オプションには「適用可能な個別サービス（個別サービスコード）」という依存先があるため、オプション専用テーブルを用意して個別サービスとの参照関係を明示する。
• セットサービスは「個別サービス選択可能数」などセット固有の情報を持つ。どの個別サービスが選択可能群に含まれるかは、図2に既にある“セット組合せ”テーブルで表されているので、この設問で新たに設計する必要はない。

1. 主キー／外部キーの扱い方（重要）

• 下位表（個別サービス、セットサービス、オプションサービス）のサービスコードは、それぞれの表の主キーであると同時に、サービス共通.サービスコード を参照する外部キーである（PK＝FK）。これにより「あるサービスコードがサービス共通に必ず1行あり、かつそのサービスはちょうど一つの下位表に属する」という1:1の関係を確実に表現できる。
• セットと個別サービスの対応（多対多）は、図2の“セット組合せ”テーブルがそのまま担う。

1. 設問の範囲

• 設問は“サービス”テーブルの分割だけを問うている。“セット組合せ”や“継続パターン”は図2のままでよい（継続パターンは設問1(2)(3) のとおり第3正規形である）。

• 主キー：サービスコード
• 注：すべてのサービス（セット／個別／オプション）に共通する属性を保持する。

• 主キー：サービスコード
• 外部キー：サービスコード → サービス共通.サービスコード（PK＝FK、1:1）
• 注：個別サービス固有の情報を保持する。

• 主キー：サービスコード
• 外部キー：サービスコード → サービス共通.サービスコード（PK＝FK、1:1）
• 注：セット固有の属性のみ保持し、構成する個別サービスは別表で管理する。

• 主キー：サービスコード
• 外部キー：サービスコード → サービス共通.サービスコード（PK＝FK、1:1）
• 外部キー：個別サービスコード → 個別サービス.サービスコード（オプションが適用できる個別サービスを参照、1:N）
• 注：本文にある「一つのオプションサービスを適用できる個別サービスは、一つだけである」を反映し、個別サービスコードは単値で参照する。

• セットサービスで選択可能な個別サービスの組合せは、図2の“セット組合せ”テーブルで管理する（分割後は、セットサービスコードがセットサービスを、個別サービスコードが個別サービスを参照する）。

誤りやすいポイント

• 下位表（個別／セット／オプション）のサービスコードを単純に主キーにするだけで、サービス共通との参照関係（PK＝FKの1:1）を書き落とす。必ず「下位表のサービスコードはサービス共通.サービスコード を参照する外部キーでもある」と明示する。
• セットと個別の対応を表すテーブルで、どちらの列がどちらのテーブルを参照するかを誤る（両方ともセットサービスを参照するような誤り）。セット組合せではセットサービスコード→セットサービス、個別サービスコード→個別サービス にそれぞれ参照させること。
• オプションサービスの「適用個別サービス」は単一の個別サービスを参照する点を見落とし、誤って多値扱いにする。本文に「一つのオプションサービスを適用できる個別サービスは、一つだけである」とある。
• 設問の範囲外の“継続パターン”まで分割してしまう。継続パターンは ｛期間、回数、曜日｝ を候補キーとする第3正規形であり（設問1(2)(3)）、分割は不要。
• サービス共通に「サービス内容」や「標準作業時間数」を入れてしまうと、セットサービスでNULLが多発する設計になる（セットはこれらを持たない場合がある）。固有属性は下位表へ。

FAQ

解答の論理構成

1. テーブル属性の整理
   “継続パターン” テーブルは【図2】に示す
   「継続パターンコード」「期間」「回数」「曜日」「継続割引率」
   の5列で構成されています。
2. 一意性を示す業務記述
   【問題文】4.(2)② に
   「期間、回数、曜日の組合せは、継続パターンコードで識別される。」
   とあるため、 ・継続パターンコード は当然一意（候補キー①）。
   ・逆に 期間＋回数＋曜日 も1行を一意に決定（候補キー②）。
3. 他列はキーにならない
   継続割引率 は割引率を示す属性であり、同じ割引率が複数行に出現し得るため一意性を担保しません。
4. よって候補キーは2つ
   ① 継続パターンコード
   ② {期間、回数、曜日}

誤りやすいポイント

• 継続割引率 を含めて {期間、回数、曜日、継続割引率} としてしまう。
• 「識別される」という文言を見落とし、複合キーを導けない。
• 主キーと候補キーの違いを混同し、「主キーは1つだから候補キーも1つ」と誤解する。

FAQ

解答の論理構成

1. 候補キーの確認
   “継続パターン” テーブルの列は 継続パターンコード、期間、回数、曜日、継続割引率 です（図2は未完成で下線はありません）。設問1(2) のとおり、候補キーは「継続パターンコード」と ｛期間、回数、曜日｝ の二つです。継続パターンは期間・回数・曜日の組合せごとに一つ定義され、継続パターンコードで識別されるからです。
2. 関数従属性の列挙
   継続パターンコード → 期間、回数、曜日、継続割引率
   ｛期間、回数、曜日｝ → 継続パターンコード、継続割引率
3. 第1正規形の判定
   すべての列は原子値で繰返し項目も無いので第1正規形を満たします。
4. 第2正規形の判定
   非キー属性は継続割引率だけです。継続割引率は ｛期間、回数、曜日｝ の一部（例えば期間と回数だけ）で決まるわけではなく、組合せ全体で決まるので、部分関数従属性はありません。
5. 第3正規形の判定
   非キー属性は継続割引率の一つだけなので、非キー属性を経由した推移的関数従属性はありません。よって第3正規形です。
6. まとめ
   ・正規形：第3正規形
   ・部分関数従属性：なし
   ・推移的関数従属性：なし

誤りやすいポイント

• 「複合キーかどうか」を確認せずに自動的に部分関数従属性があると決めつける。
• ｛期間、回数、曜日｝ も候補キーであることを見落とし、継続パターンコードだけで判定してしまう。
• 非キー属性同士を “常識的に関連がありそう” という感覚で結び付け、推移的関数従属性と誤認する。

FAQ

解答の導き方

1. セットサービス と 個別サービス の関係を決める根拠

• 問題文に「セットサービスとは、個別サービスを複数組み合わせたもので」とあり、セットは個別サービスの集合であることが明示されています。
• 図1の見積り例では、セットサービス（例：水まわり３点セット）に続いて「◆」で示された個別サービスの注文明細が並んでおり、セット注文明細の下に複数の個別注文明細が対応している表示になっています（図1ではセットの直下に個別がぶら下がっている）。
  これらから、1つのセットサービス 注文明細に対して対応する個別サービス 注文明細は「1件以上」である必要があることが分かります（少なくとも1件の個別注文明細がなければセットとして成立しないため）。よってセット→個別の個別側の多重度は1..* です。
  一方、個別サービスは単品で選択されることがあり「一つの注文で、複数のサービスを指定できる。また、個別サービスとセットサービスを一緒に指定することもできる」とあるため、ある個別サービス注文明細が「セットに属する」かどうかは任意です（属する場合はそのセットの一部、属さない場合は単品）。したがって個別側から見たセット側への参照は0..1（「属さない（0）」か「あるセットに属する（1）」）になります。
  （注：サービス定義レベルで「一つの個別サービスは、複数の異なるセットサービスに組み込まれることがある」とあるのはカタログ設計上の関係であり、注文内の注文明細レコードが同一注文で複数のセットに同時に属することを意味しない点に注意します。）

1. 個別サービス と オプションサービス の関係を決める根拠

• 問題文に「オプションサービスとは、会員の依頼によって個別サービスに追加する清掃で、単独では提供しない。」および「一つのオプションサービスを適用できる個別サービスは、一つだけである。」「一つの個別サービスに対して、複数のオプションサービスを追加できる。」とあります。
  これらから、オプション注文明細は必ずどこかの個別サービス注文明細に紐づく必要があり（単独では存在しない）、その紐づき先は「ちょうど1件」であるためオプション側の多重度は1..1です。逆に、ある個別サービス注文明細はオプションを付けないこともあり得る一方で複数付けられるため、個別側から見たオプション側の多重度は0..* となります。図1の例（個別の下に「＞＞」で示されたオプションがぶら下がっている）もこの構造を裏付けます。

1. 図3へ記入する最終的な多重度（言葉での記述）

• セットサービス 注文明細 — 個別サービス 注文明細
  • 1つのセットサービス 注文明細 に対応する個別サービス 注文明細 は1以上（1..*）
  • 1つの個別サービス 注文明細 が所属するセットサービス 注文明細 は0または1（0..1）
• 個別サービス 注文明細 — オプションサービス 注文明細
  • 1つの個別サービス 注文明細 に対応するオプションサービス 注文明細 は0以上（0..*）
  • 1つのオプションサービス 注文明細 は必ず1つの個別サービス 注文明細 に対応する（1..1）

誤りやすいポイント

• 「セットサービスは複数の個別サービス」を見て、個別側の最小値を0としてしまう誤り。セットが存在する以上、そのセットに対応する個別注文明細は少なくとも1件必要です（表現は通常1..*）。
• 「一つの個別サービスは複数のセットに組み込まれることがある」というカタログ記述を、注文内の注文明細間で「個別注文明細が同時に複数のセット注文明細に属する」と誤解する点。ここでの「一つの個別サービス」はサービス種別（カタログ上の概念）であり、注文内の注文明細レコードは一意で、同時に複数のセットに属することは想定しません。
• オプションをオプション側から「0..1」にしてしまう誤り。オプションは「単独では提供しない」ため、オプション注文明細は必ずどこかの個別注文明細に紐づき、個別側への参照は必須（1）。
• 図に記入する記号（ゼロを含むか否か）を曖昧にしてしまう点。設問は「ゼロを含むか否か」を区別することを求めているので、必ず0または1の最小値を明示してください。

FAQ

解答の導き方

1. 要件の整理
   問題文の設計方針に「セットサービスで選択された個別サービスも、単品で選択された個別サービスも、 “注文明細” テーブルには同様の形式で格納する。」とあります。これにより、セットで選んだ個別サービスも個別に選んだ個別サービスも同一の注文明細テーブルの行で扱う必要があると分かります。さらに見積りの説明に「見積り結果は、セットサービスで選択した個別サービス、及び個別サービスに追加したオプションサービスの対応関係が分かるように、サービス名の前に記号を付け、関係するサービスを並べて表示する。」とあり、セット→個別、個別→オプションという親子関係を保持する必要があることが明確です。
2. 図からの重要な前提（参照方法の決定）
   図2を見ると、注文明細の主キーは ｛注文番号, 注文明細番号｝ です（両方に下線）。親子関係にある明細（セットサービスとそれから選択された個別サービス、個別サービスとそれに追加されたオプションサービス）は、いずれも同じ注文の明細なので、親行の注文番号は子行の注文番号と同じです。したがって、親行を指すには、親行の「注文明細番号」を格納する列を追加すれば足ります。
3. 列の意味決定の論理展開
   • 図3のサブタイプ構造に対応して、注文明細内の「セットサービス注文明細」→「個別サービス注文明細」および「個別サービス注文明細」→「オプションサービス注文明細」という2種類の親子リンクを表現する必要があります。
   • それぞれの子行が参照すべき親は親行の「注文明細番号」であるため、追加する2列は「親注文明細番号（セット由来を指す列）」と「親注文明細番号（個別由来を指す列）」のように、親の注文明細番号を格納する役割になります。非該当の場合はNULLを許容し、各列は自分の行の注文番号と組み合わせて注文明細を参照する外部キーとし、参照整合性を保ちます。
4. 解答（列に設定する値の意味）
   ①・選択された個別サービスの元となったセットサービスの注文明細番号
   ②・追加されたオプションサービスの元となった個別サービスの注文明細番号

誤りやすいポイント

• 親を指す列に注文番号まで含めようとする。親行は同じ注文の明細なので、注文番号は自分の行の注文番号と同じであり、追加するのは注文明細番号を格納する列で足ります。
• 親子関係を表すのに1列にまとめてしまい、親の種別（セットか個別か）を別途判定しなければならなくなる設計にする。
• 追加列をNOT NULLにしてしまい、セット行や単品行で値を持たないケースを扱えなくする。
• 追加列に外部キー制約を設定しないまま運用すると、存在しない注文明細番号を参照するダングリング参照が発生する。
• 親子方向を逆に設計してしまい（親に子IDを列挙する等）、行の挿入・更新・削除や検索が複雑になる。

FAQ

解答の論理構成

1. テーブル間の生成タイミング
   • 注文確定 → “注文明細” に行作成
   • 実施予定登録 → “実施明細” に行作成
2. 実施対象のサービス種別
   • “サービスには、個別サービス、セットサービス及びオプションサービスの3種類”
   • “セットサービスとは、個別サービスを複数組み合わせたもの”
3. 現場で行う作業
   • “1回の実施には一つ又は複数のサービスが含まれ” とあるが、 個別に作業時間を割り当てる仕様は “個別サービス、オプションサービスごとに、予定作業時間数を割り当てる。”
   • 逆に “セットサービス” に対して作業時間は割り当てない。
4. よって “実施明細” を作る際、実際に清掃しない “セットサービス注文明細の行” は反映不要となり、理由は “セットサービスは実施対象ではないから” となる。

誤りやすいポイント

• “個別サービスを3件まとめたので作業時間を記録するだろう” と考え、セットサービスも実施対象に含めてしまう。
• 「選択した個別サービス」と「親セットサービス」の両方を二重登録して冗長なデータを生む。
• “オプションサービス” を忘れ、実施明細に含めないミス。

FAQ

解答の導き方

1. 要件の抽出
   問題文には「当初の注文確定時点の情報を含め、変更履歴を注文変更年月日ごとに保存する」とあり、変更履歴の識別単位が注文変更年月日であることが明確です。よって追加する列名は注文変更年月日とします。
2. 方針の制約確認
   問題文の方針に「新たなテーブルは追加せず、…同じ役割の列を一つだけ追加する」とあるので、同じ名前の列を必要な既存テーブルにだけ一つずつ追加する設計を採ります。
3. 現行キー関係の確認（図2参照）
   図2では注文の主キーが「注文番号」、注文明細の主キーが ｛注文番号, 注文明細番号｝、実施の主キーが「実施番号」、実施明細の主キーが ｛実施番号, 実施明細番号｝ です。また注文明細は注文番号で注文を参照し、実施は注文番号で注文を、実施明細は ｛注文番号, 注文明細番号｝ で注文明細を参照しています。
4. 変更履歴保存のために必要なキー設計の結論
   • 注文テーブル：変更履歴を注文変更年月日ごとに保存するため、同一の注文番号に対して複数行を持てるようにする必要があります。したがって注文に注文変更年月日を追加し、主キーを複合キー（注文番号, 注文変更年月日）とします。表中では「主キー欄に○」と記します。
   • 注文明細テーブル：注文明細は「当初の注文確定時点の情報を含め」て履歴を残す対象になります。注文のバージョンごとに注文明細の状態を保持するためには、｛注文番号, 注文明細番号｝ だけでは重複を避けられません。したがって注文明細にも注文変更年月日を追加し、注文明細の主キーを ｛注文番号, 注文変更年月日, 注文明細番号｝ にします。さらに注文明細は特定の注文バージョンを参照する必要があるため、追加列は外部キーとしても機能します（主キー○かつ外部キー○）。
   • 実施テーブル：実施は各回ごとに固有の実施番号で一意に識別されるため、主キーに注文変更年月日を加える必要はありません。ただし、どの注文バージョンに基づいてその実施が登録されたかを明確にするため、注文変更年月日を外部キーとして追加します（外部キー○、主キー空欄）。
   • 実施明細テーブル：実施明細も ｛実施番号, 実施明細番号｝ で一意に識別されていますが、どの注文明細（＝どの注文バージョン）に対応するかを明確にするため、注文変更年月日を外部キーとして追加します（外部キー○、主キー空欄）。
5. 参照整合性の扱い（追補）
   • 注文明細に追加した注文変更年月日は注文明細の外部キーとして 注文(注文番号, 注文変更年月日) を参照するように設定します。
   • 実施は 注文(注文番号, 注文変更年月日) を参照する外部キーを持ちます。
   • 実施明細は 実施(実施番号) と 注文明細(注文番号, 注文変更年月日, 注文明細番号) をそれぞれ参照する外部キーを持ちます。

誤りやすいポイント

• 注文明細の主キー ｛注文番号, 注文明細番号｝ に注文変更年月日を加えることを忘れ、注文のバージョンごとの明細を保持できない設計にしてしまう点。
• 注文に注文変更年月日を追加しても、子テーブル（注文明細、実施、実施明細）に同列を追加しないと参照先が不明確になり、参照整合性が維持できなくなる点。必ず関連する子テーブルにも同名の列を追加して外部キーを拡張します。
• 実施／実施明細に対して不必要に主キーに変更を加えること。これらは実施番号／｛実施番号, 実施明細番号｝ で一意に識別できるため、主キーを変えずに外部キーとして追加するだけで十分です。

FAQ