応用情報技術者 2011年 春期 午後 問06
データベースの設計と実装に関する次の記述を読んで、設問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文を図4に示す。ここで、“:顧客番号”は指定された顧客番号を、“:購入総冊数”は指定された顧客が今日を含む過去180日以内に購入した総冊数を格納するホスト変数である。
〔新刊お薦め機能の改善について〕
Y社では新刊お薦め機能を構築し、一部の顧客に対して試験的に導入した。しばらく試験運用を続けた結果、新刊お薦め機能を利用している複数の顧客から、“商品購入後にすぐにお薦め商品が更新された方が使いやすい”との指摘を受けた。
そこで、毎日バッチ処理で実行していた[手順2]の処理に加えて、顧客が商品を購入したタイミングで、その顧客に対する“購入傾向”にその時購入した商品の情報を追加することにした。その更新処理のための SQL 文を図5に示す。ここで、“:顧客番号”はその顧客の顧客番号を、“:注文番号”はその顧客の直前の注文に対応する注文番号を、“:注文明細番号”はその注文のうちの1つの注文明細に対応する注文明細番号を格納するホスト変数である。
図5の更新処理の動作確認のために、図6及び図7に示すテストデータを用意した。
図6は[手順2]の結果として“購入傾向テーブル”に格納するテストデータである。
図7は、顧客が新たに購入した書籍に関するテストデータである。
設問1：三つの手順を実現するためのエンティティとSQL文について、(1)〜(3)に答えよ。
（1）図2中のa、bに入れる適切な字句を答えよ。
模範解答
a：CREATE TABLE
b：PRIMARY KEY
解説
解答の論理構成
- 問題文には次のようなテーブル定義用 SQL が提示されています。
［ a ］ 新刊 (書籍番号 INTEGER, ジャンル INTEGER,
［ b ］ (書籍番号),
FOREIGN KEY(書籍番号)
REFERENCES 書籍(書籍番号)) - 先頭の ［ a ］ には、SQL において新しい表を作成する命令が入る必要があります。表を生成する DDL は “CREATE TABLE …”。したがって ［ a ］＝CREATE TABLE が妥当です。
- ［ b ］ の直後には (書籍番号) が続いており、列リストを指定してキー制約を与えると読み取れます。ここで必要なのは主キー制約を宣言するキーワードです。SQL 標準で主キーを宣言するのは “PRIMARY KEY …”。よって ［ b ］＝PRIMARY KEY となります。
- 以上より
• ［ a ］：CREATE TABLE
• ［ b ］：PRIMARY KEY
誤りやすいポイント
- CREATE だけを書いて TABLE を忘れる。DDL は “CREATE TABLE” が完全形です。
- PRIMARY KEY を UNIQUE と混同する。UNIQUE では外部キー参照先の一意性保証としては足りますが、主キーとしては不十分です。
- 外部キー制約と主キー制約を逆に記述してエラーになるケース。先に主キー、次に FOREIGN KEY … REFERENCES … の順が基本です。
FAQ
Q: PRIMARY KEY を省略しても外部キー制約だけで動作しませんか？
A: 主キーが無いと一意性が保証されず、参照整合性を担保できません。外部キー先がユニークであることが必須条件です。
A: 主キーが無いと一意性が保証されず、参照整合性を担保できません。外部キー先がユニークであることが必須条件です。
Q: CREATE TABLE … IF NOT EXISTS を使った方が安全ですか？
A: 本問は記述の正しさを問うため省略形を採用していません。試験解答としては “CREATE TABLE” だけで十分です。
A: 本問は記述の正しさを問うため省略形を採用していません。試験解答としては “CREATE TABLE” だけで十分です。
Q: 複合主キーにする場合はどう記述しますか？
A: PRIMARY KEY (列1, 列2, …) とカンマ区切りで列名を並べます。
A: PRIMARY KEY (列1, 列2, …) とカンマ区切りで列名を並べます。
関連キーワード: DDL, PRIMARY KEY, 外部キー, 参照整合性
設問1：三つの手順を実現するためのエンティティとSQL文について、(1)〜(3)に答えよ。
（2）図3中のc〜fに入れる適切な字句を答えよ。ここで、SQL実行時の年月日はホスト変数“:今日”に格納されているものとする（eとfは順不同）。
模範解答
c：SUM(注文明細.数量)
d：注文.注文年月日 + 180 > :今日
e：注文.顧客番号
f：書籍.ジャンル
解説
解答の論理構成
-
目的の確認
【問題文】では「今日を含めて過去 180 日以内の購入履歴から、顧客ごとに、書籍ジャンルごとの購入冊数を求める。」と指示されています。したがって
• 過去 180 日以内の注文に限定するフィルタ
• 顧客番号・ジャンルごとに冊数を合計する集約
が必須です。 -
集計列 [c] の決定
合計したいのは「冊数」、すなわち注文明細の 数量 列です。複数レコードを合計するために SQL の集約関数 SUM を適用します。
→ [c] = SUM(注文明細.数量) -
期間条件 [d] の決定
年月日は「基準日からの日数」で保持されています。新刊抽出時には【問題文】に「発行年月日 + 60 ≥ :今日」とありました。これと同じ考え方で 180 日を適用すると
注文.注文年月日 + 180 > :今日
が過去 180 日以内を表します。
→ [d] = 注文.注文年月日 + 180 > :今日 -
GROUP BY 句 [e] と [f] の決定
集計対象は「顧客ごとに、書籍ジャンルごと」ですから
• 顧客を識別する 注文.顧客番号
• ジャンルを識別する 書籍.ジャンル
の 2 列を GROUP BY に並べます。順序は SQL 的にどちらが先でも同じ意味になります。
→ [e] = 注文.顧客番号
→ [f] = 書籍.ジャンル -
以上により、模範解答は
c：SUM(注文明細.数量)
d：注文.注文年月日 + 180 > :今日
e：注文.顧客番号
f：書籍.ジャンル
となります。
誤りやすいポイント
- 期間条件を「:今日 - 180 < 注文.注文年月日」のように方向を逆に書くミス。列値が基準日からの日数であることを忘れやすいです。
- 集計列を COUNT(*) にしてしまい、同一書籍を複数冊買った場合の数量を反映できない。
- GROUP BY に 注文明細.数量 を含めてしまい、集約が成立しない。
- SUM を書きながら GROUP BY を忘れて SQL 文がエラーになる。
FAQ
Q: >= と > のどちらを使うべきでしょうか？
A: 問題文の新刊抽出例が「発行年月日 + 60 ≥ :今日」だったため、それに合わせて > または >= を使っても論理は同じです。本問では「模範解答」として > が採用されています。
A: 問題文の新刊抽出例が「発行年月日 + 60 ≥ :今日」だったため、それに合わせて > または >= を使っても論理は同じです。本問では「模範解答」として > が採用されています。
Q: GROUP BY の列順序は評価に影響しますか？
A: 影響しません。注文.顧客番号, 書籍.ジャンル と 書籍.ジャンル, 注文.顧客番号 は同等です。
A: 影響しません。注文.顧客番号, 書籍.ジャンル と 書籍.ジャンル, 注文.顧客番号 は同等です。
Q: SUM(数量) だけではなく SUM(DISTINCT 数量) とすべきでは？
A: 同一書籍を複数回購入した場合でも「その冊数をそのまま集計する」と【問題文】にあるため、DISTINCT を付けると誤りになります。
A: 同一書籍を複数回購入した場合でも「その冊数をそのまま集計する」と【問題文】にあるため、DISTINCT を付けると誤りになります。
関連キーワード: 集約関数, GROUP BY, ホスト変数, 日付計算, データ挿入
設問1：三つの手順を実現するためのエンティティとSQL文について、(1)〜(3)に答えよ。
（3）図4中のgに入れる適切な字句を答えよ。
模範解答
g：購入傾向.購入冊数 * 10
解説
解答の論理構成
-
仕様確認
【問題文】では、購入割合を
“過去180日間に購入した書籍の“総冊数に占めるジャンルごとの冊数の割合”（購入割合）が10％を超えているもの”
と定義しています。
つまり判定条件は -
整数演算に変換
SQL で小数演算を避けるために両辺に を掛けます。列・変数を当てはめると
“購入傾向.購入冊数 * 10 > :購入総冊数” -
WHERE 句への適用
図4の WHERE 句には
sql AND g ＞ :購入総冊数とあり、左辺のみが空欄です。上記より購入傾向.購入冊数 * 10を入れれば、購入割合が“10％を超えている”ジャンルだけを抽出できます。
誤りやすいポイント
- “10％を超えている”を>=と書いてしまう
“超えている”は「より大きい」であり【問題文】に等号はありません。 - 小数演算で購入傾向.購入冊数 / :購入総冊数 > 0.1と書く
データ型が整数の場合は誤って常に0になり、抽出できなくなります。 - 右辺を :購入総冊数 * 0.1とする
変数側に演算を書くとパラメータバインドのたびに計算が必要になり効率が悪く、意図もずれます。
FAQ
Q: 割合判定に* 10ではなく* 100と書くのはダメですか？
A: 判定値が“10％”なので倍で十分です。倍にした場合は右辺も倍する必要があり誤判定になります。
A: 判定値が“10％”なので倍で十分です。倍にした場合は右辺も倍する必要があり誤判定になります。
Q: :購入総冊数には同一書籍を複数購入した数量も含めるのですか？
A: はい。【問題文】の“同一書籍を複数購入した場合も、その冊数をそのまま集計する”に従い、数量をそのまま合計した値をセットします。
A: はい。【問題文】の“同一書籍を複数購入した場合も、その冊数をそのまま集計する”に従い、数量をそのまま合計した値をセットします。
Q: 浮動小数点列にして> 0.1と比較する方が読みやすいと思いますが？
A: 列追加や型変更なしで実装でき、計算誤差も避けられる整数演算の方が実務的に安全です。
A: 列追加や型変更なしで実装でき、計算誤差も避けられる整数演算の方が実務的に安全です。
関連キーワード: 集計処理, 比較演算, 割合判定, SQL最適化, インデックス利用
設問2：図6及び図7のテストデータで図5の更新処理の動作確認を行った結果について、(1)、(2)に答えよ。
（1）図6のテストデータが格納された購入傾向テーブルに対して、図7のテストデータを用いて図5の更新処理を行った結果、図6のテストデータのうち、更新されたすべてのレコードの更新後の内容（顧客番号、ジャンル、購入冊数）を答えよ。
模範解答
解説
解答の論理構成
-
更新対象レコードの決定条件
- 図5の SQL は
WHERE 購入傾向.顧客番号 = :顧客番号
AND 購入傾向.ジャンル IN (SELECT 書籍.ジャンル … )
と記述されている。したがって
• ホスト変数:顧客番号に一致する顧客
• 注文明細で指定された:注文明細番号の書籍と同じジャンル
の複合キーが購入傾向テーブルに存在するレコードのみが更新対象になる。
- 図5の SQL は
-
更新内容の計算式
- SET 購入冊数 = (SELECT 購入傾向.購入冊数 + 注文明細.数量 …)
であるため、更新後の購入冊数は
既存の購入傾向.購入冊数 ＋ 注文明細の数量。
- SET 購入冊数 = (SELECT 購入傾向.購入冊数 + 注文明細.数量 …)
-
図7の各明細と図6の照合
-
更新後に内容が変わるレコード
- ①より 顧客番号100010・ジャンル2・購入冊数2
- ③より 顧客番号100020・ジャンル3・購入冊数3
-
したがって、問題が求める「更新されたすべてのレコードの更新後の内容」は
• 100010, 2, 2
• 100020, 3, 3
となる。
誤りやすいポイント
- ジャンルが未登録のケースを見落とす
図5は INSERT ではなく UPDATE 文なので、図6に存在しない組合せ（100020,2）は追加されない。 - 同一注文内に複数明細がある場合の繰返し実行
明細単位で:注文明細番号を変更して呼び出す仕様を前提としている。 - 購入傾向.購入冊数を二重に加算してしまう
SET 句内で元値を参照しているため、手計算時に再度加算してダブルカウントしやすい。
FAQ
Q: ジャンルが存在しないときに行を追加するにはどうしますか？
A: 現行 SQL は UPDATE のみなので追加されません。必要であれば MERGE 文や INSERT 失敗時に INSERT を行うアプリケーションロジックを追加します。
A: 現行 SQL は UPDATE のみなので追加されません。必要であれば MERGE 文や INSERT 失敗時に INSERT を行うアプリケーションロジックを追加します。
Q: 同じ顧客が一度に複数冊購入した場合、数量がそのまま加算されますか？
A: はい。購入冊数 + 注文明細.数量 なので数量分だけ増えます。複数明細なら明細ごとに UPDATE を呼べばその都度加算されます。
A: はい。購入冊数 + 注文明細.数量 なので数量分だけ増えます。複数明細なら明細ごとに UPDATE を呼べばその都度加算されます。
Q: 注文バッチ（[手順2]）とリアルタイム更新の整合性は？
A: バッチで集計した後にリアルタイム更新が走る順序を保証し、重複集計を避けるために購入日付でフィルタするか、バッチ側で直近 N 日分を除外する設計が必要です。
A: バッチで集計した後にリアルタイム更新が走る順序を保証し、重複集計を避けるために購入日付でフィルタするか、バッチ側で直近 N 日分を除外する設計が必要です。
関連キーワード: 更新クエリ, ホスト変数, 外部キー制約, 集計カラム, 一意制約
設問2：図6及び図7のテストデータで図5の更新処理の動作確認を行った結果について、(1)、(2)に答えよ。
（2）(1)の結果から、図5の更新処理では一部の商品を購入したときに購入傾向テーブルが変更されないことが分かった。どのような商品を購入したときにこの問題が起こるか。35字以内で述べよ。
模範解答
「過去180日以内にその顧客が購入したことがないジャンルの商品」
または
「購入傾向テーブルにその顧客のデータがないジャンルの商品」
解説
解答の論理構成
- 現行の “購入傾向” テーブルは、[手順2] で
「今日を含めて過去 180 日以内の購入履歴から、顧客ごとに、書籍ジャンルごとの購入冊数を求める」
ことで生成されます。
➡ 過去 180 日間に一度も買われていないジャンルは、そもそもレコードが存在しません。 - 図5の更新処理は “UPDATE” だけで “INSERT” を行いません。
➡ WHERE 句に合致するレコードが無ければ、更新は 0 件で終了します。 - したがって、購入した商品のジャンルが “購入傾向” テーブルにまだ無い場合、冊数を加算すべき行が見付からず、テーブルは変化しません。
- 以上より「過去180日以内にその顧客が購入したことがないジャンルの商品」を買ったときに更新漏れが発生する、という結論になります。
誤りやすいポイント
- 新しいジャンルの購入を「0 冊 → 1 冊」に更新するには INSERT が必要なのに、UPDATE しか書かれていない点を見落とす。
- 「購入傾向テーブルにその顧客の行が無い場合だけ」と思い込み、ジャンル単位での不足を忘れる。
- テーブル作成時に複合主キー（顧客番号, ジャンル）が設定されているため、INSERT の代わりに UPDATE で済むと勘違いする。
FAQ
Q: 購入済みだが 180 日を超えているジャンルも更新されないのですか？
A: はい。180 日を超えている場合、そのジャンルは “購入傾向” テーブルに存在しないため同じ問題が起きます。
A: はい。180 日を超えている場合、そのジャンルは “購入傾向” テーブルに存在しないため同じ問題が起きます。
Q: UPDATE の前に MERGE や UPSERT を使えば問題は解決しますか？
A: はい。該当レコードがあれば UPDATE、無ければ INSERT する構文を採用すれば、購入傾向を常に正しく維持できます。
A: はい。該当レコードがあれば UPDATE、無ければ INSERT する構文を採用すれば、購入傾向を常に正しく維持できます。
Q: バッチで毎日再計算する運用のままではだめですか？
A: 即時性を求める要件（購入直後にお薦めを更新）を満たせないため、オンライントランザクションでの更新が必要です。
A: 即時性を求める要件（購入直後にお薦めを更新）を満たせないため、オンライントランザクションでの更新が必要です。
関連キーワード: UPDATE, 複合主キー, UPSERT, データ更新, 集計テーブル
