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

キーワード・論点整理

• 関係“名寄せ”のリレーション定義
  名寄せ（プロバイダID，利用者ID，名寄せID）
• 関数従属性（FD）の図示（図３）
  • {プロバイダID, 利用者ID} → 名寄せID
• 候補キー
  関係内の全属性を一意に決定し、かつ最小性を満たす属性の組み合わせ

解答の根拠

1. リレーション“名寄せ”の属性を整理すると、以下の3つです。

   プロバイダID	利用者ID	名寄せID
   主キー候補	主キー候補	派生属性

2. 図３より、関数従属性（FD）は次のように示されています。

   中央矩形（プロバイダID＋利用者ID） → 上側小矩形（名寄せID）
   すなわち

   {プロバイダID, 利用者ID} → 名寄せID
   

3. 【候補キーの定義】に照らすと、
   • {プロバイダID, 利用者ID} からリレーションの全属性（プロバイダID, 利用者ID, 名寄せID）を決定できる。
   • また，プロバイダID 単独でも，利用者ID 単独でも名寄せID を一意に決定できないため，最小性を満たす。

受験者が誤りやすいポイント

1. 名寄せID をキーと勘違いしやすい
   • 属性の意味として「本システム内で利用者を一意に識別するID」とあるため、一見「名寄せID 単独」がキーに思える。しかし図３の FD では名寄せID から他の属性を決定する矢印が描かれておらず、逆方向（名寄せID → {プロバイダID, 利用者ID}）は成り立たない。
2. 最小性を見落とす
   • {プロバイダID, 利用者ID, 名寄せID} も全属性を決定するため「スーパーキー」ではあるが，「最小性」を満たさないので候補キーではない。

試験対策として覚えておくべきポイント

• 候補キーの要件
  1. 全属性を関数従属（決定）する
  2. 最小性を満たす（不要な属性を含まない）
• 関数従属性図の読み方
  • 矢印の根元が「決定側（左辺）」，矢印先が「被決定側（右辺）」であることを確認する。
  • 矢印の向きがない場合や逆向きの FD は成立しない。
• 属性の「意味」と FD は区別する
  • 属性の説明文だけでキーを決めず，必ず与えられた関数従属性図（または表記法）からキー候補を導出する。

1. キーワード／論点整理

• タプル挿入の異常（Insertion Anomaly）
• データの重複登録（冗長性）
• 関数従属性
  • {プロバイダID, 利用者ID} → {生年月日, 性別, 郵便番号}
  • 特性ID → {特性名, 特性タイプ, 特性値}
• 第3正規形（3NF）への分解

2. 解答の論理的説明

利用者（プロバイダID，利用者ID，生年月日，性別，郵便番号，
　　　　特性ID，特性名，特性タイプ，特性値）

• {プロバイダID, 利用者ID} → {生年月日, 性別, 郵便番号}
• 特性ID → {特性名, 特性タイプ}
• 特性ID → 特性値
  が成り立ちます。

• 利用者ごとに，特性名，特性タイプが重複して登録される。
• 特性値ごとに，生年月日，性別，郵便番号が重複して登録される。
• 事前に特性ID，特性名，特性タイプを登録しておくことができない。

3. 第3正規形への分解後スキーマ

• 「利用者」：利用者固有のプロフィール情報を管理
• 「利用者特性」：特性IDごとに名称・タイプを管理
• 「利用者特性値」：利用者×特性の組み合わせで値を登録

4. 受験上の注意ポイント

• 主キーの誤認
  図3の主キーは「プロバイダID＋利用者ID＋特性ID」です。部分キーだけで考えると冗長性を見落としやすいので注意してください。
• 関数従属性の整理
  ・候補キー→非キー属性の従属性をすべて把握
  ・非キー属性同士の推移従属性を見逃さない
• 正規化の目的
  挿入・更新・削除の各異常を防ぎ，冗長性を排除する
• 3NFの定義
  すべての非キー属性は「候補キーに対して直接従属し，かつ他の非キー属性に従属していない」こと

キーワード・論点整理

• 関数従属性（FD）
  「X → Y」とは，「同じXに対しては必ず同じYが対応する」性質を表します。
• 決定項（決定側）と従属項（従属側）
  決定項が従属項を一意に決定する組み合わせです。
• 主キー（候補キー）
  履歴管理がある場合は，日付やタイムスタンプもキーに含める必要があります。
• 図2 の表記法
  • A → B
  • {A, B} → C
  • C → {A, B}

解答の論拠と手順

1. 関係「組織」
   • 表1 より「組織情報（組織名称、郵便番号）は、履歴が管理される」（「組織登録日時」）
   • よって主キーは {組織ID, 組織登録日付}
   • この主キーが「組織名称」「郵便番号」を一意に決定する
2. 関係「所属」
   • 表1 より「利用者の所属する組織及び職種は、履歴が管理される」（「所属登録日時」）
   • よって主キーは {プロバイダID, 利用者ID, 所属登録日付}
   • この主キーが「組織ID」「職種」を一意に決定する
3. 図2 の表記法 に従って記述
   • {組織ID, 組織登録日付} → 組織名称
   • {組織ID, 組織登録日付} → 郵便番号
   • {プロバイダID, 利用者ID, 所属登録日付} → 組織ID
   • {プロバイダID, 利用者ID, 所属登録日付} → 職種

関数従属性一覧

受験者が誤りやすいポイント

1. 履歴管理の有無を見落とす
   • 「組織登録日付」「所属登録日付」をキーに含めず，主キーを誤認しやすい
2. 決定項と従属項の混同
   • 「組織ID → 組織名称」だけと書いてしまい，日付を抜かす誤り
3. 複数従属属性の書き方
   • 図では属性ごとに矢印を描くが，まとめて記述してもOK（表記法に注意）

覚えておくべきポイント

• 関数従属性の定義：X → Y は「X が同じなら Y も同じ」
• 履歴管理時の主キー設定：履歴日付やタイムスタンプを必ず含める
• 正規化と FD：第1～第3正規形，BCNF では「すべてのFD の決定項が候補キーであること」がポイント
• 図2 の表記法を使いこなす
  • 単一属性のFD：A → B
  • 複数属性のFD：{A, B} → C
  • 逆多値FD：C → {A, B} など

キーワードと論点整理

• 関係“サービス提供リソース”の属性
  「プロバイダID」「サービス機能ID」「利用者ID」「利用者登録日時」「リソースID」「URI」「リソース種別」「リソース名称」「配信スキーマ」
• 候補キー
  最小の属性集合で、関係内の全タプルを一意に識別できるもの
• 部分関数従属性
  候補キーの一部の属性で決定できる属性がある場合
• 推移的関数従属性
  候補キー → A → B のように、候補キーからある属性 A を経由して別の属性 B が決まる場合

解答の論理的根拠

1. 候補キーの決定
   • 問題文より，関係“サービス提供リソース”の主キーは

     「プロバイダID」「サービス機能ID」「利用者ID」「利用者登録日時」「リソースID」
     と定義されるが，
     「利用者登録日時」は利用者とリソース紐付けの履歴管理用時刻，
     「リソースID」はリソース自体を一意に識別するIDです。

   • しかし，同一の利用者が同一サービス機能IDに対して複数のリソースを所有できることから，
     「プロバイダID」「サービス機能ID」「利用者ID」「リソースID」の４属性で一意にタプル識別が可能です。
   • さらに「利用者登録日時」は履歴管理のための附属属性であり，一意性判定には不要なので，
     最小性を考慮すると候補キーは
     {プロバイダID, サービス機能ID, 利用者ID, リソースID}
     がまず考えられます。
   • しかし図５の関数従属性表示から，

     中央の５属性（サービス機能ID, プロバイダID, 利用者ID, リソースID, タイムスタンプ）
     のうち「タイムスタンプ」（利用者登録日時）は一意性に寄与せず，
     実際は {プロバイダID, サービス機能ID, 利用者ID} だけで「リソースID」も一意に決定できると読み取れます。

   • よって最小性をみたす候補キーは
     {プロバイダID, サービス機能ID, 利用者ID}
     と判定します。
2. 部分関数従属性の有無
   • 「候補キーの一部」だけで決まる属性があるかを見ると，
     • 例えば {プロバイダID, サービス機能ID} や {サービス機能ID, 利用者ID} など部分集合では，
       「リソースID」も「URI」も決まりません。
   • よって 部分関数従属性は「なし」。
3. 推移的関数従属性の検出
   • 図５より，

     {プロバイダID, サービス機能ID, 利用者ID}
       → リソースID
       → {リソース名称, リソース種別, 配信スキーマ}
     

     が読み取れます。
   • また「URI」を介しても同様に

     {プロバイダID, サービス機能ID, 利用者ID}
       → URI
       → {リソース名称, リソース種別, 配信スキーマ}
     

   • これらはいずれも，候補キー → A → B の形をしており，**推移的関数従属性「あり」**です。

関係“サービス提供リソース”のまとめ

推移的関数従属性の具体例

{プロバイダID, サービス機能ID, 利用者ID}
  → リソースID
  → {リソース名称, リソース種別, 配信スキーマ}

{プロバイダID, サービス機能ID, 利用者ID}
  → URI
  → {リソース名称, リソース種別, 配信スキーマ}

誤りやすいポイント

• 「利用者登録日時（タイムスタンプ）」をキーに含めてしまう
  → 履歴管理用の属性で，リソースを決定するためには不要
• 「リソースID」と「URI」どちらを主キーに含めるか迷う
  → 図５の矢印関係から，まずリソースIDが候補キーで決まり，さらに URI⇔リソース名称らを推移的に決定する構造になっている
• 部分従属性と推移的従属性を混同する
  → 部分従属性はキーの一部で直接決定，推移的従属性はキー→中間属性→最終属性の２段階

試験対策として覚えておくべきポイント

• 候補キーは最小性が重要：不要な属性は除外する
• 部分関数従属性＝「キーの一部→属性」の関係。これがあれば第２正規形違反
• 推移的関数従属性＝「キー→A→B」の二段階決定。これがあれば第３正規形違反
• 図などで関数従属性を示す場合、矢印の向きと階層構造を正確に読み取る
• 正規化の流れ：
  1. 重複排除 → 2NF（部分従属性解消） → 3NF（推移的従属性解消） → BCNF…

1. キーワード・論点整理

• 正規形（第1正規形／第2正規形／第3正規形）
• 主キー（候補キー）と非キー属性
• 部分関数従属性（部分的にキーに依存する関数従属性）
• 推移的関数従属性（非キー属性を介した関数従属性）
• 分解後の関係スキーマ設計

2. 解答の要点

• 正規形：第2正規形
• 分解後の関係スキーマ：
  1. サービス提供（プロバイダID，サービス機能ID，利用者ID，利用者登録日付，リソースID）
  2. リソース （リソースID，リソース名称，URI，リソース種別，配信スキーマ）

3. なぜ第2正規形なのか

3.1 関係スキーマと主キー

サービス提供リソース
（プロバイダID，サービス機能ID，利用者ID，利用者登録日付，リソースID，URI，リソース種別，リソース名称，配信スキーマ）

• 主キー（複合キー）＝ {プロバイダID, サービス機能ID, 利用者ID, 利用者登録日付}
• 非キー属性＝ {リソースID, URI, リソース種別, リソース名称, 配信スキーマ}

3.2 部分関数従属性の有無 → 第2正規形判定

• 「サービス提供リソース」では，リソースIDや URI などは，主キーの一部（例えばプロバイダIDだけではなく，利用者IDだけでもない）で決まるものではなく，主キー全体に依存しているように設計されています。
• 従って，部分関数従属性（主キーの一部分だけに依存するケース）は存在せず，第2正規形の条件を満たします。

3.3 推移的関数従属性の存在 → 第3正規形でない理由

リソースID → {リソース名称, URI, リソース種別, 配信スキーマ}

• 主キー全体 → リソースID
• リソースID → リソース名称 ・・・

4. 第3正規形への分解

• サービス提供：主キーに完全関数従属し，非キー属性（リソースID）だけが残っています。
• リソース ：リソースID を主キーとして，その属性群を管理します。

5. 受験者が誤りやすいポイント

1. 主キーの特定ミス
   サービス提供リソースの主キーを「リソースID を含めるか否か」で混乱しやすいですが，履歴管理の「利用者登録日付」を主キーに含める設計として整理すると，リソースID は非キー属性と見なせます。
2. 部分従属性 vs 推移的従属性の取り違え
   • 部分従属：キーの一部に依存
   • 推移：キー → 非キーA → 非キーB
     本設問では「リソースID → リソース名称…」は後者です。
3. 第2正規形なのか第3正規形なのかの判断
   推移的従属が残っているかどうかをまず確認し，「第3正規形ではない」結論を導く点を押さえてください。

6. 試験対策まとめ

• 第2正規形：主キーのすべての属性に完全関数従属しているかを確認。
• 第3正規形：推移的関数従属の有無を確認。非キー→非キーの依存を残さない。
• 分解後の設計：
  • 非キー属性間の関数従属は，別関係に分離することで解消
  • 各関係の主キーを明確にし，外部キーで関連付けを行う
• 図の読み取り練習：属性間の矢印（FD）が何を意味しているか，主キー／非キー属性は何かを即座に判断できるよう演習を重ねましょう。

キーワードと論点整理

• 関係“所属”と関係“アクセスログ”を自然結合し、さらに射影して関係“利用実績”を作成
• 所属登録日付とタイムスタンプの時系列的な整合性
• アクセス時点の所属情報は，「当該タイムスタンプが所属登録日付以降」でかつ「次の所属登録日付より前」であること

解答の導出過程

1. 【問題文】より，関係“利用実績” は
   • 関係“所属”と関係“アクセスログ”の内自然結合
   • その後の射影
     により作成されています。
2. 自然結合はプロバイダID／利用者IDが一致するタプル同士をすべて組み合わせますが，本来必要なのは「アクセス時点に実際に適用されている所属」を結合する条件です。
3. 関係“所属” の例（表2）と関係“アクセスログ” の例（表3）を示します。
   表2　関係“所属” の具体例

   プロバイダID	利用者ID	組織ID	職種	所属登録日付
   A	1	#1	事務	2013-01-10
   A	1	#1	営業	2013-04-15
   A	2	#2	開発	2013-03-10
   B	1	#1	事務	2013-02-10
   B	2	#2	事務	2013-04-01

   表3　関係“アクセスログ” の具体例

   プロバイダID	利用者ID	リソースID	配信値	CRUD	タイムスタンプ	位置情報
   A	1	S1	V1	C	2013-04-10 12:00:00	P1
   A	2	S1	V2	R	2013-04-03 14:00:00	P2
   B	1	S2	V3	U	2013-04-17 16:00:00	P3
   B	1	S3	V4	D	2013-04-18 18:00:00	P4

4. 自然結合のみ行うと，プロバイダID=A, 利用者ID=1 の組み合わせでは所属タプルが2行（「事務」「営業」）あるため，アクセスログの1行と掛け合わせて2行の出力が生じます。
5. 表4 の関係“利用実績”には次のように現れます。
   表4　関係“利用実績” の具体例

   番号	プロバイダID	利用者ID	組織ID	職種	配信値	タイムスタンプ	位置情報
   ①	A	1	#1	事務	V1	2013-04-10 12:00:00	P1
   ②	A	1	#1	営業	V1	2013-04-10 12:00:00	P1
   ③	A	2	#2	開発	V2	2013-04-03 14:00:00	P2
   ④	B	1	#1	事務	V3	2013-04-17 16:00:00	P3
   ⑤	B	1	#1	事務	V4	2013-04-18 18:00:00	P4

6. 番号②のタプルは，職種が「営業」となっていますが，【問題文】のタイムスタンプは

   「利用者の所属する組織及び職種は，履歴が管理される。」
   「所属登録日時」
   とあるように，職種「営業」の登録日付 2013-04-15 より前の 2013-04-10 12:00:00 のアクセスに適用できません。

7. よって，現れてはならないタプルは ② だけです。

誤りやすいポイント

• 自然結合だけしてしまい，「所属登録日付」と「アクセス時刻」の比較条件を入れ忘れる
• 履歴管理される属性では，「開始日時」と「終了日時」の間に含まれるかを必ず考慮する
• アクセスログが1行でも，所属タプルが複数行あると組み合わせ爆発と不整合を招く

試験対策としてのポイント

• 履歴管理（タイムスタンプ管理）のある関係を結合する際は，開始～終了の時制制約を明示的に確認する
• 自然結合で済ませず，**条件付き結合（non-equijoin）**が必要なケースを見極める
• モデル設計・SQL記述では，必ず「いつから」「いつまで」の条件をWHERE句やON句に盛り込む習慣をつける

キーワード整理

• 関係“利用実績”：
  「関係“所属”」と「関係“アクセスログ”」を内自然結合し、必要な属性を射影して得られる関係。
• 内自然結合：
  両方の関係に共通するタプルのみを結合する。
• アクセスログにタプルがない利用者＝まだアクセスしていない利用者

解答の理由

1. 問題文には次のようにあるため、

   「利用者の行動傾向分析を行うために,関係“所属” 及び関係 “アクセスログ”に対して内自然結合演算及び射影演算を行い,関係 “利用実績” を作成した。」

2. 内自然結合の性質上、どちらか一方に存在しない利用者（タプル）は結果に現れない。
3. 表２（所属）では利用者 B-2（プロバイダID=B, 利用者ID=2）も存在するが、
   表３（アクセスログ）には B-2 のタプルがないため、
   表４（利用実績）にも現れていないことから、
   「アクセスログに記録がない＝まだアクセスしていない利用者」であると判断できる。

誤りやすいポイント

試験対策として覚えておくべきポイント

1. 内自然結合（inner join）
   • 両側にマッチするキー値を持つタプルだけが残る。
   • 一方にしか存在しない行は結果に登場しない。
2. 射影（projection）
   • 必要な列だけを取り出し、残りは捨てる操作。
3. 結合演算の前後で欠損するタプルの意味を正しく把握する。
   • 結合元に「ログ記録がない」ケースは結果に含まれないことを意識する。

キーワード・論点整理

• 自然結合（inner join）
  共通属性をキーにして，両リレーションに共に存在するタプルのみを結合する演算。
• 外自然結合（outer join）
  左外／右外のいずれかを指定し，一方にしかないタプルも結果に残す自然結合。
  • 左外自然結合：左側リレーションのすべてのタプルを保持
  • 右外自然結合：右側リレーションのすべてのタプルを保持
• 本問の論点
  「関係“所属”のタプルをすべて出力し，アクセスログに対応する行がない場合も残す」ために，内自然結合ではなく左外自然結合を用いる。

解答説明

〔利用者の行動傾向分析〕
利用者の行動傾向分析を行うために,関係“所属” 及び関係 “アクセスログ”に対して内自然結合演算及び射影演算を行い,関係 “利用実績” を作成した。

SELECT …
FROM 所属
  LEFT OUTER JOIN アクセスログ USING (プロバイダID, 利用者ID);

誤りやすいポイント

• 「外自然結合」とだけ書くと左右の指定が不明瞭になる
  → 必ず左外か右外を明示する
• 「完全外結合（full outer join）」を選ぶと，アクセスログにも存在しない“利用”が含まれすぎる
  → 本問では「所属側のみすべて」を残せばよく，完全外結合は不要
• SQL用語の LEFT JOIN と関係代数の「左外自然結合」が一対一対応することを押さえる

試験対策ポイント

• 自然結合（inner join） vs. 外自然結合（outer join）の違いを明確に
• 左外結合・右外結合・完全外結合の使い分け
• 関係代数で「左外自然結合演算」と表現する点を覚える
• SQLでは LEFT OUTER JOIN，RIGHT OUTER JOIN，FULL OUTER JOIN のキーワード対応を正確に
• 「保持すべきリレーション（左か右か）」を問題文でしっかり読み取るクセをつける