応用情報技術者過去問題 平成26年春期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
旅客船Web予約システムの構築に関する次の記述を読んで，設問1～4に答えよ。
R社は，これまで東京湾内で旅客船を運航してきた。旅客船の性能向上に伴い，東京湾と四国地方や九州地方の港を直接結ぶ中長距離航路に参入することになった。これまで乗船券の販売はR社の窓口と旅行代理店で扱っていたが，これを機に，乗船する顧客自身もインターネットから空席照会や予約ができるシステム(以下，本システムという)を構築する。システム運用開始後は旅行代理店も本システムを利用する。本システムの機能要件を表1に，E-R図を図1に示す。
なお，本システムでは，E-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
〔Webユーザ管理機能の実装〕
Webユーザのパスワード漏えいを防くために，パスワードそのものは本システムには保存せずに，そのハッシュ値を保存して利用する。システムへのログインの際，ユーザが入力したパスワードのハッシュ値と，保存されているハッシュ値が等しければ正しいパスワードが入力されたと判断する。
なお，ハッシュ値の計算には関数 HASH を利用する。例えば，文字列'いろは'のハッシュ値を求める場合，HASH('いろは')と記述する。
あるWebユーザがシステムにログイン可能かどうかを判定するために，正しいパスワードが入力された場合は1を，誤りの場合は0を返すSQL文を図2に示す。ここで，":ユーザID"は入力されたユーザIDを，":パスワード"は入力されたパスワードをそれぞれ格納した埋込み変数である。
〔空席照会機能の実装〕
空席照会機能において，指定した条件に合った船便とその座席のクラスごとの空席数を照会するSQL文を図3に示す。ここで，":出発日"，":出発地"，":到着地"は空席照会の条件を格納した埋込み変数である。また，座席表の列"空席状況"の値が'0'のとき，その座席を空きとする。
〔操作ログ記録機能の不具合〕
運用テストフェーズにおいて，予約受付処理が失敗するシナリオで不具合が発見された。予約受付処理が成功した場合は，処理の開始から完了までに実行されたSQL文とその結果が操作ログ表に記録された。予約受付処理が失敗した場合は，処理の開始から失敗までに実行されたSQL文とその結果が記録されるべきだが，操作ログ表には何も記録されなかった。予約受付処理の流れを図4に示す。
R社は，これまで東京湾内で旅客船を運航してきた。旅客船の性能向上に伴い，東京湾と四国地方や九州地方の港を直接結ぶ中長距離航路に参入することになった。これまで乗船券の販売はR社の窓口と旅行代理店で扱っていたが，これを機に，乗船する顧客自身もインターネットから空席照会や予約ができるシステム(以下，本システムという)を構築する。システム運用開始後は旅行代理店も本システムを利用する。本システムの機能要件を表1に，E-R図を図1に示す。
なお，本システムでは，E-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
Webユーザのパスワード漏えいを防くために，パスワードそのものは本システムには保存せずに，そのハッシュ値を保存して利用する。システムへのログインの際，ユーザが入力したパスワードのハッシュ値と，保存されているハッシュ値が等しければ正しいパスワードが入力されたと判断する。
なお，ハッシュ値の計算には関数 HASH を利用する。例えば，文字列'いろは'のハッシュ値を求める場合，HASH('いろは')と記述する。
あるWebユーザがシステムにログイン可能かどうかを判定するために，正しいパスワードが入力された場合は1を，誤りの場合は0を返すSQL文を図2に示す。ここで，":ユーザID"は入力されたユーザIDを，":パスワード"は入力されたパスワードをそれぞれ格納した埋込み変数である。
空席照会機能において，指定した条件に合った船便とその座席のクラスごとの空席数を照会するSQL文を図3に示す。ここで，":出発日"，":出発地"，":到着地"は空席照会の条件を格納した埋込み変数である。また，座席表の列"空席状況"の値が'0'のとき，その座席を空きとする。
運用テストフェーズにおいて，予約受付処理が失敗するシナリオで不具合が発見された。予約受付処理が成功した場合は，処理の開始から完了までに実行されたSQL文とその結果が操作ログ表に記録された。予約受付処理が失敗した場合は，処理の開始から失敗までに実行されたSQL文とその結果が記録されるべきだが，操作ログ表には何も記録されなかった。予約受付処理の流れを図4に示す。
設問1
図1中のa，bに入れる適切な属性名及びエンティティ間の関連を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
解答入力欄
- a：
- b：
解答例・解答の要点
- a：―
- b：運賃係数
解説
〔aについて〕
顧客エンティティとWebユーザエンティティには共通の属性「顧客番号」があります。この属性「顧客番号」は、顧客エンティティ側では主キーであり、顧客を一意に特定します。また、Webユーザエンティティ側でも主キーであり、Webユーザを一意に特定します。どちらののエンティティもある顧客番号につき一意のエンティティですから、同一の顧客番号をもつ複数のレコードが存在することはありません。したがって、顧客エンティティとWebユーザエンティティのカーディナリティは1対1であり、[a]には「―」が当てはまります。
∴a＝―
〔bについて〕
表1の「座席管理」の機能概要には、「座席にはファーストクラスやエコノミークラスなどの分類があり，その運賃はクラスに応じて設定された運賃係数を基本運賃に乗じた額になる。」とあります。座席クラスごとに運賃係数を保持する必要がありますが、座席クラスには運賃係数を表す属性がありません。したがって、[b]には「運賃係数」が当てはまると判断できます。
∴b＝運賃係数
顧客エンティティとWebユーザエンティティには共通の属性「顧客番号」があります。この属性「顧客番号」は、顧客エンティティ側では主キーであり、顧客を一意に特定します。また、Webユーザエンティティ側でも主キーであり、Webユーザを一意に特定します。どちらののエンティティもある顧客番号につき一意のエンティティですから、同一の顧客番号をもつ複数のレコードが存在することはありません。したがって、顧客エンティティとWebユーザエンティティのカーディナリティは1対1であり、[a]には「―」が当てはまります。
∴a＝―
〔bについて〕
表1の「座席管理」の機能概要には、「座席にはファーストクラスやエコノミークラスなどの分類があり，その運賃はクラスに応じて設定された運賃係数を基本運賃に乗じた額になる。」とあります。座席クラスごとに運賃係数を保持する必要がありますが、座席クラスには運賃係数を表す属性がありません。したがって、[b]には「運賃係数」が当てはまると判断できます。
∴b＝運賃係数
設問2
図2中のc，dに入れる適切な字句又は式を答えよ。
解答入力欄
- c：
- d：
解答例・解答の要点
- c：ユーザID = :ユーザID
- d：パスワードハッシュ値 = HASH(:パスワード)
解説
〔c，dについて〕
図2のSQL文のSELECT句のCOUNT(*)は、ユーザーがシステムにログイン可能時に1を、そうでないときに0を返します。本システムのユーザ認証では、入力されたユーザIDとパスワードの組が正しいときに認証をパスさせるので、WHERE句にはこの2点を満たすレコードが存在するかどうかを確認するための条件式が入ります。
まず、ユーザIDについては「ユーザID」列の中から、値が入力されたユーザID（:ユーザID）であるレコードを選択することになります。よって、一方には「ユーザID = :ユーザID」が入ります。
パスワードについてはハッシュ化済みの値がDB内に保存されているので、単純に比較するのではなく、ユーザが入力したパスワードのハッシュ値と、保存されているハッシュ値が等しければ正しいパスワードが入力されたと判断することになります。
図1のE-R図を見ると、パスワードのハッシュ値が保存されている列は「パスワードハッシュ値」、入力されたパスワードのハッシュ値は関数 HASH を使用した「HASH(:パスワード)」ですから、「パスワードハッシュ値」列の中から、値が「HASH(:パスワード)」であるレコードを選択することになります。
∴c＝ユーザID = :ユーザID
d＝パスワードハッシュ値 = HASH(:パスワード)
（順不同）
※表1の機能名「Webユーザ管理」の機能概要には「ユーザIDはシステム内で一意である。」とあり、複数の顧客が同一のユーザIDをもつことはありません。よって、WHERE句の条件を満たすレコードは高々1つしかないことがわかります。
図2のSQL文のSELECT句のCOUNT(*)は、ユーザーがシステムにログイン可能時に1を、そうでないときに0を返します。本システムのユーザ認証では、入力されたユーザIDとパスワードの組が正しいときに認証をパスさせるので、WHERE句にはこの2点を満たすレコードが存在するかどうかを確認するための条件式が入ります。
まず、ユーザIDについては「ユーザID」列の中から、値が入力されたユーザID（:ユーザID）であるレコードを選択することになります。よって、一方には「ユーザID = :ユーザID」が入ります。
パスワードについてはハッシュ化済みの値がDB内に保存されているので、単純に比較するのではなく、ユーザが入力したパスワードのハッシュ値と、保存されているハッシュ値が等しければ正しいパスワードが入力されたと判断することになります。
∴c＝ユーザID = :ユーザID
d＝パスワードハッシュ値 = HASH(:パスワード)
（順不同）
※表1の機能名「Webユーザ管理」の機能概要には「ユーザIDはシステム内で一意である。」とあり、複数の顧客が同一のユーザIDをもつことはありません。よって、WHERE句の条件を満たすレコードは高々1つしかないことがわかります。
設問3
図3中のe～hに入れる適切な字句又は式を答えよ。
解答入力欄
- e：
- f：
- g：
- h：
解答例・解答の要点
- e：COUNT(*)
- f：座席クラス
- g：B.座席クラス番号 = C.座席クラス番号
- h：A.船便番号，A.船便名，C.座席クラス番号，C.座席クラス名
解説
〔eについて〕
〔空席照会機能の実装〕には「":出発日"，":出発地"，":到着地"は空席照会の条件を格納した埋込み変数である。また，座席表の列"空席状況"の値が'0'のとき，その座席を空きとする。」とあり、WHERE句ではこの4つの条件を満たすレコードを選択していることがわかります。このWHERE句の条件に合致するレコード数がそのまま指定条件に合致する空席数となるので、[e]には結果表のレコード数を集計する「COUNT(*)」が当てはまります。
∴e＝COUNT(*)
〔fについて〕
[f]はINNER JOIN句の直後であることから表名が当てはまります。
[f]には"C"という別名が付けられており、SELECT句には「C.座席クラス番号, C.座席クラス名」と記述されているので、[f]に入るのは少なくともこの2つの属性を持っている表ということになります。図1のE-R図から「座席クラス番号」「座席クラス名」という属性を両方とも持つエンティティ（表）を探すと、座席クラス表のみであることがわかります。したがって、[f]には「座席クラス」が当てはまります。
∴f＝座席クラス
〔gについて〕
[g]はON句の直後であることから、座席クラス（C）列に関する結合条件が当てはまります。図1のE-R図より（SQL文内で登場している）船便表（A）と座席表（B）のうち、座席クラス表と関係があるのは座席表であることがわかります。よって、座席クラス表と座席クラス表に共通する属性「座席クラス番号」で結合が行われます。したがって、[g]には「B.座席クラス番号 = C.座席クラス番号」が当てはまります。
∴g＝B.座席クラス番号 = C.座席クラス番号
〔hについて〕
GROUP BY句を用いた場合、SELECT句に記述できるのはGROUP BY句で指定した列および集約関数のみに限られるという制約があります。よって、SELECT句で指定されている4つの列をグループ化対象として指定する必要があります。
∴A.船便番号，A.船便名，C.座席クラス番号，C.座席クラス名
※SQLの規格であるSQL99からは、GROUP BY句で指定した列から一意に特定できる列（この問題だと船便番号→船便名、座席クラス番号→座席クラス名）はGROUP BY句で指定しなくてもSELECT句に記述することができるようになっています。したがって、「A.船便番号, C.座席クラス番号」でも正解です。
〔空席照会機能の実装〕には「":出発日"，":出発地"，":到着地"は空席照会の条件を格納した埋込み変数である。また，座席表の列"空席状況"の値が'0'のとき，その座席を空きとする。」とあり、WHERE句ではこの4つの条件を満たすレコードを選択していることがわかります。このWHERE句の条件に合致するレコード数がそのまま指定条件に合致する空席数となるので、[e]には結果表のレコード数を集計する「COUNT(*)」が当てはまります。
∴e＝COUNT(*)
〔fについて〕
[f]はINNER JOIN句の直後であることから表名が当てはまります。
[f]には"C"という別名が付けられており、SELECT句には「C.座席クラス番号, C.座席クラス名」と記述されているので、[f]に入るのは少なくともこの2つの属性を持っている表ということになります。図1のE-R図から「座席クラス番号」「座席クラス名」という属性を両方とも持つエンティティ（表）を探すと、座席クラス表のみであることがわかります。したがって、[f]には「座席クラス」が当てはまります。
∴f＝座席クラス
〔gについて〕
[g]はON句の直後であることから、座席クラス（C）列に関する結合条件が当てはまります。図1のE-R図より（SQL文内で登場している）船便表（A）と座席表（B）のうち、座席クラス表と関係があるのは座席表であることがわかります。よって、座席クラス表と座席クラス表に共通する属性「座席クラス番号」で結合が行われます。したがって、[g]には「B.座席クラス番号 = C.座席クラス番号」が当てはまります。
∴g＝B.座席クラス番号 = C.座席クラス番号
〔hについて〕
GROUP BY句を用いた場合、SELECT句に記述できるのはGROUP BY句で指定した列および集約関数のみに限られるという制約があります。よって、SELECT句で指定されている4つの列をグループ化対象として指定する必要があります。
∴A.船便番号，A.船便名，C.座席クラス番号，C.座席クラス名
※SQLの規格であるSQL99からは、GROUP BY句で指定した列から一意に特定できる列（この問題だと船便番号→船便名、座席クラス番号→座席クラス名）はGROUP BY句で指定しなくてもSELECT句に記述することができるようになっています。したがって、「A.船便番号, C.座席クラス番号」でも正解です。
設問4
〔操作ログ記録機能の不具合〕における不具合を修正するに当たり，予約受付処理が失敗した際にも，操作ログを操作ログ表に記録するために実施すべき，予約受付処理の流れに対する対応策を40字以内で述べよ。
解答入力欄
解答例・解答の要点
- 操作ログ表への記録を予約受付処理とは別のトランザクションにする (31文字)
解説
図4を見ると、「トランザクションを開始」から「トランザクションをコミット／ロールバック」までに予約受付処理自体とその操作ログ表への記録がひとまとめになっていることがわかります。トランザクションには原子性という性質があり、一連の処理全部が正しく実行されたか、全く実行されなかったかのどちらかの状態で終了しなけばなりません。このため、予約受付処理が失敗した際のロールバックでは、予約表、予約明細表、座席表だけでなく、操作ログ表もトランザクション前の状態に戻ってしまいます。これが処理失敗時に操作ログ表のレコードが記録されなかった原因です。
予約受付処理が失敗した際にも、操作ログを操作ログ表に記録するためには、操作ログの記録処理を予約受付処理のトランザクションから分離しなければなりません。したがって、適切な対応策は「操作ログ表への記録を予約受付処理とは別のトランザクションにする」です。実装上はエラーハンドリングを行い、例外処理の中でロールバック処理＋操作ログの記録を行うことになります。
∴操作ログ表への記録を予約受付処理とは別のトランザクションにする
予約受付処理が失敗した際にも、操作ログを操作ログ表に記録するためには、操作ログの記録処理を予約受付処理のトランザクションから分離しなければなりません。したがって、適切な対応策は「操作ログ表への記録を予約受付処理とは別のトランザクションにする」です。実装上はエラーハンドリングを行い、例外処理の中でロールバック処理＋操作ログの記録を行うことになります。
∴操作ログ表への記録を予約受付処理とは別のトランザクションにする
