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

解答の論理構成

1. 属性列挙と意味の確認
   「図2」の“書籍作品”は
   書籍作品ID / タイトル / 著者ID / 著者名 / 著者役割コード / 著者役割名
   で構成される。
2. 業務規約による一意性の読み取り
   【問題文】「書籍作品には 1 人又は複数の著者が存在し、著者ごとに、主要な著者役割が一つ定められている。」
   → 作品と著者の組が一意であり、著者役割はその結果として決まる。
3. 候補キーの決定
   作品単体では著者が一意にならず、著者単体でも作品が一意にならない。よって {書籍作品ID, 著者ID} が候補キー。
4. 部分関数従属性の判定
   • 書籍作品ID → タイトル（作品固有）
   • 著者ID → 著者名（著者固有）
     どちらもキーの一部だけで決まるため“あり”。
5. 推移的関数従属性の判定
   • {書籍作品ID, 著者ID} → 著者役割コード（キー全体で決定）
   • 著者役割コード → 著者役割名（コードに依存）
     したがって {書籍作品ID, 著者ID} → 著者役割コード → 著者役割名 が推移的関数従。“あり”。

誤りやすいポイント

• 「著者役割コードも候補キーに含める」と誤解する
  → 規約で“著者ごとに主要な役割は一つ”と明示。
• 著者名やタイトルの同名問題の読み落とし
  → 【表1】「異なる書籍作品のタイトルが同名である場合がある。」などにより、識別子は ID属性 である点を再確認。
• 部分関数従と推移的関数従を混同する
  → キーの“部分”か“非キー経由”かを整理してメモすると防げる。

FAQ

1. 作品‐著者（書籍作品ID, 著者ID, 著者役割コード）
2. 作品（書籍作品ID, タイトル）
3. 著者（著者ID, 著者名）
4. 著者役割（著者役割コード、著者役割名）
   に正規化すれば、部分・推移的従属性とも排除できます。

解答の論理構成

1. 第1正規形確認
   【問題文】図2 “書籍作品（書籍作品ID、タイトル、著者ID、著者名、著者役割コード、著者役割名）” は繰り返し属性や複合属性を持たず、各列が単一値なので第1正規形です。
2. 第2正規形違反の確認
   主キーは複合候補「書籍作品ID」「著者ID」「著者役割コード」です。
   ところが “著者名” は【問題文】「著者を一意に識別する文字列」である “著者ID” のみに関数従属します。
   同様に “著者役割名” は “著者役割コード” のみに従属します。
   これは「部分関数従属」に該当し、第2正規形に違反します。
3. 第3正規形違反の確認
   部分従属を解消しても、“書籍作品著者” に “著者役割名” を残したままだと
   “著者役割コード → 著者役割名” が成立し、主キー以外の列が他の非キー列に従属する「推移的関数従属」が発生します。
   よって第3正規形でもありません。
4. 分解方針
   ① “著者ID → 著者名” を独立させて関係 “著者” を作成
   ② “著者役割コード → 著者役割名” を独立させて関係 “著者役割” を作成
   ③ “書籍作品ID → タイトル” を独立させて関係 “書籍作品” を作成
   ④ 多対多関係を表す中間表 “書籍作品著者” を配置し、外部キーとして
   • 著者ID 参照 → 著者
   • 書籍作品ID 参照 → 書籍作品
   • 著者役割コード 参照 → 著者役割
   を設定することで完全従属しか残らず、第3正規形が達成されます。

誤りやすいポイント

• 「主キー列を増やせば正規化できる」と誤解し、冗長列を残したままにしてしまう
• “タイトル” が一意だと思い込み「書籍作品ID」を不要と判断する
• 推移的関数従属と部分関数従属の区別をあいまいにする

FAQ

解答の論理構成

1. 出品会員評価
   【問題文】「会員は…出品会員を評価…会員ごと出品会員ごとに最新の評価だけを記録」。
   ⇒ 主キーは(評価する側、評価される側)。表中で後者は既に “出品会員会員ID” が存在。残る (a) は評価者の 会員ID。
2. カテゴリ
   【問題文】「カテゴリは階層構造…'データベース' の上位カテゴリが ‘情報技術’」。
   ⇒ 同一テーブル内で親カテゴリを指す外部キーが必要。図2の (b) 部分はその役割なので 上位カテゴリコード。
3. 販売書籍
   【問題文】新品・中古いずれも「販売価格」を持つ。図2 “販売書籍” は両者の共通項を格納するスーパタイプ。
   ⇒ 共通属性として最も重要な金額項目 (c) は 販売価格。
4. 中古書籍
   【問題文】「出品会員が…中古書籍を出品…」ゆえに誰が出品したかを保存する必要がある。
   図2 “中古書籍” には商品番号 PK があり、出品者を示す (d) が欠けている。
   ⇒ 出品会員会員ID（“出品会員” の PK を参照）が最適。

誤りやすいポイント

• カテゴリ階層を別テーブルで実装すると誤解し、(b) を “カテゴリ階層ID” などと書く。
• “販売書籍” に実在庫数や品質ランクを書いてしまい、共通属性という設計意図を見落とす。
• “中古書籍” に出品者を示すカラムが要ることは気付くが、名称を “会員ID” としてしまい出品者と購入者を混同。
• 主キー・外部キーの下線種別を逆に付ける。

FAQ

解答の論理構成

1. スーパタイプ／サブタイプの抽出
   • 【問題文】「出品会員とは、A 社の EC サイト上で中古書籍を販売できる会員である。」
     →「会員」をスーパタイプ、「出品会員」をサブタイプと確定。
   • 【問題文】「販売書籍は、新品書籍、中古書籍に分類される。」
     →「販売書籍」をスーパタイプ、「新品書籍」「中古書籍」をサブタイプと確定。
2. 業務ルールからエンティティ間の実体関係を決定
   a. 出品会員と中古書籍
   • 【問題文】「出品会員が、ECサイト上で中古書籍を出品する」
     → 出品会員 1 － N 中古書籍。
     b. 形態別書籍と各サブタイプ
   • 【問題文】「中古書籍は、1冊ごとに…形態別書籍ID を持つ」
   • 【問題文】「新品書籍は、形態別書籍ごとに…」
     → 形態別書籍 1 － N 中古書籍、新品書籍。
     c. 会員と出品会員評価
   • 【問題文】「会員は…出品会員を評価できる。」
     → 会員 1 － N 出品会員評価。
     d. 出品会員と出品会員評価
   • 同じ文中に「出品会員を評価できる」とあるため
     → 出品会員 1 － N 出品会員評価。
3. スーパタイプ側で表現すべきリレーション
   ガイドラインに従い、カーディナリティが同じ場合はスーパタイプで接続。
   • 販売書籍⇔形態別書籍の直接関係は存在しないため、サブタイプ経由で表現。
   • 会員⇔中古書籍の関係も直接には発生しない（注文業務経由）。
4. まとめると、下記8本
   ① 会員―|＜出品会員（汎化）
   ② 販売書籍―|＜中古書籍（汎化）
   ③ 販売書籍―|＜新品書籍（汎化）
   ④ 出品会員 1―N 中古書籍
   ⑤ 形態別書籍 1―N 中古書籍
   ⑥ 形態別書籍 1―N 新品書籍
   ⑦ 会員 1―N 出品会員評価
   ⑧ 出品会員 1―N 出品会員評価

誤りやすいポイント

• サブタイプどうし（中古書籍―新品書籍）を直接結んでしまう
• 「会員が中古書籍を購入する」ことから会員―中古書籍を作成してしまう
• 出品会員評価を「多対多」と誤って捉え、中間エンティティを設けてしまう
• 販売書籍を形態別書籍のサブタイプと誤認する（実際は別概念）

FAQ

解答の論理構成

1. 中古書籍のステータス遷移
   • 【表1】「ステータス…中古書籍の登録時に『入荷待』、入荷時に『入荷済』、受注時に『引当済』、出荷時に『出荷済』」
   • よって受注時（ア）は「'入荷済' → '引当済'」の更新が必要。
2. 新品書籍の在庫と受注残
   • 新品書籍には【表1】「実在庫数」「受注残数」が存在。
   • 【表2】出荷業務で「出荷した販売書籍に該当する、“新品書籍” テーブルの行の（イ）」と指示。
   • 出荷が完了した数量は物理在庫・受注残の両方から差し引く必要がある。
   • したがって（イ）は「実在庫数列及び受注残数列を出荷数分だけ減算」になる。

誤りやすいポイント

• 「受注時に在庫は減らさない」ことを忘れ、（ア）で在庫列を更新してしまう。
• 新品書籍の「受注残数」を出荷時に減算し忘れる。
• ステータスに存在しない値（例：発送準備中）を勝手に考案して記述する。

FAQ

解答の論理構成

1. 要求の確認
   出荷業務には次の2要求があります。
   ・“出荷時点で同一会員から複数回の注文があった場合、一つにまとめて出荷する。”（〔業務の概要〕4.(2)）
   ・“出荷時点で出荷対象の新品書籍の在庫が不足していた場合、実在庫数分だけ出荷し、残りは入荷後に出荷する。”（〔業務の概要〕4.(3)）
2. 現行スキーマの制約
   “出荷” の主キーは “出荷番号”、外部キーとして “注文番号” を保持しており
   出荷（出荷番号、注文番号、出荷日時）
   となっています。これでは「出荷1件＝注文1件」となり、複数注文の統合が不可能です。
   さらに “出荷明細” は
   出荷明細（出荷番号、商品番号）
   であり、数量を保持しないため、同じ “商品番号” を複数行にしても何冊出荷したのか、残数はいくつかを表現できません。
3. 不具合との対応付け
   ・複数注文統合不可 ⇒ 要求4.(2)を満たせない。
   ・数量分割不可 ⇒ 要求4.(3)を満たせない。
   したがって模範解答の2項目に至ります。

誤りやすいポイント

• 「注文番号を持たせればどの注文か分かるから統合できる」と早合点する。実際は1対1制約が生じる点を見落としやすいです。
• 出荷明細に数量列がないことを「中古書籍は必ず1冊」の仕様だけで納得し、新品書籍の複数冊注文を失念する。
• “在庫不足で分割出荷” 要求を「出荷テーブルを複数行にすれば良い」と思い込み、出荷明細と注文明細の整合性を検討しない。

FAQ

解答の論理構成

1. 業務要件の整理
   • 【問題文】「同一会員から複数回の注文があった場合、一つにまとめて出荷する。」
     ⇒ 1 つの “出荷番号” が 2 個以上の “注文番号” と結び付く。
   • 【問題文】「同じ新品書籍が含まれる場合がある。」
     ⇒ 1 出荷内で同一 “商品番号” が複数注文にまたがる可能性。
2. 不具合①：多重従属性
   • 元の “出荷（出荷番号、注文番号、出荷日時）” は、主キー “出荷番号” に対し “注文番号” が多値従属する。
   • 第 3 正規形条件「主キーに対し非キー属性が完全関数従属」を満たさない。
3. 不具合②：注文‐商品対応の欠落
   • “出荷明細（出荷番号、商品番号）” だけでは、どの “注文番号” 由来か判別できない。
   • 「出荷時点で出荷対象の新品書籍の在庫が不足していた場合…残りは入荷後に出荷」とあるため、後続出荷でも同じ “注文番号” を追跡できる構造が必要。
4. 正規化とリレーション再設計
   (1) “出荷” から “注文番号” を分離
   出荷（出荷番号、出荷日時）
   ・主キー：出荷番号
   (2) “出荷明細” に “注文番号” を追加し出荷行単位で管理
   出荷明細（出荷番号、商品番号、注文番号、出荷数）
   ・主キー：出荷番号 + 商品番号 + 注文番号
   ・外部キー：
   ‐ 出荷番号 → 出荷
   ‐ 商品番号 → 販売書籍
   ‐ 注文番号 → 注文
   (3) これにより
   ・出荷番号 → 出荷日時（完全関数従属）
   ・〈出荷番号、商品番号、注文番号〉 → 出荷数（完全関数従属）
   となり、第 3 正規形を満たす。

誤りやすいポイント

• “出荷明細” の主キーを〈“出荷番号”、“商品番号”〉のままにする
  → 同じ商品を複数注文分に出荷するケースで一意性が失われる。
• “注文番号” を “出荷明細” のみ複合キーに含めず非キー属性にする
  → 部分関数従属が発生し第 3 正規形違反。
• “出荷数” を追加し忘れる
  → 部分出荷（在庫不足時の分割出荷）を表現できず要件漏れ。

FAQ