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

解答の論理構成

1. 候補キーの導出
   • 表1で「電子会議番号」は電子会議を一意に識別する ⇒ 投稿を区別するには「投稿番号」と組み合わせれば良い。
     よって「{ 電子会議番号、投稿番号 }」。
   • 同じく表1で「表示順」は「一つの分野内で表示順が重複することはない」。つまり分野内では「{ 分野番号、表示順 }」が電子会議を一意に決める。そこに「投稿番号」を加えると投稿が一意になる ⇒ 「{ 分野番号、表示順、投稿番号 }」。
   • 以上2つが候補キー。
2. 部分関数従属性の検出
   • 候補キーの1つ「{ 電子会議番号、投稿番号 }」に注目。
     【問題文】「電子会議のタイトル」を示す属性は「議題」。電子会議レベルの情報であり、キーのうち「電子会議番号」だけで決まる。
     よって「電子会議番号 → 議題」が部分関数従属性。
3. 推移的関数従属性の検出
   • 電子会議レベルで「電子会議番号 → 分野番号」が成り立つ（電子会議は1つの分野に属する）。
   • さらに分野レベルで「分野番号 → 分野名」が成り立つ（表1より「分野を一意に識別する番号」「分野の名称」）。
   • よって「電子会議番号 → 分野番号 → 分野名」が推移的関数従属性。
4. 以上により、模範解答と一致する。

誤りやすいポイント

• 「表示順」は電子会議レベルの属性であり、投稿を識別するキーと誤って単独で使ってしまう。
• 「{ 分野番号、表示順 }」が電子会議を一意とする根拠を「表示順が分野ごとに連番」などと誤記し、【問題文】の「重複しない」条件を引用しない。
• 推移的従属性で「分野番号 → 分野名」を忘れ、「電子会議番号 → 分野名」を直接書いてしまう。

FAQ

解答の論理構成

1. 【表1】により関係 “電子会議投稿(電子会議番号、議題、分野番号、分野名、表示順、作成者ユーザID, 投稿番号、投稿本文、投稿者ユーザID)” が定義されている。
2. 主キー候補は “電子会議番号、投稿番号”（投稿を一意識別）である。
3. 関数従属性を整理すると
   • “分野番号 → 分野名”（同一分野で名称は一意）
   • “電子会議番号 → 議題、分野番号、表示順、作成者ユーザID”（1会議1レコード）
   • “電子会議番号、投稿番号 → 投稿本文、投稿者ユーザID”（投稿内容はこの複合キーで決定）
4. よって
   • 部分関数従属（例：電子会議番号 → 議題）が存在 → 第2正規形を満たさない。
   • 推移的関数従属（例：分野番号 → 分野名）が存在 → 第3正規形も満たさない。
5. 第3正規形へ分解
   分野(分野番号、分野名) 電子会議(電子会議番号、議題、分野番号、表示順、作成者ユーザID) 投稿(電子会議番号、投稿番号、投稿本文、投稿者ユーザID)
   • 下線：主キー
   • 点線下線：外部キー
6. 以上により、すべての非キー属性が主キーに対して非推移的かつ完全関数従属となり、第3正規形が達成される。

誤りやすいポイント

• “表示順” を “分野番号” と直接結びつけてしまい、電子会議に属すると気付かない。
• 主キーを “電子会議番号” だけと誤認し、第1正規形を見落とす。
• 外部キー指定を忘れ、正規化後のリレーションが孤立する。

FAQ

解答の論理構成

1. (a) ユーザ表の空欄
   • 要件引用：【問題文】「ユーザは、一つのグループに必ず所属し、これを主務グループと呼ぶ。」
   • 主キーではなく、グループ表の「グループID」を参照するため外部キー。
   • よって 主務グループID。
2. (b) 設備表の空欄
   • 要件引用：【問題文】「設備には、必要に応じて、当該設備の管理を行うグループを一つ定めることができる。」
   • グループを参照する外部キー。
   • よって 管理グループID。
3. (c) メッセージ表の空欄
   • 要件引用：【問題文】「ユーザは,1人又は複数のユーザにメッセージを送信することができる。」
   • 送信元ユーザを保持し、ユーザ表を参照する外部キー。
   • よって 送信元ユーザID。
4. (d)(e) メッセージ送信先表の空欄
   • 受信者識別と開封日時が必要
     ・受信者…【問題文】「送信先のユーザがメッセージを開封すると、開封日時が記録される。」
     ・主キー構造…同一メッセージに対し複数受信者 ⇒ (メッセージID, 受信者ID)で一意
   • 従い (d) は主キー送信先ユーザID、(e) は開封日時。
5. (f) スケジュール予約先表の空欄
   • 要件引用：【問題文】「スケジュールを予約されたユーザは、そのスケジュールに参加するか否かを回答することができる。」
   • 回答結果を保持する属性が必要 ⇒ 参加可否回答。主キーでも外部キーでもないため下線なし。

誤りやすいポイント

• (a) を「所属グループID」と書く
  「主務」という語を落とすと兼務との区別が付かず不正確。
• (d) の下線種別
  受信者IDを外部キーと判断し破線にすると主キー要件を満たせない。
• (f) をNULLで済むと思い属性自体を作らない
  参加可否の回答結果は履歴ではなく最新値で良いので列が必要。

FAQ

解答の導き方

1. 記号の読み方を決める
   図3の線端にある記号は最小カーディナリティ（対応関係にゼロを含むか否か）を表していると読みます。ここでは以下の読み方で統一します。
   • 黒丸 ● ：対応関係にゼロを含まない（必須、最小値は1）
   • 白抜き丸 ○ ：対応関係にゼロを含む（任意、最小値は0）
     この読み方に従って、図3の各線について「どちらのエンティティ側に●／○が付いているか」をそのまま写していきます。
2. 図3の線を一つずつ読む手順（実際の適用例）
   • 図3に表示された全エンティティを確認します（問題文の図3説明で9個のエンティティが列挙されています）。「なお、図3に表示されていないエンティティタイプは考慮しなくてよい」とあるので、図3に描かれている線だけを扱います。
   • 各線について、線の両端（または線端と中間）にある「黒丸／白抜き丸」の位置を正確に読み取ります。図3の説明には例えば次のような記述があります。
     • 「線端に「ユーザ」側が黒丸●、中間側（スケジュール予約先側）が白抜き丸◯」
     • 「線端に「ユーザ」側が黒丸●、中間側（兼務グループ側）が白抜き丸◯」
     • 「線端に「メッセージ」側が黒丸●、中間側（メッセージ送信先側）が白抜き丸◯」
     • 「線端に「設備」側が黒丸●、中間側（設備予約先側）が白抜き丸◯」
     • 「右上方に斜め上向きの線が伸び、「兼務グループ」に接続 – 線端に「グループ」側が黒丸●、中間側（兼務グループ側)が白抜き丸◯」
   • それぞれの記述を上の記号読み方に当てはめ、対応関係の「エンティティA側：●/○、エンティティB側：●/○」を決定します。
3. 図3から読み取れるリレーションシップ（結論）
   図3の線の記述に従って得られる関係は次の5本です（左側→右側の順で、各側のゼロ含有を示します）。各行は「関係：左側 （ゼロ含むか） → 右側 （ゼロ含むか）」という形式です。
   • ユーザ — スケジュール予約先：ユーザ側 ●（ゼロを含まない） → スケジュール予約先側 ○（ゼロを含む）
     • 根拠（図3の記述）：「線端に「ユーザ」側が黒丸●、中間側（スケジュール予約先側）が白抜き丸◯」
   • ユーザ — 兼務グループ：ユーザ側 ●（ゼロを含まない） → 兼務グループ側 ○（ゼロを含む）
     • 根拠（図3の記述）：「線端に「ユーザ」側が黒丸●、中間側（兼務グループ側）が白抜き丸◯」
   • グループ — 兼務グループ：グループ側 ●（ゼロを含まない） → 兼務グループ側 ○（ゼロを含む）
     • 根拠（図3の記述）：「右上方に斜め上向きの線が伸び、『兼務グループ』に接続 – 線端に『グループ』側が黒丸●、中間側（兼務グループ側)が白抜き丸◯」
   • 設備 — 設備予約先：設備側 ●（ゼロを含まない） → 設備予約先側 ○（ゼロを含む）
     • 根拠（図3の記述）：「線端に『設備』側が黒丸●、中間側（設備予約先側）が白抜き丸◯」
   • メッセージ — メッセージ送信先：メッセージ側 ●（ゼロを含まない） → メッセージ送信先側 ○（ゼロを含む）
     • 根拠（図3の記述）：「線端に『メッセージ』側が黒丸●、中間側（メッセージ送信先側）が白抜き丸◯」
   注意：上は「図3に描かれている線とその記号」をそのまま読み取った結果です。図4（関係スキーマ）や問題文中の業務ルールと照合すると、実務的に期待される最小カーディナリティ（例えば「ユーザが主務グループに必ず所属する」など）と図3の表記が一致しない箇所があることがあるため、後続の設問では図3・図4・本文を合わせて矛盾箇所を見つける必要があります。

誤りやすいポイント

• 記号の「どちら側に付いているか」を誤って逆に読む
  → 線のどの位置に黒丸/白丸があるか（エンティティ側か中間か）を必ず確認すること。端の記号はその直近のエンティティの参加要件を示す。
• 黒丸／白抜き丸の意味を取り違える
  → 本問では「黒丸 ● = ゼロを含まない（必須）」「白丸 ○ = ゼロを含む（任意）」で統一して読む。
• 結合エンティティ（兼務グループ、メッセージ送信先、設備予約先、スケジュール予約先など）を「単なる属性集合」と見なして関係を省略する
  → 図上で別エンティティ（長方形）になっている場合は独立した参加要件があるため、線と記号を個別に読む。
• 図3の表示だけに頼って業務ルールと矛盾しても気付かない
  → 図4（関係スキーマ）や本文の業務要件と照合して、図に抜けや誤りがないかチェックする癖を付ける。

FAQ

解答の導き方

1. 根拠の確認
   　問題文に「ロールは、ロールIDで一意に識別し、ロール名をもつ。」とあり、また「ユーザには、必要に応じて一つ又は複数のロールを付与でき、一つのロールを複数のユーザに付与することもできる。」とあります。これらからロールは独立に管理する必要があり、ユーザとロールの関係は多対多であることが分かります。多対多を表現するためにロールテーブルとユーザとロールを結ぶ橋渡しテーブルが必要です。
2. 追加すべき関係（スキーマの決定）
   　上の結果から最小限で必要な関係は次の二つです。主キー・外部キーは次の通りに決めます。
   　- ロール：ロールIDを主キーとする。属性はロールID（主キー）、ロール名。
   　　理由：問題文にある「ロールIDで一意に識別」に対応するためです。
   　- ロール付与先：ユーザとロールの割当を保持する橋渡しテーブル。主キーは (ロールID, ユーザID)。外部キーはロールID→ロール(ロールID)、ユーザID→ユーザ(ユーザID)。
   　　理由：ユーザとロールは多対多なので複合主キーで重複を防ぎ、参照整合性を保つために外部キーを張ります。
3. 決裁ルート側の属性追加の必要性と外部キーの向き
   　問題文に「ステップには、 承認可能なユーザ、 グループ又はロールを指定する。 ユーザ、グループ又はロールのいずれで指定されているかは、承認者区分で識別する。」とあります。既存の決裁ルートには承認ユーザID・承認グループIDがあるため、ロール指定を記録するために決裁ルートにロール参照用の属性を追加します。追加する属性名は承認ロールIDとし、これをロール(ロールID) を参照する外部キーとして定義します（向きは 決裁ルート.承認ロールID → ロール.ロールID）。
4. NULL取扱いと排他条件の明示（重要）
   　決裁ルートの同じ行で「承認ユーザID」「承認グループID」「承認ロールID」が混在すると曖昧になります。したがって承認者区分の値に応じて使用する属性を限定するチェック制約を設けます。例示すると次のような論理になります（実装はDBのCHECK句やトリガで実現）。
   　- 承認者区分 = 'ユーザ' のとき：承認ユーザIDは NOT NULL、承認グループIDと承認ロールIDはNULL。
   　- 承認者区分 = 'グループ' のとき：承認グループIDは NOT NULL、他はNULL。
   　- 承認者区分 = 'ロール' のとき：承認ロールIDは NOT NULL、他はNULL。
   　これによりどの列が有効かが明確になり、外部キー参照の意味も一意に保たれます。
5. 承認実行時の整合性チェック（補足）
   　承認処理（承認テーブルへの登録）では、実際に承認したユーザ（承認.承認者ユーザID）が、決裁ルートで指定されたグループもしくはロールに属することを確認する必要があります。これは単純な外部キーでは表現しにくいため、トリガやストアドプロシージャ、アプリケーション側でのチェックを実装して参照整合性を保証します。

• 関係スキーマ（追加）
  • ロール（ロールID: 主キー、ロール名）
  • ロール付与先（ロールID, ユーザID） 主キー：(ロールID, ユーザID)、外部キー：ロールID→ロール(ロールID)、ユーザID→ユーザ(ユーザID)
• 関係名（属性追加）
  • 決裁ルート に 属性 承認ロールID を追加（外部キー：決裁ルート.承認ロールID → ロール.ロールID。承認者区分に応じたNULL取扱いをCHECKで保証）

誤りやすいポイント

• 「承認ロールID」の意味をあいまいにする
  • 誤り：承認ロールIDを決裁ルート側の単なる識別子と考え、ロール(ロールID) への外部キーを定義しない。
  • 正解：決裁ルート.承認ロールIDはロール(ロールID) を参照する外部キーであることを明確にする。
• ロールとユーザの関係を1対多で設計してしまう
  • 誤り：ユーザテーブルにロールID列を追加して1ユーザ1ロールにしてしまう。
  • 正解：問題文にある通り多対多なのでロール付与先（橋渡しテーブル）が必要。主キーは (ロールID, ユーザID)。
• 承認者区分に応じたNULL/NOT NULLの扱いを書かない
  • 結果：どの列が有効か不明瞭になり参照整合性が壊れる。CHECK制約で明確にする。
• 承認時に実際に承認したユーザが指定されたロール・グループのメンバーかを検証しない
  • 結果：権限漏れ／不正承認が発生する。トリガやアプリ側チェックで必ず確認する。

FAQ

解答の導き方

1. 問題文から事実を抽出します。図4に「承認」関係があり、属性に「承認連番」が定義されています。本文には「承認連番は申請ひな形番号ごと・申請連番ごとに1から始まり、承認処理を行うごとに1ずつ加算される番号」とあります。さらに承認処理について「承認処理を行うと、その都度処理内容がデータベースに新規登録される。」とあり、決裁の流れには「差戻し : 一つ前のステップに戻す。」が存在します。これらを合わせて考えます。
2. 事実の意味付けをします。承認連番は「ある申請（申請ひな形番号＋申請連番）」ごとに増える番号であり、承認処理ごとに新しいレコードを登録するため、承認履歴の各レコードを一意にする識別子として機能します。差戻しにより同じステップで再度承認処理が発生することがあり得ます。
3. 不具合が起きる状況を導きます。もし「承認」関係の主キーが（申請ひな形番号、申請連番、ステップ番号）など承認連番を含まない構成になっていると、差戻し後に同じステップで再承認した際に既存の主キーと衝突して挿入できないか、前の承認記録を上書きしてしまいます。したがって不具合は「一度承認した申請が差戻しされ、再度承認処理を行うとき」に発生します。
4. 修正方針を決めます。承認処理は「その都度新規登録」され、承認履歴を重複なく保持するために、承認連番を主キーに含めて各承認イベントを一意に識別する必要があります。また、外部キーは決裁ルート参照が複合キーである点や、承認者が実際に処理したユーザのユーザIDが記録される点を正確に表記します。

誤りやすいポイント

• 承認連番を主キーに「だけ」する／逆にまったく入れないという誤解
  承認連番は「申請ひな形番号ごと・申請連番ごとに」付与されるので、単独では全体で一意にならない（他の申請と区別できない）。主キーは申請を識別する属性と組にする必要があります。
• 決裁ルートへの参照を単一属性で表す誤り
  決裁ルートは（申請ひな形番号、ステップ番号）で特定されるため、外部キーも複合で参照する点を忘れると整合性が崩れます。
• 第3正規形（3NF）の説明混同
  「非キー属性が特定の非キー属性に従属する」ような推移従属が存在しないことを示す必要があり、単に「承認連番に従属する」と書くだけでは不十分です。非キー属性は主キー全体に対して完全従属し、非キー→非キーの関数従属がないことを確認します。
• 承認者の取り扱いの取り違え
  決裁ルートで承認者がグループやロールで指定されていても、承認履歴には「承認処理を行ったユーザのユーザID」を記録するという点（問題文の定義）を見落とすと参照先を誤ります。

FAQ

• （申請ひな形番号、申請連番） → 申請（申請ひな形番号、申請連番）
• （申請ひな形番号、ステップ番号） → 決裁ルート（申請ひな形番号、ステップ番号）
• 承認者ユーザID → ユーザ（ユーザID）