基本情報技術者過去問題平成30年秋期午後問3(データベース)

基本情報技術者過去問題平成30年秋期午後問3

問3　データベース

コンサートチケット販売サイトの関係データベースの設計及び運用に関する次の記述を読んで，設問1～4に答えよ。

　D社は，Web上で会員制のコンサートチケット販売サイトを運営している。販売サイトのシステムは販売サブシステムと席予約サブシステムから構成され，販売サブシステムで購入申込み及び決済を処理し，席予約サブシステムで座席指定を処理する。本問では，販売サブシステムだけを取り扱う。
　販売サブシステムで利用しているデータベースの表構成とデータの格納例を図1に示す。下線付きの項目は主キーである。

〔コンサートの席の説明〕

コンサートの席種には，S，A及びBがある。
各席種の価格(常に有料)及び発売席数は，コンサートごとに異なる。

〔販売サブシステムの説明〕

販売サブシステムは取り扱うコンサートの席種ごとの販売可能な席数を管理する。
会員が購入申込みを行うと，販売サブシステムは一意な販売IDを生成して販売表にレコードを追加する。
会員が支払手続を行うと，決済処理として販売サブシステムは販売IDを主キーとするレコードを決済表に追加する。ここで，決済日はレコードを追加した日とする。
販売サブシステムは決済期限日の翌日に，決済期限日を過ぎた販売表中のレコードと販売IDが同じレコードが決済表にない場合，その購入申込みは取り消されたものとして，バッチ処理によって決済表に当該販売IDを主キーとするレコードを追加する。このレコードの決済日はNULLで，決済額は－1とする。
バッチ処理は，毎夜0～4時の販売サイトのシステムのメンテナンス時間帯に行う。
会員が購入を申し込んだ席数が，その時点で販売可能な席数を上回る場合には，販売サブシステムは"販売終了"と表示し，この購入申込みを受け付けない。

設問1

データベースのデータの整合性を保つためにDDLで制約をつけている。図1の表構成において，列名とその列に指定する制約の正しい組合せを，解答群の中から選べ。

解答群

解答選択欄

解答

解説

正しい。検査制約は、列に格納できるデータの値や範囲に制限を加える制約です。その列の入力値が条件に合致しているかどうかをチェックし、条件に合っていないレコードの挿入を拒否します。
本問では、支払手続があったときに"決済"表にレコードを追加しますが、追加されるレコードの"決済額"が、"販売"表の当該販売IDの"販売額"の値と一致すること^※を保証するために、"決済"表の"決済額"列に検査制約をすることが有効です。
※支払期限を過ぎた場合には、"決済額"が－1のレコードが挿入されるため、厳密にいえば"販売額"の値と完全一致という条件式は使えません。検査制約では、and や or を含む複数条件を記述することができるので、－1または"販売額"の値と一致という条件を設定することになるでしょう。
非NULL制約は、その列の値としてNULL値を認めない制約です。
本文中に「バッチ処理において決済表に当該販売IDを主キーとするレコードを追加する。このレコードの決済日はNULLで，決済額は－1とする」と説明されているように、決済期限日を過ぎたレコードに対するバッチ処理が行われると、"決済"表に対して"決済日"列がNULLであるレコードが挿入されます。"決済日"列への非NULL制約は、この業務手続きに反します。
参照制約は、"参照元の列に格納される値は参照先の列に存在する値でなければならない"という制限を課す制約です。参照制約は、外部キー制約とも呼ばれ、外部キーを設定すると参照制約が付されます。
図1の表構成では、"販売"表の"席種"列が"商品詳細"表の"席種"列を参照しているので、外部キー（参照元）となる"販売"表の"席種"列に参照制約を設定することになります。
一意性制約は、その列の値が列中で一意であることを強制する制約です。
"販売"表の"会員ID"列に一意性制約を付けると、1人の会員が同一コンサートのチケットを複数回購入したり、複数のコンサートのチケットを申し込んだりすることができなくなってしまうので不適切です。

設問2

"販売終了"の表示判定を行うために，販売できない席数を求める必要がある。販売できない席数を出力するSQL文の　に入れる正しい答えを，解答群の中から選べ。ここで，コンサートIDはC00001，席種はSである。a1とa2に入れる答えは，aに関する解答群の中から組合せとして正しいものを選ぶものとする。

a に関する解答群

解答選択欄

解答

a=エ

解説

設問の解説に入る前に、次例の表をもとにINNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOINという3つの結合指定について説明しておきます。

INNER JOIN（内部結合）: 「WHERE R.A = S.A」で結合する場合と同じ自然結合を行う。単に JOIN と指定した場合にも内部結合になる。
LEFT OUTER JOIN（左外部結合）: 基準となる左表の行を全て抽出し、右表からは左表の行と結合できる行のみを抽出する結合方法。対応する右表の行が存在しない場合には（販売ID H000006 の行のように）NULLで埋められる。
RIGHT OUTER JOIN（右外部結合）: 基準となる右表の行を全て抽出し、左表からは右表の行と結合できる行のみを抽出する結合方法。対応する左表の行が存在しない場合にはNULLで埋められる。

それではこれを踏まえて解説に移ります。

まずa1に入る結合方法から考えます。販売できない席数とは、購入申込み席数のうち、決済済みの席数と未決済（決済期限到来前）の席数を足した数です。3種類の結合のうち、INNER JOIN と RIGHT OUTER JOIN は、"販売"表の行の一部が抽出されません。これだと、購入申込みをしていて未決済の席を示す行が集計から漏れてしまいます。上記の例でいれば販売ID H000006 の行が漏れてしまう行に該当します。左外部結合を行えば、結合後に"販売"表の全ての行が残るので、この問題は起こりません。

よって、a1には左外部結合を指定する LEFT OUTER JOIN が入ります。

次にa2です。a2にはWHERE句に指定する抽出条件が入ります。
最初に左外部結合で結合した後の中間表の状態を確認します（既にコンサートID及び席種で絞られた状態として考えてください）。

購入申込みが行われた席のうち、決済済みの席では"決済額"列に－1以外の値が入力されており、未決済の場合には対応する決済表の行が存在しないので"決済額"列の値はNULLになっています。

つまり、次の2つのいずれかを満たす行を抽出すれば良いことになります。

決済済みの行: "決済額"列の値が0以上
決済期限未到来で未決済の行: "決済額"列の値がNULL

よって、a2に入る式は、

(決済表.決済額 IS NULL OR 決済表.決済額 >= 0)

になります。

後は抽出された行の"席数"列の値をSUM()で合計すれば、販売できない席数が求められるという訳です。

したがって正しい組合せは「エ」です。

申込み取消しになった席数が表示されます。
決済済みの席数が表示されます。
申込み取消しになった席数と未決済の席数の合計が表示されます。
正しい。
申込み取消しになった席数が表示されます。
決済済みの席数が表示されます。

設問3

決済期限日まで残リ3日となっても支払手続が行われていない購入申込みがある会員に，支払手続を促す電子メールを送る。この会員の氏名，電子メールアドレス及び販売IDを出力するSQL文の　に入れる正しい答えを，解答群の中から選べ。NOW はSQLを実行した日の日付を返すユーザ定義関数であり，DATEDIFF はともに日付である二つの引数を受け取って第1引数から第2引数を引いた日数を整数値で返すユーザ定義関数である。

b に関する解答群

解答選択欄

解答

b=ア

解説

このSQL文で抽出するのは、決済期限日が到来しておらず、決済が行われていない行です。

はじめに、"販売"表及び"決済"表への行追加のタイミングを確認しておきましょう。

"販売"表への行追加: 購入申込みが行われた時点
: 決済が行われた時点、または、バッチ処理により申込み取消しとなった時点

つまり、購入申込みが行われただけでまだ決済が行われていない場合には、"販売"表には対応する行が存在しますが、"決済"表には対応する行が存在しない状態になっていることになります。下図で言うと販売ID H000006 の行がこれに該当します。

正しい。副問合せでは"決済"表に存在するすべての（つまり決済済みと申込み取消しの）行が抽出され、それらの販売IDのリストが返されます。WHERE句ではNOT IN句で各行の販売IDをこのリストと照合し、リストに含まれていなければ真を返します。この操作により、"販売"表と"会員"表を結合させた中間表から決済済み又は申込み取消しになっている行が除外されるので、未決済の行のみが残ることになります。
副問合せでは"決済"表から決済額が0以上になっている（つまり決済済みとなっている）行が抽出され、それらの販売IDのリストが返されます。WHERE句ではIN句で各行の販売IDをこのリストと照合し、リストに含まれていれば真を返します。つまり、このSQL文では決済済みになっている行のみが抽出されることになるので誤りです。
"販売"表と"決済"表を販売IDで結合すると、"決済済みになっている行"と"申込み取消しになった行"のみが残ります。メールを送る対象になっている未決済の行は抽出されません。なぜなら、"決済"表にはこの2つに該当するレコードしか登録されていないからです。よって、このSQL文は誤りです。
「ウ」と同様に"販売"表と"決済"表を販売IDで結合した時点で、未決済の行は抽出結果から漏れてしまいます。「ウ」との違いは、決済額が－1の行を除外しているので、申込み取消しの行が取り除かれ、決済済みの行のみが残る点です。

設問4

会員への優待サービスのために，ポイント制度を導入する。そのために修正した会員表，決済表及び販売表の表構成を図2に示す。ポイント制度を導入するときに追加した列は0で初期化する。

　会員は購入申込み時に，1ポイント1円としてポイント残高の範囲で，販売額に充当するポイント数を指定する。販売管理システムは，指定したポイント数を使用ポイントに格納し，ポイント残高から減じる。会員は，販売額から使用ポイントを差し引いた金額を決済額として支払う。販売額の全額にポイントを充当した場合は，販売サブシステムは購入申込み時に支払手続が行われたものとし，決済処理として，決済表にレコードを追加する。
　ポイント制度の導入時に追加したバッチ処理によって，前日に決済処理された販売IDごとに，その決済額が20,000円以上，10,000円以上20,000円未満，10,000円未満の場合に，それぞれ3%，2%，1%のポイントを付与する。付与したポイント数は，付与ポイントに格納し，ポイント残高に加える。
　決済表の付与ポイントを更新する正しいSQL文を，解答群の中から選べ。NOW，DATEDIFF は設問3で使用したユーザ定義関数と同じであり，FLOOR は引数の値以下で最大の整数値を返す関数である。

解答群

解答選択欄

解答

解説

まず「ア」だけがレコードを追加するINSERT文になっていますバッチ処理では、"決済"表の追加済レコードに対して付与ポイントの値を更新するので使用するのはUPDATE文になります。よって「ア」は誤りです。

次に決済金額によってポイント付与率を変える部分に注目します。一般的なプログラム言語で使われるIF-ELSE構造をSQLで記述するためにはCASE式を使用します。

//単純CASE式
CASE [制御式]
　WHEN [値1] THEN [返り値1]
　WHEN [値2] THEN [返り値2]
　ELSE [どの条件にも一致しない場合に返す値]
END

//検索CASE式
CASE
　WHEN [条件式1] THEN [返り値1]
　WHEN [条件式2] THEN [返り値2]
　ELSE [どの条件にも一致しない場合に返す値]
END

ポイント付与率は、決済額20,000円以上のとき3%、10,000円以上20,000円未満のとき2%、それ以外（10,000円未満）では1%なので、付与ポイントを返す適切な構文は次のようになります。

CASE
　WHEN 決済額 >= 20000 THEN FLOOR(決済額 * 0.03)
　WHEN 決済額 >= 10000 THEN FLOOR(決済額 * 0.02)
　ELSE FLOOR(決済額 * 0.01)
END

したがって「ウ」が正解です。

INSERT文になっているので誤りです。
CASEに続くWHENが抜けているので構文エラーです。
正しい。
IF-ELSE構造を記述するにはCASE式を使用します。

平成30年秋期午後問題一覧

問1　問2　問3　問4　問5　問6　問7　問8　問9　問10　問11　問12　問13　採点講評