応用情報技術者 2022年 春期 午後 問06

解答の論理構成

1. 予約は “施設” が提供する “利用プラン” を選択して成立する
   【問題文】には「施設は複数の利用プランを提供していて、利用者はその中から好みのプランを選んで予約する。」とあります。したがって予約レコードは、どの “施設” のどの “利用プラン” を選んだかを示す列を必ず持つ必要があります。
2. “利用プラン” の主キーは “施設コード” と “プランコード” の複合キー
   1件のプランを一意に特定するには “施設コード” と “プランコード” の組が不可欠です。よって予約側には両方を外部キーとして格納するのが自然です。
3. 以上より、予約テーブルの [a] 列は “施設コード”, [b] 列は “プランコード”
4. 関連の多重度
   一つの “利用プラン” に対して予約は多数発生し得ますが、1件の予約が複数のプランを同時に指すことはありません。つまり “利用プラン” 1 － N “予約” です。E-R図の凡例に従い、矢印で「1対多」を表すため [c] には該当する矢印（↑）を記述します。

誤りやすいポイント

• “プランコード” だけを外部キーにしてしまう
  “プランコード” は施設横断で一意とは限りません。必ず “施設コード” と組み合わせて複合キーにする必要があります。
• “施設コード” は “利用プラン” に含まれているから予約には不要と考えるミス
  “利用プラン” への参照を張るなら、複合キーの両方を外部キーとして持たなければ参照整合性が保てません。
• 多重度を 1対1 と誤解する
  プランを予約するたびに新しい予約レコードが追加されるため、多重度は 1対多です。

FAQ

解答の論理構成

1. 【問題文】には、図2のSQL文が
   「d クーポン明細 ADD CONSTRAINT クーポン明細_IX1 UNIQUE(クーポンコード、獲得会員コード、獲得制限_1枚限り)」
   と示されています。
2. また、同じ段落で
   「図2のSQL文によってクーポン明細テーブルに対して、UNIQUE 制約を付けた」
   と記述されています。UNIQUE 制約を“既存”テーブルに追加する標準SQL文は
   ALTER TABLE <表名> ADD CONSTRAINT <制約名> UNIQUE (列, …)
   であることは基本事項です。
3. 従って、d に入る字句は ALTER TABLE でなければ文法が成立しません。

誤りやすいポイント

• CREATE TABLE と混同して「CREATE」から書き始めてしまう。今回は既存表への制約追加なので CREATE ではありません。
• 「ADD UNIQUE …」だけで始めるミス。表名の前に ALTER TABLE が必須です。
• UNIQUE 制約名を付与する構文を忘れ、「ADD UNIQUE …」の直後に列名を書いてしまうケース。

FAQ

解答の論理構成

• 【問題文】には「L 主任は、①予約テーブルの“クーポンコード”、“クーポン発行連番”に対しても、UNIQUE 制約を付けた」とあります。
  UNIQUE 制約は、指定した列の組合せが重複する行の登録を禁止します。
• 【問題文】表2「クーポンの利用」欄には、「・1枚のクーポンは一つの予約だけ利用できる。」と明記されています。
  つまり、同一の“クーポンコード”と“クーポン発行連番”の組合せは、複数の予約に現れてはなりません。
• 予約テーブル側でこの二つの列に UNIQUE 制約を施せば、あるクーポンが既に使われている状態で別予約へ再利用しようとした際に制約違反が発生し、業務要件をシステム的に担保できます。
• したがって、下線①の目的は「1枚のクーポンは一つの予約だけに利用できる。」という要件の実現です。

誤りやすいポイント

• 「同一会員1枚限りの獲得制限」や「発行上限枚数の管理」と取り違え、予約テーブルに UNIQUE 制約を付ける理由を誤解する。
• UNIQUE を主キーと混同し、NULL 値の扱い（【問題文】では「一つの列でも NULL の場合は、UNIQUE 制約違反とならない」）を見落とす。
• 「キャンセル後は再利用できる」仕様に惑わされ、キャンセル時のクーポン明細更新と予約側 UNIQUE 制約の関係を想像しすぎて本質を外す。

FAQ

解答の論理構成

1. 副問合せの目的を確認
   図4の SQL では、まず
   sql WITH 発行済枚数取得 AS ( SELECT COALESCE(MAX(e), 0) AS 発行済枚数 FROM クーポン明細 WHERE クーポンコード = :クーポンコード)
   として、ある “:クーポンコード” について既に発行されている枚数の最大値を取得し、次の発行連番を算出しています。
   ここで必要なのは「そのクーポンで現在付与されている最大の連番」です。
2. どの列が “連番” かを確定
   問題文には
   ・「このSQL文では…主キー制約によってクーポン発行連番の重複レコード作成を防止する。」
   ・「L 主任は、予約テーブルの“クーポンコード”、“クーポン発行連番”に対しても、UNIQUE 制約を付けた。」
   とあり、“クーポン発行連番” が連番を示す列であることが明言されています。
3. MAX 関数の対象列は “クーポン発行連番”
   したがって e には、連番を表す列名 “クーポン発行連番” を入れることで、
   「該当クーポンの発行連番の最大値 → 発行済枚数」
   という処理意図が正しく実装できます。
4. 結論
   e には “クーポン発行連番” が入るため、模範解答
   e：クーポン発行連番
   が成立します。

誤りやすいポイント

• 発行済枚数という別名に引きずられ “e = 発行済枚数” としてしまう。列ではなく集計結果の別名なので誤りです。
• COUNT(*) を使うと 0 から始まる連番にならず、途中欠番が出た場合に整合しません。MAX で最新値を取る必要があります。
• “クーポンコード” など主キー列と勘違いして入れるケース。MAX を取っても意味がなく、論理破綻します。

FAQ

解答の論理構成

1. まず、図5の1行目のSQLはクーポン管理テーブルで発行枚数をインクリメントする更新処理です。問題文には
   “L主任は、クーポン管理テーブルに対して初期値が0の“発行済枚数”という列を追加し、このデータ項目のカウントアップによって連番管理をするロックあり方式のSQL文（図5）を考案した。”
   とあります。したがって f には “発行済枚数” を “+1” する処理が入る必要があります。SQLの一般形は
   sql UPDATE テーブル名 SET 列名 = 列名 + 1
   なので f には
   “SET 発行済枚数 = 発行済枚数 + 1”
   が入ります。
2. 次に WHERE 句の比較対象 g です。図5の UPDATE 文は “発行済枚数 < g” のときのみ更新を行います。問題文の表3でもロックあり方式について
   “発行上限枚数に到達後の動作” 欄に “クーポン明細テーブルのクーポン発行連番が g のレコードを追加しようとして、主キー制約違反となる。”
   と記載されています。ここで “g に図5中の g と同じ字句が入る” と注記されています。
   更新の条件は “発行上限枚数” に達していないかどうかの判定なので、 g は “発行上限枚数” であると確定します。
3. 以上より
   f：SET 発行済枚数 = 発行済枚数 + 1
   g：発行上限枚数
   が正答となります。

誤りやすいポイント

• 発行済枚数をインクリメントする位置を誤り、INSERT 文側に書いてしまう。
• g を “発行済枚数” と書き換えてしまい、更新条件が常に偽になるため予約が取れなくなる。
• 図4（ロックなし方式）の副問合せロジックと混同し、COALESCE や MAX をそのまま使おうとする。
• 主キー制約違反の原因が INSERT 文ではなく UPDATE 失敗後の INSERT にある点を読み落とす。

FAQ

解答の論理構成

1. 「④ロックあり方式では、図 3 の CRUD 図の一部に変更が発生する」と記載されています。
2. ロックあり方式の具体的な SQL は図5です。
   sql UPDATE クーポン管理　f WHERE クーポンコード = :クーポンコード AND 発行済枚数 < g ; INSERT INTO クーポン明細 (クーポンコード, クーポン発行連番, 獲得会員コード, 獲得制限_1枚限り) SELECT :クーポンコード, 発行済枚数, :会員コード, 獲得制限_1枚限り FROM クーポン管理 WHERE クーポンコード = :クーポンコード;
3. 第1文で《UPDATE クーポン管理》を実行しているため、クーポン管理テーブルには
   ・SELECT で値を取得（Read）
   ・UPDATE で値を更新（Update）
   の両方が必要です。従って CRUD 区分は “RU” となります。
4. 第2文は《INSERT INTO クーポン明細》のみで、クーポン明細テーブルに対して Read は行っていません。従って CRUD 区分は “C” です。
5. 以上より、図3の下線②は「RU」、下線③は「C」となります。

誤りやすいポイント

• 「SELECT … FROM クーポン管理」を INSERT 文の中で使っているため、クーポン明細でも Read があると誤認しがちですが、実際に Read しているのはクーポン管理です。
• UPDATE が追加されると “U” だけと思い込み、“R” を落とすケースが多いです。SQL が現在値を参照してから更新している点を見落とさないようにしましょう。
• CRUD の記号は並び順に意味はありませんが、回答例では “RU” の順で示されています。複数操作があるときは両方書く必要があります。

FAQ