応用情報技術者 2014年 春期 午後 問06
旅客船 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に示す。
設問1：
図1中のabに入れる適切な属性名及びエンティティ間の関連を答え、E-R図を完成させよ。
なお、エンティティ間の関連及び属性名の表記は、図1の凡例に倣うこと。
模範解答
a：ー
b：運賃係数
解説
解答の論理構成
-
顧客と Webユーザの関連を決定
- 【問題文】の機能要件「Webユーザ管理」には、
「顧客が本システムにログインする際に使用するユーザIDとパスワードを管理する。ユーザIDはシステム内で一意である。」
とあるため、1人の顧客に対して登録できる Webユーザは1つだけ、逆に Webユーザは必ず1人の顧客に対応します。 - これは両者が “1対1” であることを示します。
- 図1の凡例では 1対1 を「──」で表記するので、a には「──」を入れます。
- 【問題文】の機能要件「Webユーザ管理」には、
-
座席クラスに追加すべき属性を決定
- 【問題文】の「座席管理」には、
「座席にはファーストクラスやエコノミークラスなどの分類があり、その運賃はクラスに応じて設定された運賃係数を基本運賃に乗じた額になる。」
と記述されています。 - ここで言及されている “運賃係数” は、クラスごとに一意に決まる定性的な値であり、エンティティ「座席クラス」が保持すべき属性です。
- よって b には「運賃係数」を入れます。
- 【問題文】の「座席管理」には、
以上より、解答は
a：「──」 b：「運賃係数」
となります。
a：「──」 b：「運賃係数」
となります。
誤りやすいポイント
- 「顧客」と「Webユーザ」を 1対多 と誤認する
“旅行代理店も本システムを利用する” という文脈から「代理店用に複数アカウントがあるのでは」と考えてしまうケースがありますが、問題文はあくまで「顧客」の観点で記述しており、代理店も“顧客として”登録される点に注意が必要です。 - “運賃係数” を「座席」側に配置してしまう
運賃計算はクラスに依存するので「座席クラス」の属性です。“座席” に持たせると同一クラスの席で冗長に繰り返し格納され、正規化の観点からも不適切になります。
FAQ
Q: 1対1 の関連を「＞」や「→」で書くと減点になりますか？
A: 凡例で「──：1対1」と明示されているため、別の記号を使うと不適合となります。必ず凡例に合わせてください。
A: 凡例で「──：1対1」と明示されているため、別の記号を使うと不適合となります。必ず凡例に合わせてください。
Q: “運賃係数” のデータ型は数値ですか？
A: 運賃計算で乗算に使うため数値型（整数または実数）が適切です。問題では型までは問われていませんが、論理設計段階で意識しておくと良いでしょう。
A: 運賃計算で乗算に使うため数値型（整数または実数）が適切です。問題では型までは問われていませんが、論理設計段階で意識しておくと良いでしょう。
Q: 顧客がアカウントを削除した場合、Webユーザ行はどう扱いますか？
A: 1対1 のため、顧客行を削除すると同時に Webユーザ行も削除（カスケード）する設計が一般的です。論理設計段階で参照整合性を定義しておくと実装が容易になります。
A: 1対1 のため、顧客行を削除すると同時に Webユーザ行も削除（カスケード）する設計が一般的です。論理設計段階で参照整合性を定義しておくと実装が容易になります。
関連キーワード: 1対1, 外部キー, 正規化, 属性設計, 運賃計算
設問2：
図2中のc、dに入れる適切な字句又は式を答えよ（cとdは順不同）。
模範解答
c：ユーザID = :ユーザID
d：パスワードハッシュ値 = HASH(:パスワード)
解説
解答の論理構成
- 【問題文】には、パスワードの保存方法として
“Webユーザのパスワード漏えいを防ぐために、パスワードそのものは本システムには保存せずに、そのハッシュ値を保存して利用する。”
とあります。したがって比較対象は“パスワードハッシュ値”列です。 - 同じ段落で
“ハッシュ値の計算には関数 HASH を利用する。”
と明記されており、ユーザが入力した “:パスワード” は HASH(:パスワード) に変換して照合する必要があります。 - 図2の SQL では WHERE 句中に c と d があり、両方の条件を満たす行が 1 行あれば COUNT(*) が 1 になり、そうでなければ 0 になります。したがって
・ユーザを特定する条件
・ハッシュ化されたパスワードを一致させる条件
の 2 つを書けば要求仕様（正しい場合は 1、誤りの場合は 0）を満たします。 - Webユーザ表の属性は図1補足で
“Webユーザ- 顧客番号 (PK・FK → 顧客.顧客番号)
- ユーザID
- パスワードハッシュ値”
と定義されているため、列名はそのまま利用します。
- 以上より c と d に入る句は次のとおりです。
- c：ユーザID = :ユーザID
- d：パスワードハッシュ値 = HASH(:パスワード)
順序は問われていないため、どちらが c/d でも正解です。
誤りやすいポイント
- “:パスワード” をそのまま比較してしまい、ハッシュ化を忘れる。
- HASH(パスワードハッシュ値) と列側を再ハッシュする誤記。
- COUNT(列名) を使い 0 行でも 1 を返してしまうミス。問題の SQL は COUNT(*) で行数を数えています。
- AND でなく OR を入れてしまい、不正なパスワードでも 1 が返る誤実装。
FAQ
Q: ユーザID が重複するケースは考慮しなくてよいのですか？
A: 【表1】Webユーザ管理の説明に“ユーザIDはシステム内で一意である。”とあるので重複は発生しません。
A: 【表1】Webユーザ管理の説明に“ユーザIDはシステム内で一意である。”とあるので重複は発生しません。
Q: ハッシュ関数が衝突した場合はどう判定されますか？
A: 本設問はあくまで SQL 文の構造を問うものです。実運用では強固なハッシュ関数を選定し、ソルトなど追加対策を講じますが、試験の想定外です。
A: 本設問はあくまで SQL 文の構造を問うものです。実運用では強固なハッシュ関数を選定し、ソルトなど追加対策を講じますが、試験の想定外です。
Q: COUNT(*) が 0 または 1 以外になることはありますか？
A: ユーザID が一意なので取得行数は 0 行（不一致）か 1 行（完全一致）のみです。
A: ユーザID が一意なので取得行数は 0 行（不一致）か 1 行（完全一致）のみです。
関連キーワード: ハッシュ関数, 認証, SQL, WHERE句, COUNT 함수
設問3：
図3中のe〜hに入れる適切な字句又は式を答えよ。
模範解答
e：COUNT(*)
f：座席クラス
g：B.座席クラス番号 = C.座席クラス番号
h：A.船便番号, A.船便名, C.座席クラス番号, C.座席クラス名
解説
解答の論理構成
-
集計列の決定
【問題文】には「座席のクラスごとの空席数を照会する」
とあり、さらに
「座席表の列 “空席状況” の値が '0' のとき、その座席を空きとする。」
と記載されています。空席を数えるだけなので 行数を数える集計関数が必要です。よって
sql COUNT(*)を指定し、e には COUNT(*) が入ります。 -
結合対象表の選択
クラス名などを取得するには、座席クラスを管理する表を結合する必要があります。図3の FROM 句には
sql INNER JOIN 座席 B ...
INNER JOIN f C ...とあり、座席クラス表を別名 C で結合していると考えられます。したがって f には 座席クラス が入ります。 -
結合条件の決定
座席 B と座席クラス C の関係は、座席の列「座席クラス番号」で結び付けます。したがって
sql B.座席クラス番号 = C.座席クラス番号が g です。 -
GROUP BY 句の決定
SELECT 句に集計対象でない列（船便番号・船便名・座席クラス番号・座席クラス名）が並んでいるため、これらすべてを GROUP BY に列挙する必要があります。よって h にはA.船便番号, A.船便名, C.座席クラス番号, C.座席クラス名が入ります。 -
以上より、解答は【模範解答】のとおり
- e：COUNT(*)
- f：座席クラス
- g：B.座席クラス番号 = C.座席クラス番号
- h：A.船便番号, A.船便名, C.座席クラス番号, C.座席クラス名
誤りやすいポイント
- COUNT(B.座席番号) と書いてしまう
― null 行が混入する可能性を誤って考慮し、不要に列名を付けるケースがあります。 - 結合タイプを LEFT JOIN と想定する
― 本要件では「空席がある座席クラスのみ」を抽出するので INNER JOIN が適切です。 - GROUP BY に C.座席クラス名 を入れ忘れる
― 集計列以外はすべて GROUP BY へ入れる原則を失念しがちです。 - WHERE 句に C.座席クラス番号 IS NOT NULL を書き足す
― 既に INNER JOIN で絞り込まれているため冗長です。
FAQ
Q: COUNT() と COUNT(1) はどちらを使っても良いのですか？
A: 多くの RDBMS では同義ですが、問題文には具体的に列を数える指示がなく、「行数」を求めたいので COUNT() を選ぶのが確実です。
A: 多くの RDBMS では同義ですが、問題文には具体的に列を数える指示がなく、「行数」を求めたいので COUNT() を選ぶのが確実です。
Q: もし空席状況が '0' 以外にも「清掃中」などの値を持つ場合、どう対処しますか？
A: 要件に基づき 空席を示す値だけ を WHERE 句で絞り込むのが原則です。複数値が空席扱いなら IN (...) を用います。
A: 要件に基づき 空席を示す値だけ を WHERE 句で絞り込むのが原則です。複数値が空席扱いなら IN (...) を用います。
Q: GROUP BY に列を増やすほど性能は落ちますか？
A: 列数が多いほどハッシュやソート対象が増えるためコストは上がります。ただし正しい結果が最優先であり、必要な列を省くことは出来ません。性能が問題になる場合はインデックスやサマリ表を検討します。
A: 列数が多いほどハッシュやソート対象が増えるためコストは上がります。ただし正しい結果が最優先であり、必要な列を省くことは出来ません。性能が問題になる場合はインデックスやサマリ表を検討します。
関連キーワード: 集約関数, INNER JOIN, GROUP BY, ハッシュ値, 予約システム
設問4：
〔操作ログ記録機能の不具合〕における不具合を修正するに当たり、予約受付処理が失敗した際にも、操作ログを操作ログ表に記録するために実施すべき、予約受付処理の流れに対する対応策を40字以内で述べよ。
模範解答
操作ログ表への記録を予約受付処理とは別のトランザクションにする。
解説
解答の論理構成
- 予約受付処理では、はじめにトランザクションを開始してから全処理を 1 つのトランザクションにまとめています。
- 本来は失敗シナリオでも操作ログを残す設計ですが、運用テストで「予約受付処理が失敗した場合は、処理の開始から失敗までに実行された SQL 文とその結果が記録されるべきだが、操作ログ表には何も記録されなかった。」という事象が発生しました。
- ログ挿入が同一トランザクション内で実行されると、主処理がロールバックされた際にログ挿入も巻き戻され、結果として “何も記録されない” 状態になります。
- したがって、ログ挿入だけはロールバックの影響を受けないようにする必要があります。
- これを最小の改修で実現する手段が「操作ログ表への記録を予約受付処理とは別のトランザクションにする」です。ログ用トランザクションを独立させれば、主処理がロールバックしてもログのコミットは確定したまま残ります。
誤りやすいポイント
- トランザクション境界をプログラムの関数単位でしか考えず、ログも一括で管理しようとしてしまう。
- “ログは読み取り専用だからロールバック対象にならない” と誤解し、INSERT もロールバックで消えることを見落とす。
- エラー発生時のみ別トランザクションにしようとして複雑化し、正常系との整合が取れなくなる。
FAQ
Q: 予約受付処理の途中で障害が起きたときにログが残らない理由は何ですか？
A: ログ挿入が同じトランザクションに含まれているため、主処理と一緒に ROLLBACK されるからです。
A: ログ挿入が同じトランザクションに含まれているため、主処理と一緒に ROLLBACK されるからです。
Q: ログ専用トランザクションにすると整合性が崩れませんか？
A: ログは「事実の記録」であり業務データとは独立しています。ロールバックの影響を避けることで、むしろ障害解析や追跡が容易になります。
A: ログは「事実の記録」であり業務データとは独立しています。ロールバックの影響を避けることで、むしろ障害解析や追跡が容易になります。
Q: 別トランザクションにする以外の方法はありますか？
A: データベースの自動コミットや非同期ログテーブルを使う方法もありますが、設問の趣旨は“確実に残す”ことなので独立トランザクションが最も直接的です。
A: データベースの自動コミットや非同期ログテーブルを使う方法もありますが、設問の趣旨は“確実に残す”ことなので独立トランザクションが最も直接的です。
関連キーワード: トランザクション管理, ロールバック, コミット, ロギング
