基本情報技術者 2010年 春期 午後 問03
関係データベースに関する次の記述を読んで、設問1〜4に答えよ。
ある中学校では、これまで表計算ソフトを使用して管理していた試験の成績をデータベース化することになった。この中学校に在籍している、又は過去に在籍したことがある生徒及び教員の情報を管理するデータベースは既に運用中であり、これらと連携させて運用する。
現在運用中のデータベースの構造は図1のとおりである。下線付きの項目は主キーを表す。
この中学校は、1年度が3期に分かれており、それぞれの期中に中間試験と期末試験を実施している。試験科目は、国語、数学、英語、理科、社会の5科目である。また、試験は学年別、科目別に統一した問題で行われている。
設問1：
問題文を見る 成績を管理するデータベースの構造について、案A及び案Bが提案された。次の記述中のに入れる正しい答えを、解答群の中から選べ。
案A 生徒番号、年度、試験IDを主キーとして管理する。
案B 生徒番号、年度、試験ID、科目IDを主キーとして管理する。
データベースを設計する上で、拡張性の考慮は重要である。その点、案Bは案Aと比較してaに対して柔軟に対応することができる。その反面、表の増加に伴い、データを検索する際のSQL文が複雑になりやすく、また、1人の生徒の1年間の成績を格納するために必要な成績表のレコード件数は、案Aではb件で済むが、案Bではc件となる。扱うデータの総量にもよるが、長期の運用を考えた場合、これらの点はデータ操作時の性能にも影響を与えるおそれがある。
データベースの設計においては、運用時の利用形態を想定した上で、操作性、拡張性、実行性能などを多角的に評価する必要がある。この中学校では操作性を重視して案Aを採用した。
aに関する解答群
ア：学期の増減
イ：試験回数の増減
ウ：試験科目の増減
エ：生徒の増減
b, cに関する解答群
ア：2
イ：4
ウ：6
エ：12
オ：24
カ：30
キ：48
ク：60
模範解答
a：ウ、b：ウ、c：カ
解説
解答の導き方
この設問では、案Aと案Bで「成績表の1レコードが何を表すか」を考えると、空欄を判断できます。レコードとは、表の1行分のデータです。
a：案Bが柔軟に対応できるもの
案Aでは、生徒番号、年度、試験IDで1レコードを特定し、科目ごとの得点を成績表の項目として持ちます。つまり、1人の生徒の1回の試験について、複数科目の得点を1レコードにまとめます。
一方、案Bでは、生徒番号、年度、試験IDに加えて科目IDも主キーに含めます。主キーとは、表の中から特定のレコードを一意に決める項目です。案Bの1レコードは、1人の生徒の1回の試験における、1科目分の得点を表します。
そのため、科目が増えたとき、案Aでは成績表に科目の得点欄を追加する必要があります。案Bでは、科目表に新しい科目を登録し、その科目の得点を成績表に追加すればよく、成績表の項目を増やす必要がありません。科目が減る場合も、案Bでは対象の科目のデータを扱わないようにできます。
ほかの選択肢も確認します。試験回数が増えても、試験IDを使って新しい試験を登録できます。生徒が増える場合は、生徒のデータが増えるだけです。したがって、案Bが案Aより柔軟なのは「試験科目の増減」であり、aはウです。
b、c：1人の生徒の1年間に必要なレコード件数
問題文には、1年度は3期に分かれ、各期に中間試験と期末試験を実施するとあります。したがって、1年間の試験回数は次のとおりです。
回
案Aでは、1レコードに1回の試験の全科目分の得点をまとめます。つまり、試験1回につき1レコードです。1年間では6回試験を受けるので、成績表は6件必要です。選択肢ではウです。
案Bでは、1レコードに1回の試験の1科目分の得点を格納します。試験は国語、数学、英語、理科、社会の5科目で行われるため、試験1回につき5レコード必要です。1年間では次の件数になります。
件
したがって、案Bでは30件必要で、選択肢ではカです。
このように、案Bは科目の追加・変更に対応しやすい一方、案Aよりレコード件数が多くなります。また、案Bでは科目名や試験名と得点を結び付けて検索するために、複数の表を組み合わせる必要があります。SQL（Structured Query Language：データベースのデータを検索・追加・変更するための言語）の記述が複雑になりやすいことも、問題文にあるとおりです。
誤りやすいポイント
- 案Aでも試験回数が増えたら、成績表に試験ごとの列を追加すると思い込むことがあります。案Aでは試験IDを使うため、試験ごとにレコードを追加できます。
- 案Aのレコード件数を、試験回数に科目数を掛けて数えてしまうことがあります。案Aは1レコードに全科目の得点をまとめるため、科目数を掛けません。
- 案Bのレコード件数を、試験回数だけで数えてしまうことがあります。案Bは1レコードが1科目分なので、試験回数に科目数を掛けます。
- 生徒の人数を掛けてしまうことがあります。ここで求めるのは「1人の生徒」の1年間分なので、生徒の人数は計算に含めません。
FAQ
Q: 案Bで科目を増やすとき、何を追加しますか？
A: 科目表に科目を登録し、その科目の得点を成績表に追加します。案Aのように、成績表に科目の得点欄を追加する必要はありません。
A: 科目表に科目を登録し、その科目の得点を成績表に追加します。案Aのように、成績表に科目の得点欄を追加する必要はありません。
Q: 案Bの主キーに科目IDを含めるのはなぜですか？
A: 同じ生徒・年度・試験でも、科目ごとに得点があるためです。科目IDを加えることで、科目ごとの得点を別々のレコードとして特定できます。
A: 同じ生徒・年度・試験でも、科目ごとに得点があるためです。科目IDを加えることで、科目ごとの得点を別々のレコードとして特定できます。
Q: 1年間の試験回数が6回になるのはなぜですか？
A: 3期それぞれに中間試験と期末試験があるため、回です。
A: 3期それぞれに中間試験と期末試験があるため、回です。
関連キーワード: 関係データベース、主キー、複合主キー、SQL、データベース設計
設問2：
問題文を見る 2009年度1学年の2学期中間試験を対象に、生徒ごとの全科目合計点を求め、降順に整列して表示する。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT 生徒表.氏名, クラス, 国語 + 数学 + 英語 + 理科 + 社会
FROM 生徒表, 履歴表, 成績表, 試験表
WHERE 生徒表.生徒番号 = 履歴表.生徒番号
AND 履歴表.生徒番号 = 成績表.生徒番号
AND 成績表.試験ID = 試験表.試験ID
AND
ORDER BY 国語 + 数学 + 英語 + 理科 + 社会 DESC
解答群
ア：履歴表.年度 = 2009 AND 履歴表.学年 = 1 AND 試験表.試験名 = '2学期中間'
イ：履歴表.年度 = 2009 AND 履歴表.学年 = 1 AND 試験表.試験名 = '2学期中間' AND 成績表.年度 = 2009
ウ：履歴表.年度 = 2009 AND 履歴表.学年 = 1 AND 試験表.試験ID = (SELECT 試験ID FROM 試験表 WHERE 試験表.試験名 = '2学期中間')
エ：履歴表.年度 = 2009 AND 履歴表.学年 = 1 AND 試験表.試験ID = ANY(SELECT 試験ID FROM 試験表 WHERE 試験表.試験名 = '2学期中間')
模範解答
イ
解説
解答の導き方
求めるのは「2009年度1学年の2学期中間試験」について、生徒ごとに5科目の点数を合計し、高い順に並べた結果です。
SQL（Structured Query Language：データベースに問い合わせや操作を行う言語）では、FROMで対象の表を指定し、WHEREで表どうしを結び付けたり、取り出す行を絞り込んだりします。
まず、WHEREには、表どうしを結び付ける条件が書かれています。生徒番号で生徒表・履歴表・成績表を結び付け、試験IDで成績表と試験表を結び付けています。このように、共通する項目を使って複数の表を結び付けることを「結合」といいます。
次に、求める年度・学年・試験を条件で指定します。履歴表.年度 = 2009と履歴表.学年 = 1で、対象の生徒の履歴を絞れます。また、試験表.試験名 = '2学期中間' で、中間試験に関する試験を選べます。
ただし、これだけでは成績表の年度は絞り込まれません。成績表と履歴表を結び付けている条件は、生徒番号が一致することだけです。そのため、履歴表から2009年度の行を選んでも、成績表から2009年度以外の点数が結び付く可能性があります。試験名も年度ごとに繰り返し使われるため、成績表の年度も条件に加える必要があります。
したがって、成績表.年度 = 2009も指定するイが適切です。この条件によって、2009年度の成績に絞れます。
選択する項目には国語 + 数学 + 英語 + 理科 + 社会があり、ORDER BYにも同じ合計式が指定されています。さらにDESCは降順、つまり合計点が高い生徒から並べる指定です。
誤りやすいポイント
- 履歴表.年度 = 2009を指定すれば、成績表の年度も自動的に2009年度になると思い込むことです。年度を絞り込む条件は、それぞれの表に対して必要です。
- アは、成績表.年度 = 2009がありません。そのため、2009年度以外の成績が対象に含まれる可能性があります。
- ウのように試験ID = (SELECT ...) とすると、= の右側には1つの値が必要です。しかし、試験名だけでは複数の試験IDが該当する可能性があるため、1つに決まるとは限りません。
- エのANYは、かっこ内の複数の試験IDのいずれかに一致するかを調べられます。しかし、試験名だけで選んだ複数の試験IDには別の年度のものも含まれ得ます。成績表.年度 = 2009がない問題は解決しません。
FAQ
Q: 履歴表.年度と成績表.年度は、同じ意味の項目ではないのですか？
A: どちらも年度を表しますが、別々の表にある項目です。履歴表.年度の条件だけでは、成績表の行は絞り込まれません。
A: どちらも年度を表しますが、別々の表にある項目です。履歴表.年度の条件だけでは、成績表の行は絞り込まれません。
Q: ANYを使えば複数の試験IDに対応できるのに、なぜエでは不十分なのですか？
A: ANYによって複数の試験IDのどれかに一致させることはできます。しかし、試験IDを試験名だけで選んでいるため、2009年度の試験に限定できません。成績表の年度を指定する条件も必要です。
A: ANYによって複数の試験IDのどれかに一致させることはできます。しかし、試験IDを試験名だけで選んでいるため、2009年度の試験に限定できません。成績表の年度を指定する条件も必要です。
Q: DESCは何を指定していますか？
A: 並べ替えの順番を降順にする指定です。この問題では、5科目の合計点が高い生徒から表示します。
A: 並べ替えの順番を降順にする指定です。この問題では、5科目の合計点が高い生徒から表示します。
関連キーワード: 関係データベース、表の結合、WHERE句、サブクエリ、ORDER BY
設問3：
問題文を見る 2008年度1学年を対象に、1年間に実施したすべての試験の各科目の平均点をクラスごとに求め、担任教員の氏名とともに表示する。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT クラス, 教員表.氏名, AVG(国語), AVG(数学), AVG(英語), AVG(理科), AVG(社会)
FROM 履歴表, 成績表, 教員表
WHERE
ORDER BY クラス
解答群
ア：履歴表.生徒番号 = 成績表.生徒番号 AND 履歴表.教員番号 = 教員表.教員番号 AND 履歴表.年度 = 2008 AND 履歴表.学年 = 1 GROUP BY クラス, 教員表.氏名
イ：履歴表.生徒番号 = 成績表.生徒番号 AND 履歴表.教員番号 = 教員表.教員番号 AND 履歴表.年度 = 2008 AND 履歴表.学年 = 1 AND 成績表.年度 = 2008 GROUP BY クラス, 教員表.氏名
ウ：履歴表.生徒番号 = 成績表.生徒番号 AND 履歴表.年度 = 2008 AND 履歴表.学年 = 1 AND 成績表.試験ID = ANY(SELECT 試験ID FROM 試験表) GROUP BY クラス, 教員表.氏名
エ：履歴表.生徒番号 = 成績表.生徒番号 AND 履歴表.年度 = 2008 AND 履歴表.学年 = 1 AND 履歴表.教員番号 = ANY(SELECT 教員番号 FROM 教員表) GROUP BY クラス, 教員表.氏名
模範解答
イ
解説
解答の導き方
この設問では、2008年度の1学年について、試験の点数をクラスごとに平均し、担任教員の氏名と一緒に表示するための条件を選びます。
まず、表示する情報がどの表にあるかを確認します。図1の履歴表には、生徒番号、年度、学年、クラス、教員番号があります。教員表には、教員番号と氏名があります。また、成績表には生徒番号や試験の年度、各科目の点数があるため、これらの表を必要な項目で結び付けます。
SQL（Structured Query Language：データベースに検索や集計を指示する言語）では、複数の表に共通する番号を条件にして、表の情報を組み合わせます。この処理を表結合といいます。今回必要な表結合は、次の2つです。
- 履歴表と成績表を、生徒番号で結び付けます。これにより、生徒の在籍情報と、その生徒の成績を対応させます。
- 履歴表と教員表を、教員番号で結び付けます。これにより、履歴表に記録された担任教員の氏名を取得できます。
次に、対象となるデータを絞り込みます。履歴表の「年度」と「学年」で、2008年度の1学年の生徒を選びます。さらに、成績表の「年度」も2008に絞ります。生徒番号で成績を結び付けただけでは、同じ生徒の別年度の成績も対象に入る可能性があるためです。
したがって、必要な条件は次のとおりです。
- 履歴表.生徒番号 = 成績表.生徒番号
- 履歴表.教員番号 = 教員表.教員番号
- 履歴表.年度 = 2008
- 履歴表.学年 = 1
- 成績表.年度 = 2008
最後に、クラスごと、かつ教員氏名ごとに集計します。AVGは、指定した項目の平均を求める集約関数です。たとえばAVG(国語)は、対象となる成績の国語の点数を平均します。複数の項目ごとにまとめるGROUP BYには、集計せずに表示する「クラス」と「教員表.氏名」の両方が必要です。
これらの条件と集計方法を満たすのは、成績表の年度も指定しているイです。
誤りやすいポイント
- アは、履歴表の年度と学年は指定していますが、成績表の年度を指定していません。そのため、同じ生徒の別年度の成績まで平均に含まれる可能性があります。
- ウの「成績表.試験ID = ANY(SELECT 試験ID FROM 試験表)」は、試験表にあるいずれかの試験IDと一致するかを調べています。2008年度の試験に限定する条件ではありません。また、履歴表と教員表を教員番号で結び付けていないため、担任教員の氏名を正しく対応させられません。
- エの「履歴表.教員番号 = ANY(SELECT 教員番号 FROM 教員表)」は、履歴表の教員番号が教員表にある教員番号のどれかと一致するかを調べています。教員表の行と履歴表の行を対応させる条件ではないため、担任以外の教員の氏名が表示される可能性があります。また、成績表の年度も指定していません。
- WHEREは対象データを絞り込む部分で、GROUP BYは絞り込んだデータをグループに分ける部分です。選択肢では、空欄にWHEREの条件だけでなくGROUP BYも含まれています。
FAQ
Q: 履歴表.年度と成績表.年度の両方を指定するのはなぜですか？
A: 履歴表.年度は、生徒がその年度に何年生で、どのクラスに在籍していたかを選ぶためです。成績表.年度は、平均の対象にする試験の年度を選ぶためです。目的が異なるので、両方を指定します。
A: 履歴表.年度は、生徒がその年度に何年生で、どのクラスに在籍していたかを選ぶためです。成績表.年度は、平均の対象にする試験の年度を選ぶためです。目的が異なるので、両方を指定します。
Q: なぜGROUP BYにクラスと教員表.氏名の両方を書くのですか？
A: クラスごとに平均点を出し、教員氏名も表示するためです。クラスと教員氏名の組合せでグループを作ることで、その組合せに属する成績の平均を求められます。
A: クラスごとに平均点を出し、教員氏名も表示するためです。クラスと教員氏名の組合せでグループを作ることで、その組合せに属する成績の平均を求められます。
Q: 試験の種類や期を指定しなくてよいのですか？
A: 問題では、1年間に実施したすべての試験が対象です。成績表.年度を2008に指定すれば、その年度に行われた試験の成績をまとめて平均できます。
A: 問題では、1年間に実施したすべての試験が対象です。成績表.年度を2008に指定すれば、その年度に行われた試験の成績をまとめて平均できます。
関連キーワード: 関係データベース、表結合、WHERE句、GROUP BY、集約関数、主キー
設問4：
問題文を見る 生徒“情報太郎”の成績を、試験IDの昇順に表示する。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
なお、生徒“情報太郎”が複数人いた場合は、過去に在籍したことがある生徒も含めて生徒番号の昇順に表示する。
SELECT 生徒表.生徒番号, 成績表.年度, 試験名, 国語, 数学, 英語, 理科, 社会
FROM 生徒表, 成績表, 試験表
WHERE
ORDER BY 生徒表.生徒番号, 成績表.年度, 試験表.試験ID
解答群
ア：生徒表.生徒番号 = 成績表.生徒番号 AND 成績表.試験ID = 試験表.試験ID AND 生徒表.氏名 = '情報太郎'
イ：生徒表.生徒番号 = 成績表.生徒番号 AND 成績表.試験ID = 試験表.試験ID AND 生徒表.氏名 = '情報太郎' AND 生徒表.生徒番号 = (SELECT 生徒番号 FROM 生徒表)
ウ：成績表.生徒番号 = (SELECT 生徒番号 FROM 生徒表 WHERE 氏名 = '情報太郎') GROUP BY 試験表.試験ID
エ：成績表.生徒番号 = (SELECT 生徒番号 FROM 生徒表 WHERE 氏名 = '情報太郎') GROUP BY 成績表.年度
模範解答
ア
解説
解答の導き方
このSQL文では、生徒の名前で対象を絞り込み、成績表と試験表を正しく結び付ける条件をWHERE句（検索する行を絞り込む条件を書く部分）に入れます。
まず、FROM句には生徒表、成績表、試験表があります。これらの表から必要な情報を取り出すには、表どうしで同じ意味をもつ項目の値が一致する行を結び付けます。この条件を結合条件といいます。
- 生徒表と成績表は、生徒番号が同じ行どうしを結び付けます。これにより、成績がどの生徒のものか分かります。
- 成績表と試験表は、試験IDが同じ行どうしを結び付けます。これにより、成績がどの試験のものか分かります。
- 生徒表の氏名が「情報太郎」である行に絞ります。
この3つの条件をすべて満たす必要があるため、条件はANDでつなぎます。選択肢アは、生徒番号による結合、試験IDによる結合、氏名による絞り込みの3条件をそろえています。
また、問題文には「過去に在籍したことがある生徒も含めて」とあります。氏名が一致する生徒を生徒表からすべて抽出すればよく、現在在籍している生徒だけに絞る条件は必要ありません。氏名が同じ生徒が複数人いても、それぞれの生徒番号で成績表と結び付けられます。
最後のORDER BY句は、結果の並べ替え方を指定します。項目は左から順に優先されるため、生徒番号、年度、試験IDの順に並びます。これにより、複数の「情報太郎」がいる場合は生徒番号の昇順になり、その生徒の成績は年度、試験IDの順に並びます。
したがって、必要な条件をすべて満たすのはアです。
誤りやすいポイント
- 氏名だけで成績表と結び付けることはできません。同じ氏名の生徒がいる可能性があるため、生徒番号で結び付けます。
- 生徒番号による結合条件を忘れると、生徒と無関係な成績が組み合わさることがあります。
- 試験IDによる結合条件を忘れると、成績と無関係な試験名が組み合わさることがあります。
- 選択肢イは、サブクエリ（SQL文の中に書く別の検索）で生徒表の生徒番号をすべて取り出そうとしています。生徒が複数いる場合、1つの値として比較できないため、条件として適切ではありません。
- 選択肢ウとエは、氏名が一致する生徒番号をサブクエリで1つだけ取り出す形です。同姓同名の生徒が複数いる場合に対応できません。また、GROUP BYは行をグループにまとめる指定なので、試験ごとの成績を並べて表示する目的には合いません。
FAQ
Q: 氏名で絞り込めば、生徒番号で結び付けなくてもよいですか？
A: よくありません。同じ氏名の生徒が複数人いる可能性があります。生徒番号を使うことで、それぞれの生徒の成績を正しく結び付けられます。
A: よくありません。同じ氏名の生徒が複数人いる可能性があります。生徒番号を使うことで、それぞれの生徒の成績を正しく結び付けられます。
Q: 試験表との結合条件はなぜ必要ですか？
A: 成績表の試験IDと試験表の試験IDを対応させるためです。この条件がないと、成績に関係のない試験名が表示されるおそれがあります。
A: 成績表の試験IDと試験表の試験IDを対応させるためです。この条件がないと、成績に関係のない試験名が表示されるおそれがあります。
Q: ORDER BYの項目はどの順に使われますか？
A: 左から順に優先されます。このSQL文では、まず生徒番号、次に年度、最後に試験IDで並べ替えます。
A: 左から順に優先されます。このSQL文では、まず生徒番号、次に年度、最後に試験IDで並べ替えます。
関連キーワード: 関係データベース、SQL、結合条件、WHERE句、ORDER BY句、サブクエリ
