応用情報技術者 2012年 春期 午後 問06
複数の図書館の検索システムの統合に関する次の記述を読んで、設問1〜4に答えよ。
隣接するA市とB市は、半年後に合併を控えており、様々な情報システムの統合を計画している。両市が運営する図書館システムについて、統合を検討した結果、両図書館の貸出し可能な蔵書が確認できる統合検索サービスを実現することにした。その設計は、システム開発会社のC君が担当することとなった。
A市とB市の現在の図書館システムのテーブル構造を表1と表2に示す。表1と表2において、下線は主キーを表す。
C君が統合検索サービスの実現方式について、調査検討を行った結果を次に示す。
・両システムの蔵書テーブル中の“蔵書番号”には、共に12桁の数字が使われており、“蔵書A”テーブルと“蔵書B”テーブルの間で重複があった。
・両システムとも貸出記録テーブルの“返却日”の値は、貸出中はNULLだが、返却後は返却した日付を設定していた。
・両システムのテーブルを統合する際、既存のテーブル定義とデータを保持したまま、一つのスキーマ上に各テーブルを実装することにした。
・統合検索サービスを実現する際、①統合検索向けのテーブルを作成して夜間バッチ処理で両市図書館の情報をコピーする方法と、ビューを用いて両市図書館を直接参照する方法を比較し、ビューを用いて実現することにした。
C君が統合検索サービスを実現するために作成した“統合検索”ビューを図1に示す。
〔統合検索サービスの拡張〕
統合検索サービスの構築中に、市民からの強い要望があり、両市の図書館で貸出可能な蔵書の確認だけでなく、貸出予約できる機能を追加することになった。そこでC君が検討した結果、両システムの蔵書テーブルに“貸出状況”の列を追加した。追加後の蔵書テーブルを表2に示す。たとじ、“貸出状況”の列には“貸出中”、“貸出可”又は“予約済”(貸出中ではないが、予約されていて借りられない状態)のいずれかが格納されているものとする。貸出予約は、“貸出状況”の値が“貸出可”となっている蔵書を、“予約済”に変更する処理である。
蔵書テーブルの変更後、C君はビューを使って、貸出予約に対応した“統合貸出予約”ビューを図2のとおり作成した。“統合貸出予約”ビューは、“貸出状況”の値が“貸出可”となっている蔵書の一覧を表示するものである。
設問1：
図1と図2のSQL文中のa〜gに入れる適切な字句を答えよ。列名は、テーブル名を省略せずに、“テーブル名.列名”と記述すること。
模範解答
a：蔵書A.書籍番号 = 書籍A.書籍番号
b：貸出記録A.返却日 IS NULL
c：UNION 又は UNIONALL
d：蔵書B.ISBN番号 = 書籍B.ISBN番号
e：貸出記録B.返却日 IS NULL
f：蔵書A.貸出状況 = '貸出可'
g：蔵書B.貸出状況 = '貸出可'
解説
解答の論理構成
-
A 市側の結合条件（[a]）
問題文には「同じ本が複数冊ある場合、“書籍番号”は同じだが“蔵書番号”は1冊ごとに異なる。」とあります。
したがって “蔵書A” と “書籍A” を結び付けるキーは “書籍番号” であり、
sql 蔵書A.書籍番号 = 書籍A.書籍番号が適切です。 -
未返却判定（[b]、[e]）
両システムとも「貸出記録テーブルの“返却日”の値は、貸出中は NULL」と記述されています。
“返却日 IS NULL” が「現在貸出中」を示すため、
sql 貸出記録A.返却日 IS NULLと
sql 貸出記録B.返却日 IS NULLを使い、これらの蔵書番号を NOT IN で除外します。 -
2 市分の結果統合（[c]）
2 つの SELECT を 1 つのビューにまとめるためには集合演算子が必要です。重複を排除したい場合は UNION、単純結合でよければ UNION ALL を使います。いずれも文法的に正当なので
sql UNION -- または UNION ALLが解となります。 -
B 市側の結合条件（[d]）
表 2 から “蔵書B” は “ISBN番号” を保持し、書誌情報は “書籍B” に “ISBN番号” をキーとして格納されています。よって
sql 蔵書B.ISBN番号 = 書籍B.ISBN番号が適切です。 -
貸出可の蔵書だけを抽出（[f]、[g]）
追加仕様では「“貸出状況”の列には“貸出中”、“貸出可”又は“予約済”」が入り、「貸出予約は、“貸出状況”の値が“貸出可”となっている蔵書」を対象とすると明示されています。
よって
sql 蔵書A.貸出状況 = '貸出可'と
sql 蔵書B.貸出状況 = '貸出可'を条件に加えます。
以上より模範解答は
a：蔵書A.書籍番号 = 書籍A.書籍番号
b：貸出記録A.返却日 IS NULL
c：UNION 又は UNIONALL
d：蔵書B.ISBN番号 = 書籍B.ISBN番号
e：貸出記録B.返却日 IS NULL
f：蔵書A.貸出状況 = '貸出可'
g：蔵書B.貸出状況 = '貸出可'
b：貸出記録A.返却日 IS NULL
c：UNION 又は UNIONALL
d：蔵書B.ISBN番号 = 書籍B.ISBN番号
e：貸出記録B.返却日 IS NULL
f：蔵書A.貸出状況 = '貸出可'
g：蔵書B.貸出状況 = '貸出可'
誤りやすいポイント
- 結合キーの取り違え
A 市は “書籍番号”、B 市は “ISBN番号” がキーであることを混同しやすいです。 - NULL 判定の方向
「貸出中を除く」＝ NULL を含む行を除外するので、サブクエリ側で IS NULL、外側で NOT IN となる点に留意してください。 - UNION と UNION ALL の選択
両市が同一蔵書を所蔵している場合、重複有無の要件を読み飛ばしがちです。試験ではいずれも正解ですが、実務では要件に合わせて選択します。 - 文字列リテラルの全角／半角
'貸出可' を '貸出可能' と書き換えたり、シングルクォートを忘れたりすると実行時エラーになります。
FAQ
Q: NOT IN と LEFT JOIN … IS NULL ではどちらが速いですか？
A: インデックスや NULL 行数によって異なります。小規模なら差は僅少ですが、大量データでは外部結合＋IS NULL の方が最適化しやすい RDBMS もあります。チューニング時に実行計画を確認しましょう。
A: インデックスや NULL 行数によって異なります。小規模なら差は僅少ですが、大量データでは外部結合＋IS NULL の方が最適化しやすい RDBMS もあります。チューニング時に実行計画を確認しましょう。
Q: UNION ALL を使うと貸出可能冊数の合計はどうなりますか？
A: 各 SELECT ブロックで GROUP BY して冊数を数えているため、UNION ALL でも重複行は生じません。重複書誌が存在しても図書館名が異なるため論理的に別行となります。
A: 各 SELECT ブロックで GROUP BY して冊数を数えているため、UNION ALL でも重複行は生じません。重複書誌が存在しても図書館名が異なるため論理的に別行となります。
Q: 予約済みをキャンセルしたら 貸出可 に戻すだけで十分ですか？
A: 問題設定上は列値の変更だけで済みますが、実システムでは予約管理テーブルを設け、監査証跡や複数予約順序を保持するのが一般的です。
A: 問題設定上は列値の変更だけで済みますが、実システムでは予約管理テーブルを設け、監査証跡や複数予約順序を保持するのが一般的です。
関連キーワード: ビュー, 結合条件, NULL判定, 集合演算子, サブクエリ
設問2：
本文中の下線①の方法を用いた場合に、利用者が貸出状況を正しく確認できない可能性がある。その理由を30字以内で述べよ。
模範解答
夜間バッチ処理後に貸出状況が変わることがあるから
解説
解答の論理構成
- 問題文では、統合検索サービスを実装する方法として
「“①統合検索向けのテーブルを作成して夜間バッチ処理で両市図書館の情報をコピーする方法”」が提示されています。 - 同時に、貸出可否を示す最新情報は各館の「“貸出記録A”」「“貸出記録B”」で常時更新されます。例えば、
「両システムとも貸出記録テーブルの“返却日”の値は、貸出中はNULLだが、返却後は返却した日付を設定していた。」
と記述されており、貸出・返却の都度データが変化することが分かります。 - ①の方法ではコピー処理が「夜間バッチ」でしか行われません。日中に利用者が検索したとき、バッチ後に発生した貸出・返却は統合テーブルに反映されていないため、検索結果と実際の蔵書状況にズレが生じます。
- 以上より、「夜間バッチ処理後に貸出状況が変わることがあるから」という解答となります。
誤りやすいポイント
- 「夜間バッチ＝毎日更新だから十分」と判断し、リアルタイム性の欠如を見落とす。
- バッチ処理の頻度を上げれば解決できると考え、設問が問う“正しく確認できない可能性”の本質を外す。
- ビュー採用の理由を単に“実装が楽”と誤解し、可用性や保守性に触れない。
FAQ
Q: バッチ処理を1時間ごとに実行すれば問題は解決しますか？
A: 頻度を上げても実行〜完了までの間に発生した取引は反映されず、完全なリアルタイム性は確保できません。ビューで直接参照する方が確実です。
A: 頻度を上げても実行〜完了までの間に発生した取引は反映されず、完全なリアルタイム性は確保できません。ビューで直接参照する方が確実です。
Q: ビュー方式でもパフォーマンス低下が心配ですが？
A: 適切なインデックス設計と必要列の限定により、リアルタイム性を保ちつつ性能を確保できます。さらにマテリアライズド・ビューなどの選択肢もあります。
A: 適切なインデックス設計と必要列の限定により、リアルタイム性を保ちつつ性能を確保できます。さらにマテリアライズド・ビューなどの選択肢もあります。
Q: 貸出状況だけ別に同期させる案はどうですか？
A: 実装可能ですが、蔵書・書誌情報と整合性を保つためには複数テーブルの差分同期が必要となり、結局複雑さとリアルタイム性のトレードオフが残ります。
A: 実装可能ですが、蔵書・書誌情報と整合性を保つためには複数テーブルの差分同期が必要となり、結局複雑さとリアルタイム性のトレードオフが残ります。
関連キーワード: ビュー, バッチ処理, リアルタイム性, データ同期, 貸出状況
設問3：
図1の“統合検索”ビューは更新不可能なビューである。“統合検索”ビューが更新不可能なビューとなっている理由を解答群の中から選び、記号で答えよ。
解答群
ア：検索条件を複数指定しているから
イ：集約関数を用いているから
ウ：複数の表からビューを作成しているから
エ：副問合せを用いているから
模範解答
イ
解説
解答の論理構成
- 【問題文】には、ビュー定義の冒頭で
CREATE VIEW 統合検索（書籍名、著者名、出版社名、ISBN番号、図書館名、貸出可能冊数）
とあり、取得列に “貸出可能冊数” が含まれています。 - 同じくビュー定義の SELECT 句には
COUNT(書籍A.書籍番号)
と記載されており、さらに
GROUP BY 書籍名, 著者名, 出版社名, 書籍A.ISBN番号
と行集約を行っています。 - 集約関数 COUNT と GROUP BY によって生成される “貸出可能冊数” は、元表の個々の行に一意に対応しません。このようなビューは行の更新・挿入・削除を行うと、どの基表のどの行を操作すべきか決定できないため SQL 標準では “更新不可能” と定義されます。
- したがって、更新不可能な理由は「集約関数を用いているから」であり、解答群の記号は【イ】となります。
誤りやすいポイント
- 「複数の表を結合しているから更新できない」と思い込み、【ウ】を選択してしまう。結合ビューでも主キーが保持されていれば更新可能なケースがあります。
- NOT IN の副問合せに目がいき、【エ】を選択してしまう。副問合せの有無は更新可否に直接影響しません。
- 複数の検索条件や WHERE 句の存在を理由に【ア】を選ぶ。検索条件だけでは更新不可能とは判定されません。
FAQ
Q: 集約関数を含むビューを更新可能にする方法はありますか？
A: 直接更新はできませんが、INSTEAD OF トリガやアプリケーション側で更新ロジックを実装し、擬似的に対応させることがあります。
A: 直接更新はできませんが、INSTEAD OF トリガやアプリケーション側で更新ロジックを実装し、擬似的に対応させることがあります。
Q: 結合ビューでも更新可能になる条件は？
A: 更新対象の列が一方の表にのみ属し、その表の主キーがビューに含まれていれば更新が許される場合があります。
A: 更新対象の列が一方の表にのみ属し、その表の主キーがビューに含まれていれば更新が許される場合があります。
Q: GROUP BY を外せば更新可能になりますか？
A: 集約列がなくなり、各行が元表の行に対応する形になれば、その他の条件を満たす限り更新可能になる可能性があります。
A: 集約列がなくなり、各行が元表の行に対応する形になれば、その他の条件を満たす限り更新可能になる可能性があります。
関連キーワード: ビュー更新制約, 集約関数, GROUP BY, SQL標準, アップデート可否
設問4：
図2の“統合貸出予約”ビューで一意キーとなるのはどれか。列名を全て答えよ。
模範解答
蔵書番号, 図書館名
解説
解答の論理構成
-
各市の蔵書テーブルの主キー
表1と表2で、下線が付された主キーはどちらも“蔵書番号”である。すなわち、
・A市側 “蔵書A”テーブル：主キーは“蔵書番号”
・B市側 “蔵書B”テーブル：主キーは“蔵書番号”
よって「同じ図書館内では“蔵書番号”が一意」になります。 -
しかし両市を合わせると“蔵書番号”が衝突
問題文には「“蔵書A”テーブルと“蔵書B”テーブルの間で重複があった。」と明記されています。このため、単独の“蔵書番号”では統合ビュー内で一意性が保てません。 -
ビューに追加された“図書館名”列
図2の“統合貸出予約”ビューは、
・A市側の行に文字列 “A市図書館”
・B市側の行に文字列 “B市図書館”
をそれぞれ“図書館名”列として付加しています。これにより「同じ番号でも別の図書館名なら別行」と区別が可能になります。 -
一意キーの決定
以上より、
・組合せ① 蔵書番号＋図書館名：両市を跨いでも重複がない
・組合せ② 蔵書番号のみ：両市間で重複する
・組合せ③ 図書館名のみ：各館に多数の蔵書があるため重複する
したがって“蔵書番号, 図書館名”の組合せが一意キーとなります。
誤りやすいポイント
- 「主キーは蔵書番号だから統合後も同じだろう」と思い込み、重複の記述を読み落とす。
- “ISBN番号”や“書籍番号”をキー候補に挙げてしまう。これらは同じ本の複数冊で重複する。
- “貸出状況”列を含めるべきと勘違いする。値が「貸出可／貸出中／予約済」の三択なので一意性とは無関係。
FAQ
Q: ISBN番号を含めれば安全ではありませんか？
A: ISBN番号は版ごとに一意ですが、同じISBNを持つ蔵書は何冊も存在するため一意キーになりません。
A: ISBN番号は版ごとに一意ですが、同じISBNを持つ蔵書は何冊も存在するため一意キーになりません。
Q: 将来図書館が増えた場合でも同じ設計で大丈夫？
A: 新しい図書館名が別値で追加されるだけなので、“蔵書番号, 図書館名”の複合キーは引き続き一意性を保てます。
A: 新しい図書館名が別値で追加されるだけなので、“蔵書番号, 図書館名”の複合キーは引き続き一意性を保てます。
Q: 図書館名をマスタ表で管理してもキーは変わりますか？
A: 文字列の代わりに図書館IDを格納しても、蔵書番号との複合で一意という考え方は同じです。
A: 文字列の代わりに図書館IDを格納しても、蔵書番号との複合で一意という考え方は同じです。
関連キーワード: 複合キー, 主キー, 重複チェック, ビュー設計, データ統合
