問題プレビューID: e4d29285応用情報技術者試験 2022年(令和4年度)春期 午後

問 6
応用情報技術者試験 2022年(令和4年度)春期 午後

クーポン発行サービスに関する次の記述を読んで,設問1〜4に答えよ。

K社は,インターネットでホテル,旅館及びレストラン(以下,施設という)の予約を取り扱う施設予約サービスを運営している。各施設は幾つかの利用プランを提供していて,利用者はその中から好みのプランを選んで予約する。会員向けサービスの拡充施策として,現在稼働している施設予約サービスに加え,クーポン発行サービスを開始することにした。

発行するクーポンには割引金額が設定されていて,施設予約の際に料金の割引に利用することができる。K社は,施設,又は都道府県,若しくは市区町村を提携スポンサとして,提携スポンサと合意した割引金額,枚数のクーポンを発行する。

クーポン発行に関しては,提携スポンサによって各種制限が設けられているので,クーポンの獲得,及びクーポンを利用した予約の際に,制限が満たされていることをチェックする仕組みを用意する。

提携スポンサによって任意に設定可能なチェック仕様の一部を表1に,クーポン発行サービスの概要を表2に示す。

表 1 提携スポンサによって任意に設定可能なチェック仕様(一部)

提携スポンサクーポンの獲得制限クーポンを利用した予約制限
施設・同一会員による同一クーポンの獲得可能枚数を,1枚に制限する(以下,"同一会員1枚限りの獲得制限"という)。・設定した施設だけを予約可能にする。
・利用金額が設定金額以上の予約だけを可能にする。
都道府県,市区町村・設定地区に居住する会員だけが獲得可能にする。・設定地区にある施設だけを予約可能にする。

表 2 クーポン発行サービスの概要

利用局面概要
クーポンの照会・発行予定及び発行中クーポンの情報は,会員向けのメール配信によって会員に周知され,施設予約サービスにおいて検索,照会ができる。
クーポンの獲得・発行中のクーポンを利用するためには,会員がクーポン獲得を行う必要がある。
・クーポン獲得を行える期間は定められている。
・クーポンの発行枚数が上限に達すると,以降の獲得はできない。
クーポンの利用・獲得したクーポンは,施設予約サービスにおいて料金の割引に利用できる。
・1枚のクーポンは一つの予約だけに利用できる。
・クーポンを利用した予約をキャンセルすると,そのクーポンを別の予約に利用できる。
・クーポンの利用期間は定められていて,期限を過ぎたクーポンは無効となる。

〔クーポン発行サービスと施設予約サービスのE-R図〕

クーポン発行サービスと施設予約サービスで使用するデータベース(以下,予約サイトデータベースという)のE-R図(抜粋)を図1に示す。予約サイトデータベースでは,E-R図のエンティティ名をテーブル名に,属性名を列名にして,適切なデータ型で表定義した関係データベースによってデータを管理する。

クーポン管理テーブルの列名の先頭に"獲得制限"又は"予約制限"が付く列は,クーポンの獲得制限,又はクーポンを利用した予約制限のチェック処理で使用し,チェックが不要な場合にはNULLを設定する。"獲得制限_1枚限り"には,"同一会員1枚限りの獲得制限"のチェックが必要なときは'Y'を,不要なときはNULLを設定する。

図 1 予約サイトデータベースのE-R図(抜粋)

太字は原図の実線の下線(主キー)を表す。破線の下線(外部キー)は省略している。

  • 会員(会員コード,氏名,会員住所都道府県コード,会員住所市区町村コード)
  • 予約(予約コード,会員コード,[a],[b],利用人数,利用開始日時,クーポンコード,クーポン発行連番)
  • 利用プラン(施設コード,プランコード,最小利用人数,最大利用人数,1名分利用料金)
  • クーポン管理(クーポンコード,発行上限枚数,割引金額,獲得可能開始日時,獲得可能終了日時,利用期間開始日,利用期間終了日,獲得制限_1枚限り,獲得制限_会員住所都道府県コード,獲得制限_会員住所市区町村コード,予約制限_施設コード,予約制限_最低利用金額,予約制限_施設住所都道府県コード,予約制限_施設住所市区町村コード)
  • クーポン明細(クーポンコード,クーポン発行連番,獲得会員コード,獲得制限_1枚限り)
  • 施設(施設コード,施設名,施設住所都道府県コード,施設住所市区町村コード)

エンティティ間の関連は次のとおりである。

  • 会員 → 予約(1対多)
  • 会員 → クーポン管理(1対多)
  • 利用プラン → 予約(1対多)
  • 予約 → クーポン明細(1対多)
  • クーポン管理 → クーポン明細(1対多)
  • 施設 → クーポン管理(1対多)
  • 利用プラン —[c]— 施設

凡例 エンティティ名 —:1対1 →:1対多 ←→:多対多

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

データベース設計者であるL主任は,"同一会員1枚限りの獲得制限"を制約として実装するために,図2のSQL文によってクーポン明細テーブルに対して,UNIQUE制約を付けた。なお,予約サイトデータベースにおいては,UNIQUE制約を構成する複数の列で一つの列でもNULLの場合は,UNIQUE制約違反とならない。

図 2 "同一会員1枚限りの獲得制限"を制約とするためのSQL文

[d] クーポン明細 ADD CONSTRAINT クーポン明細_IX1
UNIQUE(クーポンコード,獲得会員コード,獲得制限_1枚限り)

L主任は,①予約テーブルの"クーポンコード","クーポン発行連番"に対しても,UNIQUE制約を付けた。

予約サイトデータベースでは,更新目的の参照処理と更新処理においてレコード単位にロックを掛け,多重処理を行う設定としている。ロックが掛かるとトランザクションが終了するまでの間,他のトランザクションによる同一レコードに対する処理はロック解放待ちとなる。

〔クーポン獲得処理の連番管理方式〕

クーポン発行サービスと施設予約サービスのCRUD図(抜粋)を図3に示す。

クーポン新規登録処理では,1種類のクーポンにつき1レコードをクーポン管理テーブルに追加する。クーポン獲得チェック処理では,獲得可能期間,会員住所による獲得制限,発行上限枚数に関するチェックを行う。チェックの結果,エラーがない場合に表示される同意ボタンを押すことによって,クーポン獲得処理を行う。

図 3 クーポン発行サービスと施設予約サービスのCRUD図(抜粋)

処理名テーブル名
会員予約クーポン管理クーポン明細
クーポン発行サービスクーポン新規登録--C-
クーポン獲得チェックR-RR
クーポン獲得R-②R③CR
施設予約サービス施設予約前チェックRRRR
施設予約実行RC-R
施設予約キャンセルRRD--

注記 C:追加,R:参照,U:更新,D:削除

クーポン発行サービスでは,上限の定められた発行枚数分のクーポンを抜けや重複なく連番管理する方式が必要になる。特に,提携スポンサが都道府県,市区町村であるクーポンは割引金額が大きく,クーポンの発行直後にトラフィックが集中することが予想される。発行上限枚数到達後にクーポン獲得処理が動作する場合の考慮も必要である。L主任は,トラフィック集中時のリソース競合によるレスポンス悪化を懸念して,ロック解放待ちを発生させない連番管理方式(以下,ロックなし方式という)のSQL文(図4)を考案した。このSQL文では,ロックを掛けずに参照し,主キー制約によってクーポン発行連番の重複レコード作成を防止する。

ここで,関数COALESCE(A, B)は,AがNULLでないときはAを,AがNULLのときはBを返す。また,":クーポンコード",":会員コード"は,該当の値を格納する埋込み変数である。

図 4 ロックなし方式のSQL文

INSERT INTO クーポン明細 (クーポンコード, クーポン発行連番, 獲得会員コード, 獲得制限_1枚限り)
WITH 発行済枚数取得 AS (SELECT COALESCE(MAX([e]), 0) AS 発行済枚数
FROM クーポン明細 WHERE クーポンコード = :クーポンコード)
SELECT :クーポンコード,
  (SELECT 発行済枚数 + 1 FROM 発行済枚数取得 WHERE
    (SELECT 発行済枚数 FROM 発行済枚数取得) < 発行上限枚数),
  :会員コード, 獲得制限_1枚限り
FROM クーポン管理 WHERE クーポンコード = :クーポンコード

〔クーポン獲得処理の連番管理方式の見直し〕

ロックなし方式をレビューしたM課長は,トラフィック集中時に主キー制約違反が発生することによって,会員による再オペレーションが頻発するデメリットを指摘し,ロック解放待ちを発生させることによって更新が順次行われる連番管理方式(以下,ロックあり方式という)の検討と方式の比較,高負荷試験の実施を指示した。

L主任は,クーポン管理テーブルに対して初期値が0の"発行済枚数"という列を追加し,このデータ項目のカウントアップによって連番管理をするロックあり方式のSQL文(図5)を考案した。

図 5 ロックあり方式のSQL文

UPDATE クーポン管理 [f]
WHERE クーポンコード = :クーポンコード AND 発行済枚数 < [g];
INSERT INTO クーポン明細 (クーポンコード, クーポン発行連番, 獲得会員コード, 獲得制限_1枚限り)
SELECT :クーポンコード, 発行済枚数, :会員コード, 獲得制限_1枚限り
FROM クーポン管理 WHERE クーポンコード = :クーポンコード;

④ロックあり方式では,図3のCRUD図の一部に変更が発生する。

L主任は,ロックなし方式とロックあり方式の比較を表3にまとめ,高負荷試験を実施した。

表 3 ロックなし方式とロックあり方式の比較

方式ロック解放待ち主キー制約違反による再オペレーション発行上限枚数に到達後の動作
ロックなし発生しない発生する副問合せで取得する発行済枚数+1の値がNULLになり,クーポン明細テーブルのクーポン発行連番がNULLのレコードを追加しようとして,主キー制約違反となる。
ロックあり発生する発生しない更新が行われず,クーポン明細テーブルのクーポン発行連番が[g]のレコードを追加しようとして,主キー制約違反となる。

注記 表3中の[g]には,図5中の[g]と同じ字句が入る。

高負荷試験実施の結果,どちらの方式でも最大トラフィック発生時のレスポンス,スループットが規定値以内に収まることが確認できた。そこで,会員による再オペレーションの発生しないロックあり方式を採用することにした。

設問1

〔クーポン発行サービスと施設予約サービスのE-R図〕について,(1)〜(3)に答えよ。

(1) 図1中の[a]〜[c]に入れる適切なエンティティ間の関連及び属性名を答え,E-R図を完成させよ。なお,エンティティ間の関連及び属性名の表記は,図1の凡例及び注記に倣うこと。

(2) 図2中の[d]に入れる適切な字句を答えよ。

(3) 本文中の下線①は,どのような業務要件を実現するために行ったものか。30字以内で述べよ。

設問2

図4中の[e]に入れる適切な字句を答えよ。

設問3

図5中の[f],[g]に入れる適切な字句を答えよ。

設問4

本文中の下線④について,図3中の下線②,下線③の変更後のレコード操作内容を,注記に従いそれぞれ答えよ。

模範解答

設問1 (1) a 施設コード,b プランコード(a,bは順不同),c ↑ (2) d ALTER TABLE (3) 1枚のクーポンは一つの予約だけに利用できる。

設問2 e クーポン発行連番

設問3 f SET 発行済枚数 = 発行済枚数 + 1,g 発行上限枚数

設問4 下線② RU,下線③ C

解説

設問1(1)では,予約は利用プランの施設コードとプランコードを外部キーとしてもつので,aは施設コード,bはプランコードであり,順不同である。cは利用プランと施設の間の関連であり,施設を起点とする1対多の矢印(↑)である。(2)では,既存のクーポン明細テーブルに制約を追加するので,dはALTER TABLEである。(3)では,予約テーブルのクーポンコードとクーポン発行連番にUNIQUE制約を付けることで,1枚のクーポンは一つの予約だけに利用できるという業務要件を実現している。

設問2のeは,発行済枚数を取得するためのMAX関数の引数であり,クーポン発行連番である。

設問3のfは発行済枚数をカウントアップするSET句(SET 発行済枚数 = 発行済枚数 + 1)であり,gは上限判定の比較対象である発行上限枚数である。

設問4では,ロックあり方式でクーポン管理テーブルの発行済枚数を更新するようになるため,クーポン獲得処理のクーポン管理に対する操作は参照・更新(RU)に,クーポン明細に対する操作は追加だけ(C)に変わる。

関連問題

本文が同じ・似ている問題を表示しています