基本情報技術者過去問題平成26年秋期午後問4(データベース)

基本情報技術者過去問題平成26年秋期午後問4

問4　データベース

書籍を管理する関係データベースの設計及び運用に関する次の記述を読んで，設問1～4に答えよ。

　ある会社の資料室では，社員(以下，利用者という)と窓口担当者の利便性を向上する目的で，所蔵する書籍を管理するデータベースを再構築することにした。この会社の資料室では，業務に関連する書籍を管理しており，利用者への貸出しも実施している。
　従来のデータベースは，図1に示すとおり，所蔵する書籍と現在の貸出状況を管理する書籍表で構成されている。下線付きの項目は，主キーを表す。

〔書籍表に関する説明〕

利用者に貸出中の書籍には，貸出日と返却予定日が格納されている。これらの項目が null の書籍は，貸し出されていないことを表す。
社員番号には，その書籍を貸し出している利用者の社員番号が格納されている。人事系のシステムで管理している社員表と結合することで，氏名と連絡先が検索できる。
返却されたら，貸出日，返却予定日及び社員番号には null を設定する。

設問1

データベースの再構築に当たり，利用者と窓口担当者から要望を提出してもらった。解答群に示した要望のうち，従来の書籍表(図1)及び社員表からは検索できない情報を二つ選べ。

解答群

現在，貸出中でない書籍の一覧
現在，貸出中の書籍の一覧
現在，書籍を貸出中の利用者の連絡先
書籍ごとの累積貸出回数
返却予定日を過ぎても貸出中の書籍の一覧
利用者ごとの貸出履歴

解答選択欄

解答

※順不同

解説

貸出日が null のレコードを抽出することで検索可能です。
貸出日が null でないのレコードを抽出することで検索可能です。
書籍表と社員表を結合し、貸出日が null でないのレコードの社員番号に対応する連絡先を抽出することで検索可能です。
履歴や貸出回数をカウントするデータ列が存在しないので検索できません。
現在貸出中で、返却予定日が現在の日時よりも前であるレコードを抽出することで検索可能です。
社員表にも書籍表にも履歴を記録するデータ列が存在しないので検索できません。

したがって検索できない情報は「エ」「カ」の2つです。

設問2

利用者と窓口担当者からの要望を踏まえ，データベースを図2に示す表構成で再構築して，運用を始めた。運用開始後に延滞したことがある利用者の社員番号と，延滞した書籍名を，社員番号の昇順に表示したい。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。ここで，SQL文中のCURRENT_DATE値関数は，現在の日付をDATE型で返却する。

〔貸出表に関する説明〕

1冊の貸出しに対して一意の貸出番号を付与し，貸出表に情報を記録する。
返却日には，返却された日付を格納する。返却日が null の書籍は，貸出中であることを表す。
書籍が返却された後も，貸出表に記録された情報は残す。
貸出日，返却予定日及び返却日は DATE 型である。

a に関する解答群

解答選択欄

解答

a=ア

解説

SELECT文で"社員番号"列、"書籍名"列の取出しを指定し、最後の ORDER BY句で社員番号の昇順で整列することを指定しているので、WHERE句では「貸出表のレコードのうち、返却が遅延したレコード」を抽出する条件式を指定することになります。

返却が遅延したレコードとは、

返却日が返却予定日よりも後である
返却予定日が過ぎているのに、まだ(現在まで)返却されていない

という2つのいずれかに該当するものです。

上記の2つの条件をSQL文にすると、

貸出表.返却日 > 貸出表.返却予定日
貸出表.返却日 IS NULL AND 貸出表.返却予定日 < CURRENT_DATE

となるのでこの2つの式を OR で結合したSQL文が、返却が遅延した(している)レコードを抽出する条件式になります。

∴a＝ア

正しい。
BETWEEN句で、返却日が貸出日と返却予定日の間という条件を指定していますが、これは遅延なく返却されたレコードを抽出するものなので誤りです。また、「返却日 IS NULL」と「返却予定日 < CURRENT_DATE」を OR で結合していることも誤りです。
「貸出表.返却日 != 貸出表.返却予定日」では、返却予定日よりも前の返却期間内に返却されたレコードや、現在貸出中のレコードも抽出されてしまうので誤りです。
「貸出表.返却日 IS NOT NULL」は、返却期間内外問わず返却済みであるすべてのレコードが抽出されてしまうので誤りです。

設問3

貸出回数が多い順に書籍番号，書籍名及び貸出回数を表示したい。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。

b に関する解答群

COUNT(*)
MAX(書籍表.書籍番号)
SUM(貸出表.貸出番号)
貸出表.貸出番号
書籍表.書籍番号

解答選択欄

解答

b=ア

解説

WHERE句で結合条件を指定して表を結合すると、両方の表に存在するレコードのみから成る表が返される内部結合(INNER JOIN)になるので、書籍表と貸出表の結合によって、貸出表に存在する(貸出実績のある)レコードのみから成る表が生成されます。

書籍ごとに貸出回数を集計したいので、書籍番号と書籍名でグループ化したあと、グループごとのレコード件数をCOUNT文を使って求めます。さらにORDER BY句に DESC を指定することで貸出回数の多い順(降順)に整列します。

∴b＝ア：COUNT(*)

正しい。
MAXはグループごとの最大値を求める文です。
SUMはグループごとの値の合計を求める文です。
貸出回数の集計関数が指定されていないので誤りです。
GROUP BY句を使用した場合のSELECT文には、GROUP BY句で指定した列名、及び集計関数しか指定できないという制約があるため構文エラーとなります。

設問4

資料室に設置されている端末からだけでなく，利用者が自席のPCからも書籍を検索できるようにしたところ，貸出実績の増加と利用者からのアクセスの急増に伴い，書籍名を入力して貸出中か否かを表示する処理で，レスポンスの低下が顕在化した。レコード件数を確認したところ，書籍表が865件，貸出表が10,382件だった。
　そこで，インデックスを設定して検索性能の向上を図ることにした。インデックスの設定によって最も効果が期待できる項目として適切なものを，解答群の中から選べ。

解答群

書籍表.書籍名
貸出表.書籍番号
貸出表.社員番号
貸出表.貸出日
貸出表.返却日

解答選択欄

解答

解説

「書籍名を入力して貸出中か否かを表示する処理」でレスポンスが低下しているという記述から、レコード数が10000行を超える貸出表から利用者が入力した書籍のデータを検索するのに時間が掛かっていることがわかります。

インデックス(index)は、索引(さくいん)とも呼ばれデータベースへのアクセス効率を向上させるために、検索対象となるデータ項目に対して設定されます。インデックスは、表内のあらゆる列に対して作成できますが、作成の目的が利用者が入力した書籍と同じ書籍のレコードを素早く検索することなので"貸出表の書籍番号"に設定するのが最も効果的です。

∴イ：貸出表.書籍番号

平成26年秋期午後問題一覧

問1　問2　問3　問4　問5　問6　問7　問8　問9　問10　問11　問12　問13　採点講評