応用情報技術者過去問題 平成25年春期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
テキストマイニングツールを活用したシステムへの機能追加における設計と実装に関する次の記述を読んで,設問1~4に答えよ。
D社は,家電製品を製造販売する大手企業であり,顧客サポートシステムとホームページを運用している。顧客サポートシステムでは,製品に対する問合せや回答を管理している。ホームページでは,顧客と社員が発言を書き込める製品別の掲示板や活用事例が用意されている。D社では,サポート内容や製品の活用事例を検索するためのキーワードをマスタとして一元管理している。
今回,更なる製品販売・活用推進,顧客満足度向上のために,テキストマイニングツール(以下,ツールという)を導入し,顧客サポートシステムとホームページの機能を強化した新顧客サポートシステム(以下,新システムという)を構築することになった。このツールによって,掲示板への発言内容とキーワードを,キーワードマスタを用いて関連付ける。また,発言内容を分析し,肯定的か否定的かを自動的に判別する。製品に対する問合せや回答の内容,製品の活用事例についても同様に,ツールとキーワードマスタを用い,キーワードを関連付ける。運用方法としては,毎日,夜間にツールで処理した結果を新システムに取り込む。
新システム全体のE-R図の抜粋を図1に,各エンティティの概要を表1に示す。 新システムでは,E-R図のエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。
〔発言キーワードへの重み付け〕
発言キーワードの重みを,掲示板上の社員以外の顧客による発言のうち,そのキーワードを含む発言数と定義する。つまり,登録されたキーワードを含む発言数が多いほど話題性が高く,重要度の高いキーワードであると定義する。発言キーワード重み表にその重みの値を集計するためのSQL文を図2に示す。ただし,掲示板上の発言にはなく,キーワード表だけに存在するキーワードの重みは0として集計する。また,発言キーワード重み表のレコードは集計の前に削除されている。
なお,関数 COALESCE(A,B) は,AがNULLでないときはAを,AがNULLのときはBを返す。〔顧客サポートシステムの機能強化〕
顧客サポートシステムでは,電話やインターネットからの問合せや回答を管理している。掲示板に書き込まれた否定的な発言を,含まれるキーワードの重みの総和が大きいものから順にリストアップする機能を追加する。そのリストの上位から順に,各発言に対する回答を記入する画面を開き,回答履歴から類似した内容を照会して,適切な回答を担当者が掲示板に書き込むことで,顧客満足度向上を目指す。
否定的な発言を,含まれるキーワードの重みの総和が大きいものから順に出力するためのSQL文を図3に示す。〔活用事例コンテンツの充実〕
ホームページのコンテンツの一つとして各製品の用途に応じた活用事例紹介がある。活用事例が検索されやすくするために,活用事例ごとにキーワードを登録するだけでなく,活用シーンにもキーワードを関連付けることによって,より的確に活用シーンを検索できるようにする。
ビデオカメラの活用事例の画面例を図4に,活用シーンに登録されているキーワードの例を表2に示す。図4の活用事例は学校行事の活用シーンなので,表2に登録されている運動会や文化祭といったキーワードでも,この活用事例が検索されるようにしたい。
D社は,家電製品を製造販売する大手企業であり,顧客サポートシステムとホームページを運用している。顧客サポートシステムでは,製品に対する問合せや回答を管理している。ホームページでは,顧客と社員が発言を書き込める製品別の掲示板や活用事例が用意されている。D社では,サポート内容や製品の活用事例を検索するためのキーワードをマスタとして一元管理している。
今回,更なる製品販売・活用推進,顧客満足度向上のために,テキストマイニングツール(以下,ツールという)を導入し,顧客サポートシステムとホームページの機能を強化した新顧客サポートシステム(以下,新システムという)を構築することになった。このツールによって,掲示板への発言内容とキーワードを,キーワードマスタを用いて関連付ける。また,発言内容を分析し,肯定的か否定的かを自動的に判別する。製品に対する問合せや回答の内容,製品の活用事例についても同様に,ツールとキーワードマスタを用い,キーワードを関連付ける。運用方法としては,毎日,夜間にツールで処理した結果を新システムに取り込む。
新システム全体のE-R図の抜粋を図1に,各エンティティの概要を表1に示す。 新システムでは,E-R図のエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。
〔発言キーワードへの重み付け〕
発言キーワードの重みを,掲示板上の社員以外の顧客による発言のうち,そのキーワードを含む発言数と定義する。つまり,登録されたキーワードを含む発言数が多いほど話題性が高く,重要度の高いキーワードであると定義する。発言キーワード重み表にその重みの値を集計するためのSQL文を図2に示す。ただし,掲示板上の発言にはなく,キーワード表だけに存在するキーワードの重みは0として集計する。また,発言キーワード重み表のレコードは集計の前に削除されている。
なお,関数 COALESCE(A,B) は,AがNULLでないときはAを,AがNULLのときはBを返す。〔顧客サポートシステムの機能強化〕
顧客サポートシステムでは,電話やインターネットからの問合せや回答を管理している。掲示板に書き込まれた否定的な発言を,含まれるキーワードの重みの総和が大きいものから順にリストアップする機能を追加する。そのリストの上位から順に,各発言に対する回答を記入する画面を開き,回答履歴から類似した内容を照会して,適切な回答を担当者が掲示板に書き込むことで,顧客満足度向上を目指す。
否定的な発言を,含まれるキーワードの重みの総和が大きいものから順に出力するためのSQL文を図3に示す。〔活用事例コンテンツの充実〕
ホームページのコンテンツの一つとして各製品の用途に応じた活用事例紹介がある。活用事例が検索されやすくするために,活用事例ごとにキーワードを登録するだけでなく,活用シーンにもキーワードを関連付けることによって,より的確に活用シーンを検索できるようにする。
ビデオカメラの活用事例の画面例を図4に,活用シーンに登録されているキーワードの例を表2に示す。図4の活用事例は学校行事の活用シーンなので,表2に登録されている運動会や文化祭といったキーワードでも,この活用事例が検索されるようにしたい。
設問1
図1のE-R図中のa,bに入れる適切な属性名及びエンテイティ間の関連を答え,E-R図を完成させよ。
なお,エンティティ間の関連及び属性名の表記は,図1の凡例に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)
なお,エンティティ間の関連及び属性名の表記は,図1の凡例に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)
解答入力欄
- a:
- b:
解答例・解答の要点
- a:→
- b:サポート分類ID
解説
〔aについて〕
発言エンティティと発言キーワードエンティティには共通の属性「発言ID」があります。この属性「発言ID」は、発言側では主キーであり、発言を一意に特定します。また、発言キーワードエンティティ側ではキーワードIDとともに(複合)主キーであり、発言とキーワードの関連を一意に特定します。表1のエンティティ名「発言キーワード」の概要には「一つの発言に対して一つ以上のキーワードが関連付けられる」とあります。つまり、発言キーワードエンティティには同一の発言IDをもつ複数のレコードが存在しえるということです。したがって、発言エンティティと発言キーワードエンティティのカーディナリティは1対多であり、[a]には「→」が当てはまります。
∴a=→
〔bについて〕
サポートエンティティとサポート分類エンティティの間には関連があります。関係データベースでは、共通の属性を持たせることによってエンティティ同士を関連付けますが、サポートエンティティには、サポート分類エンティティの主キーである「サポート分類ID」を表す属性は含まれていません。よって、サポート分類エンティティの主キーを参照する属性が、サポートエンティティでの外部キーとして必要であることがわかります。したがって、[b]には「サポート分類ID」が当てはまります。
∴b=サポート分類ID
発言エンティティと発言キーワードエンティティには共通の属性「発言ID」があります。この属性「発言ID」は、発言側では主キーであり、発言を一意に特定します。また、発言キーワードエンティティ側ではキーワードIDとともに(複合)主キーであり、発言とキーワードの関連を一意に特定します。表1のエンティティ名「発言キーワード」の概要には「一つの発言に対して一つ以上のキーワードが関連付けられる」とあります。つまり、発言キーワードエンティティには同一の発言IDをもつ複数のレコードが存在しえるということです。したがって、発言エンティティと発言キーワードエンティティのカーディナリティは1対多であり、[a]には「→」が当てはまります。
∴a=→
〔bについて〕
サポートエンティティとサポート分類エンティティの間には関連があります。関係データベースでは、共通の属性を持たせることによってエンティティ同士を関連付けますが、サポートエンティティには、サポート分類エンティティの主キーである「サポート分類ID」を表す属性は含まれていません。よって、サポート分類エンティティの主キーを参照する属性が、サポートエンティティでの外部キーとして必要であることがわかります。したがって、[b]には「サポート分類ID」が当てはまります。
∴b=サポート分類ID
設問2
図2中のc,dに入れる適切な字句又は式を答えよ。
解答入力欄
- c:
- d:
解答例・解答の要点
- c:LEFT OUTER JOIN
- d:発言.社員番号 IS NULL
解説
〔cについて〕
SQL文の OMOMI の直後にはON句があるため、[c]にはキーワード表とOMOMIを結合させる何らかの結合句が入ることがわかります。
ここで〔発言キーワードへの重み付け〕を読むと、「掲示板上の発言にはなく,キーワード表だけに存在するキーワードの重みは0として集計する」とあります。キーワード表とOMOMIを(INNER)JOIN句で内部結合してしまうと、キーワード表だけに存在している(発言が0回の)キーワードIDは結合条件に合致せず、出力されないことになってしまいます。発言がないキーワードも重み0として出力したいので、キーワード表の全レコードを出力しつつ、発言がなかったキーワードのCNT列はNULLにしなければなりません。これを実現するために、キーワード表を左表、OMOMIを右表として左外部結合を行う必要があります。OMOMI.CNT列のNULL値は、主問合せのSELECT句にあるCOALESCE関数により0に変換されて出力されます。よって、[c]には「LEFT OUTER JOIN」が当てはまります。
∴c=LEFT OUTER JOIN
〔dについて〕
〔発言キーワードへの重み付け〕には、「発言キーワードの重みを,掲示板上の社員以外の顧客による発言のうち,そのキーワードを含む発言数と定義する」とあります。単純にキーワードに紐づく発言数だけを知りたいのであれば、発言キーワード表の発言IDをキーワードIDごとに数えればよいのですが、社員以外の顧客による発言という条件があるので、SQL文で集計対象のレコードを絞り込む必要があります。表1のエンティティ名「発言」の概要には「社員番号には、(中略)社員以外の顧客による発言の場合はNULLが入る」とあるので、WHERE句には「発言表の社員番号列がNULLである」という条件を指定して、レコードを絞り込めば良いと判断できます。よって、[d]には「発言.社員番号 IS NULL」が当てはまります(「発言.」は省略可)。
※NULLを比較するときには"="は使えず、"IS NULL"と"IS NOT NULL"を使うことに注意が必要です。
∴d=発言.社員番号 IS NULL
SQL文の OMOMI の直後にはON句があるため、[c]にはキーワード表とOMOMIを結合させる何らかの結合句が入ることがわかります。
ここで〔発言キーワードへの重み付け〕を読むと、「掲示板上の発言にはなく,キーワード表だけに存在するキーワードの重みは0として集計する」とあります。キーワード表とOMOMIを(INNER)JOIN句で内部結合してしまうと、キーワード表だけに存在している(発言が0回の)キーワードIDは結合条件に合致せず、出力されないことになってしまいます。発言がないキーワードも重み0として出力したいので、キーワード表の全レコードを出力しつつ、発言がなかったキーワードのCNT列はNULLにしなければなりません。これを実現するために、キーワード表を左表、OMOMIを右表として左外部結合を行う必要があります。OMOMI.CNT列のNULL値は、主問合せのSELECT句にあるCOALESCE関数により0に変換されて出力されます。よって、[c]には「LEFT OUTER JOIN」が当てはまります。
∴c=LEFT OUTER JOIN
〔dについて〕
〔発言キーワードへの重み付け〕には、「発言キーワードの重みを,掲示板上の社員以外の顧客による発言のうち,そのキーワードを含む発言数と定義する」とあります。単純にキーワードに紐づく発言数だけを知りたいのであれば、発言キーワード表の発言IDをキーワードIDごとに数えればよいのですが、社員以外の顧客による発言という条件があるので、SQL文で集計対象のレコードを絞り込む必要があります。表1のエンティティ名「発言」の概要には「社員番号には、(中略)社員以外の顧客による発言の場合はNULLが入る」とあるので、WHERE句には「発言表の社員番号列がNULLである」という条件を指定して、レコードを絞り込めば良いと判断できます。よって、[d]には「発言.社員番号 IS NULL」が当てはまります(「発言.」は省略可)。
※NULLを比較するときには"="は使えず、"IS NULL"と"IS NOT NULL"を使うことに注意が必要です。
∴d=発言.社員番号 IS NULL
設問3
図3中のe,fに入れる適切な字句又は式を答えよ。
解答入力欄
- e:
- f:
解答例・解答の要点
- e:発言.否定的フラグ = '1'
- f:ORDER BY WEIGHT DESC
解説
図3のSQL文は「否定的な発言を,含まれるキーワードの重みの総和が大きいものから順に出力するための」ものです。
〔eについて〕
図3のSQL文には、発言が否定的かどうかの条件指定がないため追加する必要があります。表1のエンティティ名「発言」の概要には「否定的フラグ属性には,否定的な発言内容の場合は'1'が,そうでない場合は'0'が入る」とあります。否定的な発言だけを集計対象としたいのですから、[e]には「発言.否定的フラグ = '1'」が当てはまります(「発言.」は省略可)。
∴e=発言.否定的フラグ = '1'
〔fについて〕
GROUP BY句の後ですので、位置的にHAVING句またはORDER BY句が候補となります。このSQL文では「総和が大きいものから順に出力する」必要がありますが、図3のSQL文にはORDER BY句がなく、このままだと並び替える動作が行われないため追加しなければなりません。
発言に含まれるキーワードの重みの総和は、SELECT句に記されている"SUM(発言キーワード重み.重み)"であり、"WEIGHT"という別名(エイリアス)がつけられています。降順(値の大きい順)に並び替えるときには列名に続けて"DESC"と指定するので、[f]には「ORDER BY WEIGHT DESC」が当てはまります。
※GROUP BY句やHAVING句で列の別名が使えないのに、何でORDER BY句ではOKなの?と感じた方がいらっしゃるかもしれません。SQLの各句の実行順序は、ざっくり FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY となっていて、ORDER BY句はSELECT句の後に実行されます。このため、SELECT句の別名を指定してもエラーにならないのです。
∴f=ORDER BY WEIGHT DESC
〔eについて〕
図3のSQL文には、発言が否定的かどうかの条件指定がないため追加する必要があります。表1のエンティティ名「発言」の概要には「否定的フラグ属性には,否定的な発言内容の場合は'1'が,そうでない場合は'0'が入る」とあります。否定的な発言だけを集計対象としたいのですから、[e]には「発言.否定的フラグ = '1'」が当てはまります(「発言.」は省略可)。
∴e=発言.否定的フラグ = '1'
〔fについて〕
GROUP BY句の後ですので、位置的にHAVING句またはORDER BY句が候補となります。このSQL文では「総和が大きいものから順に出力する」必要がありますが、図3のSQL文にはORDER BY句がなく、このままだと並び替える動作が行われないため追加しなければなりません。
発言に含まれるキーワードの重みの総和は、SELECT句に記されている"SUM(発言キーワード重み.重み)"であり、"WEIGHT"という別名(エイリアス)がつけられています。降順(値の大きい順)に並び替えるときには列名に続けて"DESC"と指定するので、[f]には「ORDER BY WEIGHT DESC」が当てはまります。
※GROUP BY句やHAVING句で列の別名が使えないのに、何でORDER BY句ではOKなの?と感じた方がいらっしゃるかもしれません。SQLの各句の実行順序は、ざっくり FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY となっていて、ORDER BY句はSELECT句の後に実行されます。このため、SELECT句の別名を指定してもエラーにならないのです。
∴f=ORDER BY WEIGHT DESC
設問4
図1のE-R図には,〔活用事例コンテンツの充実〕を実現するために必要なエンティティを一つ追加する必要がある。解答欄中央の空白のエンティティに,そのエンティティ名として格納するデータの意味を表す名前を付け,その属性を全て挙げよ。さらに,関連するエンティティ名を解答欄左右のエンティティに記入し,解答欄中央のエンティティとの関連を図1の凡例に倣って示せ。
解答入力欄
- (図表で回答する問題のため解答入力欄はありません。)
解答例・解答の要点
解説
〔活用事例コンテンツの充実〕には「活用事例ごとにキーワードを登録するだけでなく,活用シーンにもキーワードを関連付ける」とあります。また、表2より1つの活用シーンについて複数個のキーワードが対応する場合があることがわかります。活用事例とキーワードの対応は多対多であり、活用事例キーワードエンティティを用意することによりそれぞれの紐づけが行われています。これと同様に、活用シーンとキーワードに多対多の対応をもたせるため、これらを紐づけるための新たなエンティティが必要です。活用事例キーワードエンティティに倣い、新たなエンティティとその属性を、関係スキーマ:エンティティ名(属性名1, 属性名2, …)で表すと、
活用シーンキーワード(活用シーンID, キーワードID)
となります。カーディナリティについても、活用事例キーワードエンティティと同様に、活用シーンエンティティと活用シーンキーワードエンティティについては1対多、キーワードエンティティと活用シーンキーワードエンティティについては1対多となります。したがって、答えは下図のようになります。※活用事例キーワードエンティティや活用シーンキーワードエンティティのように、多対多の関係をもつエンティティについて、2つの1対多の関係に変換するためにこれらの間に設けられるエンティティを「連関エンティティ」といいます。
活用シーンキーワード(活用シーンID, キーワードID)
となります。カーディナリティについても、活用事例キーワードエンティティと同様に、活用シーンエンティティと活用シーンキーワードエンティティについては1対多、キーワードエンティティと活用シーンキーワードエンティティについては1対多となります。したがって、答えは下図のようになります。※活用事例キーワードエンティティや活用シーンキーワードエンティティのように、多対多の関係をもつエンティティについて、2つの1対多の関係に変換するためにこれらの間に設けられるエンティティを「連関エンティティ」といいます。