HOME»基本情報技術者平成30年春期問題»午後問3
基本情報技術者過去問題 平成30年春期 午後問3
⇄問題文と設問を画面2分割で開く⇱問題PDF問3 データベース
小学生を対象とした，ある子供会の名簿を管理する関係データベースに関する次の記述を読んで，設問1～4に答えよ。
D子供会は，小学校に入学するときに入会を受け付け，小学校を卒業したら退会する。D子供会では，会員名簿を管理するためのデータベースを構築して，会の運営に活用している。
このたび，児童のイベントへの参加実績を記録するために，活動表とイベント表を追加した。
データベースの表構成とデータ格納例を図1に示す。下線付きの項目は，主キーを表す。
D子供会は，小学校に入学するときに入会を受け付け，小学校を卒業したら退会する。D子供会では，会員名簿を管理するためのデータベースを構築して，会の運営に活用している。
このたび，児童のイベントへの参加実績を記録するために，活動表とイベント表を追加した。
データベースの表構成とデータ格納例を図1に示す。下線付きの項目は，主キーを表す。
設問1
6年生を対象に実施するイベントの案内を配布するために，6年生の保護者の氏名と住所を抽出する。ここで，同一の保護者は重複して抽出しない。また，同じ住所に氏名が同じ保護者は，複数人いないものとする。正しいSQL文を，解答群の中から選べ。
解答群
解答選択欄
解答
- ウ
解説
- SQL文にIN句が含まれる場合には、まずIN句の副問合せが返す結果から考えるのが定石です。SELECT 児童表.保護者番号 FROM 児童表 WHERE 児童表.学年 = 6この副問合せは、児童表から"学年"列の値が6である保護者番号の集合を返します。主問合せ側のWHERE句では、保護者番号をNOT IN句で比較しているため、副問合せの結果セットに含まれない行が選択されます。この結果、6年生の児童を持たない保護者が表示されることになります。よって誤りです。
- 保護者表と児童表を自然結合（列名 = 列名）していないことが問題です。通常であれば、保護者表と児童表を共通列で自然結合し、その中から"学年"が 6 の行を選択するという流れになりますが、自然結合の指定がないとFROM句で指定した表の「直積」から行を選択することになります。
直積は、2つの関係(表)に含まれる要素のすべての組合せから成る表ですから、児童と保護者の全ての組合せが存在します。つまり、6年生の児童1人に対して全ての保護者が関連付けられてしまうことになります。この直積に対して学年が6の行を選択しても、表示されのは保護者表の全ての保護者名と住所になります。 - 正しい。図1を用いて抽出過程を示します。
- 保護者表と児童表を保護者番号で結合する。
- 上の中間表から"学年"列の値が6の行を抽出する。
- 保護者氏名と住所を抜き出す。
- 保護者表と児童表を保護者番号で結合する。
- HAVING句で「グループ化列または集計関数」以外を指定しているためエラーになります。
設問2
イベント番号が 18001 のイベントに参加した児童のうち，1年生である児童の保護者の保護者番号と氏名を抽出する。ここで，同一の保護者は重複して抽出しない。次のSQL文の に入れる正しい答えを，解答群の中から選べ。
a に関する解答群
解答選択欄
- a:
解答
- a=エ
解説
- FROM句に活動表を指定していないので、イベント表と児童表の関連付けができません。また、表同士の自然結合が未指定なので、3つの表の直積演算の結果に対してWHERE句の条件を適用することになります。この結果、保護者表のすべての保護者番号と保護者氏名が表示されます。
- FROM句に活動表を指定していないので、イベント表と児童表の関連付けができません。
- HAVING句で、グループ化列以外である「児童表.学年」を用いているため構文エラーになります。
- 正しい。3つの表を結合した後、学年"1"とイベント番号"18001"を条件にして行を選択し、選択された行の保護者番号と保護者氏名を表示します。
設問3
イベント名と，そのイベントに参加した児童の数を表示する。次のSQL文の に入れる正しい答えを，解答群の中から選べ。ここで，イベント名は全て異なるものとする。
b に関する解答群
- AVG(活動表.イベント番号)
- COUNT(*)
- MAX(活動表.イベント番号)
- SUM(活動表.イベント番号)
解答選択欄
- b:
解答
- b=イ
解説
図1の例を用いて活動表とイベント表を結合し、イベント名でグループ化すると以下のようになります。
- AVG()は平均値を求める集計関数です。"イベント番号"の値の平均値が表示されるので誤りです。
- 正しい。グループごとに行数を数えればイベントに参加した児童数が集計できます。
- MAX()は最大値を求める集計関数です。"イベント番号"の値のうち最大値が表示されるので誤りです。
- SUM()は合計値を求める集計関数です。"イベント番号"の値の合計が表示されるので誤りです。
設問4
年度の切替えのために，次に示す手順で表を更新する。(1)，(2)は入会前の準備のために3月31日に実行し，(3)～(7)は6年生が退会した4月1日に実行する。次のSQL文の に入れる正しい答えを，解答群の中から選べ。
〔手順〕
〔手順〕
- 新入会児童の保護者のうち，未登録の保護者を登録する。
- 新入会児童を登録する。このとき，学年の値は0とする。
- 活動表のレコードを全て削除する。
- 児童表の全ての児童に対して，学年の値に1を加える。
- 児童表から，学年の値が7の児童を削除する。
- 次のSQL文を実行して，保護者表から，在籍する児童がいなくなった保護者を削除する。
- イベント表のレコードを全て削除してから，新年度の計画に合わせてイベントを登録する。
c に関する解答群
解答選択欄
- c:
解答
- c=エ
解説
(5)までの処理で、新1年生の行が追加され、子供会を退会した旧6年生の行は削除されています。(6)は保護者表の更新処理になります。
この処理で削除すべきは「在籍する児童がいなくなった保護者」の行です。児童が在籍する保護者の一覧は、児童表から得られますから、保護者表の各行ごとにこの一覧と比較し、その一覧に当該保護者番号が含まれなければ児童は在籍していないと判断できます。
児童が在籍する保護者番号の一覧は下記のSELECT文で得られます。
この処理で削除すべきは「在籍する児童がいなくなった保護者」の行です。児童が在籍する保護者の一覧は、児童表から得られますから、保護者表の各行ごとにこの一覧と比較し、その一覧に当該保護者番号が含まれなければ児童は在籍していないと判断できます。
児童が在籍する保護者番号の一覧は下記のSELECT文で得られます。
SELECT 児童表.保護者番号 FROM 児童表
児童の在籍がない保護者だけを抽出したいので、WHERE句にはNOT IN句を指定し、副問合せの保護者番号一覧に存在しない保護者番号を持つ行だけを抽出します。したがって「エ」のSQL文が適切です。- 保護者表において"保護者番号"列は主キーでありNOT NULL制約が課せられています。「保護者番号＝NULL」の行は存在しないため、WHERE句の結果は常に偽となり、どの行も削除対象になりません。
- 児童表の学年が7の行（旧6年生の行）は、(5)の処理において削除されているため、どの行の削除対象になりません。もし(5)の前に実行したとしても、この条件では旧6年生の保護者を無条件に削除対象としてしまうため、弟・妹が在籍中であってもその保護者は削除されてしまいます。
- このSQL文を実行すると児童が在籍する保護者が削除されてしまいます。
- 正しい。