総視聴再生時間43万分以上(2017年5月13日現在)の動画で基本情報技術者試験の過去問&キーワード解説!スキマ時間に動画!〜これじょIT〜

PR広告

平成30年度秋 基本情報技術者試験午後 問3 データベース|合格率アップ!動画解説!

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

 D社は、Web上で会員制のコンサートチケット販売サイトを運営している。販売サイトのシステムは販売サブシステムと席予約サブシステムから構成され、販売サブシステムで購入申込み及び決済を処理し、席予約サブシステムで座席指定を処理する。本問では、販売サブシステムだけを取り扱う。

 販売サブシステムで利用しているデータベースの表構成とデータの格納例を図1に示す。下線付きの項目は主キーである。

会員表

会員ID 氏名 電子メールアドレス
K00001 情報太郎 taro@example.com
... ... ...

商品表

コンサートID コンサート情報 開催日時
C00001 クリスマスコンサート2018 in 東京 出演: Xバンド... 2018-12-24 18:00:00
... ... ...

商品詳細表

コンサートID 席種 価格 発売席数
C00001 S 6000 500
... ... ... ...

決済表

販売ID 決済日 決済額
H000001 2018-09-29 12000
... ... ...

商品表

販売ID 会員ID コンサートID 席種 席数 販売日 販売額 決済期限日
H000001 K00001 C00001 S 2 2018-09-02 12000 2018-10-01
... ... ... ... ... ... ... ...

図1 販売サブシステムで利用しているデータベースの標高生徒データの格納例

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

(1) コンサートの席種には、S、A及びBがある。

(2) 各席種の価格(常に有料)及び発売席数は、コンサートごとに異なる。

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

(1) 販売サブシステムは取り扱うコンサートの席種ごとの販売可能な席数を管理する。

(2) 会員が購入申込みを行うと、販売サブシステムは一意な販売IDを生成して販売表にレコードを追加する。

(3) 会員が支払手続を行うと、決済処理として販売サブシステムは販売IDを主キーとするレコードを決済表に追加する。ここで、決済日はレコードを追加した日とする。

(4) 販売サブシステムは決済期限日の翌日に、決済期限日を過ぎた販売表中のレコードと販売IDが同じレコードが決済表にない場合、その購入申込みは取り消されたものとして、バッチ処理によって決済表に当該販売IDを主キーとするレコードを追加する。このレコードの決済日はNULLで、決済額は-1とする。

(5) バッチ処理は、毎夜0~4時の販売サイトのシステムのメンテナンス時間帯に行う。

(6) 会員が購入を申し込んだ席数が、その時点で販売可能な席数を上回る場合には、販売サブシステムは"販売終了"と表示し、この購入申込みを受け付けない。

設問1

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

解答群

表名.列名 制約
決済表.決済額 検査制約
決済表.決済日 非NULL制約
商品詳細表.席種 参照制約
販売表.会員ID 一意性制約

設問2

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

SELECT 
  SUM(販売表.席数)
FROM 販売表 a1 決済表 ON 販売表.販売ID = 決済表.販売ID 
WHERE 販売表.コンサートID = 'C00001'
AND   販売表.席種 = 'S'
AND   a2

a に関する解答群

a1 a2
INNER JOIN 決済表.決済額 = -1
INNER JOIN 決済表.決済額 >= 0
LEFT OUTER JOIN (決済表.決済額 IS NULL OR 決済表.決済額 = -1)
LEFT OUTER JOIN (決済表.決済額 IS NULL OR 決済表.決済額 >= 0)
RIGHT OUTER JOIN (決済表.決済額 IS NULL OR 決済表.決済額 = -1)
RIGHT OUTER JOIN (決済表.決済額 IS NULL OR 決済表.決済額 >= 0)

設問3

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

SELECT 会員表.氏名, 会員表.電子メールアドレス, 販売表.販売ID
   b

b に関する解答群

FROM 会員表, 販売表
WHERE DATEDIFF(販売表.決済期限日, NOW()) = 3
AND   販売表.会員ID = 会員表.会員ID
AND   販売表.販売ID NOT IN (SELECT 販売ID FROM 決済表)

FROM 会員表, 販売表
WHERE DATEDIFF(販売表.決済期限日, NOW()) = 3
AND   販売表.会員ID = 会員表.会員ID
AND   販売表.販売ID IN (SELECT 販売ID FROM 決済表 WHERE >= 0)

FROM 会員表, 販売表, 決済表
WHERE DATEDIFF(販売表.決済期限日, NOW()) = 3
AND   販売表.会員ID = 会員表.会員ID
AND   販売表.販売ID = 決済表.販売ID

FROM 会員表, 販売表, 決済表
WHERE DATEDIFF(販売表.決済期限日, NOW()) = 3
AND   販売表.会員ID = 会員表.会員ID
AND   販売表.販売ID = 決済表.販売ID
AND   決済表.決済額 <> -1

設問4

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

会員表

会員ID 氏名 電子メールアドレス ポイント残高

決済表

販売ID 決済日 決済額 付与ポイント

販売表

販売ID 会員ID コンサートID 席種 席数 販売日 販売額 決済期限日 使用ポイント

図2 修正した会員表、決済表及び販売表の表構成

 会員は購入申込み時に、1ポイント1円としてポイント残高の範囲で、販売額に充当するポイント数を指定する。販売管理システムは、指定したポイント数を使用ポイントに格納し、ポイント残高から減じる。会員は、販売額から使用ポイントを差し引いた金額を決済額として支払う。販売額の全額にポイントを充当した場合は、販売サブシステムは購入申込み時に支払手続が行われたものとし、決済処理として、決済表にレコードを追加する。

 ポイント制度の導入時に追加したバッチ処理によって、前日に決済処理された販売IDごとに、その決済額が20,000円以上、10,000円以上20,000円未満、10,000円未満の場合に、それぞれ3%、2%、1%のポイントを付与する。付与したポイント数は、付与ポイントに格納し、ポイント残高に加える。

 決済表の付与ポイントを更新する正しいSQL文を、解答群の中から選べ。NOW、DATEDIFF は設問3で使用したユーザ定義関数と同じであり、FLOOR は引数の値以下で最大の整数値を返す関数である。

解答群

INSERT INTO 決済表( 付与ポイント )
  SELECT 
     IF     決済額 >= 20000 THEN FLOOR(決済額 * 0.03)
     ELSEIF 決済額 >= 10000 THEN FLOOR(決済額 * 0.02)
     ELSE                       FLOOR(決済額 * 0.01) 
     END
  WHERE DATEDIFF(NOW(), 決済日) = 1

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

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

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

設問1 解説

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

解答群

表名.列名 制約
決済表.決済額 検査制約
決済表.決済日 非NULL制約
商品詳細表.席種 参照制約
販売表.会員ID 一意性制約

問題文の〔コンサートの席の説明〕を基にE-R図にすると...

(1) コンサートの席種には、S、A及びBがある。

(2) 各席種の価格(常に有料)及び発売席数は、コンサートごとに異なる。

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

(1) 販売サブシステムは取り扱うコンサートの席種ごとの販売可能な席数を管理する。

(2) 会員が購入申込みを行うと、販売サブシステムは一意な販売IDを生成して販売表にレコードを追加する。

(3) 会員が支払手続を行うと、決済処理として販売サブシステムは販売IDを主キーとするレコードを決済表に追加する。ここで、決済日はレコードを追加した日とする。

(4) 販売サブシステムは決済期限日の翌日に、決済期限日を過ぎた販売表中のレコードと販売IDが同じレコードが決済表にない場合、その購入申込みは取り消されたものとして、バッチ処理によって決済表に当該販売IDを主キーとするレコードを追加する。このレコードの決済日はNULLで、決済額は-1とする。

(5) バッチ処理は、毎夜0~4時の販売サイトのシステムのメンテナンス時間帯に行う。

(6) 会員が購入を申し込んだ席数が、その時点で販売可能な席数を上回る場合には、販売サブシステムは"販売終了"と表示し、この購入申込みを受け付けない。

平成30年度秋 基本情報技術者試験午後 過去問3 データベース

設問2 解説

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

SELECT 
  SUM(販売表.席数)
FROM 販売表 a1 決済表 ON 販売表.販売ID = 決済表.販売ID 
WHERE 販売表.コンサートID = 'C00001'
AND   販売表.席種 = 'S'
AND   a2

a に関する解答群

a1 a2
INNER JOIN 決済表.決済額 = -1
INNER JOIN 決済表.決済額 >= 0
LEFT OUTER JOIN (決済表.決済額 IS NULL OR 決済表.決済額 = -1)
LEFT OUTER JOIN (決済表.決済額 IS NULL OR 決済表.決済額 >= 0)
RIGHT OUTER JOIN (決済表.決済額 IS NULL OR 決済表.決済額 = -1)
RIGHT OUTER JOIN (決済表.決済額 IS NULL OR 決済表.決済額 >= 0)

平成30年度秋 基本情報技術者試験午後 過去問3 データベース

設問3 解説

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

SELECT 会員表.氏名, 会員表.電子メールアドレス, 販売表.販売ID
   b

b に関する解答群

FROM 会員表, 販売表
WHERE DATEDIFF(販売表.決済期限日, NOW()) = 3
AND   販売表.会員ID = 会員表.会員ID
AND   販売表.販売ID NOT IN (SELECT 販売ID FROM 決済表)

FROM 会員表, 販売表
WHERE DATEDIFF(販売表.決済期限日, NOW()) = 3
AND   販売表.会員ID = 会員表.会員ID
AND   販売表.販売ID IN (SELECT 販売ID FROM 決済表 WHERE >= 0)

FROM 会員表, 販売表, 決済表
WHERE DATEDIFF(販売表.決済期限日, NOW()) = 3
AND   販売表.会員ID = 会員表.会員ID
AND   販売表.販売ID = 決済表.販売ID

FROM 会員表, 販売表, 決済表
WHERE DATEDIFF(販売表.決済期限日, NOW()) = 3
AND   販売表.会員ID = 会員表.会員ID
AND   販売表.販売ID = 決済表.販売ID
AND   決済表.決済額 <> -1

平成30年度秋 基本情報技術者試験午後 過去問3 データベース

設問4 解説

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

会員表

会員ID 氏名 電子メールアドレス ポイント残高

決済表

販売ID 決済日 決済額 付与ポイント

販売表

販売ID 会員ID コンサートID 席種 席数 販売日 販売額 決済期限日 使用ポイント

図2 修正した会員表、決済表及び販売表の表構成

 会員は購入申込み時に、1ポイント1円としてポイント残高の範囲で、販売額に充当するポイント数を指定する。販売管理システムは、指定したポイント数を使用ポイントに格納し、ポイント残高から減じる。会員は、販売額から使用ポイントを差し引いた金額を決済額として支払う。販売額の全額にポイントを充当した場合は、販売サブシステムは購入申込み時に支払手続が行われたものとし、決済処理として、決済表にレコードを追加する。

 ポイント制度の導入時に追加したバッチ処理によって、前日に決済処理された販売IDごとに、その決済額が20,000円以上、10,000円以上20,000円未満、10,000円未満の場合に、それぞれ3%、2%、1%のポイントを付与する。付与したポイント数は、付与ポイントに格納し、ポイント残高に加える。

 決済表の付与ポイントを更新する正しいSQL文を、解答群の中から選べ。NOW、DATEDIFF は設問3で使用したユーザ定義関数と同じであり、FLOOR は引数の値以下で最大の整数値を返す関数である。

解答群

INSERT INTO 決済表( 付与ポイント )
  SELECT 
     IF     決済額 >= 20000 THEN FLOOR(決済額 * 0.03)
     ELSEIF 決済額 >= 10000 THEN FLOOR(決済額 * 0.02)
     ELSE                       FLOOR(決済額 * 0.01) 
     END
  WHERE DATEDIFF(NOW(), 決済日) = 1

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

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

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

平成30年度秋 基本情報技術者試験午後 過去問3 データベース

PR広告

フェイスブックコメント

平成28年度秋 基本情報技術者試験 午後 テキスト・動画解説

平成28年度秋 基本情報技術者試験 午前 テキスト・動画解説

平成28年度春 基本情報技術者試験 午後 テキスト・動画解説

平成28年度春 基本情報技術者試験 午前 テキスト・動画解説

平成27年度秋 基本情報技術者試験 午後 テキスト・動画解説

平成27年度春 基本情報技術者試験 午後 テキスト・動画解説

平成27年度春 基本情報技術者試験 午前 テキスト・動画解説

平成26年度秋 基本情報技術者試験 午前 テキスト・動画解説

平成26年度春 基本情報技術者試験 午前 テキスト・動画解説