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

要件記述と設計要素の対応を確かめる

データベース設計の問題では、業務要件の一文ごとに、それが設計のどの要素にどう反映されるべきかを自分の言葉で言い直してから、問われている箇所と照らし合わせる姿勢が役立ちます。思い込みではなく記述を根拠に考えましょう。

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

確認すべき観点は一つ、「業務の記述が、データ同士のどんな結び付きや決まり方を述べているか」です。その読み取りを先に固め、設計側の表現がその内容を過不足なく反映しているかを照合してください。

視点

設計の良し悪しは、業務のルールをどれだけ忠実に写し取れているかで判断されます。一般常識や他の業務での経験から補って読むと、記述にない前提を持ち込みがちです。まず業務の記述だけから、何がどんな単位で存在し、互いにどう関係し、何が決まれば何が決まるのかを書き出し、その整理を設計の表現と一つずつ突き合わせるのが基本の進め方です。

確認ポイント

• 業務の記述のうち、データ同士の関係やデータの決まり方に触れている箇所をすべて拾い出したか
• 拾い出した内容を、記述にない前提を足さずに自分の言葉で言い直せているか
• 言い直した内容と設計側の表現を照合し、食い違いや漏れがないかを根拠付きで説明できるか

解説

グループウェアのデータベース設計を題材に、電子会議投稿の候補キーと関数従属性の分析、第3正規形への分解、図4の属性の補完、図3のリレーションシップ（グループの自己参照を含む）と○・●の判断、ロール管理の追加、差戻し後の再承認で主キーが重複する不具合への対処を問う問題です。公式解答例では、承認の主キーを｛申請ひな形番号，申請連番，承認連番｝という最小の組合せに改めています。

問題の全体像

D社は次期グループウェア（GW）を開発しており、S君が利用者管理・予約・コミュニケーション・ワークフローの各機能について、概念データモデル（図3）と関係スキーマ（図4）を設計しました。出題趣旨によると、業務要件を分析する力、関数従属性や正規化理論を使ってデータモデルを分析する力、データモデルの問題を見つける力が問われています。設問1は正規化されていない関係「電子会議投稿」の分析と分解です。設問2は図4の空欄補完と、図3の不足しているリレーションシップの記入です。設問3は、T部長が指摘したロール管理の欠落（指摘事項①）と、承認処理で起きる不具合（指摘事項②）への対応です。採点講評によると、全体の正答率は高かった一方、設問1(2)では外部キーの破線が抜けた解答、設問2(2)ではグループの自己参照が抜けた解答、設問3(2)では主キーに承認連番を単に足しただけの解答が目立ちました。

設問1(1)

表1の制約に着目します。投稿番号は「電子会議番号との組合せで投稿を一意に識別する」とあるので、｛電子会議番号，投稿番号｝が候補キーになります。また、表示順は「一つの分野内で重複することはない」ので、｛分野番号，表示順｝から電子会議番号が一意に決まります。したがって、電子会議番号を｛分野番号，表示順｝に置き換えた｛分野番号，表示順，投稿番号｝も、全ての属性を決定する最小の組合せとなり、候補キーです。公式解答例もこの二つを挙げています。部分関数従属性は「あり」です。候補キーの一部である電子会議番号だけで議題（ほかに分野番号・表示順・作成者ユーザIDも）が決まるためで、公式解答例では例として「電子会議番号→議題」を示しています。推移的関数従属性も「あり」です。電子会議番号から分野番号が決まり、分野番号から分野名が決まるので、公式解答例では「電子会議番号→分野番号→分野名」を示しています。

設問1(2)

候補キーの一部への部分関数従属性があるので第2正規形ではなく、公式解答例では「第1正規形」と答えています。第3正規形へは、電子会議に関する属性、分野に関する属性、投稿に関する属性の三つに分解します。公式解答例の関係スキーマと、各属性のキーの扱いは次のとおりです。【分野（分野番号，分野名）】分野番号が主キー（実線の下線）で、外部キーはありません。【電子会議（電子会議番号，議題，分野番号，表示順，作成者ユーザID）】電子会議番号が主キー（実線）です。分野番号は分野を参照する外部キー（破線）、作成者ユーザIDはユーザを参照する外部キー（破線）です。議題と表示順には下線がありません。【投稿（電子会議番号，投稿番号，投稿本文，投稿者ユーザID）】｛電子会議番号，投稿番号｝が主キーで、両方に実線を付けます。電子会議番号は電子会議を参照する役割も持ちますが、公式解答例では主キーの実線だけが付けられています。投稿者ユーザIDはユーザを参照する外部キー（破線）で、投稿本文には下線がありません。採点講評によると、作成者ユーザIDと投稿者ユーザIDに破線がない解答が散見されました。分解する関係の中だけで考えず、図4のユーザなど関連する関係との整合性を確認して、外部キーを漏れなく明記することが大切です。

設問2(1)(a)

1.(4)に「ユーザは、一つのグループに必ず所属し、これを主務グループと呼ぶ」とあります。ユーザ1人につき主務グループは一つなので、関係「ユーザ」に属性として持たせます。一方、兼務は複数あり得るので、別の関係「兼務グループ」になっています。公式解答例は「主務グループID」で、グループを参照する外部キーとして破線の下線を付けます。

設問2(1)(b)

2.(2)に「設備には、必要に応じて、当該設備の管理を行うグループを一つ定めることができる」とあります。設備1件につき管理グループは最大一つなので、関係「設備」の属性にします。公式解答例は「管理グループID」で、グループを参照する外部キーとして破線の下線を付けます。

設問2(1)(c)

メッセージには送信者が1人必ず存在します。一方、送信先は複数あり得るので、関係「メッセージ送信先」に分けられています。したがって、関係「メッセージ」には送信した側のユーザを持たせます。公式解答例は「送信元ユーザID」で、ユーザを参照する外部キーとして破線の下線を付けます。

設問2(1)(d)

3.(1)の「1人又は複数のユーザにメッセージを送信することができる」から、1件のメッセージに複数の送信先があります。メッセージIDだけでは行を識別できないので、｛メッセージID，送信先ユーザID｝が主キーになります。公式解答例は「送信先ユーザID」で、主キーを構成するので実線の下線を付けます（ユーザを参照する役割も持ちます）。公式解答例の備考では、d・eは順不同とされています。

設問2(1)(e)

3.(1)に「送信先のユーザがメッセージを開封すると、開封日時が記録される」とあります。開封はメッセージ単位ではなく送信先のユーザごとに発生するので、関係「メッセージ送信先」の属性になります。公式解答例は「開封日時」で、キーではないため下線は付けません。d・eは順不同です。

設問2(1)(f)

2.(1)に「スケジュールを予約されたユーザは、そのスケジュールに参加するか否かを回答することができる」とあります。回答は予約と予約先ユーザの組ごとに決まるので、関係「スケジュール予約先」の属性になります。公式解答例は「参加可否回答」で、下線は付けません。

設問2(2)

図4の外部キーと問題文から、図3に足りないリレーションシップを全て洗い出します。以下、「A 1 対 B 多」は、A側の端とB側の端にそれぞれ付く記号を示します。公式解答例で追加されているのは次の8本です。①ユーザ1対予約多（予約者ユーザID）：ユーザ側●、予約側○。予約者は必ず存在しますが、予約しないユーザもいるためです。②予約1対設備予約先多：予約側●、設備予約先側○。2.(3)により、スケジュールだけの予約もあり得るためです。③予約1対スケジュール予約先多：予約側●、スケジュール予約先側○。設備だけの予約もあり得るためです。④ユーザ1対メッセージ多（送信元ユーザID）：ユーザ側●、メッセージ側○。送信元は必ず存在しますが、送信しないユーザもいるためです。⑤メッセージ1対メッセージ送信先多：両側とも●。送信先は1人以上であり、送信先は必ずいずれかのメッセージに属するためです。⑥グループ1対ユーザ多（主務グループID）：グループ側●、ユーザ側○。ユーザは必ず主務グループに所属しますが、1.(4)の「ユーザが一人も所属しないグループが存在する」によりユーザ側はゼロを含みます。⑦グループ1対設備多（管理グループID）：両側とも○。管理グループは「必要に応じて」定めるものであり、設備を管理しないグループもあるためです。⑧グループの自己参照（上位グループID）：グループからグループへの1対多で、両側とも○。表1で上位グループIDはNULLになり得ますし、下位のグループを持たないグループもあるためです。図3にもともと描かれている5本（ユーザ●―○スケジュール予約先、ユーザ●―○兼務グループ、グループ●―○兼務グループ、設備●―○設備予約先、ユーザ●―○メッセージ送信先）は、そのまま残します。採点講評によると、⑧の自己参照を書き漏らした解答が散見されました。1.(2)の「上位のグループを一つ定めることができる」から読み取るべき関係です。

設問3(1)

1.(3)と1.(4)によると、ロールはロールIDで一意に識別されてロール名を持ち、ユーザとロールは多対多で付与されます。そこで、ロール本体と、多対多を解消する連関の関係を追加します。公式解答例の関係スキーマと、キーの扱いは次のとおりです。【ロール（ロールID，ロール名）】ロールIDが主キー（実線）です。【ロール付与先（ロールID，ユーザID）】｛ロールID，ユーザID｝が主キーで、両方に実線を付けます。ロールIDはロールを、ユーザIDはユーザを参照する役割も持ちますが、公式解答例では主キーの実線で示されています。さらに、4.(3)ではステップの承認者をユーザ・グループ・ロールのいずれでも指定できますが、図4の決裁ルートには承認ユーザIDと承認グループIDしかありません。そこで、公式解答例では関係「決裁ルート」に属性「承認ロールID」を追加しています。これはロールを参照する外部キーにあたり、既存の承認ユーザID・承認グループIDと同じ扱いの属性です。

設問3(2)

図4の承認の主キーは｛申請ひな形番号，申請連番，ステップ番号｝です。一方、4.(5)の差戻しと、4.(7)の「承認処理を行うと、その都度処理内容がデータベースに新規登録される」を合わせて考えると、差戻しで戻ったステップで同じ申請を再度処理したとき、同じ主キーの値の行をもう一度登録することになり、登録できません。公式解答例では、その状況を「一度承認を行った申請が差戻しされた後、再度承認処理を行ったとき」（40字以内）と述べています。答案では、差戻しによって同じステップで2回目の承認処理が起こることを押さえます。修正にあたっては、表1で承認連番が「申請ひな形番号ごと・申請連番ごとに1から始まり、承認処理を行うごとに1ずつ加算される」とされている点に着目します。この定義から、｛申請ひな形番号，申請連番，承認連番｝だけで承認処理を一意に識別できます。公式解答例の関係スキーマは「承認（申請ひな形番号，申請連番，ステップ番号，承認連番，承認処理結果，コメント，承認者ユーザID，承認日時）」で、キーの扱いは次のとおりです。主キーは申請ひな形番号・申請連番・承認連番で、いずれも実線です。ステップ番号は主キーから外れ、申請ひな形番号と組み合わせて決裁ルートを参照する外部キーとして破線を付けます。承認者ユーザIDはユーザを参照する外部キー（破線）です。承認処理結果・コメント・承認日時には下線がありません。採点講評によると、既存の主キーに承認連番を単に追加しただけの解答が多くありました。その場合は主キーが最小にならないので、各属性の意味・制約から最小の組合せを確認することが求められます。

学習のポイント

表1の意味・制約から候補キーと関数従属性を導く練習をしましょう。自己参照のリレーションシップや○・●を問題文の語句から判断すること、主キー・外部キーの下線を正確に書き分けること、主キーを最小限の属性の組合せにすることも重点です。