応用情報技術者試験の過去問&キーワードを動画2443本の動画で解説!スキマ時間に動画!※2017年9月11日現在

PR広告

平成28年度秋 応用情報技術者試験 午後問6 データベース 設問3 合格率アップ!動画付き解説!

ネットショップの会員管理に関する次の記述を読んで、設問1~4に答えよ。

 W社は、日用雑貨の製造・販売事業を国内で展開する中堅企業である。自社直営店やデパートなどでの販売に加えて、一般消費者向けにネットショップでも自社製品を販売している。

 ネットショップでは、購入者は会員登録を行う必要がある。会員に対しては、購入の履歴から会員の嗜好(しこう)を把握してダイレクトメールを発送し、さらに購入金額の合計に応じた会員種別を付与している。

 会員種別には一般会員と特別会員があり、特別会員は購入時に5%の割引が適用される。一般会員と特別会員の判定は、月末日のメンテナンス時間(23時30分~23時59分)のバッチ処理(以下、会員種別判定バッチ処理という)によって行われ、当月の購入金額の合計が5万円以上であれば翌月の初めから月末までは特別会員、5万円未満であれば一般会員となる。

 W社では、1回の購入金額が少額である日用雑貨の性質から、頻繁に購入する会員(リピータ)を獲得することが重要と考え、リピータが特別会員の資格を維持しやすくなる判定ルールを取り入れた。具体的には、購入の履歴中の1回ごとの購入を購入単位として、その日時の古いものから順に調べて購入金額の合計が5万円に達したら、それより後の日時の購入単位は繰越し扱いとし、翌月以降の会員種別判定バッチ処理の対象に回すことにした。

〔データベースの設計〕

 ネットショップの会員管理システム(以下、本システムという)について、E-R図を図1に示す。

 購入エンティティの購入ステータス属性は、購入が完了しているか否かを表す。"受注"、"入金済み"、"完了"のいずれかの値をもち、"完了"となったものだけが会員種別判定バッチ処理の対象となる。購入者は、購入単位ごとに代金を支払う。W社は、入金が確認された後に商品を発送し、購入ステータス属性を"完了"とする。購入エンティティの判定処理状態属性は、"未処理"、"判定処理済み"、"繰越し"のいずれかの値をもつ。

 会員エンティティの会員種別属性は、入会時には"一般会員"の値をもち、会員種別判定バッチ処理のたびに、"一般会員"か"特別会員"のいずれかの値が格納される。会員エンティティの会員番号属性には、1以上の整数が格納される。

 商品エンティティの商品定価属性には、その商品の定価が格納される。一方、購入明細エンティティの商品単価属性には、会員種別による割引を考慮した販売時の単価が格納される。また、購入エンティティの購入金額属性には、関連する購入明細の商品単価と個数の積を合算した金額が格納される。

 なお、本システムでは、E-R図のエンティティ名を表名に、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。

平成28年度秋 応用情報技術者試験 午後問6 データベース 合格率アップ!動画解説!

〔会員の晴好の把握〕

 会員の嗜好を把握してダイレクトメールを発送するために、過去1年分の購入の履歴から、各会員がその1年間に購入した商品の商品分類名と商品分類ごとの購入金額合計の一覧(過去の購入済み商品分類一覧)を表示する図2のSQL文を作成した。

 なお、":一年前"は、1年前の日時を表す埋込み変数である。

SELECT 
    t1.会員番号
  , t1.氏名
  , t6.商品分類番号
  , t6.商品分類名
  , c AS 購入金額合計
FROM 会員 t1
INNER JOIN (
  SELECT
      t2.購入番号
    , t2.会員番号
  FROM 購入 t2
  WHERE d > :一年前
) t3 ON t1.会員番号 = t3.会員番号
INNER JOIN 購入明細 t4 ON t3.購入番号 = t4.購入番号
INNER JOIN 商品 t5 ON t4.商品番号 = t5.商品番号
INNER JOIN 商品分類 t6 ON t5.商品分類番号 = t6.商品分類番号
GROUP BY 
    t1.会員番号
  , t1.氏名
  , t6.商品分類番号
  , t6.商品分類名

図2 過去の購入済み商品分類一覧を表示するSQL文

〔会員種別の判定〕

 カーソルを使用した会員種別判定バッチ処理を行う図3のプログラムを作成した。

 会員種別判定バッチ処理では、会員の購入の履歴を会員番号と購入日時の昇順に処理を行い、特別会員と判定されるまでの購入の履歴は購入単位ごとに"判定処理済み"とするが、特別会員と判定された後の購入の履歴は購入単位ごとに"繰越し"として、翌月以降の会員種別判定バッチ処理の対象にする。購入の履歴中の購入金額の合計が5万円未満の場合は、全ての購入の履歴を"判定処理済み"とする。

 なお、":判定対象期限"は判定対象である月の最終日時を表す埋込み変数である。また、変数 kounyu_no、kounyu_kingaku、kaiin_no、goukei、current_kaiin_no、update_flag はそれぞれ適切な型で宣言されているものとする。LOOP から ENDLOOP までは処理の繰返し範囲を表す。FETCH文でカーソルから行を取り出して処理を続け、取り出す行がない場合には処理の繰返しを抜ける。

DECLARE cur CURSOR FOR
  SELECT
      t2.会員番号
    , t2.購入番号
    , t2.購入金額
  FROM 購入 t2
  WHERE e
  AND t2.購入日時 <= : 判定対象制限
  AND t2.判定処理状態 <> '判定処理済み'
  f;
UPDATE 会員 t1 SET t1.会員種別 = '一般会員';
SET current_kaiin_no = 0;
SET goukei = 0;
OPEN cur;
fetch_loop LOOP
  FETCH cur INTO kaiin_no, kounyu_no, kounyu_kingaku;
  IF kaiin_no <> current_kaiin_no THEN
    SET current_kaiin_no = kaiin_no;
    SET update_flag = 0;
    SET goukei = 0;
  END IF;
  IF update_flag = 0 THEN
    SET goukei = goukei + kounyu_kingaku;
    UPDATE 購入 t2 SET t2.判定処理状態 = '判定処理済み'
      WHERE t2.購入番号 = kounyu_no;
    IF g THEN
      UPDATE 会員 t1 h 
      WHERE t1.会員番号 = kaiin_no;
      SET update_flag = 1;
    END IF
  ELSE
    UPDATE 購入 t2 SET t2.判定処理状態 = '繰越し'
    WHERE t2.購入番号 = kounyu_no;
  END IF;
END LOOP fetch_loop;
CLOSE cur;

図3 カーソルを使用した会員種別判定バッチ処理を行うプログラム(一部)

〔会員種別の履歴の確認〕

 会員種別について、会員から"自身の会員種別の履歴を確認したい"という要望が多数寄せられた。当該機能を実現するために、図1のE-R図に対して、既存のエンティティとの間に1対多の関連をもつ新しいエンティティを一つ追加し、会員種別の判定後、その結果の適用日時を含めて記録するようにした。

設問3

図3中のehに入れる適切な字句又は式を答えよ。なお、表の列名には必ずその表の別名を付けて答えよ。

設問3の解説

正解は

eには「t2.購入ステータス = '完了'」

fには「ORDER BY t2.会員番号, t2.購入日時」

gには「goukei >= 50000」

hには「SET t1.会員種別 = '特別会員'」

が入ります!

問題文

設問3

図3中のe~hに入れる適切な字句又は式を答えよ。なお、表の列名には必ずその表の別名を付けて答えよ。

ポイント:問題文をSQL化する

DECLARE cur CURSOR FOR
  SELECT
      t2.会員番号
    , t2.購入番号
    , t2.購入金額
  FROM 購入 t2
  WHERE e
  AND t2.購入日時 <= : 判定対象制限
  AND t2.判定処理状態 <> '判定処理済み'
  f;

このSQLを見ると、購入に対して、購入日時と判定処理状態でデータを抽出しています。

購入日時が判定対象制限であるかと、判定処理済みではないことです。

この他にも抽出条件があるのでしょうか

問題文の[データベースの設計]に

"受注"、"入金済み"、"完了"のいずれかの値を持ち、"完了"となったものだけが会員種別判定バッチ処理の対象となる

とあります。

この処理がSQLにありません。

従って、eには「t2.購入ステータス = '完了'」が入ります。

購入にt2とエイリアスがつけられていることに注意してください。

続いてfですが、WHERE句の次にきています。

集計する必要が問題文からないので、ORDER BYが入ることが推測されます。

並びに関する制約があるのでしょうか?

[会員種別の判定]に

会員種別判定バッチ処理では、会員の購入の履歴を会員番号と購入日時の昇順に処理を行い

とあります。

「会員の購入の履歴を会員番号と購入日時の昇順」をSQL化すると「ORDER BY t2.会員番号, t2.購入日時」となります。

従って、fには「ORDER BY t2.会員番号, t2.購入日時」が入ります。

続いてg, hです。

g, hは次のSQL分にあります。

プログラムやSQLは、細かく分割してあげることで、読みやすくなります。

下記はIF - ELSEで分けています。

  IF update_flag = 0 THEN
    SET goukei = goukei + kounyu_kingaku;
    UPDATE 購入 t2 SET t2.判定処理状態 = '判定処理済み'
      WHERE t2.購入番号 = kounyu_no;
    IF g THEN
      UPDATE 会員 t1 h 
      WHERE t1.会員番号 = kaiin_no;
      SET update_flag = 1;
    END IF
  ELSE

このSQLから、わかるのは、合計金額が算出され、購入の判定処理状態が「判定処理済み」とされていることです。

そのあとに何か処理があるのでしょうか?

「判定処理済み」の時の処理を問題文から追ってみましょう

[会員種別の判定]より

特別会員と判定されるまでの購入の履歴は購入単位ごとに"判定処理済み"とするとあり...

さらに問題文より、当月の購入金額の合計が5万円以上であれば翌月の初めから月末までは特別会員、5万円未満であれば一般会員とあります。

この5万円以上であれば、特別会員という処理と考えられます。

gには「5万円以上」の「goukei >= 50000」が入ります。

hには「特別会員にする」「SET t1.会員種別 = '特別会員'」が入ります。

平成28年度秋 応用情報技術者試験午後

問1 情報セキュリティ 設問1

問1 情報セキュリティ 設問2

問1 情報セキュリティ 設問3

問2 経営戦略 設問1

問2 経営戦略 設問2

問2 経営戦略 設問3

問2 経営戦略 設問4

問3 プログラミング 設問1

問3 プログラミング 設問2

問3 プログラミング 設問3

問4 システムアーキテクチャ 設問1

問4 システムアーキテクチャ 設問2

問4 システムアーキテクチャ 設問3

問4 システムアーキテクチャ 設問4

問5 ネットワーク 設問1

問5 ネットワーク 設問2

問5 ネットワーク 設問3

問6 データベース 設問1

問6 データベース 設問2

問6 データベース 設問3

問6 データベース 設問4

問8 情報システム開発 設問1

問8 情報システム開発 設問2

問8 情報システム開発 設問3

問8 情報システム開発 設問4

問9 プロジェクトマネジメント 設問1

問9 プロジェクトマネジメント 設問2

問9 プロジェクトマネジメント 設問3

問10 サービスマネジメント 設問1

問10 サービスマネジメント 設問2

問10 サービスマネジメント 設問3

問11 システム監査 設問1

問11 システム監査 設問2

問11 システム監査 設問3

問11 システム監査 設問4

問11 システム監査 設問5

問11 システム監査 設問6


PR広告

フェイスブックコメント

難解な応用情報技術者試験午前で80点中48点を取る優しい方法

平成28年度春 応用情報技術者試験 午後 テキスト・動画解説

平成28年度春 応用情報技術者試験 午後 動画解説再生リスト

平成28年度春 応用情報技術者試験 午前 テキスト・動画解説

平成28年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成27年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成27年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成27年度春 応用情報技術者試験 午後 テキスト・動画解説

平成27年度春 応用情報技術者試験 午後 動画解説再生リスト

平成27年度春 応用情報技術者試験 午前 テキスト・動画解説

平成27年度春 応用情報技術者試験 午前 動画解説再生リスト

平成26年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成26年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成26年度春 応用情報技術者試験 午前 テキスト・動画解説

平成26年度春 応用情報技術者試験 午前 動画解説再生リスト