データベーススペシャリスト 2012年 午後1 問02

解答の導き方

1. どの項目が必要か

• 問題文に「会員はログイン後に任意のコンテンツをお気に入りとして登録することができる。」とあります。従って「どの会員が」「どのコンテンツを」登録したかを記録する必要があり、会員側の識別子として「会員番号」、コンテンツ側の識別子として「コンテンツID」が必要です。
• また「お気に入りは、登録した日時の順に表示される。」とあるので、表示順を決めるために「登録日時」列が必要です。

1. お気に入りの主キーをどうするか（重複防止・表現意図の検討）

• 「お気に入り」は会員とコンテンツの関係を表す結びつき（多対多の関係を解消する中間関係）です。関係を一意に識別するには通常その両端の識別子の組合せを主キーとします。問題文に「同一会員が同一コンテンツを複数回登録できる」といった記述はないため、同じ会員・同じコンテンツの重複登録は想定せず、主キーは (会員番号, コンテンツID) とします。登録日時は表示順を決める属性であり、主キーには含めません。

1. 「サービス」「コンテンツ」「利用権限」の主キー・外部キー

• 「サービスは、Webサイトごとに複数提供され、WebサイトIDとサービスIDの組合せによって識別される。」とあるので、サービスの主キーは (WebサイトID, サービスID) です。WebサイトIDはWebサイトテーブルの主キーを参照する外部キーになります。
• 「コンテンツはコンテンツIDによって識別され、...」とあるので、コンテンツの主キーは コンテンツIDです。コンテンツはどのサービス・どのWebサイトの下にあるかが図3に示されているため、コンテンツ側にWebサイトIDとサービスIDを持たせ、サービスを特定するために (WebサイトID, サービスID) を参照する外部キーを張ります（参照先はサービスの主キー (WebサイトID, サービスID)）。なお問題文は「コンテンツはコンテンツIDによって識別され」ると明示しているので、主キーにコンポジットを用いる必要はありません。
• 「権限区分には“参照可”、“参照不可”、“参照及び投稿可” の3種類があり、コンテンツIDと会員区分の組合せでいずれかに定められる。」とあるので、利用権限テーブルの主キーは (コンテンツID, 会員区分) で、コンテンツIDはコンテンツ表のコンテンツIDを参照する外部キーになります。会員区分は会員の属性であり値集合（列挙）として扱うのが自然で、問題文の設計意図に従えば会員区分自体を参照整合性で別テーブルにするかは任意ですが、主キーには含めます。

• お気に入り（会員番号, コンテンツID, 登録日時）
  主キー：会員番号、コンテンツID
  外部キー：会員番号 → 会員(会員番号)
  　　　　：コンテンツID → コンテンツ(コンテンツID)
  解説：登録日時は表示順のための属性で、同一会員が同一コンテンツを重複登録することを許さない設計なら主キーに含めません。
• サービス（WebサイトID, サービスID, サービス名）
  主キー：WebサイトID、サービスID
  外部キー：WebサイトID → Webサイト(WebサイトID)
• コンテンツ（コンテンツID, WebサイトID, サービスID, コンテンツ名, ディレクトリ）
  主キー：コンテンツID
  外部キー：(WebサイトID, サービスID) → サービス(WebサイトID, サービスID)
  補足：コンテンツIDが一意であると明示されているため主キーは単一キーで良く、WebサイトID/サービスIDは所属関係を示す参照です。
• 利用権限（コンテンツID, 会員区分, 権限区分）
  主キー：コンテンツID、会員区分
  外部キー：コンテンツID → コンテンツ(コンテンツID)
  補足：会員区分は値集合（一般会員・期限付き会員・有償会員等）として扱い、権限区分は「参照可／参照不可／参照及び投稿可」を格納します。

誤りやすいポイント

• お気に入りの主キーに登録日時を含めると、同一会員が同一コンテンツを複数回登録できる設計になり、通常想定される「お気に入りは一件だけ登録される」振る舞いと齟齬が生じます。登録日時は順序付けのための属性であり、主キーには含めないのが適切です。
• サービスの主キーをサービスID単独にしてしまう誤り。問題文は「WebサイトIDとサービスIDの組合せによって識別される。」と明示しているため、サービスIDはウェブサイト内での識別子であり、WebサイトIDと合わせた複合主キーが正解です。
• コンテンツの主キーを (WebサイトID, サービスID, コンテンツID) のように冗長にする誤り。問題文で「コンテンツはコンテンツIDによって識別され」るとあるので、コンテンツID単独を主キーとすべきです。
• コンテンツがサービスを参照する際に、サービスの複合主キー (WebサイトID, サービスID) の両方を外部キーとして張らずに片方だけにする誤り。参照整合性を保つには複合キー全体と対応させる必要があります。
• 利用権限の主キーを権限区分だけにする誤り。仕様は「コンテンツIDと会員区分の組合せで定める」と明示しているため、(コンテンツID, 会員区分) が主キーです。

FAQ

解答の導き方

1. 主キーの特定
   図3の「掲示板」には「WebサイトID、掲示板ID、メッセージ番号、タイトル、掲示板開設日時、説明文、掲示板開設者会員番号、メッセージ本文、メッセージ投稿者会員番号、メッセージ投稿日時」が列挙されています。問題文にも「メッセージは、掲示板単位に連番のメッセージ番号によって、一意に識別される。」とあるため、各行は個々のメッセージを表し、主キーは (WebサイトID, 掲示板ID, メッセージ番号) で確定します。
2. 関数従属性の整理
   主キーに対する従属性を整理すると、(WebサイトID, 掲示板ID, メッセージ番号) → メッセージ本文、メッセージ投稿者会員番号、メッセージ投稿日時　が成り立ちます。一方、タイトル、掲示板開設日時、説明文、掲示板開設者会員番号は掲示板固有の属性であり、これらは (WebサイトID, 掲示板ID) により決まる（＝主キーの一部にのみ従属する）ため、非キー属性の部分関数従属が存在します。
3. 正規形の判定手順に当てはめる

• 第1正規形（1NF）：各属性は単一値で格納されており満たします。
• 第2正規形（2NF）：複合主キーの一部への非キー属性の依存（部分関数従属）を許さないため、ここでは満たしません。
• 第3正規形（3NF）：3NFは2NF成立を前提とするため満たしません。

誤りやすいポイント

• 主キー誤認：行の粒度（この表が「掲示板」単位か「メッセージ」単位か）を確認せずに主キーを (WebサイトID, 掲示板ID) とすると誤判定します。図中に「メッセージ番号」があるかどうかを必ず確認してください。
• 1NFと繰り返し行の混同：掲示板に複数メッセージがあることは行が複数あるだけで、列が多値になっているとは限りません。1NFは列の値が原子的かどうかを見ます。
• 「掲示板IDだけで一意」と決めつける誤り：問題文に掲示板の一意性が「WebサイトIDと 掲示板IDの組合せ」とある場合、単独の掲示板IDでは不十分です。
• 部分従属と推移的従属の混同：部分従属は「主キーの一部に依存」、推移的従属は「非キー属性が別の非キー属性に依存」です。判定時に区別してください。
• 正規化判定前に候補キーを明示しない：必ずキーを明示してから従属性を検討してください。

FAQ

• 掲示板表（主キー (WebサイトID, 掲示板ID)）：タイトル、掲示板開設日時、説明文、掲示板開設者会員番号
• メッセージ表（主キー (WebサイトID, 掲示板ID, メッセージ番号)）：メッセージ本文、メッセージ投稿者会員番号、メッセージ投稿日時、（必要なら）親メッセージ番号（返信用）
  この分割で非キー属性は各表の主キーに完全従属し、2NF/3NFの要件を満たせます。

解答の導き方

1. 図3の元設計の問題点を特定する
   図3の掲示板表を見ると、掲示板に関する属性（タイトル、掲示板開設日時、説明文、掲示板開設者会員番号）とメッセージに関する属性（メッセージ番号、メッセージ本文、メッセージ投稿者会員番号、メッセージ投稿日時）が同じ表に混在しています。問題文には「掲示板は、WebサイトIDと掲示板IDの組合せによって一意に識別される」とあり、また「メッセージは、掲示板単位に連番のメッセージ番号によって、一意に識別される」とあります。したがって元表の実際の行は各メッセージを表し、行の一意キーは (WebサイトID, 掲示板ID, メッセージ番号) になります。
2. 関数従属を明示する（第2正規形の観点）
   上記から次の関数従属が成り立ちます。
   ・(WebサイトID, 掲示板ID) → タイトル, 掲示板開設日時, 説明文, 掲示板開設者会員番号
   ・(WebサイトID, 掲示板ID, メッセージ番号) → メッセージ本文, メッセージ投稿者会員番号, メッセージ投稿日時

1. 分解方針（第3正規形にする）
   関数従属ごとに属性を適切な関係へ分離します。すなわち掲示板固有の属性は掲示板を表す関係へ、メッセージ固有の属性はメッセージを表す関係へ移します。さらに問題文に「返信が行われると、返信対象の投稿メッセージ (親メッセージ)との関係はツリー構造で表示される」とあるため、メッセージ間の親子関係を保持する属性を用意する必要があります。
2. 第3正規形であることの確認
   分解後、各関係について非キー属性が主キーに対して完全関数従属していること、かつ非キー属性同士の従属（推移的従属）がないことを確認します。掲示板関係では非キー属性はすべて (WebサイトID, 掲示板ID) に完全に従属します。メッセージ関係でも非キー属性（メッセージ本文等）は (WebサイトID, 掲示板ID, メッセージ番号) に完全に従属し、親メッセージ番号は参照キーであって他の非キー属性を決定しないため、第3正規形を満たします。
3. 親メッセージの参照の扱い（重要）
   「メッセージ番号は掲示板単位に連番」であるため、親メッセージを示すには単に親のメッセージ番号だけを格納して参照するのは不十分です。参照整合性を保証するには行自身にあるWebサイトID, 掲示板IDと組み合わせて (WebサイトID, 掲示板ID, 親メッセージ番号) が親メッセージの主キー (WebサイトID, 掲示板ID, メッセージ番号) を参照するように定義します。親がいない（ルート）投稿は親メッセージ番号をNULL許容とします。

誤りやすいポイント

• 親メッセージ参照を「親メッセージ番号」だけで定義してしまう（誤り）。メッセージ番号は掲示板単位での連番なので、必ず同じ行のWebサイトIDと 掲示板IDと組にして参照する必要がある。
• メッセージ表の主キーを単にメッセージ番号だけにしてしまう（誤り）。グローバルに一意でないため衝突する。
• 掲示板表に掲示板開設者の氏名などを入れてしまう（誤り）。会員情報は会員（会員番号）にまとめ、掲示板表には会員番号だけを外部キーとして持たせるべき。これをしないと更新時に推移的従属や更新異常が発生する。
• 親メッセージ番号を NOT NULLにしてしまう（誤り）。ルート投稿は親がないためNULLを許容する。
• 返信関係を別に独立したテーブルで管理すると説明する受験生がいるが、親子の木構造はメッセージ表の自己参照で十分表現でき、冗長な別表は不要であることが多い（業務要件により別構造が必要な場合は検討）。
• メッセージの順序（連番をどう付与するか）をスキーマだけで解決しようとして実装方法を示さない（試験では「掲示板単位の連番で識別される」との記述を根拠にキー設計を説明すること）。

FAQ

解答の論理構成

1. 要件分析
   問題文の要望(ア)に、
   ・「会員の区分として、一般会員、期限付き会員の他に、“有償会員”を新規に追加する。」
   ・「サービスの区分として、“有償サービス”を新設する。 〔新データベースのテーブル構造の設計〕 で定義したサービスは“無償サービス”とする。」
   ・「有償会員が購入したサービスを管理するために、“購入サービス”を新たに定義する。」
   と明示されています。ここから「会員」「サービス」を親とした汎化‐特化（スーパタイプ‐サブタイプ）の構造と、購買履歴を表すリレーションシップ（エンティティタイプ「購入サービス」）が必要であることが分かります。
2. 会員側の汎化‐特化
   既存の「一般会員」「期限付き会員」に加え、「“有償会員”」を同列に配置し、親の「会員」から三方向に分岐させます。これにより区分追加時でも親エンティティ「会員」の定義を変更せず拡張可能になります。
3. サービス側の汎化‐特化
   問題文で「“無償サービス”とする。」とあるため、親「サービス」から「無償サービス」「有償サービス」の２サブタイプをぶら下げます。こちらも区分追加時の保守性を担保します。
4. 購入履歴のリレーションシップ
   「“購入サービス”を新たに定義する。」という記述は、エンティティタイプ「購入サービス」が「有償会員」と「有償サービス」を関連付ける多対多関係を表すことを示唆しています。したがって
   ・「有償会員」→「購入サービス」
   ・「有償サービス」→「購入サービス」
   という二本のリレーションシップを書き込みます。
5. 図の完成
   以上を反映すると、模範解答図のように
   ・左ブロック：親「会員」＋サブタイプ「一般会員」「期限付き会員」「有償会員」＋「購入サービス」への連結
   ・右ブロック：親「サービス」＋サブタイプ「無償サービス」「有償サービス」＋「購入サービス」への連結
   という形で要望(ア)を完全に満たす概念データモデルになります。

誤りやすいポイント

• 「有償会員」ではなく「会員」と「購入サービス」を直接結んでしまい、ビジネスルール（無償会員は購入しない）を表せなくなる。
• 「購入サービス」をリレーションシップではなく単なる属性として扱い、購入日や支払方法など複数レコードが想定されるデータを格納できなくなる。
• 「無償サービス」を描かず「サービス」→「有償サービス」だけを特化と誤認し、排他関係が破綻する。
• 汎化‐特化を表すときにスーパタイプとサブタイプの記号（三角形や線分）を省略し、読み手に関係が伝わらない。

FAQ

解答の論理構成

1. まずメルマガ自体を定義
   • 【問題文】「“メルマガはWebサイトIDとメルマガIDの組合せによって一意に識別し、メルマガ名を管理する。」
     ⇒ 実体：メルマガ
     ⇒ 主キー： WebサイトID 、メルマガID
     ⇒ 属性：メルマガ名
2. 会員とメルマガの多対多関係を解消
   • 【問題文】「会員は、利用しているWebサイトについて、複数のメルマガを購読できる。」
     ⇒ N対Nを1対N × N対1に分割する中間表が必要
     ⇒ 実体：購読メルマガ
     ⇒ 主キー： WebサイトID 、メルマガID 、会員番号
     ⇒ 属性：購読開始日
3. 配信履歴（バックナンバ）を管理
   • 【問題文】「配信したメルマガは、バックナンバとしてメルマガ本文を配信日時ごとに管理する。」
     ⇒ 実体：バックナンバ
     ⇒ 主キー： WebサイトID 、メルマガID 、配信日時
     ⇒ 属性：メルマガ本文
4. 参照整合性
   • 購読メルマガ と バックナンバ の外部キーは（ WebサイトID 、メルマガID ）で メルマガ を参照。
   • 購読メルマガ の外部キー（ 会員番号 ）は 会員 を参照。
     これで全文要件をカバーし、第3正規形も満たします。

誤りやすいポイント

• メルマガIDを単独主キーにして「 WebサイトIDを列追加」しただけにする → 複数サイトでIDが重複したとき一意性が崩れる。
• 購読メルマガ の主キーを（ 会員番号 、メルマガID ）としWebサイトIDを含め忘れる → 異なるWebサイトのメルマガを区別できなくなる。
• バックナンバ で 配信日時 をPKに入れず、最新だけ保持する想定にしてしまう → 履歴閲覧要件を満たせない。

FAQ

解答の論理構成

1. 手順1の目的を確認
   • 【問題文】“旧データベースと項目の構成方法が異なるデータを編集する。”
   • 具体作業：“分割・統合” によって新DBと同じ項目構成に合わせる。
2. WebサイトB（表2）の列構成を確認
   • 列 “氏名漢字” に “斎藤△太郎” の形式で姓と名が同一セルに格納。
   • 新DBでは【問題文】表1と同様に “姓(漢字)”“名(漢字)” が別列。
   • よって “姓名分離” が必須。
3. WebサイトC（表3）の列構成を確認
   • “住所” 列には “東京都文京区◯◯2－28－8－102” のように都道府県＋市区町村以下が混在。
   • 新DBでは表1・表2と同様に “都道府県” 列が独立している。
   • よって “都道府県抽出” が必要。
4. 以上より編集内容をまとめる
   • B：氏名列を 姓 と 名 に切り出す。
   • C：住所列から 都道府県 を切り出す。

誤りやすいポイント

• “フリガナを統一” など手順3で求められる標準化と混同する。
• Cの編集対象を “メールアドレス” と誤認し、重複チェックを挙げてしまう。
• “分離” と “統合” の両方を羅列し、20字以内を超過する。

FAQ

解答の導き方

誤りやすいポイント

• 採番規則が違うことだけを見て「重複しない」と誤判断する。どちらも9桁の番号なので、同じ値が生じ得る。
• 表中の例示（見た目）と注釈（採番規則）を混同し、注釈を無視する。注釈の採番規則に従って考える必要がある。
• 手順2をそのまま実行しても、既存参照（利用Webサイトテーブル等）との整合性や外部キー制約の影響を考慮しない。
• 回避策として単に「桁数を長くする」だけで済ませ、既存データとのマッピングを怠るとリンク切れが発生する。

FAQ

解答の導き方

1. 手順3の目的と例を確認する。問題文は「データの表記のばらつきを解消するために、標準データに変換する」と定め、例として「姓と名のフリガナは、ひらがなに変換する」と示している。これは表記揺れの解消が手順3の主要目的であることを意味する。
2. 表1〜3で表記揺れの箇所を洗い出す。具体例を直接確認すると次のように異なっている。
   • 姓の異字体の混在：表1に「沢田」、表2に「澤田」、表2に「髙橋△次郎」、表1に「高橋」がある。これらは旧字体／異体字の混在である。
   • フリガナの表記差：表1に「さいとう（ひらがな）」、表3に「サイトウ（カタカナ）」といった異なる表記がある（手順3の例はこれに対応する）。
   • 住所表記のばらつき：表1に「文京区◯◯◯2-28-8-102」、表2に「文京区〇〇２－２８－８－１０２」、表3に「東京都文京区◯◯2－28-8-102」など、数字の全角・半角、ハイフンの種類、丁目以降の書式が異なる。
   • メールアドレスの存在：表3にメールアドレス列（例: 「saito@aa.jitec.jp」）があり、事業要件で「メルマガを配信」することが明記されているため、メールの正規化も検討対象となる。
   • 表2に「△は空白を表す」と注記があるため、「斎藤△太郎」等は空白の表現であることに注意する。
3. なぜ上の結論が導かれるかを論理的に説明する。
   • 旧字体／異体字は文字列比較に影響して重複検出や突合作業（手順2でキーを統一する作業）で誤判定を招く。したがって「旧字体→新字体」の変換で同一人の文字列を一致させやすくする必要がある。
   • 住所は位置情報照合や郵便照合、重複判定に使うため、丁目以降（番地以下）の表記様式（算用数字／ハイフン等）を統一すると検索・比較が安定する。手順3は「WebサイトAのデータを基準として標準化を行う」としているので、基準に合わせて明確な変換ルールを定めるべきである。
4. 最終的に手順3で要求される「表記揺れの解消」に対して優先度が高く、表1〜3の実データに直接現れているものを選んで上記の2項目を解答とする。メールアドレス正規化も重要な候補であるが、設問の要求（2項目）に合わせて上記2点を選んだ。

誤りやすいポイント

• 表2の「△」を見て「△自体が別の文字」と誤認する。注記に「△は空白を表す」とある点を必ず確認すること。
• 「斎藤」を旧字体の例と誤って扱う（表中の例は「澤田／沢田」「髙橋／高橋」が旧字体・異体字の実例）。
• 住所の違いを「単なる見た目の違い」と軽視し、丁目以降の数値・ハイフン・全角半角の差を標準化対象から漏らす。
• メルマガ配信の要件があるのにメールアドレスの正規化を見落とすと、配信や重複判定で問題が発生する。
• 標準化の基準を定めずに無作為に文字を変換すると、法的・契約上の表記（本人確認書類と整合が必要な項目）で問題となる可能性がある。

FAQ

解答の論理構成

1. 【問題文】では手順4の作業方法に「この作業を行うための具体的な判定基準、ルールは別途定義する。」とあります。これは“判定＝同一人物かどうか”を意味します。
2. さらに冒頭で「各Webサイトの重複した会員データを統合する」と述べられており、目的は重複レコードの統合です。
3. 手順1〜3で形式統一までは完了していますが、まだ重複行は残存します。そこで、同一人物と考えられる複数行を「特定」する処理が必要になります。
4. 以上より、(d)には「複数のWebサイトを利用している、同一人物であると考えられる会員の会員データを特定する」が妥当となります。

誤りやすいポイント

• 「削除」や「統合」と書いてしまう
  手順4は“候補の特定”であり、削除・統合は手順5以降です。
• 判定基準の作成と誤解する
  判定基準は「別途定義する」と既に書かれているため、(d)は基準作成ではなく特定作業です。
• 「重複データの検索」だけを書く
  “同一人物であると考えられる”という主旨を明示しないと減点対象となります。

FAQ