令和7年度 春期 応用情報技術者試験 午後 問6 販売管理システムのE-RとSQL集計

種別	種別名	概要
H	葉書	キャンペーンについて、特定の会員に葉書で案内する。 全国の店舗及びインターネットでの販売が対象の場合、直近1年以内に購入した実績がある会員に案内する。 特定地域の店舗が対象の場合、店舗ごとに決められた範囲（以下、店舗エリアという）に住む会員に案内する。店舗エリアは、その店舗に来客が見込まれる会員の居住範囲を複数の郵便番号で定めた範囲として定義する。店舗エリアの範囲は、最初に対象店舗の半径5km以内の地域に該当する郵便番号を登録し、定期的に見直す。
E	電子メール	キャンペーンについて、全ての会員に電子メールで案内する。案内の内容は、会員の住所や購入実績に応じてカスタマイズする。
B	バナー広告	釣り情報のWebサイトやソーシャルメディアにバナー広告を表示して、キャンペーンをアピールする。
L	リスティング広告 (検索連動型広告)	Webサイトの検索エンジンにおいて、釣り関連のキーワードを入力した際、検索結果にURLリンク付きのテキストを表示して、キャンペーンをアピールする。

エンティティと属性、およびリレーションシップを示したE-R図。

[エンティティと属性]

• 店舗エリア詳細：店舗エリア番号(主キー), a
• 店舗エリア：店舗エリア番号(主キー), 店舗エリア名
• 店舗：店舗番号(主キー), 店舗エリア番号(外部キー), 店舗郵便番号, 店舗住所
• 会員：会員番号(主キー), 会員郵便番号, 会員住所, 会員メールアドレス
• 売上：売上番号(主キー), 店舗番号(外部キー), 売上年月日, 会員番号(外部キー), キャンペーン番号(外部キー), キャンペーン種別
• 売上明細：売上番号(主キー・外部キー), 売上明細番号(主キー), 商品番号(外部キー), 商品個数, 商品単価
• 商品：商品番号(主キー), 商品名, 商品種別, 商品標準売価, 商品原価
• キャンペーン：キャンペーン番号(主キー), キャンペーン名, 目標会員獲得数, 目標売上向上金額, 目標利益向上金額, 予算金額, 開始年月日, 終了年月日
• キャンペーン実施：キャンペーン番号(主キー・外部キー), キャンペーン実施番号(主キー), キャンペーン種別, キャンペーン実施金額, 開始年月日, 終了年月日
• キャンペーン商品：キャンペーン番号(主キー・外部キー), キャンペーン商品番号(主キー), キャンペーン商品売価

[リレーションシップ]

• 店舗エリアから店舗エリア詳細へ 1対1 の線（店舗エリア側が矢印）
• 店舗エリアから店舗へ 1対多 の矢印
• 会員から売上へ 1対多 の矢印
• 店舗から売上へ 1対多 の矢印
• キャンペーンから売上へ 1対多 の矢印
• 売上から売上明細へ 1対多 の矢印
• 商品から売上明細へ 1対多 の矢印
• キャンペーンからキャンペーン実施へ 1対多 の矢印
• キャンペーンからキャンペーン商品へ 1対多 の矢印
• 商品からキャンペーン商品の間の線上に空欄 b が存在する

[凡例]

• 線のみ：1対1
• 片側矢印：1対多
• 両側矢印：多対多

[注記]
属性名の実線の下線は主キー、破線の下線は外部キーを示す。主キーの実線が付いている属性名には、外部キーの破線を付けない。

SELECT CK.キャンペーン種別, COALESCE(UK.売上計,0) AS 売上計,
  COALESCE(UK.原価計,0) AS 原価計,
  COALESCE(UK.売上計,0) - COALESCE(UK.原価計,0) AS 粗利計,
  CK.キャンペーン実施金額計
  <span id="q6_blank_c" class="blank-label">c</span>
(SELECT キャンペーン種別, SUM(キャンペーン実施金額) AS キャンペーン実施金額計
 FROM キャンペーン実施
 WHERE キャンペーン番号 = :キャンペーン番号
 GROUP BY キャンペーン種別) CK
  <span id="q6_blank_d" class="blank-label">d</span>
(SELECT U.キャンペーン種別, SUM(M.商品個数 * M.商品単価) AS 売上計,
  <span id="q6_blank_e" class="blank-label">e</span> AS 原価計
 FROM 売上 U
  INNER JOIN 売上明細 M ON U.売上番号 = M.売上番号
  INNER JOIN 商品 S ON M.商品番号 = S.商品番号
 WHERE U.キャンペーン番号 = :キャンペーン番号
 GROUP BY U.キャンペーン種別) UK
ON CK.キャンペーン種別 = UK.キャンペーン種別

SELECT K.会員郵便番号, COUNT(*) AS 売上実績数
FROM 売上 U INNER JOIN 会員 K ON U.会員番号 = K.会員番号
WHERE U.店舗番号 = :店舗番号
AND U.売上年月日 >= :一年前年月日
  <span id="q6_blank_f" class="blank-label">f</span>
GROUP BY K.会員郵便番号
  <span id="q6_blank_g" class="blank-label">g</span>

Webキャンペーン実績 (キャンペーン番号, キャンペーン実施番号, キャンペーン種別, 広告番号, 実績年月日, 広告表示位置, キーワード, 広告費用, 表示回数, クリック数, 会員登録数)

注記 下線は主キーを示す。