応用情報技術者過去問題 平成28年秋期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
ネットショップの会員管理に関する次の記述を読んで，設問1～4に答えよ。
W社は，日用雑貨の製造・販売事業を国内で展開する中堅企業である。自社直営店やデパートなどでの販売に加えて，一般消費者向けにネットショップでも自社製品を販売している。
ネットショップでは，購入者は会員登録を行う必要がある。会員に対しては，購入の履歴から会員の嗜好(しこう)を把握してダイレクトメールを発送し，さらに購入金額の合計に応じた会員種別を付与している。
会員種別には一般会員と特別会員があり，特別会員は購入時に5%の割引が適用される。一般会員と特別会員の判定は，月末日のメンテナンス時間(23時30分～23時59分)のバッチ処理(以下，会員種別判定バッチ処理という)によって行われ，当月の購入金額の合計が5万円以上であれば翌月の初めから月末までは特別会員，5万円未満であれば一般会員となる。
W社では，1回の購入金額が少額である日用雑貨の性質から，頻繁に購入する会員(リピータ)を獲得することが重要と考え，リピータが特別会員の資格を維持しやすくなる判定ルールを取り入れた。具体的には，購入の履歴中の1回ごとの購入を購入単位として，その日時の古いものから順に調べて購入金額の合計が5万円に達したら，それより後の日時の購入単位は繰越し扱いとし，翌月以降の会員種別判定バッチ処理の対象に回すことにした。
〔データベースの設計〕
ネットショップの会員管理システム(以下，本システムという)について，E-R図を図1に示す。
購入エンティティの購入ステータス属性は，購入が完了しているか否かを表す。"受注"，"入金済み"，"完了"のいずれかの値をもち，"完了"となったものだけが会員種別判定バッチ処理の対象となる。購入者は，購入単位ごとに代金を支払う。W社は，入金が確認された後に商品を発送し，購入ステータス属性を"完了"とする。購入エンティティの判定処理状態属性は，"未処理"，"判定処理済み"，"繰越し"のいずれかの値をもつ。
会員エンティティの会員種別属性は，入会時には"一般会員"の値をもち，会員種別判定バッチ処理のたびに，"一般会員"か"特別会員"のいずれかの値が格納される。会員エンティティの会員番号属性には，1以上の整数が格納される。
商品エンティティの商品定価属性には，その商品の定価が格納される。一方，購入明細エンティティの商品単価属性には，会員種別による割引を考慮した販売時の単価が格納される。また，購入エンティティの購入金額属性には，関連する購入明細の商品単価と個数の積を合算した金額が格納される。
なお，本システムでは，E-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
〔会員の嗜好の把握〕
会員の嗜好を把握してダイレクトメールを発送するために，過去1年分の購入の履歴から，各会員がその1年間に購入した商品の商品分類名と商品分類ごとの購入金額合計の一覧(過去の購入済み商品分類一覧)を表示する図2のSQL文を作成した。
なお，":一年前"は，1年前の日時を表す埋込み変数である。
〔会員種別の判定〕
カーソルを使用した会員種別判定バッチ処理を行う図3のプログラムを作成した。
会員種別判定バッチ処理では，会員の購入の履歴を会員番号と購入日時の昇順に処理を行い，特別会員と判定されるまでの購入の履歴は購入単位ごとに"判定処理済み"とするが，特別会員と判定された後の購入の履歴は購入単位ごとに"繰越し"として，翌月以降の会員種別判定バッチ処理の対象にする。購入の履歴中の購入金額の合計が5万円未満の場合は，全ての購入の履歴を"判定処理済み"とする。
なお，":判定対象期限"は判定対象である月の最終日時を表す埋込み変数である。また，変数 kounyu_no，kounyu_kingaku，kaiin_no，goukei，current_kaiin_no，update_flag はそれぞれ適切な型で宣言されているものとする。LOOP から ENDLOOP までは処理の繰返し範囲を表す。FETCH文でカーソルから行を取り出して処理を続け，取り出す行がない場合には処理の繰返しを抜ける。
〔会員種別の履歴の確認〕
会員種別について，会員から"自身の会員種別の履歴を確認したい"という要望が多数寄せられた。当該機能を実現するために，図1のE-R図に対して，既存のエンティティとの間に1対多の関連をもつ新しいエンティティを一つ追加し，会員種別の判定後，その結果の適用日時を含めて記録するようにした。
W社は，日用雑貨の製造・販売事業を国内で展開する中堅企業である。自社直営店やデパートなどでの販売に加えて，一般消費者向けにネットショップでも自社製品を販売している。
ネットショップでは，購入者は会員登録を行う必要がある。会員に対しては，購入の履歴から会員の嗜好(しこう)を把握してダイレクトメールを発送し，さらに購入金額の合計に応じた会員種別を付与している。
会員種別には一般会員と特別会員があり，特別会員は購入時に5%の割引が適用される。一般会員と特別会員の判定は，月末日のメンテナンス時間(23時30分～23時59分)のバッチ処理(以下，会員種別判定バッチ処理という)によって行われ，当月の購入金額の合計が5万円以上であれば翌月の初めから月末までは特別会員，5万円未満であれば一般会員となる。
W社では，1回の購入金額が少額である日用雑貨の性質から，頻繁に購入する会員(リピータ)を獲得することが重要と考え，リピータが特別会員の資格を維持しやすくなる判定ルールを取り入れた。具体的には，購入の履歴中の1回ごとの購入を購入単位として，その日時の古いものから順に調べて購入金額の合計が5万円に達したら，それより後の日時の購入単位は繰越し扱いとし，翌月以降の会員種別判定バッチ処理の対象に回すことにした。
〔データベースの設計〕
ネットショップの会員管理システム(以下，本システムという)について，E-R図を図1に示す。
購入エンティティの購入ステータス属性は，購入が完了しているか否かを表す。"受注"，"入金済み"，"完了"のいずれかの値をもち，"完了"となったものだけが会員種別判定バッチ処理の対象となる。購入者は，購入単位ごとに代金を支払う。W社は，入金が確認された後に商品を発送し，購入ステータス属性を"完了"とする。購入エンティティの判定処理状態属性は，"未処理"，"判定処理済み"，"繰越し"のいずれかの値をもつ。
会員エンティティの会員種別属性は，入会時には"一般会員"の値をもち，会員種別判定バッチ処理のたびに，"一般会員"か"特別会員"のいずれかの値が格納される。会員エンティティの会員番号属性には，1以上の整数が格納される。
商品エンティティの商品定価属性には，その商品の定価が格納される。一方，購入明細エンティティの商品単価属性には，会員種別による割引を考慮した販売時の単価が格納される。また，購入エンティティの購入金額属性には，関連する購入明細の商品単価と個数の積を合算した金額が格納される。
なお，本システムでは，E-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
会員の嗜好を把握してダイレクトメールを発送するために，過去1年分の購入の履歴から，各会員がその1年間に購入した商品の商品分類名と商品分類ごとの購入金額合計の一覧(過去の購入済み商品分類一覧)を表示する図2のSQL文を作成した。
なお，":一年前"は，1年前の日時を表す埋込み変数である。
カーソルを使用した会員種別判定バッチ処理を行う図3のプログラムを作成した。
会員種別判定バッチ処理では，会員の購入の履歴を会員番号と購入日時の昇順に処理を行い，特別会員と判定されるまでの購入の履歴は購入単位ごとに"判定処理済み"とするが，特別会員と判定された後の購入の履歴は購入単位ごとに"繰越し"として，翌月以降の会員種別判定バッチ処理の対象にする。購入の履歴中の購入金額の合計が5万円未満の場合は，全ての購入の履歴を"判定処理済み"とする。
なお，":判定対象期限"は判定対象である月の最終日時を表す埋込み変数である。また，変数 kounyu_no，kounyu_kingaku，kaiin_no，goukei，current_kaiin_no，update_flag はそれぞれ適切な型で宣言されているものとする。LOOP から ENDLOOP までは処理の繰返し範囲を表す。FETCH文でカーソルから行を取り出して処理を続け，取り出す行がない場合には処理の繰返しを抜ける。
会員種別について，会員から"自身の会員種別の履歴を確認したい"という要望が多数寄せられた。当該機能を実現するために，図1のE-R図に対して，既存のエンティティとの間に1対多の関連をもつ新しいエンティティを一つ追加し，会員種別の判定後，その結果の適用日時を含めて記録するようにした。
設問1
〔データベースの設計〕について，(1)，(2)に答えよ。
- 図1中のaに入れる適切なエンティティ間の関連を解答群の中から選び，記号で答えよ。
- 図1中のbに入れる適切な属性名を答えよ。なお，属性名の表記は，図1の凡例に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)
a に関する解答群
- ―
- →
- ←
- ↔
解答入力欄
- a：
- b：
解答例・解答の要点
- a：イ
- b：商品番号
解説
- 〔aについて〕会員エンティティと購入エンティティには共通の属性「会員番号」があります。この属性「会員番号」は、会員エンティティ側では主キーであり、会員を一意に特定します。また、購入エンティティ側では外部キーであり、1人の会員が異なる日時に購入を行うことが考えられます（つまり、同一の会員番号の値を持つ複数のレコードが存在しえるということです）。
原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ(多重度)は「1対多」になります。
したがって、会員エンティティと購入エンティティのカーディナリティは1対多であり、aには「→」が当てはまります。
∴a＝イ：→
- 〔bについて〕
関係データベースでは主キーと外部キーによって表を関連付けます。
購入明細エンティティには外部キーとして属性"商品番号"があり、これは商品エンティティの主キーを参照していると考えられます。つまり、商品エンティティには主キーとして属性"商品番号"が必要ということなります。したがって、bには「商品番号」が当てはまります。
∴b＝商品番号
設問2
図2中のc，dに入れる適切な字句又は式を答えよ。なお，表の列名には必ずその表の別名を付けて答えよ。
解答入力欄
- c：
- d：
解答例・解答の要点
- c：SUM(t4.商品単価 * t4.個数)
- d：t2.購入日時
解説
まず、SQL文内のエイリアスと各表の関係を確認しておきましょう。
購入金額合計という名前がつけられているので、購入金額に関する列の値の合計を示す文が入ると考えられます。図2のSQL文のFROM句で結合されている各表のうち、購入金額に関する列をもつのは購入明細表であり、購入金額は商品単価列の値と個数列の値の積で表されます。
結合された表は、商品分類番号及び商品分類名でグループ化されているので、集約関数SUMを使用してグループごとに合計すれば、商品分類ごとの購入金額合計を求めることができます。図2のSQL文中において購入明細表には t4 というエイリアスが与えられているため、cには「SUM(t4.商品単価 * t4.個数)」が当てはまります。
（※t4.はつけなくてもSQL文は動作しますが、問題の指示により列名の前に表名をつける必要があるので注意してください）
∴c＝SUM(t4.商品単価 * t4.個数)
〔dについて〕
図2のSQL文の
∴d＝t2.購入日時
- t1 … 会員表
- t2 … 購入表
- t3 … 購入表から1年以内の購入データを選択し、購入番号と会員番号を抜き出した表
- t4 … 購入明細表
- t5 … 商品表
- t6 … 商品分類表
購入金額合計という名前がつけられているので、購入金額に関する列の値の合計を示す文が入ると考えられます。図2のSQL文のFROM句で結合されている各表のうち、購入金額に関する列をもつのは購入明細表であり、購入金額は商品単価列の値と個数列の値の積で表されます。
結合された表は、商品分類番号及び商品分類名でグループ化されているので、集約関数SUMを使用してグループごとに合計すれば、商品分類ごとの購入金額合計を求めることができます。図2のSQL文中において購入明細表には t4 というエイリアスが与えられているため、cには「SUM(t4.商品単価 * t4.個数)」が当てはまります。
（※t4.はつけなくてもSQL文は動作しますが、問題の指示により列名の前に表名をつける必要があるので注意してください）
∴c＝SUM(t4.商品単価 * t4.個数)
〔dについて〕
図2のSQL文の
SELECT t2.購入番号, t2.会員番号
FROM 購入 t2 WHERE d > :一年前
の部分は、購入（t2）表のd列の値が"1年前の日時"より後のレコードから購入番号と会員番号を取得するという意味です。図2のSQL文で集計対象となっているレコードは「各会員が1年間に購入した商品」であるため、購入表から購入日が過去1年以内のレコードを抽出することになります。購入表の列のうち、1年前の日時と比較される値を持つ列は、"購入日時"列であると考えられます。したがって、dには「t2.購入日時」が当てはまります。FROM 購入 t2 WHERE d > :一年前
∴d＝t2.購入日時
設問3
図3中のe～hに入れる適切な字句又は式を答えよ。なお，表の列名には必ずその表の別名を付けて答えよ。
解答入力欄
- e：
- f：
- g：
- h：
解答例・解答の要点
- e：t2.購入ステータス = '完了'
- f：ORDER BY t2.会員番号，t2.購入日時
- g：goukei >= 50000
- h：SET t1.会員種別 = '特別会員'
解説
〔eについて〕
〔データベースの設計〕には、「購入エンティティの購入ステータス属性は，購入が完了しているか否かを表す。"受注"，"入金済み"，"完了"のいずれかの値をもち，"完了"となったものだけが会員種別判定バッチ処理の対象となる。」とあります。つまり、SELECT文で購入（t2）表の購入ステータス列の値が"完了"であるレコードを取得します。したがって、WHERE句の条件となるeには「t2.購入ステータス = '完了'」が当てはまります。
∴e＝t2.購入ステータス = '完了'
〔fについて〕
〔会員種別の判定〕には、「会員種別判定バッチ処理では，会員の購入の履歴を会員番号と購入日時の昇順に処理を行い…」とあります。つまり、WHERE句で選択したレコードを、ORDER BY句を用いて会員番号と購入日時の昇順に並び替えます（列名の直後にASCを記述することにより昇順になりますが省略可能です）。したがって、fには「ORDER BY t2.会員番号, t2.購入日時」が当てはまります。
∴f＝ORDER BY t2.会員番号，t2.購入日時
〔g，hについて〕
update_flagが"0"のときは、goukeiに購入金額（kounyu_kingaku）を加算し、購入表の"判定処理状態"列の値を"判定処理済み"に更新しています。このとき、gが真ならば、update_flagを"1"に更新しています。そして、update_flagが"0"でないときは、購入表の"判定処理状態"列の値を"繰越し"に更新しています。
つまり、update_flagは購入表の"判定処理状態"列の更新の判定に用いる変数であると考えられます。問題文中には「当月の購入金額の合計が5万円以上であれば翌月の初めから月末までは特別会員」とあり、〔会員種別の判定〕には「特別会員と判定された後の購入の履歴は購入単位ごとに"繰越し"として，翌月以降の会員種別判定バッチ処理の対象にする。」とあるので、購入金額の合計が5万円以上になった時点で、会員（t1）表の会員種別列の値を"特別会員"に更新し、update_flagを"1"に更新します。update_flagを"1"にすることによって、当該会員の以降の購入には"判定処理状態"列に"繰越し"が設定されることになります。
したがって、gには「goukei >= 50000」が、hには「SET t1.会員種別 = '特別会員'」が当てはまります。
∴g＝goukei >= 50000
h＝SET t1.会員種別 = '特別会員'
〔データベースの設計〕には、「購入エンティティの購入ステータス属性は，購入が完了しているか否かを表す。"受注"，"入金済み"，"完了"のいずれかの値をもち，"完了"となったものだけが会員種別判定バッチ処理の対象となる。」とあります。つまり、SELECT文で購入（t2）表の購入ステータス列の値が"完了"であるレコードを取得します。したがって、WHERE句の条件となるeには「t2.購入ステータス = '完了'」が当てはまります。
∴e＝t2.購入ステータス = '完了'
〔fについて〕
〔会員種別の判定〕には、「会員種別判定バッチ処理では，会員の購入の履歴を会員番号と購入日時の昇順に処理を行い…」とあります。つまり、WHERE句で選択したレコードを、ORDER BY句を用いて会員番号と購入日時の昇順に並び替えます（列名の直後にASCを記述することにより昇順になりますが省略可能です）。したがって、fには「ORDER BY t2.会員番号, t2.購入日時」が当てはまります。
∴f＝ORDER BY t2.会員番号，t2.購入日時
〔g，hについて〕
update_flagが"0"のときは、goukeiに購入金額（kounyu_kingaku）を加算し、購入表の"判定処理状態"列の値を"判定処理済み"に更新しています。このとき、gが真ならば、update_flagを"1"に更新しています。そして、update_flagが"0"でないときは、購入表の"判定処理状態"列の値を"繰越し"に更新しています。
つまり、update_flagは購入表の"判定処理状態"列の更新の判定に用いる変数であると考えられます。問題文中には「当月の購入金額の合計が5万円以上であれば翌月の初めから月末までは特別会員」とあり、〔会員種別の判定〕には「特別会員と判定された後の購入の履歴は購入単位ごとに"繰越し"として，翌月以降の会員種別判定バッチ処理の対象にする。」とあるので、購入金額の合計が5万円以上になった時点で、会員（t1）表の会員種別列の値を"特別会員"に更新し、update_flagを"1"に更新します。update_flagを"1"にすることによって、当該会員の以降の購入には"判定処理状態"列に"繰越し"が設定されることになります。
したがって、gには「goukei >= 50000」が、hには「SET t1.会員種別 = '特別会員'」が当てはまります。
∴g＝goukei >= 50000
h＝SET t1.会員種別 = '特別会員'
設問4
〔会員種別の履歴の確認〕について，(1)，(2)に答えよ。
- 追加するエンティティとの間に多対1の関連をもたせる既存のエンティティのエンティティ名を答えよ。
- 追加するエンティティに含めるべき属性名を全て答えよ。なお，主キーや外部キーであることを示す下線は付けなくてよい。
解答入力欄
解答例・解答の要点
- 会員
- 会員番号，会員種別，適用日時
解説
- 追加するエンティティは会員の会員種別の履歴に関するもので、会員の会員番号と会員種別の適用日時により、履歴を一意に特定することになると考えられます。当然1人の会員に対して複数の適用日時が存在することが想定されるので、会員エンティティと追加するエンティティのカーディナリティは1対多になります。したがって、追加するエンティティと関連をもたせる既存のエンティティ名は「会員」となります。
∴会員 - 追加するエンティティでは会員の会員番号と会員種別の適用日時により、会員種別の履歴を管理することになります。したがって、追加されるエンティティには少なくとも、「会員番号，適用日時，会員種別」の属性が必要です。（関係スキーマ：エンティティ名（属性名1，属性名2，…）を記述するならば、会員種別履歴（会員番号，適用日時，会員種別）のようになります）
∴会員番号，会員種別，適用日時
