応用情報技術者過去問題 平成24年春期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
複数の図書館の検索システムの統合に関する次の記述を読んで，設問1～4に答えよ。
隣接するA市とB市は，半年後に合併を控えており，様々な情報システムの統合を計画している。両市が運営する図書館システムについて，統合を検討した結果，両図書館の貸出し可能な蔵書が確認できる統合検索サービスを実現することにした。その設計は，システム開発会社のC君が担当することになった。
A市とB市の現在の図書館システムのテーブル構造を表1と表2に示す。表1と表2において，下線は主キーを表す。
C君が統合検索サービスの実現方式について，調査検討を行った結果を次に示す。
〔統合検索サービスの拡張〕
統合検索サービスの構築中に，市民からの強い要望があり，両市の図書館で貸出可能な蔵書の確認だけでなく，貸出予約もできる機能を追加することになった。そこでC君が検討した結果，両システムの蔵書テーブルに"貸出状況"の列を追加した。追加後の蔵書テーブルを表3に示す。ただし，"貸出状況"の列には"貸出中"，"貸出可"又は"予約済"(貸出中ではないが，予約されていて借りられない状態)のいずれかが格納されているものとする。貸出予約は，"貸出状況"の値が"貸出可"となっている蔵書を，"予約済"に変更する処理である。
蔵書テーブルの変更後，C君はビューを使って，貸出予約に対応した"統合貸出予約"ビューを図2のとおり作成した。"統合貸出予約"ビューは，"貸出状況"の値が"貸出可"となっている蔵書の一覧を表示するものである。
隣接するA市とB市は，半年後に合併を控えており，様々な情報システムの統合を計画している。両市が運営する図書館システムについて，統合を検討した結果，両図書館の貸出し可能な蔵書が確認できる統合検索サービスを実現することにした。その設計は，システム開発会社のC君が担当することになった。
A市とB市の現在の図書館システムのテーブル構造を表1と表2に示す。表1と表2において，下線は主キーを表す。
- 両システムの蔵書テーブル中の"蔵書番号"には，共に10桁の数字が使われており，"蔵書A"テーブルと"蔵書B"テーブルの間で重複があった。
- 両システムとも貸出記録テーブルの"返却日"の値は，貸出中はNULLを，返却後は返却した日付を設定していた。
- 両システムのテーブルを統合する際，既存のテーブル定義とデータを保持したまま，一つのスキーマ上に各テーブルを実装することにした。
- 統合検索サービスを実現する際，①統合検索向けのテーブルを作成して夜間バッチ処理で両市図書館の情報をコピーする方法と，ビューを用いて両市図書館の情報を直接参照する方法を比較し，ビューを用いて実現することにした。
統合検索サービスの構築中に，市民からの強い要望があり，両市の図書館で貸出可能な蔵書の確認だけでなく，貸出予約もできる機能を追加することになった。そこでC君が検討した結果，両システムの蔵書テーブルに"貸出状況"の列を追加した。追加後の蔵書テーブルを表3に示す。ただし，"貸出状況"の列には"貸出中"，"貸出可"又は"予約済"(貸出中ではないが，予約されていて借りられない状態)のいずれかが格納されているものとする。貸出予約は，"貸出状況"の値が"貸出可"となっている蔵書を，"予約済"に変更する処理である。
設問1
図1と図2のSQL文中のa～gに入れる適切な字句を答えよ。列名は，テーブル名を省略せずに，"テーブル名.列名"と記述すること。
解答入力欄
- a：
- b：
- c：
- d：
- e：
- f：
- g：
解答例・解答の要点
- a：蔵書A.書籍番号 = 書籍A.書籍番号
- b：貸出記録A.返却日 IS NULL
- c：UNION 又は UNION ALL
- d：蔵書B.ISBN番号 = 書籍B.ISBN番号
- e：貸出記録B.返却日 IS NULL
- f：蔵書A.貸出状況 = '貸出可'
- g：蔵書B.貸出状況 = '貸出可'
解説
〔aについて〕
SELECT句やWHERE句には書籍Aテーブルと蔵書Aテーブルが登場しており、結合を行うと考えられます。(内部)結合を行うにはINNER JOIN句を用いますが、他にFROM句で複数のテーブル名を指定し、WHERE句に「表名.列名 = 表名.列名」を記述する方法もあります。
表1を見ると、蔵書Aテーブルと書籍Aテーブルは共通のキー"書籍番号"をもち、蔵書Aテーブル側では書籍Aテーブルを参照する外部キーになっています。このことから結合に用いるキーは"書籍番号"とわかります。したがって、[a]には「蔵書A.書籍番号 = 書籍A.書籍番号」が当てはまります。
∴a＝蔵書A.書籍番号 = 書籍A.書籍番号
〔bについて〕
このSQL文は貸出し可能なすべての蔵書を抽出することが目的です。主問合せ側ではNOT INを用いて副問合せの結果リストと比較をしているので、①NOT IN句の直後の副問合せで貸出中の蔵書番号を取得し、②主問合せ側で貸出中でない（＝貸出し可能）の蔵書を抽出するという流れでレコードの絞り込みを行うことになります。
本文中には「両システムとも貸出記録テーブルの"返却日"の値は，貸出中はNULLを，返却後は返却した日付を設定していた。」とあります。今回は貸出中である蔵書番号のリストを取得したいので、[b]には「貸出記録A.返却日 IS NULL」が当てはまります。
貸出記録A.はつけなくてもSQL文は動作しますが、問題の指示により列名の前にテーブル名をつける必要がある点、NULLをマッチさせるには"="ではなく"IS NULL"を使う点に注意が必要です。
∴b＝貸出記録A.返却日 IS NULL
cの解説は後に回します。
〔dについて〕
基本的には[a]と同様ですが、表1より蔵書Bテーブルと書籍Bテーブルは（A市とは異なり）共通のキー"ISBN番号"をもち、蔵書Bテーブル側では書籍Bテーブルを参照する外部キーであることがわかります。このことから結合に用いるキーは"ISBN番号"、[d]には「蔵書B.ISBN番号 = 書籍B.ISBN番号」が当てはまることがわかります。
∴d＝蔵書B.ISBN番号 = 書籍B.ISBN番号
〔eについて〕
SQL文の構造はA市の部分と同じです。貸出中である蔵書番号のリストを取得したいので、[e]には「貸出記録B.返却日 IS NULL」が当てはまります。[b]と同じ理由なので説明は割愛します。
∴e＝貸出記録B.返却日 IS NULL
〔cについて〕
SELECT文とSELECT文の間で2つの取得結果を結び付ける部分であり、A市図書館の貸出し可能な蔵書とB市図書館の貸出し可能な蔵書をまとめるための構文が当てはまります。
今回は単純に取得結果のレコードを併合した結果を表示したいので、2つの関係の和集合を得る「UNION」を使うのが適切であると判断できます。したがって、[c]には「UNION」が当てはまります。
※UNION ALLでも正解となっているのは、前半のSELECT文の取得結果と後半のSELECT文の取得結果に重複するレコードがないからです。UNIONは重複行を除いた結果を返す一方、UNION ALLは重複行を含めた結果を返すという違いが重複がないときにはどちらを使っても結果は同じです。
∴c＝UNION 又は UNION ALL
〔f、gについて〕
図2のSQL文の[a]、[c]及び[d]にこれまでの解答を当てはめると以下のようになります。
[f]と[g]は結合した表から目的のレコードを取り出すための条件式が入ります。〔統合検索サービスの拡張〕には、「"統合貸出予約"ビューは，"貸出状況"の値が"貸出可"となっている蔵書の一覧を表示するものである」とありますから、[f]には「蔵書A.貸出状況 = '貸出可'」が、[g]には「蔵書B.貸出状況 = '貸出可'」がそれぞれ当てはまると判断できます。
∴f＝蔵書A.貸出状況 = '貸出可'
g＝蔵書B.貸出状況 = '貸出可'
SELECT句やWHERE句には書籍Aテーブルと蔵書Aテーブルが登場しており、結合を行うと考えられます。(内部)結合を行うにはINNER JOIN句を用いますが、他にFROM句で複数のテーブル名を指定し、WHERE句に「表名.列名 = 表名.列名」を記述する方法もあります。
表1を見ると、蔵書Aテーブルと書籍Aテーブルは共通のキー"書籍番号"をもち、蔵書Aテーブル側では書籍Aテーブルを参照する外部キーになっています。このことから結合に用いるキーは"書籍番号"とわかります。したがって、[a]には「蔵書A.書籍番号 = 書籍A.書籍番号」が当てはまります。
∴a＝蔵書A.書籍番号 = 書籍A.書籍番号
〔bについて〕
このSQL文は貸出し可能なすべての蔵書を抽出することが目的です。主問合せ側ではNOT INを用いて副問合せの結果リストと比較をしているので、①NOT IN句の直後の副問合せで貸出中の蔵書番号を取得し、②主問合せ側で貸出中でない（＝貸出し可能）の蔵書を抽出するという流れでレコードの絞り込みを行うことになります。
本文中には「両システムとも貸出記録テーブルの"返却日"の値は，貸出中はNULLを，返却後は返却した日付を設定していた。」とあります。今回は貸出中である蔵書番号のリストを取得したいので、[b]には「貸出記録A.返却日 IS NULL」が当てはまります。
貸出記録A.はつけなくてもSQL文は動作しますが、問題の指示により列名の前にテーブル名をつける必要がある点、NULLをマッチさせるには"="ではなく"IS NULL"を使う点に注意が必要です。
∴b＝貸出記録A.返却日 IS NULL
cの解説は後に回します。
〔dについて〕
基本的には[a]と同様ですが、表1より蔵書Bテーブルと書籍Bテーブルは（A市とは異なり）共通のキー"ISBN番号"をもち、蔵書Bテーブル側では書籍Bテーブルを参照する外部キーであることがわかります。このことから結合に用いるキーは"ISBN番号"、[d]には「蔵書B.ISBN番号 = 書籍B.ISBN番号」が当てはまることがわかります。
∴d＝蔵書B.ISBN番号 = 書籍B.ISBN番号
〔eについて〕
SQL文の構造はA市の部分と同じです。貸出中である蔵書番号のリストを取得したいので、[e]には「貸出記録B.返却日 IS NULL」が当てはまります。[b]と同じ理由なので説明は割愛します。
∴e＝貸出記録B.返却日 IS NULL
〔cについて〕
SELECT文とSELECT文の間で2つの取得結果を結び付ける部分であり、A市図書館の貸出し可能な蔵書とB市図書館の貸出し可能な蔵書をまとめるための構文が当てはまります。
※UNION ALLでも正解となっているのは、前半のSELECT文の取得結果と後半のSELECT文の取得結果に重複するレコードがないからです。UNIONは重複行を除いた結果を返す一方、UNION ALLは重複行を含めた結果を返すという違いが重複がないときにはどちらを使っても結果は同じです。
∴c＝UNION 又は UNION ALL
〔f、gについて〕
図2のSQL文の[a]、[c]及び[d]にこれまでの解答を当てはめると以下のようになります。
∴f＝蔵書A.貸出状況 = '貸出可'
g＝蔵書B.貸出状況 = '貸出可'
設問2
本文中の下線①の方法を用いた場合に，利用者が貸出状況を正しく確認できない可能性がある。その理由を30字以内で述べよ。
解答入力欄
解答例・解答の要点
- 夜間バッチ処理後に貸出状況が変わることがあるから (24文字)
解説
夜間バッチ処理でコピーする方法で運用する場合、次回の夜間バッチ処理が実行される前にコピー元のデータに変更があった場合、それが統合検索向けのテーブルに反映されません。換言すると、1日1回しか貸出記録状況を更新しないため、日中に行われた貸出や返却をリアルタイムに反映することができません。このため、例えば、蔵書が返却されていて貸出可の状態であっても総合検索サービス上に表示されなかったり、蔵書が貸し出されているのに総合検索サービス上に表示されたりといった不具合が起こります。他方で、ビューを用いる方法では検索のつど実表からビューが生成されるため最新の情報を得る事ができます。
したがって、答えは「夜間バッチ処理後に貸出状況が変わることがあるから」などです。
∴夜間バッチ処理後に貸出状況が変わることがあるから
したがって、答えは「夜間バッチ処理後に貸出状況が変わることがあるから」などです。
∴夜間バッチ処理後に貸出状況が変わることがあるから
設問3
図1の"統合検索"ビューは更新不可能なビューである。"統合検索" ビューが更新不可能なビューとなっている理由を解答群の中から選び，記号で答えよ。
解答群
- 検索条件を複数指定しているから
- 集約関数を用いているから
- 複数の表からビューを作成しているから
- 副問合せを用いているから
解答入力欄
解答例・解答の要点
- イ
解説
更新不可能なビューとは、実表に対して実表の行を挿入、更新または削除することができないビューで、更新可能なビューの逆です。ビューを更新可能とするためには、ビュー定義に次に挙げる構造を含めてはいけませんので、図1の"統合検索"ビューが、下記のうちどれに該当するために更新不可能になっているのかを考えます。
考え方としては、ビューを更新する場合は、実際は作成元の実表が更新されます。よって、ビューを更新できるためには元のテーブルの行が特定できる必要があります。ビュー定義でGROUP BY句やDISTINCT句により複数の行を圧縮した場合は、ビューは更新不可能です。また、集約関数のようにビューの列に元のテーブルに直接存在しない値がある場合も同様にビューは更新不可能です。
「ウ」についてですが、ビュー表と実表を1対1に対応付けられる場合には複数の表から成るビューも更新できます。図1の"統合検索"ビューはUNIONで併合しているだけなので、更新対象の行は実表の行と1対1で対応しています。よって、更新不可能なビューである事由には該当しません。
∴イ：集約関数を用いているから
- 集約関数（AVG、COUNT、SUM、MIN、MAXなど）
- 2つ以上の表の結合（更新可能な結合，和集合及び列を除く）
- GROUP BY、ORDER BY、MODEL、CONNECT BY、START WITH、DISTINCTの各句
- SELECT構文のリストにコレクション式
- SELECT構文のリストにある副問合せ
- WITH READ ONLYが指定された副問合せ
考え方としては、ビューを更新する場合は、実際は作成元の実表が更新されます。よって、ビューを更新できるためには元のテーブルの行が特定できる必要があります。ビュー定義でGROUP BY句やDISTINCT句により複数の行を圧縮した場合は、ビューは更新不可能です。また、集約関数のようにビューの列に元のテーブルに直接存在しない値がある場合も同様にビューは更新不可能です。
「ウ」についてですが、ビュー表と実表を1対1に対応付けられる場合には複数の表から成るビューも更新できます。図1の"統合検索"ビューはUNIONで併合しているだけなので、更新対象の行は実表の行と1対1で対応しています。よって、更新不可能なビューである事由には該当しません。
∴イ：集約関数を用いているから
設問4
図2の"統合貸出予約"ビューで一意キーとなるのはどれか。列名を全て答えよ。
解答入力欄
解答例・解答の要点
- 蔵書番号，図書館名
解説
蔵書A・Bテーブルはそれぞれ"蔵書番号"列が主キーになっています。つまり、各テーブルのみで考えれば、"蔵書番号"列は一意キーです。しかし、本文中には「両システムの蔵書テーブル中の"蔵書番号"には，共に10桁の数字が使われており，"蔵書A"テーブルと"蔵書B"テーブルの間で重複があった」とあり、A市の蔵書情報とB市の蔵書情報の和集合をとった場合、"蔵書番号"列だけでは一意性がなく、主キーとして不適切であることがわかります。
市別に蔵書番号を見れば蔵書情報を一意に特定できるので、この2つの情報を主キーにすることを考えます。ビュー定義で市を表す情報を探すと、A市とB市でビューの列"図書館名"にそれぞれ"A市図書館"及び"B市図書館"がセットされています。"蔵書番号"と"図書館名"の組を主キーにすれば、"統合貸出予約"ビューの行を一意に特定できるので、主キーは「蔵書番号，図書館名」の組合せとなります。
∴蔵書番号，図書館名
市別に蔵書番号を見れば蔵書情報を一意に特定できるので、この2つの情報を主キーにすることを考えます。ビュー定義で市を表す情報を探すと、A市とB市でビューの列"図書館名"にそれぞれ"A市図書館"及び"B市図書館"がセットされています。"蔵書番号"と"図書館名"の組を主キーにすれば、"統合貸出予約"ビューの行を一意に特定できるので、主キーは「蔵書番号，図書館名」の組合せとなります。
∴蔵書番号，図書館名
