平成23年度特別 データベーススペシャリスト 午後Ⅰ 問1

候補キーを先に確定させて考える

正規化の段階や分解方法を判断する前に、関係スキーマの候補キーを漏れなく確定させることが出発点です。与えられた従属関係から、どの属性の組が全体を一意に決めるかを丁寧に確かめましょう。

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

この問題で問われている判断の多くは、候補キーとの関係を基準に行われます。キーの見落としや取り違えがあると以降の判断が連鎖的にずれるため、最初にキーを一つずつ検証し、根拠を持って確定させることが重要です。

視点

候補キーとは、その値が決まれば行が一意に定まり、かつ余分な属性を含まない極小の属性の組のことです。候補キーは複数存在する場合もあります。そのため、一つ見つけて終わりにせず、別の組み合わせでも同じ性質を満たすものがないかを確かめる姿勢が求められます。検証では、ある属性集合から従属関係を順にたどり、決定できる属性の範囲がどこまで広がるかを確かめる考え方が役立ちます。

確認ポイント

• 与えられた従属関係をすべて書き出し、どの属性がどの属性を決めているかを整理したか
• 候補キーと考えた属性の組が全属性を決定でき、かつ一部を除くと決定できなくなるかを確かめたか
• 候補キーが一つとは限らないと意識し、他の属性の組み合わせも検討したか

解説

フィットネスクラブの会員管理データモデルを題材にした、データベース基礎理論の問題です。表1の意味・制約から関数従属性を読み取り、第1正規形違反の指摘、関数従属性図の完成、候補キーの特定、第1～第3正規形の判定と分解、全属性がキー属性である関係の更新時異状、自明でない多値従属性の特定と第4正規形への分解までを一通り問うています。

問題の全体像

D社は、メタボリックシンドローム対策として専任スタッフが会員を支援する有料メニューを提供するため、従来の会員管理データモデルを再設計しています。図1には、会員、会員種別、オプション、メニュー、スタッフ、自己記録、目標、測定、予約時間割の関係スキーマが示されています。図3～5は属性間の関数従属性、表1は属性の意味と制約、表2・表3は予約時間割とクラスの具体例です。出題趣旨によると、本問のねらいは、関数従属性の完成と識別、正規形の識別、問題点の指摘と関係の分割、高次の正規化（多値従属性・第4正規形）の適用能力を評価することです。採点講評によると、全体として正答率は高く、第3正規形までの知識で足りない場面に備えて高次の正規化も身に付けてほしいとされています。

設問1(1)

着目点は、表1の「有料メニュー選択」の説明です。申込み時に複数の有料メニューを選択でき、「この属性の値は、メニューIDの集合となる」と明記されています。第1正規形では属性値が単一値でなければならないため、集合を値にもつこの属性が違反の原因になります。なお、オプション選択は三つの中から一つを選ぶため単一値であり、違反の原因ではありません。公式解答例では、有料メニュー選択は複数のメニューIDの集合であり、値が単一値ではないことを理由として挙げています。40字以内の答案では、「有料メニュー選択」という属性名、「メニューIDの集合（複数値）」であること、「単一値でない」ことの三点を押さえます。

設問1(2)

図3の太枠は空欄で、図1の三つの関係の属性を割り当て、矢印を描いて完成させます。左の箱には利用可能日と利用可能時間が既に入っています。表1には「会員種別ごとに入会金、月会費、利用可能日、利用可能時間が決まっている」とあるので、左の箱の空欄は入会金と月会費で、会員種別IDから左の箱へ矢印を引きます。右下の箱には料金と定員があるのでメニューの属性群と判断でき、空欄はメニュー名と標準時間です。中央の空欄はメニューIDで、メニューIDから右下の箱へ矢印を引きます。右上の箱の空欄はオプション名と追加月額で、オプションIDから右上の箱へ矢印を引きます（表1ではオプションはオプションIDで一意に識別されます）。公式解答例もこの配置と矢印になっています。

設問1(3)

図4の関数従属性を読むと、スタッフIDは姓・名・性別・生年月日・電話番号・住所・プロフィールと登録日を決定します。取得資格と対応可能トレーニングを決定するのは {スタッフID、 更新日} です（表1：更新日ごとに履歴も保存）。担当設定日と勤務店舗を決定するのは {スタッフID、 担当可能日時} です。すべての属性を一意に決めるには、更新日と担当可能日時の両方が必要で、どちらか一方を欠くと残りの属性群が決まりません。そのため、公式解答例の候補キーは {スタッフID、 更新日、 担当可能日時} です。採点講評では、この3属性の組が候補キーであることに気づいていない誤答が散見されたとされ、タプルを一意に識別できる属性の組を状況記述から判別するよう求めています。

設問2(1)

図5を関係ごとに読み分けます。自己記録は、{会員ID、 記録日} から入力日と体重・最高血圧・最低血圧・総歩数への矢印があり、キーの一部や非キー属性からの従属はありません。したがって部分関数従属性も推移的関数従属性もなく、第3正規形です。測定も、{会員ID、 測定日時} がすべての属性を決定するだけなので、同じく第3正規形です。目標では、体脂肪率・筋肉量・体重・腹囲を決定するのが {会員ID、 設定日、 経過日数} なので、これが候補キーです。一方、基準日とスタッフIDはキーの一部である {会員ID、 設定日} だけで決まります（表1：同一会員・同一設定日で異なる基準日は設定できない）。これが部分関数従属性に当たるため、目標は第1正規形にとどまります。非キー属性間の従属は示されていないので、推移的関数従属性はなしです。公式解答例の表記は {会員ID、 設定日} → {基準日、 スタッフID} です。採点講評では、目標について候補キー・正規形・部分関数従属の有無・具体例の一部を誤る解答が散見されたとされ、部分関数従属性の定義を正確に理解するよう求めています。

設問2(2)

(1)の判定で第3正規形でないのは目標だけなので、関係名は目標です。部分関数従属性 {会員ID、 設定日} → {基準日、 スタッフID} を別の関係に切り出し、残りを元のキーで構成すれば第3正規形になります。公式解答例では、目標1（会員ID、 設定日、 基準日、 スタッフID）の主キーを {会員ID、 設定日}、目標2（会員ID、 設定日、 経過日数、 体脂肪率、 筋肉量、 体重、 腹囲）の主キーを {会員ID、 設定日、 経過日数} とし、それぞれに下線を引いています。答案では、分解後の両関係に共通属性 {会員ID、 設定日} を残して結合で元に戻せるようにすることと、主キーの下線を正しく付けることが要点です。

設問3(1)

表1によると、予約枠IDはメニューID・予定日時ごとに割り振られ、個人トレーニングでない場合は予約枠ごとに複数のスタッフが複数の会員を担当します。表2では、予定日時とメニューIDが会員とスタッフの組合せの数だけ重複して記録されています。採点講評では、本設問は関係の属性がすべてキー属性であることに起因する更新時の不都合を問うものとされています。公式解答例では次の三つを挙げています。①削除時異状：会員ID=A1の予約をすべて削除すると、スタッフID=S1が担当するメニューID=M1の情報もなくなる。②修正時異状：会員ID=C1の予定日時を変更するとき、複数行の予定日時をすべて変更しないと不具合が生じる。③挿入時異状：会員ID=A1の予約がないと、スタッフID=S1が担当するメニューID=M1の情報を登録できない。60字以内の答案では、表2の具体的な値（会員ID・スタッフID・メニューIDなど）を使い、どの操作をすると何が起きるかを一つに絞って書きます。

設問3(2)(a)

表3の予約枠ID=R4を見ると、会員C1・C2・C3とスタッフS2・S3のすべての組合せ（3×2=6行）が存在します。会員の集合とスタッフの集合は、予約枠IDによって互いに独立に決まっています。これは表1の「予約枠IDごとに複数のスタッフが複数の会員を担当する」という記述と一致します。図6は図2の多値従属性の凡例と同じ形で、左の単独の箱が決定側、右の二つの箱が従属側です。したがって、公式解答例のとおり a は予約枠IDです。

設問3(2)(b)

(a)で見たように、予約枠IDの値ごとに、会員IDの値集合がスタッフIDとは独立に決まります（予約枠ID →→ 会員ID）。公式解答例では b を会員IDとしています。b と c は順不同とされており、会員IDとスタッフIDのどちらを b に書いてもかまいません。

設問3(2)(c)

同様に、予約枠IDの値ごとに、スタッフIDの値集合が会員IDとは独立に決まります（予約枠ID →→ スタッフID）。多値従属性は常に対になって成り立つため、予約枠ID →→ 会員ID | スタッフID と表せます。公式解答例では c をスタッフIDとし、b と c は順不同とされています。

設問3(3)

自明でない多値従属性 予約枠ID →→ 会員ID | スタッフID に基づき、関係クラスを（予約枠ID、 会員ID）と（予約枠ID、 スタッフID）の二つに分解し、表3の値を射影して重複行を取り除きます。公式解答例では、一方の表（予約枠ID、 会員ID）はR1-A1、R2-B1、R2-B2、R3-A1、R4-C1、R4-C2、R4-C3の7行です。もう一方の表（予約枠ID、 スタッフID）はR1-S1、R2-S2、R3-S1、R4-S2、R4-S3の5行です。R4-S2などを表3の行数のまま繰り返さないことが重要で、「表の欄はすべて埋まるとは限らない」という注記もこれを示唆しています。二つの表を予約枠IDで結合すると表3に戻ることで、情報を失わない分解であると確認できます。採点講評では、(3)で第4正規形の具体例を正確に分割できていない解答が散見されたとされています。

学習のポイント

表1の制約文から関数従属性・多値従属性を正確に抽出することが重点です。特に、複数の決定項が組み合わさる候補キーの特定、部分関数従属性の定義、第4正規形への分解の具体例（重複行の除去）を確実に身に付けましょう。