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

解答の論理構成

1. 【問題文】の図3は「関係“名寄せ”、“利用者”の属性間の関数従属性」と明記されている。
2. 図2の凡例より、矩形Ａ→矩形Ｂの矢印は “Ａ → Ｂ” を意味する。
3. 図3で中央の太枠に格納された｛プロバイダID、利用者ID｝から、上部の「名寄せID」へ矢印が伸びているため
   $\{\text{プロバイダID}、\text{利用者ID}\}\to \text{名寄せID}$
   が成立する。
4. 名寄せIDからプロバイダID・利用者IDに向かう矢印は存在しない。従って
   $\text{名寄せID}\nrightarrow \{\text{プロバイダID}、\text{利用者ID}\}$
   である。
5. 関係“名寄せ”（プロバイダID、利用者ID、名寄せID）で全ての属性を関数的に決定できる最小属性集合を列挙すると、｛プロバイダID、利用者ID｝のみが該当し、これが候補キーとなる。

誤りやすいポイント

• 「名寄せIDは一意に識別するID」とあるため単独キーと決め付ける。矢印の向きを無視すると誤答になる。
• ｛プロバイダID、利用者ID｝のどちらか片方だけでキーになると誤解する。図3では二つがセットになって初めて名寄せIDを決定している。
• 関数従属性の集合全体ではなく、図3に描かれた部分のみを見て候補キーを判定すべき点を見落とす。

FAQ

解答の導き方

1. キー候補の決定
   表1に「利用者ごとに複数の特性が管理される」とあるので、同一の（プロバイダID、利用者ID）に対して複数の特性IDが対応し得ます。したがって一行はプロバイダ＋利用者＋特性の組合せを表し、自然な候補キーは
   $\{プロバイダID,利用者ID,特性ID\}$ です。
2. 図と表から読み取れる主要な関数従属性
   • 表1に「特性IDに対応する特性の名称」「特性タイプ」とあるので
     $特性ID\to 特性名,特性タイプ$
   • 図3の矢印と属性の意味から
     $\{プロバイダID,利用者ID\}\to 生年月日,性別,郵便番号$
   • 利用者の特性値はプロバイダ＋利用者＋特性で定まるため
     $\{プロバイダID,利用者ID,特性ID\}\to 特性値$
3. 正規形の判定と挿入異常の原因
   上の従属性から、生年月日等は候補キー $\{プロバイダID,利用者ID,特性ID\}$ の真部分 $\{プロバイダID,利用者ID\}$ にのみ依存し、また特性名・特性タイプは $特性ID$ のみに依存します。これらは部分従属であり第2正規形を満たしません。結果として挿入時に次のような問題（挿入異常）が発生します。
   短くまとめると：
   "特性を事前登録できず挿入異常が生じる"
   （具体的には、特性（特性ID・特性名・特性タイプ）を利用者と独立して登録できない、あるいは利用者が特性を持たない場合に利用者情報を挿入できない、という異常が起きます。）
4. 第3正規形への分解
   部分従属を取り除くために、従属性ごとに関係に分解します。第3正規形へ分解した一例を示します（関係名は任意、主キーを下線で示します）。
   利用者（プロバイダID、利用者ID、生年月日、性別、郵便番号）
   利用者特性（特性ID、特性名、特性タイプ）
   利用者特性値（プロバイダID、利用者ID、特性ID、特性値）
   分解後は、利用者特性値 の (プロバイダID, 利用者ID) が 利用者 を、特性IDが 利用者特性 を参照する外部キー制約を張ることで整合性を保ちます。

誤りやすいポイント

• 図3の矢印を誤読して $\{プロバイダID,利用者ID\}\to 特性ID$ としてしまう（実際は利用者は複数特性を持ちうる）。
• 特性IDの一意性のスコープ（プロバイダ内かシステム全体か）を確認せずに分解する。表1の記述を確認して扱いを決める。
• 「部分従属」と「推移的従属」を混同して、どの従属性が第3正規形違反かを誤判断する。
• 挿入異常だけでなく、更新・削除異常も同じ根本（冗長な属性配置）に起因することを忘れる。

FAQ

解答の論理構成

1. 所属関係に含まれる属性と制約を整理
   • 【表１】では、所属関係の構成要素として
     「プロバイダID」「利用者ID」「組織ID」「職種」「所属登録日付」
     が示されています。
   • また制約として
     「利用者が所属する組織での職種は、同時期には一つだけ登録できる。」
     と記述されています。
2. 「同時期には一つだけ」の読み替え
   • “同時期” を一意に表すキーが 「所属登録日付」です。
   • したがって組 ( {\text{プロバイダID}, \text{利用者ID}, \text{所属登録日付}} )
     が決まれば、その時点での「職種」は一意に定まります。
     → 関数従属性
     [ {\text{プロバイダID}, \text{利用者ID}, \text{所属登録日付}} ;\to; \text{職種} ]
3. 組織履歴と所属履歴の関係付け
   • 所属表のキーに含まれる「組織ID」は、同じタイムスタンプで “その時点での組織” を指す必要があります。
   • 所属表の履歴行は1行1時点ですから、同じ組
     [ {\text{プロバイダID}, \text{利用者ID}, \text{所属登録日付}} ]
     に対して「組織ID」も一意となります。加えて履歴情報として組織側の日付「組織登録日付」が必要です。
     よって
     [ {\text{プロバイダID}, \text{利用者ID}, \text{所属登録日付}} ;\to; {\text{組織ID}, \text{組織登録日付}} ]
4. 組織表の履歴制約
   • 【表１】には
     「組織情報（組織名称、郵便番号）は、履歴が管理される。」
     とあり、組織表の主キーが
     ({\text{組織ID}, \text{組織登録日付}})
     であることを示唆しています。
   • したがって
     [ {\text{組織ID}, \text{組織登録日付}} ;\to; {\text{組織名称}, \text{郵便番号}} ]
5. 図２の凡例に従い、以上３本の関数従属性を図４に配置
   • 先頭のFDを示す矢印：
     「プロバイダID／利用者ID／所属登録日付」から「職種」へ
   • ２本目のFDを示す矢印：
     「プロバイダID／利用者ID／所属登録日付」から「組織ID／組織登録日付」へ
   • ３本目のFDを示す矢印：
     「組織ID／組織登録日付」から「組織名称／郵便番号」へ
     これで図４の □ は
     「プロバイダID」「利用者ID」「所属登録日付」
     「組織ID」「組織登録日付」
     「組織名称」「郵便番号」
     が正しく埋まります。

誤りやすいポイント

• 「職種」が決定すると思い込む
  一見 “事務”“営業” などは固有の職種名なのでキーに見えますが、同じ職種を複数利用者が持つため逆向きFDになりがちです。
• 履歴日付の漏れ
  組織側も履歴管理されるため「組織登録日付」を忘れるとFDが成り立たず、正規化判定を誤ります。
• 主キーの取り違え
  所属表の主キーを 「プロバイダID」「利用者ID」だけと早合点し、同一利用者の転籍・異動履歴を区別できなくなるケースが頻発します。

FAQ

解答の論理構成

1. 図5の中央長方形には
   プロバイダID、サービス機能ID、利用者ID｜リソースID、タイムスタンプ
   という配置が示されている。上下2段で区切られているが、その中間の縦線は論理的な境界を示すだけで、各行は1レコードである。
2. 【問題文】「関係 “サービス提供リソース”（プロバイダID, サービス機能ID, 利用者ID, …）」より、行を一意に識別できる最小組合せを探す。
   • プロバイダID単独では、同じプロバイダが複数機能・利用者を持つ。
   • サービス機能ID単独でも、同一機能を複数プロバイダが提供し得る。
   • 利用者ID単独はプロバイダ内でしか一意でない。
     結果、3属性を合わせた {プロバイダID、サービス機能ID、利用者ID} が最小の一意識別子＝候補キー。
3. 部分関数従属性の判定
   部分関数従属性は “候補キーの真部分集合 → 非キー属性” が存在するときに生じる。図5にその矢印は描かれていないため なし。
4. 推移的関数従属性の判定
   • 図5下部の矢印
     {プロバイダID、サービス機能ID、利用者ID} → リソースID
     リソースID → {URI、リソース種別、リソース名称、配信スキーマ}
     2段階で非キー属性が別の非キー属性を決定しており、推移的関数従属性 あり。
5. 以上より模範解答と一致する。

誤りやすいポイント

• 「サービス名称／管理者名」→サービス機能IDの矢印を見て“部分従属性あり”と誤判定。これは候補キーの一部ではなく別集合からの従属性なので該当しない。
• リソースIDを候補キーに含めて {プロバイダID、サービス機能ID、利用者ID、リソースID} としてしまう。リソースIDは他の3属性によって一意に決まる（従属性側）ため冗長。
• 推移的従属性を “URI → リソースID” と逆向きに読んでしまう。図では矢印の向きが決定側→従属側である点に留意。

FAQ

解答の導き方

1. 対象属性の確認
   図1に「サービス提供リソース（プロバイダID、サービス機能ID、利用者ID、利用者登録日付、リソースID、URI、リソース種別、リソース名称、配信スキーマ）」とあるので、この関係の属性は上記のとおりです。図5の図示を見ると、リソースに関する属性群（リソース名称、URI、リソース種別、配信スキーマ）は「リソースID」から決まることが示されており、また中央上部（サービス機能ID、プロバイダID、利用者ID）から「利用者登録日付」へ関係が示されています。これらから関数従属性を整理します。
2. 図5から読み取れる主要な関数従属性（要約）

• 「リソースID」 → 「リソース名称、URI、リソース種別、配信スキーマ」（図5の矢印より）
• 「プロバイダID、サービス機能ID、利用者ID」 → 「利用者登録日付」 （図5の中央上部の矢印と、属性の説明「利用者登録日時は利用者、プロバイダごとにWebサービスとして本システムに登録した日付」から）

1. 候補キーの決定と正規形判定の手順

• 候補キー候補を考えると、問題文の意味と図5の矢印から、「プロバイダID、サービス機能ID、利用者ID」の組がこの関係内で利用者の登録情報を一意に表すキーになると判断できます（以後これをKと表す）。
• 第1正規形（1NF）：属性が原子値で表されているため満たします。
• 第2正規形（2NF）：候補キーKが複合キーである場合、非キー属性がKの部分集合のみに依存していないかを調べます。ここで、リソースに関する属性は「リソースID」に依存しますが、「リソースID」はKの一部分（例えばプロバイダIDや 利用者IDのみ）ではなくKによって決まる非主キー属性として扱われます。利用者登録日付はK全体に依存しています。したがって、非主キー属性がKの真部分集合のみに依存する「部分関数従属性」は図5から読み取れず、第2正規形は満たします。
• 第3正規形（3NF）：非キー属性が別の非キー属性を決定している（推移的関数従属性）がないかを調べます。ここではK → リソースID （リレーション内にリソースIDを保持しているため成立）かつ リソースID → リソース名称 等が成立するため、リソース名称 等はKを介してリソースIDによって決まる「推移的関数従属性」を持ち、第3正規形の要件を満たしません。

1. 第3正規形に分解する関係スキーマ（主キーは下線で示す）

• サービス提供（プロバイダID、サービス機能ID、利用者ID、リソースID、利用者登録日付）
  • 説明：K = (プロバイダID, サービス機能ID, 利用者ID) を主キーとし、そのもとで利用者登録日付や登録したリソースIDを保持します。
• リソース（リソースID、リソース名称、URI、リソース種別、配信スキーマ）
  • 説明：リソースIDを主キーとし、リソースに固有の属性群を格納します。

誤りやすいポイント

• 図中の矢印の読み方を誤るとキーや従属性を間違える。図5で複数属性を含む大きな矩形から矢印が伸びている場合は、その矩形に含まれる属性の集合が決定子として扱われる点に注意してください。
• 候補キーの定義を誤ると「部分関数従属性」と「推移的関数従属性」を取り違える。例えば誤ってリソースIDを候補キーに含めると、リソース名称 等の従属性が「部分従属性」に見えてしまい判定を誤ります。
• 候補キーに不要な属性を含めると、その集合は最小性を失い候補キーではなく非最小なキー（スーパーキー）になります。最小性（要素の取り除きで一意性が保てなくなること）を必ず確認してください。
• 切り出すべき属性群（今回はリソースに関する属性）がどれかを見落とすと、第3正規形化のための分解が不十分になります。

FAQ

解答の論理構成

1. 前提
   • 関係“所属”は履歴管理されるため、同一キーで複数レコードが保持される（表2）。
   • 「アクセスした時点の利用者の所属を前提」とは、タイムスタンプ 時点で有効な1件に限定することを意味する。
2. 内自然結合の結果（表4）
   • 内自然結合は キー属性「プロバイダID」「利用者ID」で単純に結合するため、有効・無効を区別せずタプルを生成する。
   • 取得後に「アクセス時点の有効性」フィルタを掛けなければならない。
3. 各タプルの検証

   タプル	属する“アクセスログ”	検証対象“所属”	判定
   ①	タイムスタンプ2013-04-10 12:00:00	所属登録日付2013-01-10	有効（登録日が先）
   ②	タイムスタンプ2013-04-10 12:00:00	所属登録日付2013-04-15	無効（登録日が後）
   ③	タイムスタンプ2013-04-03 14:00:00	所属登録日付2013-03-10	有効
   ④	タイムスタンプ2013-04-17 16:00:00	所属登録日付2013-02-10	有効
   ⑤	タイムスタンプ2013-04-18 18:00:00	所属登録日付2013-02-10	有効

4. 結論
   有効性を満たさないのはタプル ② のみ。

誤りやすいポイント

• 所属登録日付 と タイムスタンプ の大小関係を見落とし、「最新＝最大日付」と短絡してしまう。
• 履歴管理の有効判定を忘れ、内自然結合だけで正答だと判断する。
• 複数の“所属”レコードがある利用者（A,1）を例外なく評価対象とする必要に気付かない。

FAQ

解答の論理構成

1. 問題文は次の通り述べています。
   ――【問題文】「関係“所属” 及び関係 “アクセスログ”に対して内自然結合演算及び射影演算を行い、関係 “利用実績” を作成した。」
2. 内自然結合は、指定された属性値が両関係で一致する行だけを残し、片方にしかない行は削除します。
3. したがって、 ・“所属”には行があるが、“アクセスログ”に行が無い利用者
   は結合条件を満たさず“利用実績”に現れません。
4. 具体例
   表2にある「プロバイダID=B・利用者ID=2」は表3に無く、表4にも現れません。
5. よって「“アクセスログ”にタプルが無い＝まだアクセスしていない」利用者が条件となります。

誤りやすいポイント

• 「自然結合＝外部結合」と誤解して、片方に無い行も残ると思い込む。
• “所属”側に無い利用者が落ちると考え、“アクセスログ”側だけを見落とす。
• 表4の欠損を単なる抜粋だと誤認し、結合演算の性質を見逃す。

FAQ

解答の論理構成

1. 現状の演算
   ①【問題文】「関係“所属” 及び関係 “アクセスログ” に対して内自然結合演算 … を行い、関係 “利用実績” を作成」とある。
   ② その結果、表4では“所属”に存在する (プロバイダID=A, 利用者ID=1, 職種=営業、所属登録日付=2013-04-15) などは写っているが、「表3に行が無い利用者」は出力されない。
2. 要件の確認
   【小問説明】「現れない利用者も…タプルに現れるように」とある。つまり“所属”の行は全て残す必要がある。
3. 外部結合の性質
   • 左外自然結合演算：左関係の全組を保持し、右に対応する組が無ければNULLを付与する。
   • 右外・完全外では左側の残り方が要件を満たさない／冗長。
4. 結論
   よって「内自然結合演算」を「関係“所属”を左とする左外自然結合演算（または“所属”のタプルを全て出力する外自然結合演算）」に変更する。

誤りやすいポイント

• 単に「外部結合」と書いて左右を示さない
  ⇒ 右外結合にすると目的を達成できない。
• 「完全外結合」を選択
  ⇒ 余計に“アクセスログ”のみの行まで加わり、設問の趣旨とずれる。
• SQL 用語の LEFT JOIN だけを書く
  ⇒ リレーショナル代数の記号論である「左外自然結合演算」を明示すべき。

FAQ