基本情報技術者過去問題令和元年秋期午後問3(データベース)

基本情報技術者過去問題令和元年秋期午後問3

問3　データベース

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

　D社の部署である資料室は，業務に関連する書籍を所蔵しており，従業員への貸出しを2015年4月から実施している。
　所蔵する書籍を管理するデータベースは，書籍の情報を管理する書籍情報表と貸出状況を管理する貸出表とで構成されている。データベース構成を，図1に示す。下線付きの項目は主キーを表し，下破線付きの項目は外部キーを表す。各書籍は1冊しか所蔵していない。

〔貸出表に関する説明〕

従業員に書籍を貸し出す際は，一意の貸出番号，貸し出す書籍のISBNコード，従業員番号，貸出日及び返却予定日を設定し，返却日にはNULLを設定したレコードを追加する。
書籍が返却されたら，対象のレコードの返却日に返却された日付を設定する。

設問1

次のSQL文は，ISBNコードが ISBN978-4-905318-63-7 の書籍の貸出し状態を表示するSQL文である。ISBNコードで貸出表を検索し，最も新しい貸出日のレコードの返却日にNULLが設定されている場合は，"貸出中"が表示される。また，最も新しい貸出日のレコードの返却日に日付が設定されている場合，及び貸出実績のない書籍の場合は，"貸出可"が表示される。　に入れる正しい答えを，解答群の中から選べ。ここで，検索に使用するISBNコードの書籍は必ず所蔵されているものとする。また，返却された書籍はその日のうちに再び貸し出されることはない。

a に関する解答群

b に関する解答群

DISTINCT 貸出表.貸出日
MAX(貸出表.貸出日)
MIN(貸出表.貸出日)
貸出表.貸出日

解答選択欄

解答

a=エ
b=イ

解説

設問のSQL文は、2つのSELECT文の結果をUNION ALL句で足し合わせる構造となっています。UNION ALL句は、2つの関係の(要素の重複を許した)和集合を得る集合演算です。

①のSELECT文: ISBNコードで貸出表を検索し、最も新しい貸出日のレコードを取得する。返却日がNULLの場合は"貸出中"を、日付が設定されている場合は"貸出可"を表示する。
②のSELECT文: 対象となる書籍に貸出実績がない場合に限り、対象のISBNコード及び"貸出可"のレコードを表示する。

貸出実績のある書籍の場合には①から1レコードが返され、②は何も返しません。逆に貸出実績のない書籍の場合には①は何も返さず、②から1レコードが返される仕組みになっています。結果として、どちらの場合でも"貸出中"または"貸出可"を表示する1行だけが表示されることになります。

〔aについて〕
「CASE WHEN」に続く字句が問われています。この列名は「AS」を使って"書籍状態"と命名されているように、"貸出中"または"貸出可"のいずれかを表示することになります。

SQLのSELECT文でCASE句を使用すると、プログラムのif～else構造のように列の値が条件式に合致するかどうかによって返す値を変えることができます。

CASE
　WHEN {条件式} THEN {真の場合の値}
　WHEN {条件式} THEN {真の場合の値}
　：
　ELSE {偽の場合の値}
END

今回は、"返却日"がNULLのときに"貸出中"を、"返却日"が日付である(NULLでない)ときに"貸出可"を表示したいので、

CASE
　WHEN 貸出表.返却日 IS NULL THEN "貸出中"
　ELSE "貸出可"
END

または、

CASE
　WHEN 貸出表.返却日 IS NULL THEN "貸出中"
　WHEN 貸出表.返却日 IS NOT NULL THEN "貸出可"
END

という記述で分岐させることになります。この問では下の構文を使用しています。したがって「エ」のSQL文が適切です。

∴a＝エ

返却日がNULLでないときに"貸出中"と表示されるので不適切です。
返却日がNULLでないときに"貸出中"と表示されるので不適切です。
返却日がNULLのときに"貸出可"と表示されるので不適切です。
正しい。

〔bについて〕
①のSQLのWHERE句を見ると、貸出表のレコードをISBNコードで絞り込み、さらに貸出日が副問合せの結果と一致する行のみを表示対象としています。

まず、貸出表からISBNコードで絞り込むと、次のように対象のISBNコードをもつレコードの貸出レコード一覧となります。

本文中にも説明されているように、"貸出中"／"貸出可"の判断の基準となるのは"対象ISBNコードをもつレコードのうち最も新しい貸出日のレコード"ですから、副問合せは最も新しい貸出日、すなわち貸出日の値が最も大きい日付を返す必要があります。

貸出表から最も新しい日付の値を得るには、対象のISBNコードでレコードを絞り込んだ後、MAX(貸出表.貸出日)で最も大きい値を得ればOKです。この日付が副問合せの結果となります。

∴b＝イ：MAX(貸出表.貸出日)

副問合せからは全貸出日のリストが返されるので誤りです。
正しい。
副問合せからは最も古い貸出日が返されるので誤りです。
副問合せからは全貸出日のリストが返されるので誤りです。

設問2

2018年4月1日から2019年3月31日までの間に4回以上貸し出した書籍の一覧を取得することにした。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。

c に関する解答群

解答選択欄

解答

c=イ

解説

SQLにおいて日付での絞り込みなど、ある列の値が一定範囲内であるレコードを抽出したいときにはBETWEEN句を使用します。

WHERE 列名 BETWEEN 下限値 AND 上限値

BETWEEN句で対象期間に貸し出されたレコードを絞り込んだ後、ISBNコードと書籍名でグループ化します。さらに、HAVING句でレコード数が4以上のグループに絞り込めば期待通りの結果を得られます。

∴c＝イ

"2018年4月1日以降"または"2019年3月31日以前"という条件で絞り込んでいるので誤りです。これだと全レコードが選択されてしまうので、全期間中で4回以上貸し出した書籍を表示する結果となります。
正しい。
「COUNT(*) >= 4」の部分で構文エラーとなります。WHERE句の比較条件として集計関数を使用することはできません。
貸出日を含めてグループ化してしまうと、貸出日ごとにグループが作られるため1グループ1レコードとなってしまいます。HAVING句で「COUNT(*) >= 4」を指定すると何も表示されない結果となります。

設問3

従業員と資料室担当者の利便性を向上させる目的で，所蔵する書籍を管理するデータベースを再構築することにした。
　データベースの再構築に当たり，従業員と資料室担当者から要望が出された。次の記述中の　に入れる適切な答えを，解答群の中から選べ。

〔従業員と資料室担当者からの要望〕

要望1: ISBNコードが同じ書籍を複数冊所蔵できるようにしたい。
要望2: 書籍の購入日を管理できるようにしたい。
要望3: ISBNコードごとに所蔵する書籍数及び貸出し中の書籍数(以下，貸出中件数という)が分かるようにしたい。
要望4: ISBNコードが同じ書籍は同じラックに保管して，書籍が収納されているラックが分かるようにしたい。

　従業員と資料室担当者からの要望を反映したデータベース構成案を，図2に示す。下線付きの項目は主キーを表し，下破線付きの項目は外部キーを表す。

〔要望に対するデータベース修正内容〕

修正1: 要望1に対応するために書籍表を追加して，資料室で所蔵している各書籍に一意の書籍番号を割り振って，それを主キーとした。また，貸出表のISBNコードを書籍番号に変更した。
修正2: 要望2に対応するために書籍表に購入日を設けた。
修正3: 要望3に対応するために書籍管理ビューを追加した。
修正4: 要望4に対応するためにラック表を追加して，書籍情報表に外部キーとしてラック番号を追加した。

　要望を反映したデータベース構成案では，既に所蔵している書籍とISBNコードが同じ書籍を追加購入した場合に，レコードを追加する必要のある表はdである。
　また，需要がなくなった書籍を廃棄する場合は，ISBNコードが同じ書籍を全て廃棄する。データベースに対して行う操作は，次の①～④を，eの順序で行う必要がある。

書籍情報表の主キーが対象ISBNコードのレコードを削除する。
書籍表から対象ISBNコードに対応する書籍番号を抽出する。
書籍表の対象ISBNコードに対応するレコードを削除する。
貸出表の対象書籍番号に対応するレコードを削除する。

d に関する解答群

書籍表
書籍表及びラック表
書籍情報表及び書籍表
書籍情報表，書籍表及びラック表

e に関する解答群

②→①→③→④
②→①→④→③
②→③→①→④
②→③→④→①
②→④→①→③
②→④→③→①

解答選択欄

解答

d=ア
e=カ

解説

〔dについて〕
選択肢には、書籍表、書籍情報表、ラック表があるのでそれぞれについて考えてみます。既に所蔵している書籍と同じものを追加購入するということがポイントです。

書籍表: 所蔵している全ての書籍には一意の書籍番号を割り振って管理します。書籍を追加購入した場合には、既に所蔵している書籍と同じであるなしにかかわらず書籍表にレコードを追加しなければなりません。
書籍情報表: 既に所蔵している書籍があるときには、その書籍情報を保持する書籍情報表のレコードが存在しているはずです。書籍表のレコードと書籍情報表のレコードはISBNコードで関連付ければ良いので、書籍情報表に新たなレコードを追加する必要はありません。
ラック表: ISBNコードが同じ書籍は同じラックに保管することになっています。既に所蔵している書籍があるときには、その書籍情報を保持する書籍情報表のレコードが存在していて、そのレコードのラック番号で保管するラックが決められています。よって、ラック表に新たなレコードを追加する必要はありません。

したがって、レコードを追加する必要がある表は「書籍表」だけです。

∴d＝ア：書籍表

〔eについて〕
外部キーが設定されている列と、その外部キーの参照先となっている列には、表間の整合性を保つために参照制約というものが課せられます。参照制約とは、外部キーを持つレコードを追加する場合に、その外部キーの値は参照先のレコードの主キーとして存在するものでなければならない、また、別表から主キーの値を参照されているレコードは削除することができないという制限です。データベースからレコードを削除するときには、表間の参照制約に違反しない順序で削除を行う必要があります。

同じ書籍全てを廃棄する際にレコードを削除する必要のある、書籍表、書籍情報表、貸出表には以下の参照関係があります。

別表から参照されているレコードは削除できないのですから、最初にどの表からも参照されていない「貸出表」のレコードを削除し、その次に貸出表だけから参照されていた「書籍表」のレコードを削除し、最後に「書籍情報表」のレコードを削除すれば参照制約に違反せずに削除可能です。本文中の番号で言えば「②→④→③→①」となります。

∴e＝カ：②→④→③→①

令和元年秋期午後問題一覧

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