令和4年春期試験午後問題 問6
問6 データベース
⇱問題PDF
クーポン発行サービスに関する次の記述を読んで，設問1～4に答えよ。
クーポン発行サービスに関する次の記述を読んで，設問1～4に答えよ。
広告
K社は，インターネットでホテル，旅館及びレストラン(以下，施設という)の予約を取り扱う施設予約サービスを運営している。各施設は幾つかの利用プランを提供していて，利用者はその中から好みのプランを選んで予約する。会員向けサービスの拡充施策として，現在稼働している施設予約サービスに加え，クーポン発行サービスを開始することにした。
発行するクーポンには割引金額が設定されていて，施設予約の際に料金の割引に利用することができる。K社は，施設，又は都道府県，若しくは市区町村を提携スポンサとして，提携スポンサと合意した割引金額，枚数のクーポンを発行する。
クーポン発行に関しては，提携スポンサによって各種制限が設けられているので，クーポンの獲得，及びクーポンを利用した予約の際に，制限が満たされていることをチェックする仕組みを用意する。
提携スポンサによって任意に設定可能なチェック仕様の一部を表1に，クーポン発行サービスの概要を表2に示す。
〔クーポン発行サービスと施設予約サービスのE-R図〕
クーポン発行サービスと施設予約サービスで使用するデータベース(以下，予約サイトデータベースという)のE-R図(抜粋)を図1に示す。予約サイトデータベースでは，E-R図のエンティティ名をテーブル名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによってデータを管理する。
クーポン管理テーブルの列名の先頭に"獲得制限"又は"予約制限"が付く列は，クーポンの獲得制限，又はクーポンを利用した予約制限のチェック処理で使用し，チェックが必要ない場合にはNULLを設定する。"獲得制限_1枚限り"には，"同一会員1枚限りの獲得制限"のチェックが必要なときは'Y'を，不要なときはNULLを設定する。
データベース設計者であるL主任は，"同一会員1枚限リの獲得制限"を制約として実装するために，図2のSQL文によってクーポン明細テーブルに対して，UNIQUE制約を付けた。なお，予約サイトデータベースにおいては，UNIQUE制約を構成する複数の列で一つの列でもNULLの場合は，UNIQUE制約違反とならない。
L主任は，①予約テーブルの"クーポンコード"，"クーポン発行連番"に対しても，UNIQUE制約を付けた。
予約サイトデータベースでは，更新目的の参照処理と更新処理においてレコード単位にロックを掛け，多重処理を行う設定としている。ロックが掛かるとトランザクションが終了するまでの間，他のトランザクションによる同一レコードに対する処理はロック解放待ちとなる。
〔クーポン獲得処理の連番管理方式〕
クーポン発行サービスと施設予約サービスのCRUD図(抜粋)を図3に示す。
クーポン新規登録処理では，1種類のクーポンにつき1レコードをクーポン管理テーブルに追加する。クーポン獲得チェック処理では，獲得可能期間，会員住所による獲得制限，発行上限枚数に関するチェックを行う。チェックの結果，エラーがない場合に表示される同意ボタンを押すことによって，クーポン獲得処理を行う。
クーポン発行サービスでは，上限の定められた発行枚数分のクーポンを抜けや重複なく連番管理する方式が必要になる。特に，提携スポンサが都道府県，市区町村であるクーポンは割引金額が大きく，クーポンの発行直後にトラフィックが集中することが予想される。発行上限枚数到達後にクーポン獲得処理が動作する場合の考慮も必要である。L主任は，トラフィック集中時のリソース競合によるレスポンス悪化を懸念して，ロック解放待ちを発生させない連番管理方式(以下，ロックなし方式という)のSQL文(図4)を考案した。このSQL文では，ロックを掛けずに参照し，主キー制約によってクーポン発行連番の重複レコード作成を防止する。
ここで，関数COALESCE(A，B)は，AがNULLでないときはAを，AがNULLのときはBを返す。また，":クーポンコード"，":会員コード"は，該当の値を格納する埋込み変数である。
〔クーポン獲得処理の連番管理方式の見直し〕
ロックなし方式をレビューしたM課長は，トラフィック集中時に主キー制約違反が発生することによって，会員による再オペレーションが頻発するデメリットを指摘し，ロック解放待ちを発生させることによって更新が順次行われる連番管理方式(以下，ロックあり方式という)の検討と方式の比較，高負荷試験の実施を指示した。
L主任は，クーポン管理テーブルに対して初期値が0の"発行済枚数"という列を追加し，このデータ項目のカウントアップによって連番管理をするロックあり方式のSQL文(図5)を考案した。
④ロックあり方式では，図3のCRUD図の一部に変更が発生する。
L主任は，ロックなし方式とロックあり方式の比較を表3にまとめ，高負荷試験を実施した。
高負荷試験実施の結果，どちらの方式でも最大トラフィック発生時のレスポンス，スループットが規定値以内に収まることが確認できた。そこで，会員による再オペレーションの発生しないロックあり方式を採用することにした。
発行するクーポンには割引金額が設定されていて，施設予約の際に料金の割引に利用することができる。K社は，施設，又は都道府県，若しくは市区町村を提携スポンサとして，提携スポンサと合意した割引金額，枚数のクーポンを発行する。
クーポン発行に関しては，提携スポンサによって各種制限が設けられているので，クーポンの獲得，及びクーポンを利用した予約の際に，制限が満たされていることをチェックする仕組みを用意する。
提携スポンサによって任意に設定可能なチェック仕様の一部を表1に，クーポン発行サービスの概要を表2に示す。
クーポン発行サービスと施設予約サービスで使用するデータベース(以下，予約サイトデータベースという)のE-R図(抜粋)を図1に示す。予約サイトデータベースでは，E-R図のエンティティ名をテーブル名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによってデータを管理する。
クーポン管理テーブルの列名の先頭に"獲得制限"又は"予約制限"が付く列は，クーポンの獲得制限，又はクーポンを利用した予約制限のチェック処理で使用し，チェックが必要ない場合にはNULLを設定する。"獲得制限_1枚限り"には，"同一会員1枚限りの獲得制限"のチェックが必要なときは'Y'を，不要なときはNULLを設定する。
予約サイトデータベースでは，更新目的の参照処理と更新処理においてレコード単位にロックを掛け，多重処理を行う設定としている。ロックが掛かるとトランザクションが終了するまでの間，他のトランザクションによる同一レコードに対する処理はロック解放待ちとなる。
〔クーポン獲得処理の連番管理方式〕
クーポン発行サービスと施設予約サービスのCRUD図(抜粋)を図3に示す。
クーポン新規登録処理では，1種類のクーポンにつき1レコードをクーポン管理テーブルに追加する。クーポン獲得チェック処理では，獲得可能期間，会員住所による獲得制限，発行上限枚数に関するチェックを行う。チェックの結果，エラーがない場合に表示される同意ボタンを押すことによって，クーポン獲得処理を行う。
ここで，関数COALESCE(A，B)は，AがNULLでないときはAを，AがNULLのときはBを返す。また，":クーポンコード"，":会員コード"は，該当の値を格納する埋込み変数である。
ロックなし方式をレビューしたM課長は，トラフィック集中時に主キー制約違反が発生することによって，会員による再オペレーションが頻発するデメリットを指摘し，ロック解放待ちを発生させることによって更新が順次行われる連番管理方式(以下，ロックあり方式という)の検討と方式の比較，高負荷試験の実施を指示した。
L主任は，クーポン管理テーブルに対して初期値が0の"発行済枚数"という列を追加し，このデータ項目のカウントアップによって連番管理をするロックあり方式のSQL文(図5)を考案した。
L主任は，ロックなし方式とロックあり方式の比較を表3にまとめ，高負荷試験を実施した。
広告
設問1
〔クーポン発行サービスと施設予約サービスのE-R図〕について，(1)～(3)に答えよ。
- 図1中のa～cに入れる適切なエンティティ間の関連及び属性名を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例及び注記に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください。) - 図2中のdに入れる適切な字句を答えよ。
- 本文中の下線①は，どのような業務要件を実現するために行ったものか。30字以内で述べよ。
解答例・解答の要点
- a：施設コード
b：プランコード
c：↑
- d：ALTER TABLE
- 1つのクーポンは一つの予約だけに利用できる (21文字)
解説
- 〔a、bについて〕
予約エンティティと利用プランエンティティには多対1の関係があります。多側の予約エンティティには1側の書類エンティティの（複合）主キーである属性「施設コード」「プランコード」を参照する外部キーがあるはずですが、これらに相当する属性がないので追加する必要があると判断できます。予約エンティティ内の属性「施設コード」「プランコード」は、利用プランエンティティの主キーを参照する外部キーとなるため、エンティティの下部に波線が付きます。
したがって、[a]には「施設コード」、[b]には「プランコード」が当てはまります（順不同）。
∴ab＝施設コード、プランコード
〔cについて〕
施設エンティティと利用プランエンティティには共通の属性「施設コード」があります。この属性「施設コード」は、施設エンティティ側では主キーであり、施設を一意に特定します。また、利用プランエンティティ側では、属性「プランコード」と共に（複合）主キーであり、利用プランを一意に特定します。同時に、利用プランエンティティの属性「施設コード」は、施設エンティティの属性「施設コード」を参照する外部キーです。本文中には「各施設は幾つかの利用プランを提供していて」とあり、同一の施設が複数の異なる利用プランをもつことが考えられます。つまり、利用プランエンティティには同一の「施設コード」の値を持つ複数のレコードが存在しえるということです。以上より、施設エンティティと利用プランエンティティのカーディナリティは1対多です。したがって、[c]には「↑」が当てはまります。
∴c＝↑
※原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は「1対多」です。 - 〔dについて〕
テーブルに制約を付与するSQL文について確認します。制約を付ける際に使用する構文は以下が基本形です。ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 制約の内容ALTER TABLEはテーブル定義を変更する際に使用する文です。したがって、[d]には「ALTER TABLE」が当てはまります。
∴d＝ALTER TABLE - 下線①のUNIQUE制約により、予約テーブルには同一の「クーポンコード」と「クーポン発行連番」の組の値をもつ複数のレコードは存在しえないことがわかります。なぜこのような制約を付けるのかというと、クーポン明細テーブルでは「クーポンコード」と「クーポン発行連番」が主キーになっているので値が同一の組は登録できませんが、予約エンティティでは外部キーなので誤って同一の組が登録されてしまうおそれがあるからです。UNIQUE制約を設定するのは、この不具合を避け、予約エンティティとクーポン明細エンティティがE-R図のとおり1対1であることを担保するためです。
これに該当する業務要件を表1、表2から探すと、表2の"クーポンの利用の概要"の「1枚のクーポンは一つの予約だけに使用できる」であると考えられます。なので、これをそのまま抜き出して解答とします。したがって、答えは「1枚のクーポンは一つの予約だけに使用できる」などです。
∴1つのクーポンは一つの予約だけに利用できる
広告
設問2
図4中のeに入れる適切な字句を答えよ。
解答例・解答の要点
e：クーポン発行連番
解説
〔eについて〕WITH句はWITH 相関名 AS (SELECT文)をSELECT文の前に記述することで、WITH句内のSELECT文の結果を相関名で呼び出すことができます。SQL文の結果を変数として扱うみたいなイメージです。
図4のSQL文では、発行対象クーポンの発行済枚数を一時的に格納しておくためにWITH句が使われています。WITH句の実行により、発行済枚数取得には対象クーポンの発行済枚数を保持する1行1列（スカラ値）の結果セットが保持されます。
メインのSELECT文を見ると、発行済枚数取得.発行済枚数に1を加算した値が、INSERT文におけるクーポン明細テーブルのクーポン発行連番列の値に対応していることがわかります。つまり、クーポンを発行する度にクーポン発行連番が1ずつ増加していくことが確認できます。これを踏まえれば、あるクーポンの発行済枚数を得るには、クーポン明細テーブルから該当のクーポンコードのレコードを選択して、クーポン発行連番のうち最も大きな値を取得すればよいです。よって、MAX(クーポン発行連番)という集計関数を使うことになります。COALESCE()に0を設定しているのは、初めてそのクーポンを発行する（該当のクーポンコードのレコードがない）ときに初期値の0を設定するためです。
したがって、[e]には「クーポン発行連番」が当てはまります。
∴e＝クーポン発行連番
表3に説明がありますが、このSQL文では、発行済枚数が上限に達すると、
WHERE (SELECT 発行済枚数 FROM 発行済枚数取得) < 発行上限枚数
の結果が0行となり、発行済枚数 + 1がNULLとなります。クーポン発行連番列は主キーの一部なので、主キー制約のNOT NULL制約によりINSERT文がエラーとなります。これにより、発行上限枚数を超えるクーポンの獲得が防止される仕組みになっています。広告
設問3
図5中のf，gに入れる適切な字句を答えよ。
解答例・解答の要点
f：SET 発行済枚数 = 発行済枚数 + 1
g：発行上限枚数
g：発行上限枚数
解説
〔fについて〕UPDATE文の基本形は以下のとおりです。空欄にはUPDATE文で更新対象とする列名と更新後の値が当てはまります。
UPDATE テーブル名 SET 列名 = 値 WHERE 更新対象行の条件
図5を見ると、UPDATE文の実行後にINSERT文が実行しており、INSERT文では、クーポン管理テーブルの発行済枚数をクーポン発行連番の値として使用しています。そのため、当該クーポンが発行されるときは、図5の前文にあるとおり、まずUPDATE文でクーポン管理.発行済枚数をカウントアップしておく必要があります。したがって、空欄には従前の発行済枚数を＋1する処理が適切です。したがって、[f]には「SET 発行済枚数 = 発行済枚数 + 1」が当てはまります。∴f＝SET 発行済枚数 = 発行済枚数 + 1
〔gについて〕
表2の"クーポンの獲得の概要"には、「クーポンの発行枚数が上限に達すると，以降の獲得はできない。」とあります。発行済枚数が上限に達しているときに＋1をしてしまうと、上限を超えた枚数が発行されることになってしまうので、発行済枚数が発行上限枚数より少ないときに限りカウントアップするようにしなければなりません。したがって、[g]には「発行上限枚数」が当てはまります。
∴g＝発行上限枚数
表3に説明がありますが、このSQL文ではクーポン管理テーブルの発行済枚数が発行上限枚数を超えないようになっています。発行上限枚数に達しているときには、発行上限枚数の値をクーポン発行連番としてレコードを挿入しようとしますが、既に発行済であるため主キー制約のUNIQE制約によってINSERT文がエラーとなります。これにより、発行上限枚数を超えるクーポンの獲得が防止される仕組みになっています。また、クーポン獲得チェック処理でエラーが返されるので会員は同意ボタンを押すことができなくなり、再オペレーションが頻発するというデメリットはなくなります。
広告
設問4
本文中の下線④について，図3中の下線②，下線③の変更後のレコード操作内容を，注記に従いそれぞれ答えよ。
解答例・解答の要点
下線②：RU
下線③：C
下線③：C
解説
CRUD図の"CRUD"は、C(Create)：追加、R(Read)：参照、U(Update)：更新、D(Delete)：削除の頭文字を合わせたもので、システムの機能がどのテーブル（エンティティ）に対してどのような操作を行うのかを一覧にした図です。"CRUD"とSQL文のデータ操作は以下のように対応しています。- C … INSERT文
- R … SELECT文
- U … UPDATE文
- D … DELETE文
∴下線②＝RU
下線③＝C
広告
