平成22年秋期試験午後問題　問2(データベース)

平成22年秋期試験午後問題　問2

問2　データベース

　コールセンターの対応記録管理に関する次の記述を読んで，設問1～4に答えよ。
　F社では，新しいソフトウェア製品の発売と同時に，そのソフトウェア製品に関する質問を受けるコールセンターを開設することにした。コールセンターでの対応内容は，すべてデータベースに記録する。

〔コールセンターの業務〕

製品を購入した利用者には，一意な利用者IDが発行されている。質問を受ける際は，この利用者IDを通知してもらう。
対応内容をデータベースに記録する際，その質問の原因を特定する種別を設定する。種別とは，"マニュアル不備"，"使用法誤解"などの情報である。それぞれの種別に対して一意に種別IDを割り当てる。
データベースを検索し，過去に同じ種別IDをもつ類似の質問があった場合は，その受付番号を類似受付番号として記録しておく。

　図1は，これらの業務を基に，データベースを構成するデータ項目を抽出したものである。下線付きの項目は主キーを表す。

設問1

図1に示したデータ項目を正規化して図2に示す表を設計し，運用を始めた。実施した正規化に関する説明文の　に入れる正しい答えを，解答群の中から選べ。

　図1に示した状態は非正規形と呼ばれ，1事実1か所の関係が成立していないので，重複更新，事前登録，関係喪失などの問題がある。このため，第1正規化から順に第3正規化までを行うことにした。
　　まず，第1正規化の作業では，a。次に，第2正規化の作業では，b。そして，第3正規化の作業では，c。

a，b，c に関する解答群

受付番号と類似受付番号の組合せを主キーとし，繰返し要素を排除した
既に当該正規形に準じていたので，適用は不要だった
データ参照時の処理機能を考慮し，質問と回答を一つの表で管理するようにした
利用者表，サポート員表及び種別表を作成し，主キー以外の項目における関数従属性を排除した
類似表を作成し，主キーの一部における関数従属性を排除した

解答選択欄

a=ア
b=オ
c=エ

解説

関係データベースの正規化は以下の手順で行われます。

第1正規化: 関係データベースでは、属性と値を2次元の表で定義するので、繰り返し項目や集団項目をもつものはそのままでは表現できない。これらを排除し平坦な表で表せるようにする。
第2正規化: 主キーまたは復号主キーの一部の項目から一意に特定できる属性を別表に分離する。
第3正規化: 主キー以外の項目から一意に特定できる属性を別表に分離する。

〔aについて〕
第1正規化の作業では、繰り返し項目がある場合にこれを排除します。
図1「データベースを構成するデータ項目」をみると、"類似受付番号"が繰返し項目となっていることがわかります。また"類似受付番号"した時に表中の行を一意に特定するためには、受付ごとに発行される"受付番号"と"類似受付番号"を複合主キーにします。

図1「データベースを構成するデータ項目」を第1正規化まで行うと下表のようになります。

∴a＝ア: 受付番号と類似番号の組合せを主キーとし、繰返し要素を排除した

〔bについて〕
第2正規化の作業では、主キーまたは複合主キーの一部の項目から一意に特定できる属性を別表に分離します。
主キーは、"受付番号"と"類似番号"の組み合わせですので、この二つ又はどちらかの項目によって一意に特定できる属性を探します。
図1「データベースを構成するデータ項目」を見ると、"受付番号"が決まると、類似番号以外のすべての項目が一意に特定できる事がわかります。したがってこれらの項目を別表(対応表)に移動します。

解答によれば、類似表を作成し、主キーの一部における関数従属性を排除したとなっていますが手順通りに正規化を行い"類似受付番号"以外の属性をすべて別表に移したために、結果的に類似表が出来上がったと考えるとつじつまが合うと思います。

第2正規化まで行うと下表のようになります。

∴b＝オ: 類似表を作成し、主キーの一部における関数従属性を排除した

〔cについて〕
第3正規化の作業では、主キー以外の項目から一意に特定できる属性を別表に分離します。
主キー以外の項目から一意に特定できる属性なのは、

"利用者ID"→"利用者名"、"電話番号"、"メールアドレス"
"サポート員ID"→"サポート員名"
"種別ID"→"種別"

の3つですので、これらを"利用者表"、"サポート員表"、"種別表"として分離します。

第3正規化まで行ったものが、図2「正規化検討後の表」ということになります。

∴c＝エ: 利用者表、サポート員表及び種別表を作成し、主キー以外の項目における関数従属性を排除した

設問2

ある利用者から"オプションの指定方法"に関する質問を受けた。過去に類似の質問があったかどうかを確認するため，"オプション"というキーワードを含む質問をすべて抽出する。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。

解答群

質問 ANY ('%オプション%')
質問 ANY ('_オプション_')
質問 IN ('%オプション%')
質問 IN ('_オプション_')
質問 LIKE '%オプション%'
質問 LIKE '_オプション_'

解答選択欄

解説

SQL文の中で、あいまいな条件抽出をしたいときに使うのがLIKE句です。検索したい文字列にワイルドカード("%"や"_"など)を使用することができるので完全に一致するものだけでなく、部分的に一致するものも抽出することが可能です。

LIKE句で使うワイルドカードの意味ですが、"%"は「0個またはそれ以上の文字で構成される任意の文字列」、"_"は「任意の1文字」を表します。

この設問の場合は、属性"質問"の値に"オプション"というキーワードを含む行をすべて抽出すれば良いことがわかります。
文字列の中に"オプション"が含まれていればOKなので、LIKE句の検索文字列として「%オプション%」を指定します。

%オプション%の意味は、「0個以上の任意の文字列＋オプション＋0個以上の任意の文字列」ですので、結果的に"オプション"というキーワードを含むすべての行が抽出されることになります。

ANY句は、抽出条件に範囲を持たせたい場合に使用するSQLです。抽出条件をANY句に続くサブクエリで指定します。
IN句は、複数の候補(値)の、どれかが当てはまる場合にデータを表示したい場合に使用します。

設問3

製品のバージョンアップに当たり，コールセンターの対応記録を参考にして機能改善を検討することにした。種別が"使用法誤解"であった質問を抽出し，類似件数の多い順に表示する。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。

解答群

解答選択欄

解説

製品のバージョンアップのためにコールセンターに問合せの多かった質問を分析するSQLです。類似表の中の類似受付番号が多いほど、多くの質問が寄せられていることになるので、SQLでどの受付番号の質問と類似の問合せが多かったかを出力します。

まず対応表、種別表、類似表を受付番号列で結合します。その中で種別が"使用法誤解"である行だけを抽出し、類似受付番号ごとにその数を数えます。

そして、その結果が多かった類似受付番号から順番に、番号と個数の行を表に出力します。