データベーススペシャリスト 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. 属性と図2の確認
   図2の関係スキーマを見ると、関係「書籍作品」の属性は「書籍作品ID、タイトル、著者ID、著者名、著者役割コード、著者役割名」となっています。これを出発点に考えます。
2. 候補キー（主キー）の決定根拠

• 問題文に「書籍作品には1人又は複数の著者が存在し、著者ごとに、 主要な著者役割が一つ定められている。」とあるため、同じ書籍作品IDに対して著者IDが複数存在する可能性があります。したがって、書籍作品IDだけでは行（一意のタプル）を識別できません。
• また、表中の説明に「タイトル　書籍作品のタイトル。異なる書籍作品のタイトルが同名である場合がある。」とあるので、タイトルだけでも一意になりません。さらに「著者名　著者の氏名。同姓同名の著者が存在する。」ともあるため、著者名単独でも一意になりません。
  以上より、行を一意に識別するには書籍作品IDと 著者IDの組合せが必要であり、候補キー（主キー）は (書籍作品ID, 著者ID) となります。

1. 関数従属性（FD: functional dependencies）の導出
   図2と本文の記述から導かれる主要な従属性を明示します（簡潔化のため「→」で表記します）。

• 書籍作品ID → タイトル
  （書籍作品IDがその作品のタイトルを決めます）
• 著者ID → 著者名
  （著者IDが著者名を決めます）
• 著者役割コード → 著者役割名
  （コードが役割名を決めます）
• (書籍作品ID, 著者ID) → 著者役割コード
  （各作品の各著者に対して主要な著者役割が一つ定まるため）

1. 正規形の判定手順

• 第1正規形（1NF）: 属性は原子的であり、図2の定義は行と列の表形式で表現されているため1NFは満たします。
• 第2正規形（2NF）の判定: 2NFは「主キーが複合キーの場合、非キー属性がキーの一部にのみ依存してはならない」という条件です。ここで主キーは (書籍作品ID, 著者ID) ですから、非キー属性がキーの一部にのみ依存しているかを調べます。
  • タイトル は 書籍作品IDのみで決まる（書籍作品ID → タイトル）ので、キーの一部（書籍作品ID）への部分従属です。
  • 著者名 は 著者IDのみで決まる（著者ID → 著者名）なので、キーの一部（著者ID）への部分従属です。
    これらにより部分従属が存在するため、2NFを満たしません。従って3NFを判定する前に2NFを満たしていないため、現状は第1正規形にとどまります。
• 第3正規形（3NF）の観点でも問題があります。例えば (書籍作品ID, 著者ID) → 著者役割コード と 著者役割コード → 著者役割名 の連鎖により、(書籍作品ID, 著者ID) → 著者役割名 という推移的従属が生じます。これにより3NFの条件（非キー属性が他の非キー属性を介して主キーに従属してはならない）も満たしません。

1. 第3正規形への分解（主キー・外部キーを明記） 第3正規形に分解する標準的な方法として、部分従属と推移的従属を除去するために属性を分割します。妥当な分解例は以下のとおりです（主キーはPK、外部キーはFKと明記します）。

• 著者（主キー: 著者ID）
  属性: 著者ID（PK）、著者名
  説明: 著者ID → 著者名 を満たします。
• 著者役割（主キー: 著者役割コード）
  属性: 著者役割コード（PK）、著者役割名
  説明: 著者役割コード → 著者役割名 を保持します。
• 書籍作品（主キー: 書籍作品ID）
  属性: 書籍作品ID（PK）、タイトル
  説明: 書籍作品ID → タイトル を保持します。
• 書籍作品著者（主キー: 書籍作品ID, 著者ID）
  属性: 書籍作品ID（PKの一部, FK → 書籍作品.書籍作品ID）、著者ID（PKの一部, FK → 著者.著者ID）、著者役割コード（FK → 著者役割.著者役割コード）
  説明: 各作品と著者の組合せごとに一行を持ち、著者の役割コードはこの組合せで一意に定まるためここに保持します。

誤りやすいポイント

• 主キーに「著者役割コード」を含めてしまう誤り
  → 「(書籍作品ID, 著者ID)」が既にタプルを一意に識別するため、著者役割コードはその結果で決まる非キー属性です。著者に対して1つの役割しか定まらないという前提があるため、役割コードを主キーに含める必要はありません。
• 「複数著者だから1NF違反」と考える誤り
  → 複数著者は行が複数存在することで表現されるため、必ずしも複数値属性や非原子的属性を意味しません。正しくは複合主キーや別の関連テーブルで扱います。
• タイトルや著者名を主キー扱いする誤り
  → 問題文に「異なる書籍作品のタイトルが同名である場合がある」「同姓同名の著者が存在する」とあるため、これらは一意性で主キーにできません。ID属性を使う必要があります。
• 分解後に外部キーを明示しない誤り
  → 正規化の目的は冗長の排除だけでなく、整合性を保つためにFKを明確にすることも重要です。設計時にFKを明示してください。

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. 形態別書籍との関連
   • 「新品書籍は、 形態別書籍ごとに…」
   • 「中古書籍は、 1冊ごとに…」
     ⇒両サブタイプとも必ず一つの「形態別書籍」に属するので
     形態別書籍 － 新品書籍
     形態別書籍 － 中古書籍
     の２本のリレーションシップを記述。
3. 出品会員と中古書籍の関連
   • 「出品会員が、 ECサイト上で中古書籍を出品するには…」
     ⇒中古書籍は必ず一人の出品会員が出品するため
     出品会員 － 中古書籍 のリレーションシップを設定。
4. 会員による評価
   • 「会員は… 出品会員を評価できる。 会員による評価は、 会員ごと出品会員ごとに最新の評価だけを記録する。」
     ⇒評価は「会員」と「出品会員」の組で一意なので
     会員 － 出品会員評価
     出品会員 － 出品会員評価
     の２本のリレーションシップが必要。
5. 以上をまとめると、解答に入れるべきリレーションシップは次のとおり。
   • 汎化１：会員 ⟶ 出品会員
   • 汎化２：販売書籍 ⟶ 新品書籍
   • 汎化３：販売書籍 ⟶ 中古書籍
   • 通常リレーションシップ
     ・会員 － 出品会員評価
     ・出品会員 － 出品会員評価
     ・出品会員 － 中古書籍
     ・形態別書籍 － 新品書籍
     ・形態別書籍 － 中古書籍

誤りやすいポイント

• 「会員」と「出品会員」の関係を単なる１対１関係と誤認し、汎化を落とす。
• 「販売書籍」を介さずに「新品書籍」「中古書籍」を直接扱い、スーパタイプを設定し忘れる。
• 会員評価を「出品会員評価」単独エンティティと捉え、「会員」側のリレーションを欠落させる。
• 形態別書籍との関連を「販売書籍」スーパタイプにつなげ、サブタイプとのカーディナリティを誤って二重表現してしまう。

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

解答の導き方

• 「出荷業務 (2)」に「出荷時点で同一会員から複数回の注文があった場合、一つにまとめて出荷する」とあるため、1つの出荷が複数の注文を含む可能性があります。
• 「出荷業務 (3)」に「出荷対象の新品書籍の在庫が不足していた場合、実在庫数分だけ出荷し、残りは入荷後に出荷する」とあるため、1つの注文が複数回に分けて出荷される（1つの注文が複数の出荷に分割される）可能性があります。

1. 出荷に「注文番号」を持たせていると、出荷は1つの注文しか参照できず、業務要件の「複数注文をまとめて出荷する」や「注文を分割出荷する」を表現できません。
2. 出荷明細に「注文番号」がないと、出荷した各商品がどの注文のどの注文明細に対応するかを識別できません。特に「同一会員からの複数回の注文に、同じ新品書籍が含まれる場合がある」ため、出荷内で同じ商品番号が複数の注文に対応することがあり得ます。出荷明細の主キーが (出荷番号, 商品番号) のままだと、同一出荷・同一商品が複数注文分あっても区別できず、情報欠落や整合性破壊が生じます。

• 出荷テーブルから「注文番号」を取り除き、出荷は出荷単位（出荷番号）と出荷日時だけを持つようにする。これにより1つの出荷が複数の注文と紐づけられるようにする。
• 出荷明細は「出荷番号」「注文番号」「商品番号」をキー化し、さらに「出荷数」を保持して、どの出荷でどの注文のどの商品を何個出荷したかを記録できるようにする。こうすると部分出荷（注文の一部を先に出荷し、残りを後で出荷）や、複数注文のまとめ出荷を正しく表現できます。
• 出荷明細は行レベルで注文明細と整合を取る必要があるため、(注文番号, 商品番号) を注文明細（注文の行）への外部キーとして設定する。

• 出荷：主キーは出荷番号で、非キー属性出荷日時は出荷番号に直接従属しており、推移的依存はありません。
• 出荷明細：主キーは（出荷番号、注文番号、商品番号）で、非キー属性出荷数はその全体に対して意味を持ち（どの出荷でどの注文のどの商品を何個出荷したか）、主キーの一部にのみ依存するような不適切な設計にはしていないため第2NFも満たします。非キー属性間の推移的依存も存在しないため第3NFを満たします。

誤りやすいポイント

• 出荷に「注文番号」を残したままにする（これだと1出荷＝1注文しか表現できず、まとめ出荷・分割出荷を表現できません）。
• 出荷明細に「注文番号」を入れずに主キーを (出荷番号, 商品番号) にする（同一出荷内で同一商品が複数注文分ある場合に区別できません）。
• 出荷明細の外部キーを注文明細ではなく注文（注文番号 のみ）にだけ参照させる（行レベルの整合性が保てません）。行単位の整合性を取るには注文明細（注文番号、商品番号）を参照する必要があります。
• 正規化の用語を混同すること：「非キー属性が主キーに対して完全関数従属する」は第2正規形の要件、「非キー属性が主キーに対して推移的従属していない」は第3正規形の要件である点を誤って説明すると減点につながります。

FAQ