応用情報技術者過去問題平成23年特別午後問6(データベース)

応用情報技術者過去問題平成23年特別午後問6

問6　データベース

データベースの設計と実装に関する次の記述を読んで，設問1，2に答えよ。

　Y社は，インターネットで個人向けに書籍を販売する書籍販売サイトを運営している。書籍販売サイトでの顧客からの注文を受け付ける注文管理システム(以下，現行システムという)では，書籍情報，注文情報に加えて，顧客の会員情報を管理している。現行システムのE-R図を図1に示す。現行システムでは，E-R図のエンティティ名を表名，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理している。

〔新刊お薦め機能の追加について〕
　Y社では，販売促進のために"新刊お薦め機能"を書籍販売サイトに追加することにした。新刊お薦め機能は，顧客の購入履歴から顧客が興味をもつ書籍ジャンルを推定し，そのジャンルで過去60日以内に発行された書籍(以下，新刊という)をすべてお薦め商品として表示する機能である。ここで，過去180日間に購入した書籍の"総冊数に占めるジャンルごとの冊数の割合"(購入割合)が10%を超えているものを，その顧客が興味をもつ書籍ジャンルとする。同一書籍を複数購入した場合も，その冊数をそのまま集計する。新刊お薦め機能は，次の三つの手順によって実現するものとする。

[手順1]: 全書籍から新刊だけを抽出する。
[手順2]: 今日を含めて過去180日以内の購入履歴から，顧客ごと，書籍ジャンルごとの購入冊数を求める。
[手順3]: 今日を含めて過去180日以内の，その顧客の購入割合が10%を超えているジャンルについて，そのジャンルの新刊をお薦め商品として表示する。

　[手順1]を実現するために，新しいエンティティ"新刊"を追加し，"新刊"に対応するテーブルを作成するためのSQL文と，データを挿入するためのSQL文を作成した。エンティティとSQL文を図2に示す。ここで，":今日"は，SQL実行時の年月日を格納するホスト変数である。図2中の"発行年月日＋60＞:今日"は，発行年月日がSQL実行時の年月日を含めて過去60日以内であることを示す。また，現行システムで年月日を格納する列と，年月日を格納するホスト変数は，基準日からの日数を値としている。

　[手順2]を実現するために，新しいエンティティ"購入傾向"を追加し，"購入傾向"に対応するテーブルを作成するためのSQL文と，データを挿入するためのSQL文を作成した。エンティティとSQL文を図3に示す。

　[手順3]を実現するために，お薦め商品の情報を抽出するSQL文を作成した。SQL文を図4に示す。ここで，":顧客番号"は指定された顧客の顧客番号を，":購入総冊数"は指定された顧客が今日を含めて過去180日以内に購入した総冊数を格納するホスト変数である。

〔新刊お薦め機能の改善について〕
　Y社では新刊お薦め機能を構築し，一部の顧客に対して試験的に導入した。しばらく試験運用を続けた結果，新刊お薦め機能を利用している複数の顧客から，"商品購入後にすぐにお薦め商品が更新された方が使いやすい"との指摘を受けた。
　そこで，毎日バッチ処理で実行していた[手順2]の処理に加えて，顧客が商品を購入したタイミングで，その顧客に対する"購入傾向"にその時購入した商品の情報を追加することにした。その更新処理のためのSQL文を図5に示す。ここで，":顧客番号"はその顧客の顧客番号を，":注文番号"はその顧客の直前の注文に対応する注文番号を，":注文明細番号"はその注文のうちの一つの注文明細に対応する注文明細番号を格納するホスト変数である。

　図5の更新処理の動作確認のために，図6及び図7に示すテストデータを用意した。図6は[手順2] の結果として"購入傾向テーブル"に格納するテストデータである。図7は，顧客が新たに購入した書籍に関するテストデータである。

設問1

三つの手順を実現するためのエンティティとSQL文について，(1)～(3)に答えよ。

図2中のa，bに入れる適切な字句を答えよ。
図3中のc～fに入れる適切な字句を答えよ。ここで，SQL実行時の年月日はホスト変数"：今日"に格納されているものとする。
図4中のgに入れる適切な字句を答えよ。

解答入力欄

- a：
- b：
- c：
- d：
- e：
- f：
- g：

解答例・解答の要点

a：CREATE TABLE
b：PRIMARY KEY

c：SUM(注文明細.数量)
d：注文.注文年月日＋180＞:今日
e：注文.顧客番号
f：書籍.ジャンル

g：購入傾向.購入冊数 * 10

解説

テーブルを作成するSQL文の基本形について確認します。
CREATE TABLE 表名
(
-- 属性の定義部
列名データ型,
列名データ型,
列名データ型,
…
-- フィールド属性定義部
PRIMARY KEY (主キー列名),
FOREIGN KEY (外部キー列名) REFERENCES 外部表名 (外部表の主キー列名)
)
PRIMARY KEY句で指定した列がテーブルの（複合）主キーになります。他にはFOREIGN KEY句で外部キーを指定し、REFERENCE句で参照先のテーブル・列を設定することができます。

〔aについて〕
テーブルを作成するためのSQL文は「CREATE TABLE」です。

〔bについて〕
テーブルには必ず主キーを定義しなければなりません。新刊エンティティの主キー属性は"書籍番号"であるため（あるいは図3より購入傾向エンティティの主キー属性は"顧客番号"と"ジャンル"であるため）、主キーを定義するためのPRIMARY KEY句にこれらの列名を指定します。したがって、[b]には「PRIMARY KEY」が当てはまります。

∴a＝CREATE TABLE
　b＝PRIMARY KEY

※PRIMARY KEY と FOREIGN KEY は空欄の穴埋めで出題される可能性があるので、書けるようになっておきましょう。
このSQL文の目的は、〔新刊お薦め機能の追加について〕の[手順2]にある「今日を含めて過去180日以内の購入履歴から，顧客ごと，書籍ジャンルごとの購入冊数を求める」ことです。

図3のデータ挿入用SQL文のINSERT文について、データ挿入先のテーブル名・列名を指定した後にSELECT文を記述することで、その取得結果をデータ挿入しています。SELECT文においてテーブルの内部結合を行うには、FROM句やINNER JOIN句でテーブル名を指定し、ON句で結合を行う各テーブルの列名を"="で結ぶ方法があります。しかしこれとは別に、結合するテーブル名をFROM句で指定し、ON句で指定する結合条件をWHERE句で指定することでも可能で、図3ではこちらの方法が用いられています。

〔dについて〕
WHERE句には「今日を含めて過去180日以内の購入履歴」を示す条件がないので追加する必要があります。「"発行年月日 + 60 > :今日"は，発行年月日がSQL実行時の年月日を含めて過去60日以内であることを示す」とあるので、これを参考にすると記すべき条件は"書籍が購入された日 + 180 > :今日"です。FROM句の各テーブルのうち、書籍が購入された日を示すのは注文テーブルの注文年月日列であると考えられます。したがって、[d]には「注文.注文年月日 + 180 > :今日」が当てはまります（"注文."は省略可）。

※今日を含めて過去180日以内は、「注文日 >= :今日 - 179」→「注文日 > :今日 - 180」なので、-180を移項して「注文日 + 180 > :今日」です。

∴d＝注文.注文年月日 + 180 > :今日

〔eについて〕
GROUP BY句には「顧客ごと，書籍ジャンルごと」に集計することが記されていないので追加する必要があります。FROM句の各テーブルのうち、顧客を一意に特定するのは注文テーブルの顧客番号列、書籍ジャンルを一意に特定するのは書籍テーブルのジャンル列であると考えられます。したがって、[e]には「注文.顧客番号」が、[f]には「書籍.ジャンル」が当てはまります（"注文."、"書籍."は省略可）。

∴e、f＝注文.顧客番号、書籍.ジャンル（順不同）

〔cについて〕
空欄には購入冊数を表す列ないしは関数が必要です。〔新刊お薦め機能の追加について〕には「同一書籍を複数購入した場合も，その冊数をそのまま集計する」とあるため、単純に顧客ごとジャンルごとのグループで書籍の注文数量を合計することになります。書籍の注文数量は、注文明細テーブルの注文列に記録されていますから、[c]には「SUM(注文明細.数量)」が当てはまります（"注文明細."は省略可）。

∴c＝SUM(注文明細.数量)
〔gについて〕
このSQL文の目的は、〔新刊お薦め機能の追加について〕の[手順3]にある「今日を含めて過去180日以内の，その顧客の購入割合が10%を超えているジャンルについて，そのジャンルの新刊をお薦め商品として表示する」ことです。

購入傾向エンティティには、過去180日以内の購入履歴が集計され、顧客ごとジャンルごとの購入冊数が記録されています。お薦めをする新刊のジャンルは「その顧客の購入割合が10%を超えているジャンル」ですが、WHERE句にはこの条件がないので追加する必要があります。

購入傾向テーブルの購入冊数列は顧客ごとジャンルごとの購入冊数を表し、ホスト変数":購入総冊数"が今日を含めて過去180日以内のその顧客の総購入冊数を表します。よって、条件としては「購入傾向.購入冊数 > :購入総冊数 * 0.1」が必要だと考えられます。条件式の意味を保ったまま解答欄に合うように両辺を10倍すると、[g]には「購入傾向.購入冊数 * 10」が当てまります。
※購入傾向.購入冊数 / 0.1 でも問題ないと思います。

∴g＝購入傾向.購入冊数 * 10

設問2

図6及び図7のテストデータで図5の更新処理の動作確認を行った結果について，(1)，(2)に答えよ。

図6のテストデータが格納された購入傾向テーブルに対して，図7のテストデータを用いて図5の更新処理を行った結果，図6のテストデータのうち，更新されたすべてのレコードの更新後の内容(顧客番号，ジャンル，購入冊数)を答えよ。
(1)の結果から，図5の更新処理では一部の商品を購入したときに購入傾向テーブルが変更されないことが分かった。どのような商品を購入したときにこの問題が起こるか。35字以内で述べよ。

解答入力欄

- (図表で回答する問題のため解答入力欄はありません。)

解答例・解答の要点

・過去180日以内にその顧客が購入したことがないジャンルの商品 (30文字)
・購入傾向テーブルにその顧客のデータがないジャンルの商品 (27文字)

解説

図5のSQL文は、直前の注文の一つの注文明細の数量を、購入傾向テーブルの購入冊数に加算するものです。
まず、主問合せ側のWHERE句で、購入傾向テーブルから更新対象の顧客番号とジャンルを持つレコードを抽出します。B部分は、当該注文明細における書籍のジャンルを取得するための副問合せです（IN句の後にあるものの、取得結果は必ず1件です）。
抽出された更新対象レコードの購入冊数は副問合せAで得られた値によって更新されます。A部分は、当該注文明細での書籍（テーブル）のジャンルが購入傾向テーブルのジャンルと一致するレコードに対して、購入冊数と数量の和を取得する副問合せです。

図7の各テストデータから各ホスト変数に値を代入して、図5のSQL文の実行結果について考えます。
1つ目のデータでは副問合せBの結果（該当の注文明細での書籍のジャンル）は「2」であるため、図6の顧客番号："100010"，ジャンル："2"のレコードの購入冊数をその購入冊数（＝1）と書籍の購入数量（＝1）の和（＝2）に更新します。
2つ目のデータでは副問合せBの結果は「2」ですが、図6に顧客番号："100020"，ジャンル："2"のレコードは存在しないため、更新は行われません。

3つ目のデータでは副問合せBの結果は「3」であるため、図6の顧客番号："100020"，ジャンル："3"のレコードは購入冊数をその購入冊数（＝2）と書籍の購入数量（＝1）の和（＝3）に更新します。
更新されたレコードは2つなので、更新されたレコードと更新後の内容は、
(1)では図7の2つ目のテストデータについて、購入傾向テーブルに該当顧客の該当注文明細の書籍のジャンルが存在しないために、更新対象となりませんでした。このように、購入した書籍に対応する顧客番号とジャンルの組合せが購入傾向テーブルに登録されていない場合には、更新対象レコードが存在しないため注文数量の加算が行われません。過去180日以内に購入されたことのないジャンルの商品が購入されるとこの不具合が発生します。

∴過去180日以内にその顧客が購入したことがないジャンルの商品
　購入傾向テーブルにその顧客のデータがないジャンルの商品