平成25年度春期 データベーススペシャリスト 午後Ⅰ 問1

従属関係の根拠を書き出して整理する

この分野では、属性どうしの従属関係を与えられた情報だけから確かめて書き出すことが、後続の判断すべての土台になります。思い込みで関係を補わず、根拠となる記述や値を一つずつ照らし合わせながら整理を進めましょう。

この問題で問われていること

確認すべき観点は一つです。どの属性の組がどの属性を決めているかを、問題に示された条件や記述に基づいて自分の手で書き出し、その整理が途中で変わらないかを検討の各段階で見直すことを意識してください。

視点

関数従属性は、ある属性の値が決まると別の属性の値が一通りに定まるという関係を表す考え方です。この関係の捉え方によって、キーの見極め、表の分け方、表を結び付けた結果の見え方が連動して変わります。個々の判断に入る前に関係を明示しておくと、検討の筋道を追いやすくなります。

確認ポイント

• 書き出した各従属関係について、その根拠を問題中の記述や値に対応づけられるか
• 書き出した関係に、根拠のない思い込みや一般常識による補足が混ざっていないか
• 後の判断を行うとき、最初に書き出した関係と食い違う前提を置いていないか

解説

アクセスログ分析システムの関係スキーマを題材に、候補キーの特定、第3正規形への分解、履歴を管理する関係の関数従属性図の作成、部分関数従属性と推移的関数従属性の判定、内自然結合と外自然結合の違いを問う基礎理論の問題です。表1の意味及び制約と図2の表記法を正確に読み取ることが要になります。

問題の全体像

M社は、Webサービスのアクセスログを分析する本システムの関係スキーマ（図1）を検討しています。図3〜5は属性間の関数従属性の図で、表1は各属性の意味と制約です。〔利用者の行動傾向分析〕では、関係“所属”と関係“アクセスログ”を内自然結合し、射影して関係“利用実績”を作っています。出題趣旨によると、この問題のねらいは3つあります。①正規化の基礎的な理解、②関数従属性の図から関係スキーマを分析して正規形に変換する力、③外結合と内結合の代数演算の違いです。採点講評では、全体の正答率は高かったものの、キー属性・非キー属性・推移的関数従属性などの基本概念を正確に身に付けていない答案が見られたと指摘されています。

設問1(1)

図3では、{プロバイダID，利用者ID}から名寄せIDへ矢印が出ています。表1によると、利用者IDはプロバイダ内でだけ一意なので、利用者ID単独では識別できません。また名寄せIDは、プロバイダごとに登録された利用者が同一人物であることを示すIDです。そのため、複数のプロバイダの利用者が同じ名寄せIDを持つことがあり、名寄せIDは{プロバイダID，利用者ID}を決定しません。したがって公式解答例のとおり、候補キーは{プロバイダID，利用者ID}の一つだけです。

設問1(2)(a)

表1によると、特性IDは利用者ごとに複数管理されるので、関係“利用者”の主キーは{プロバイダID，利用者ID，特性ID}になります。図3では、特性名と特性タイプは特性IDだけに従属し、生年月日・性別・郵便番号は{プロバイダID，利用者ID}だけに従属しています。どちらも主キーの一部への従属（部分関数従属性）です。そのため、特性を定義しただけで、まだそれを持つ利用者がいない段階では主キーがそろわず、タプルを挿入できません。公式解答例では、挿入時の問題として「事前に特性ID，特性名，特性タイプを登録しておくことができない」を挙げています。あわせて「利用者ごとに，特性名，特性タイプが重複して登録される」「特性値ごとに，生年月日，性別，郵便番号が重複して登録される」も解答例に含まれています。35字以内の答案では、どの属性がどの単位で重複するか、または何を事前に登録できないかを具体的に一つ書きます。

設問1(2)(b)

部分関数従属性を、決定項ごとに別の関係へ分けます。公式解答例の関係スキーマと主キー（答案では実線の下線で示す部分）は次の3つです。①利用者（プロバイダID，利用者ID，生年月日，性別，郵便番号）。主キーは{プロバイダID，利用者ID}です。②利用者特性（特性ID，特性名，特性タイプ）。主キーは{特性ID}です。③利用者特性値（プロバイダID，利用者ID，特性ID，特性値）。主キーは{プロバイダID，利用者ID，特性ID}です。特性値は、どの利用者のどの特性の値かが決まって初めて決まるので、3つの属性の組が主キーになります。分解後の各関係では、非キー属性が主キー全体だけに従属し、非キー属性どうしの従属もありません。したがって第3正規形です。答案では、複合キーを構成する属性すべてに実線の下線を引くことが必要です。

設問1(3)

表1の制約から、次のことが読み取れます。所属情報（組織と職種）は所属登録日付で履歴が管理されます。利用者の組織・職種は同時期に一つだけです。組織情報（組織名称，郵便番号）は組織登録日付で履歴が管理されます。これをもとに、図4の空欄を公式解答例のとおり埋めます。右上の3つの枠は{プロバイダID，利用者ID，所属登録日付}、左上の単独の枠は職種、右下の2つの枠は{組織ID，組織登録日付}、左下の2つの枠は{組織名称，郵便番号}です。矢印は次の3本です。①{プロバイダID，利用者ID，所属登録日付}→職種。②{プロバイダID，利用者ID，所属登録日付}→組織ID（右上の枠から下へ、組織IDへ向かう矢印）。③{組織ID，組織登録日付}→{組織名称，郵便番号}。②は、ある利用者がある所属登録日付の時点で所属していた組織が一つに決まることを表します。これがないと、関係“所属”の組織IDがどこから決まるのかを図で示せません。採点講評では、属性をキー属性と非キー属性のグループに分けられていない解答が多かったと指摘されています。履歴を表す日付を決定項に含めることが要点です。

設問2(1)(a)

図5では、{プロバイダID，サービス機能ID，利用者ID}を囲む枠から、利用者登録日付とリソースIDへ矢印が出ています。リソースIDとURIは互いに決定し合い、URIから{リソース種別，リソース名称，配信スキーマ}が決まります。表1にあるとおり、複数のWebサービスが同じリソースを利用することがあります。そのため、リソースIDやURIから{プロバイダID，サービス機能ID，利用者ID}は決まりません。よって関係“サービス提供リソース”の全属性を決定できる極小の属性集合は、公式解答例のとおり{プロバイダID，サービス機能ID，利用者ID}の一つだけです。

設問2(1)(b)

公式解答例は「なし」です。サービス名称と管理者名は{プロバイダID，サービス機能ID}に従属します。しかしこれらは関係“Webサービス”の属性で、関係“サービス提供リソース”には含まれません。関係“サービス提供リソース”の非キー属性は、利用者登録日付もリソースIDも候補キー全体から決まります。それ以外の属性もリソースIDやURIを経由して決まります。候補キーの真部分集合だけで決まる非キー属性はないので、部分関数従属性はありません。図5には関係“サービス提供リソース”にない属性への矢印も描かれています。答案では、対象の関係に含まれる属性だけで判断することが要点です。

設問2(1)(c)

公式解答例は「あり」で、具体例として次の2つを示しています。①{プロバイダID，サービス機能ID，利用者ID}→リソースID→{リソース名称，リソース種別，配信スキーマ}。②{プロバイダID，サービス機能ID，利用者ID}→URI→{リソース名称，リソース種別，配信スキーマ}。候補キーは、非キー属性のリソースID（またはURI）を経由してリソース名称などを決定しています。一方で、リソースIDやURIから候補キーは決まりません。だから推移的関数従属性になります。採点講評では注意点が示されています。リソースID→URIとURI→リソースIDが両方成り立つため、リソースID→URI→{リソース名称，リソース種別，配信スキーマ}は推移的関数従属性ではありません。これを推移的関数従属性とした誤答が多かったとされています。A→B→CでもB→Aが成り立つ場合は推移的関数従属性にならない、という定義に沿って判定します。

設問2(2)(a)

繰り返し項目はなく、各属性は単一の値なので第1正規形は満たしています。設問2(1)のとおり部分関数従属性はないので第2正規形も満たします。しかし推移的関数従属性があるので第3正規形ではありません。よって公式解答例のとおり「第2正規形」です。

設問2(2)(b)

推移的関数従属性の中間にあるリソースID（URI）を決定項として、リソースの属性を別の関係に分けます。公式解答例の関係スキーマと主キー（答案では実線の下線で示す部分）は次の2つです。①サービス提供（プロバイダID，サービス機能ID，利用者ID，リソースID，利用者登録日付）。主キーは{プロバイダID，サービス機能ID，利用者ID}です。②リソース（リソースID，リソース名称，URI，リソース種別，配信スキーマ）。主キーは{リソースID}です。関係“サービス提供”にはリソースIDを残し、リソースの属性を参照できるようにします。関係“リソース”ではURIも候補キーになり得ます。ただし公式解答例ではリソースIDを主キーにしています。答案では、複合主キーを構成する3つの属性すべてと、リソースIDに実線の下線を付けることが必要です。

設問3(1)

表2と表3は{プロバイダID，利用者ID}で内自然結合されています。そのため、アクセス時刻と所属登録日付の前後関係に関係なく組み合わされています。プロバイダA・利用者1のアクセスは2013-04-10 12:00:00です。この時点の所属は、2013-01-10に登録された「#1・事務」です。「#1・営業」は2013-04-15の登録で、アクセスより後です。したがって、営業と組み合わせたタプル②は現れてはなりません。ほかのタプルは次のとおり問題ありません。③はA・利用者2の開発（2013-03-10登録）で、04-03のアクセスより前の登録です。④と⑤はB・利用者1の事務（2013-02-10登録）で、04-17と04-18のアクセスより前の登録です。公式解答例は②です。

設問3(2)

表2にはプロバイダB・利用者2がいますが、表3にはこの利用者のアクセスログがありません。内自然結合は、両方の関係に一致するタプルがある組合せしか出力しないため、この利用者は表4に現れません。公式解答例は「関係“アクセスログ”にタプルがない利用者」または「まだアクセスしていない利用者」です。25字以内で、アクセスログに該当タプルがないという条件を書きます。

設問3(3)

結合相手のない関係“所属”側のタプルも結果に残すには、外自然結合を使います。このとき、アクセスログ側の属性にはナルが入ります。公式解答例は「関係“所属”を左とする左外自然結合演算」または「関係“所属”のタプルを全て出力する外自然結合演算」です。単に「外結合」と書くのではなく、どちらの関係のタプルを全て残すのかを30字以内で明示することが要点です。採点講評では、この設問は他の設問に比べて正答率が低く、内自然結合と外自然結合の演算結果の違いについて理解を深めてほしいとされています。

学習のポイント

キー属性と非キー属性の分類、A→B→C かつ B→A のときは推移的関数従属性にならないという定義、登録日付を含む複合キーによる履歴管理、外自然結合で結合相手のないタプルを残す方法を重点的に確認してください。