データベースの設計と実装

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日以内であることを示す。また，現行システムで年月日を格納する列と，年月日を格納するホスト変数は，基準日からの日数を値としている。

テーブル作成用SQL文
a 新刊（書籍番号 INTEGER, ジャンル INTEGER,
    b（書籍番号），
    FOREIGN KEY(書籍番号)
    REFERENCES 書籍(書籍番号)）

DELETE FROM 新刊;
INSERT INTO 新刊（書籍番号，ジャンル）
    SELECT 書籍番号，ジャンル FROM 書籍 WHERE 発行年月日 + 60 > :今日;

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

a 購入傾向
    (顧客番号 INTEGER, ジャンル INTEGER, 購入冊数 INTEGER,
    b(顧客番号, ジャンル),
    FOREIGN KEY(顧客番号) 
    REFERENCES 顧客(顧客番号))

DELETE FROM 購入傾向;
INSERT INTO 購入傾向（顧客番号，ジャンル，購入冊数）
    SELECT 注文.顧客番号，書籍.ジャンル，c
    FROM 注文，注文明細，書籍
    WHERE d
    AND 注文.注文番号 = 注文明細.注文番号
    AND 注文明細.書籍番号 = 書籍.書籍番号
    GROUP BY e，f;

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

SELECT 書籍.書籍番号，書籍.書籍名，書籍.価格，書籍説明 FROM 書籍，新刊，購入傾向
    WHERE 書籍.書籍番号 = 新刊.書籍番号
    AND 購入傾向.顧客番号 = :顧客番号
    AND 書籍.ジャンル = 購入傾向.ジャンル
    AND g > :購入総冊数

【新刊お薦め機能の改善について】

Y 社では新刊お薦め機能を構築し，一部の顧客に対して試験的に導入した。しばらく試験運用を続けた結果，新刊お薦め機能を利用している複数の顧客から，"商品購入後にすぐにお薦め商品が更新された方が使いやすい"との指摘を受けた。

そこで，毎日バッチ処理で実行していた【手順2】の処理に加えて，顧客が商品を購入したタイミングで，その顧客に対する"購入傾向"にその時購入した商品の情報を追加することにした。その更新処理のための SQL 文を図5に示す。ここで，":顧客番号"はその顧客の顧客番号を，":注文番号"はその顧客の直前の注文に対応する注文番号を，":注文明細番号"はその注文のうちの一つの注文明細に対応する注文明細番号を格納するホスト変数である。

UPDATE 購入傾向 SET 購入冊数 =
    (SELECT 購入傾向.購入冊数 + 注文明細.数量 FROM 注文明細，書籍
    WHERE 注文明細.注文番号 = :注文番号 AND 注文明細.注文明細番号 = :注文明細番号
        AND 注文明細.書籍番号 = 書籍.書籍番号 AND 書籍.ジャンル = 購入傾向.ジャンル)
WHERE 購入傾向.顧客番号 = :顧客番号
    AND 購入傾向.ジャンル IN
        (SELECT 書籍.ジャンル FROM 注文明細，書籍
        WHERE 注文明細.注文番号 = :注文番号 AND 注文明細.注文明細番号 = :注文明細番号
        AND 注文明細.書籍番号 = 書籍.書籍番号)

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