【応用情報技術者試験平成24年春期】問6 データベース,SQL問題解説

1 応用情報技術者試験平成24年春期問6 「複数の図書館の検索システム統合」解説
- 1.1 設問1
  - 1.1.1 図1の穴埋め
    - 1.1.1.1 図1 SQL実行例
  - 1.1.2 図2の穴埋め（空欄f, g）

応用情報技術者試験平成24年春期問6 「複数の図書館の検索システム統合」解説

応用情報技術者試験平成24年春期　問6 データベース問題を解説します。

本記事中では問題文の全ての記載は省いて解説します。

問題全文は下記IPA Webサイトより
https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2012h24_1/2012h24h_ap_pm_qs.pdf

（問題文冒頭抜粋）
隣接するA市とB市は，半年後に合併を控えており，様々な情報システムの統合を計画している。両市が運営する図書館システムについて，統合を検討した結果，両図書館の貸出し可能な蔵書が確認できる統合検索サービスを実現することにした。その設計は，システム開発会社のC君が担当することになった。
A市とB市の現在の図書館システムのテーブル構造を表1と表2に示す。表1と表2において，下線は主キーを表す。
引用元：IPA独立行政法人情報処理推進機構：平成24年度春期⁽³⁾問題冊子・配点割合・解答例・採点講評（PDF）

設問1

（問題文中に登場する、DB各テーブル設計と設問1に該当するSQL）

画像引用元：IPA独立行政法人情報処理推進機構：平成24年度春期⁽³⁾問題冊子・配点割合・解答例・採点講評（PDF）

図1の穴埋め

図1に記載されたSQLは問題のテーマにもなっている統合検索サービスのために「貸出可能な蔵書」の抽出を目的としています。

CREATE VIEWでビュー表「統合検索」を作成しており、ビューの元となるSELECTの条件に穴埋めが存在していることを確認します。

作成するビュー表に対し、SELECT文が二つ存在していますが、それぞれの本体のWHERE句で指定すべき条件（空欄a, b）を考えます。

ひとつめのSELECT文はFROM句に蔵書 A, 書籍A、ふたつめも同様に蔵書 B, 書籍Bとそれぞれ複数のテーブルが指定されていますので、内部結合を行っていることがわかります。

今回の様にINNER JOINを記述しないで内部結合を行うためには結合条件をWHERE句で指定しなくてはいけませんので、WHERE句のひとつめの条件（空欄a, d)にはFROMに指定したテーブルのカラム同士をイコールで結ぶ記述が入ることがわかります。

【空欄a】蔵書 A.書籍番号 = 書籍 A.書籍番号
【空欄d】蔵書 B.ISBN番号 = 書籍 B.ISBN番号

（A,Bで書籍の主キー列が異なることに注意）

次に空欄b, eですが、こちらは両者それぞれで先ほどの2つのSELECT文の中でNOT IN ~ 以降の副問合せのSELECTに対するWHERE句の条件となります。

NOT IN ( SELECT 貸出記録 A.蔵書番号　FROM 貸出記録 A WHERE 空欄b )
NOT IN ( SELECT 貸出記録 B.蔵書番号　FROM 貸出記録 B WHERE 空欄e )

NOT IN（含まれない）の意味を考えると、副問合わせでは今回の目的である「貸出可能な〜」とは逆の意味の「貸出不可」の行をリストで返す必要があります。

問題文中「C君が統合検索サービスの実現方式について，調査検討を行った結果を次に示す。」の箇所、
「・両システムとも貸出記録テーブルの”返却日”の値は，貸出中はNULLを，返却後は返却した日付を設定していた。」
より、

貸出中のレコード（返却日がNULL）は貸出不可ですので空欄に入る解答としてはそれぞれ下記となります。

【空欄b】貸出記録 A.返却日 IS NULL
【空欄e】貸出記録 B.返却日 IS NULL

（SQLでNULLの判定はイコールではなく、ISまたはIS NOTとなります）

残りは2つのSELECT文の間に挟まれた【空欄C】ですが、「統合検索サービス」の目的からもわかるとおり、統合を行うための「UNION」が解答となります。

UNIONを実行するためには統合する表同士の列の種類が同じである必要がありますが、
2つのSELECTで選択している列はそれぞれ、作成するビュー表の※列、と対応していることがわかります。

また、UNIONには行の重複を省かないためのUNION ALLも存在しますが、今回統合を行う2つのSELECTの結果に重複行が存在しないため、どちらを使っても同じ結果となります。
（図書館名を指定しているため、全く同じ書籍であっても行単位で重複することはない）

※書籍名, 著者名, 出版社名, ISBN番号, 図書館名, 貸出可能冊数

【空欄c】 UNION または UNION ALL

図1 SQL実行例

（ひとつめのSELECT）貸出可の蔵書　A

+-------------+-------------+----------------+--------------+---------------+--------------------+
| A.書籍名    | A.著者名    | A.出版社名     | A.ISBN番号   | A市図書館     | 貸出可能冊数       |
+-------------+-------------+----------------+--------------+---------------+--------------------+
| book_01     | author_x    | publisher_a    | 001-001      | A市図書館     |                  2 |
| book_03     | author_y    | publisher_a    | 001-003      | A市図書館     |                  1 |
| book_04     | author_z    | publisher_b    | 001-004      | A市図書館     |                  1 |
+-------------+-------------+----------------+--------------+---------------+--------------------+

（ふたつめのSELECT）貸出可の蔵書　B

+-------------+-------------+----------------+--------------+---------------+--------------------+
| B.書籍名    | B.著者名    | B.出版社名     | B.ISBN番号   | B市図書館     | 貸出可能冊数       |
+-------------+-------------+----------------+--------------+---------------+--------------------+
| book_02     | author_x    | publisher_a    | 001-002      | B市図書館     |                  2 |
| book_03     | author_y    | publisher_a    | 001-003      | B市図書館     |                  1 |
+-------------+-------------+----------------+--------------+---------------+--------------------+

蔵書A, Bの統合（UNION）統合検索ビュー

+-----------+-----------+--------------+------------+---------------+--------------------+
| 書籍名    | 著者名    | 出版社名     | ISBN番号   | 図書館名      | 貸出可能冊数       |
+-----------+-----------+--------------+------------+---------------+--------------------+
| book_01   | author_x  | publisher_a  | 001-001    | A市図書館     |                  2 |
| book_03   | author_y  | publisher_a  | 001-003    | A市図書館     |                  1 |
| book_04   | author_z  | publisher_b  | 001-004    | A市図書館     |                  1 |
| book_02   | author_x  | publisher_a  | 001-002    | B市図書館     |                  2 |
| book_03   | author_y  | publisher_a  | 001-003    | B市図書館     |                  1 |
+-----------+-----------+--------------+------------+---------------+--------------------

図2の穴埋め（空欄f, g）

〔統合検索サービスの拡張〕
統合検索サービスの構築中に，市民からの強い要望があり，両市の図書館で貸出可能な蔵書の確認だけでなく，貸出予約もできる機能を追加することになった。そこでC君が検討した結果，両システムの蔵書テーブルに”貸出状況”の列を追加した。追加後の蔵書テーブルを表3に示す。ただし，”貸出状況”の列には”貸出中”，”貸出可”又は”予約済”(貸出中ではないが，予約されていて借りられない状態)のいずれかが格納されているものとする。貸出予約は，”貸出状況”の値が”貸出可”となっている蔵書を，”予約済”に変更する処理である。
引用元：IPA独立行政法人情報処理推進機構：平成24年度春期⁽³⁾問題冊子・配点割合・解答例・採点講評（PDF）

続いて問題中、上記〔統合検索サービスの拡張〕の説明欄で登場する、”統合貸出予約”ビューの作成に関する図2の穴埋め（空欄f,g）に移ります。

（統合貸出予約ビューに関する問題中の表と図）

（図2の説明）
蔵書テーブルの変更後，C君はビューを使って，貸出予約に対応した”統合貸出予約”ビューを図2のとおり作成した。”統合貸出予約”ビューは，”貸出状況”の値が”貸出可”となっている蔵書の一覧を表示するものである。
引用元：IPA独立行政法人情報処理推進機構：平成24年度春期⁽³⁾問題冊子・配点割合・解答例・採点講評（PDF）