基本情報技術者 2018年 秋期 午後 問03

解答の導き方

• 決済表．決済日に非NULL制約を付けるのは適切ではありません。非NULL制約は、その列に必ず値を入れるルールです。しかし、取消しを表す決済表のレコードでは「決済日はNULL」と決められています。NULLは、値が設定されていない状態を表します。
• 商品詳細表．席種に参照制約を付けるのも適切ではありません。参照制約は、ある表の値が別の表に登録されている値を参照するようにするルールです。図1には席種だけを管理する別の表はなく、席種には「S、A及びB」があります。この列には、決められた値だけを認める検査制約が適しています。
• 販売表．会員IDに一意性制約を付けるのも適切ではありません。一意性制約は、同じ値を複数の行に登録できないようにするルールです。同じ会員が複数回購入を申し込むことはあり得るため、会員IDが販売表で重複する場合があります。購入申込みごとに一意に生成される販売IDのように、会員IDとは別の列で申込みを区別します。

誤りやすいポイント

• 「値を決められた範囲にする制約」と「別の表にある値を参照させる制約」を混同しないようにします。前者は検査制約、後者は参照制約です。
• NULLは、必ずしも入力漏れではありません。この問題では、取消しを表すレコードの決済日として意図的に使われます。
• 会員IDは会員を識別するための値ですが、販売表では同じ会員が複数の申込みを行えるので、一意とは限りません。

FAQ

解答の導き方

誤りやすいポイント

• INNER JOINを選ぶと、決済表にまだレコードがない未決済の申込みが除外されます。期限前の申込みも席数に含める必要があります。
• 取消しのレコードは、決済日がNULLでも決済額は−1です。決済日がNULLかどうかだけでは、未決済と取消しを区別できません。
• 取消しを除外する条件は、決済額が−1ではない行を選ぶことです。選択肢では、未決済のNULLと支払済みの0以上を合わせて指定する条件が必要です。
• RIGHT OUTER JOINは、販売表ではなく決済表側の行を残します。決済表に行がない未決済の申込みを数えられません。

FAQ

解答の導き方

1. 必要な情報を持つ表を選びます。
   会員の氏名と電子メールアドレスは会員表にあります。販売IDと決済期限日は販売表にあります。そのため、会員表と販売表を使います。
2. 販売表の申込みを、該当する会員に対応付けます。
   販売表には会員IDがあるので、会員表の会員IDと一致させます。複数の表を共通の項目で対応付けることを、表の結合といいます。解答群アの「販売表.会員ID = 会員表.会員ID」が、この対応付けをしています。
3. 期限日まで残り3日かを確認します。
   問題文では、DATEDIFFは第1引数から第2引数を引いた日数を返すと説明されています。したがって、DATEDIFF(販売表.決済期限日, NOW())は「決済期限日 − 実行日」です。これが3なら、決済期限日まで3日あります。
4. 支払手続が行われていない申込みを選びます。
   支払手続が行われると、販売表と同じ販売IDのレコードが決済表に追加されます。一方、販売期限を過ぎた申込みの取消しについても、問題文には「決済期限日の翌日に」決済表へレコードを追加するとあります。つまり、決済期限日まであと3日の申込みなら、取消しを示すレコードもまだ追加されていません。
   そのため、決済表に同じ販売IDがないことを条件にします。解答群アの「販売表.販売ID NOT IN (SELECT 販売ID FROM 決済表)」は、決済表にその販売IDが存在しない申込みを選びます。この条件が、支払手続が行われていない申込みに合います。

誤りやすいポイント

• 決済表にレコードがあることを、支払済みと決めつけないこと
  決済表には、支払手続によるレコードだけでなく、期限を過ぎて取り消された申込みのレコードも追加されます。取消しの場合は決済額が−1です。
• 「決済表にない」と「決済額が−1ではない」を混同しないこと
  この設問で探すのは、支払済みでも取消し済みでもない、決済表にまだ販売IDがない申込みです。決済表のレコードを探す条件ではありません。
• DATEDIFFの引き算の順序に注意すること
  この問題では第1引数から第2引数を引きます。決済期限日を先に、NOWを後に指定することで、期限日までの日数を調べています。
• 会員表と販売表の対応付けを忘れないこと
  販売表の会員IDと会員表の会員IDを結び付けないと、販売IDに関係のない会員の氏名や電子メールアドレスが表示される可能性があります。

FAQ

解答の導き方

CASE WHEN 条件 THEN 値
WHEN 条件 THEN 値
ELSE それ以外の値 END

UPDATE 決済表 SET 付与ポイント = (
  CASE WHEN 決済額 >= 20000 THEN FLOOR(決済額 * 0.03)
  WHEN 決済額 >= 10000 THEN FLOOR(決済額 * 0.02)
  ELSE FLOOR(決済額 * 0.01) END )
  WHERE DATEDIFF(NOW(), 決済日) = 1

誤りやすいポイント

• アは、既存のレコードを更新するのではなく、新しいレコードを追加しようとしています。 INSERT文は新しいレコードを追加する処理です。今回は、決済表にすでにあるレコードの付与ポイントを更新するため、処理の種類が合いません。
• イは、CASE式の書き方が正しくありません。 条件を直接並べる形式では、CASEの後にWHENを置きます。選択肢イはCASEの後に条件を書いているため、正しい形ではありません。
• エのIF ... THEN ... ELSEIF ... ENDは、このように値を返す式として使う書き方ではありません。 選択肢ウのように、CASE WHEN ... THEN ... ENDの形にします。
• 決済額の条件を確認する順番にも注意が必要です。 20,000円以上を先に判定することで、該当するレコードに3%を適用できます。

FAQ