基本情報技術者 2019年 秋期 午後 問03

解答の導き方

1. 最も新しい貸出日を選びます。
   SQL（Structured Query Language：データベースを操作する言語）では、MAX関数を使うと、指定した値の中から最大の値を取り出せます。日付の場合、最大の値は最も新しい日付です。
   したがって、bにはMAX(貸出表.貸出日)が入ります。外側の検索では、その結果と貸出日が一致するレコードを探すので、最も新しい貸出日のレコードを選べます。よって、bはイです。
   MIN(貸出表.貸出日)では最も古い日付になります。また、貸出日をそのまま指定しても、貸出履歴が複数あると最も新しい日付に絞れません。
2. 最新の貸出が返却済みかを判定します。
   貸出表に関する説明では、貸出時に「返却日にはNULLを設定」し、返却されたら返却日を設定するとあります。つまり、返却日がNULLなら貸出中で、日付が設定されていれば返却済みです。
   CASE式は、条件に応じて表示する値を切り替える書き方です。返却日がNULLなら「貸出中」、NULLでなければ「貸出可」と表示するので、aには次の条件が入ります。
   貸出表.返却日 IS NULL THEN '貸出中' WHEN 貸出表.返却日 IS NOT NULL THEN '貸出可'
   これは選択肢のエです。NULLは値が設定されていない状態を表すため、NULLかどうかの判定にはIS NULLまたはIS NOT NULLを使います。
3. 貸出履歴がない場合も「貸出可」と表示します。
   SQLの後半では、NOT EXISTSによって、そのISBNコードの貸出履歴がないことを確認します。履歴がなければ、書籍情報表から「貸出可」を表示します。
   貸出履歴がないとき、最初の検索では最も新しい貸出日のレコードが見つかりません。一方、後半の検索は貸出履歴がない条件を満たすため、「貸出可」を表示できます。貸出履歴がある場合は、最初の検索で最新の貸出状態が表示されます。

誤りやすいポイント

• NULLを返却済みと取り違えないことです。
  貸出時に返却日へNULLを設定するため、NULLは「まだ返却されていない」状態を表します。
• MAX関数とMIN関数を取り違えないことです。
  最も新しい日付を求めるにはMAX関数を使います。MIN関数で得られるのは最も古い日付です。
• 貸出履歴がない場合の処理を見落とさないことです。
  貸出履歴がある場合の検索だけでは、履歴のない書籍の状態は表示できません。そのため、後半の検索で貸出履歴がない書籍に「貸出可」を表示しています。

FAQ

解答の導き方

1. 書籍情報と貸出情報を対応させます。
   SQLでは、FROM句に書かれた表を使って検索します。WHERE句にある「書籍情報表.ISBNコード = 貸出表.ISBNコード」は、ISBNコードが同じ書籍情報と貸出情報を対応させる条件です。これにより、どの書籍の貸出記録かを特定できます。
2. 貸出日が指定期間内の記録だけを残します。
   期間の初日以降であり、かつ最終日以前であることが必要です。つまり、貸出日は次の両方の条件を満たさなければなりません。
   貸出表.貸出日 >= '2018-04-01' 貸出表.貸出日 <= '2019-03-31'
   SQLのBETWEENは、指定した範囲の両端を含む条件です。そのため、期間の両端を含める次の条件でも同じ意味になります。
   AND 貸出表.貸出日 BETWEEN '2018-04-01' AND '2019-03-31'
3. 書籍ごとに貸出記録をまとめます。
   GROUP BYは、指定した項目の値が同じ行をひとまとめにする句です。書籍ごとに貸出回数を数えるため、ISBNコードと書籍名でグループ化します。
   GROUP BY 書籍情報表.ISBNコード, 書籍情報表.書籍名
   そのグループ内の行数をCOUNT(*)で数えれば、その書籍の期間内の貸出回数になります。
4. 貸出回数が4回以上のグループだけを残します。
   HAVINGは、GROUP BYでまとめた後のグループに条件を付ける句です。したがって、COUNT(*)が4以上であることはHAVINGで指定します。
   HAVING COUNT(*) >= 4

誤りやすいポイント

• 選択肢アのORに注意します。
  指定期間に入るには、貸出日が初日以降であることと、最終日以前であることの両方が必要です。ORにすると、どちらか一方を満たすだけで対象になります。例えば、期間より前の日付は「最終日以前」を満たし、期間より後の日付は「初日以降」を満たしてしまいます。
• 集計条件をWHEREに置かないようにします。
  WHEREは、集計する前の個々の行を絞り込むための句です。一方、「4回以上」のように集計後の回数を条件にする場合は、HAVINGを使います。
• 貸出日をGROUP BYに加えないようにします。
  貸出日もグループ化すると、書籍ごとではなく、書籍と貸出日の組合せごとに回数を数えることになります。期間全体の貸出回数を調べるには、貸出日でグループを分けず、WHEREで期間を絞り込みます。

FAQ

解答の導き方

d：追加購入した書籍を登録する表

e：書籍を廃棄するときの操作順序

1. まず②で、対象ISBNコードに対応する書籍番号を調べます。ISBNコードが同じ書籍を複数冊所蔵している場合があるため、該当する書籍番号を先に確認します。
2. 次に④で、その書籍番号を参照している貸出表のレコードを削除します。貸出表のレコードが残っていると、書籍表の書籍番号を削除できません。
3. 続いて③で、書籍表から対象ISBNコードに対応するレコードを削除します。
4. 最後に①で、書籍表から参照されなくなったISBNコードを、書籍情報表から削除します。

誤りやすいポイント

• ISBNコードと書籍番号を同じものとして考えないようにします。ISBNコードは同じ書籍をまとめる番号で、書籍番号は所蔵している1冊ずつを区別する番号です。
• 追加購入時に、書籍情報表にも行を追加すると考えないようにします。同じISBNコードの書籍情報はすでに登録されているため、追加するのは書籍表の行です。
• 削除では、参照している側の表から先に消します。貸出表が書籍表を参照し、書籍表が書籍情報表を参照しているため、貸出表、書籍表、書籍情報表の順に削除します。
• 対象ISBNコードの行を先に削除すると、書籍表から書籍番号を調べられなくなります。そのため、②は最初に行います。

FAQ