基本情報技術者 2011年 秋期 午後 問02
従業員データベースの設計と運用に関する次の記述を読んで、設問1〜4に答えよ。
C社は、2011年4月1日の組織編成の変更に伴い、従業員データベースの再構築を行った。組織編成の変更前は図1に示すとおり、部だけで編成されていたが、事業の拡大及び従業員数の増加に合わせて、図2に示すとおり、部と課からなる組織編成となった。
設問1：
問題文を見る 組織編成の変更を反映するために、図3に示す表中の部に関する情報の変更について、A案とB案を考えて比較検討した。図4に示すA案では、部名と課名の組合せに対して一意の部署コードを割り当てた。図5に示すB案では、部名と課名のそれぞれにコードを割り当て、従業員表の部コードを課コードに変更した。次の記述中のに入れる適切な答えを、解答群の中から選べ。
最初は、部名と課名の組合せに対して一意の部署コードを割り当てた、A案によって管理しようとした。しかし、これでは、aを変更する必要が生じた場合に複数行を修正する必要があるので、正規化におけるbの観点から好ましくない。また、例えばcを表示する際にLIKE述語を使用したデータ依存の検索が必要になるなど、柔軟性が低いことが分かった。このため、B案の構成でデータベースを再構築した。
aに関する解答群
ア：課名
イ：氏名
ウ：表名
エ：部名
bに関する解答群
ア：関係喪失
イ：検索性能
ウ：事前登録
エ：重複更新
cに関する解答群
ア：ある課に属する従業員の氏名の一覧
イ：ある部に属する従業員の氏名の一覧
ウ：従業員の氏名の一覧
エ：部署名の一覧
模範解答
a：エ、b：エ、c：イ
解説
解答の導き方
まず、図4のA案と図5のB案で、組織の情報をどのように保存しているかを比べます。表は、同じ種類の情報を行と列に整理したものです。
図4のA案では、部名と課名を組み合わせた名前に対して、部署コードを割り当てています。従業員表には、その部署コードが記録されています。一方、図5のB案では、部表と課表を分けています。課表には、その課がどの部に属するかを示す部コードがあり、従業員表には課コードがあります。
aは、部名を変更する場合を考えます。
A案では、部名と課名を組み合わせた情報が、課ごとの行に含まれています。たとえば、同じ部に複数の課があれば、それぞれの課の行に同じ部名が記録されます。そのため部名を変更すると、該当する複数の行を修正しなければなりません。選択肢では「部名」に当たるエです。
A案では、部名と課名を組み合わせた情報が、課ごとの行に含まれています。たとえば、同じ部に複数の課があれば、それぞれの課の行に同じ部名が記録されます。そのため部名を変更すると、該当する複数の行を修正しなければなりません。選択肢では「部名」に当たるエです。
bは、複数行を修正する必要があることの問題点を考えます。
正規化とは、同じ情報を何度も記録しないように、表の分け方を整えることです。同じ部名が複数行に記録されていると、変更時にすべての行を修正する必要があります。修正漏れがあると、同じ部なのに行によって異なる部名が残るおそれがあります。このように、重複している情報を複数箇所で更新する問題を「重複更新」といいます。したがって、選択肢はエです。
正規化とは、同じ情報を何度も記録しないように、表の分け方を整えることです。同じ部名が複数行に記録されていると、変更時にすべての行を修正する必要があります。修正漏れがあると、同じ部なのに行によって異なる部名が残るおそれがあります。このように、重複している情報を複数箇所で更新する問題を「重複更新」といいます。したがって、選択肢はエです。
cは、A案で検索するときにLIKE述語が必要になる例を考えます。
A案の部署名には、部名と課名が連結して入っています。ある部に属する従業員をまとめて探すには、たとえば部署名が「総務部」で始まる行を検索する必要があります。LIKE述語は、SQL（Structured Query Language：データベースを操作・検索するための言語）で、文字列の一部が一致するかを調べる検索条件です。
A案の部署名には、部名と課名が連結して入っています。ある部に属する従業員をまとめて探すには、たとえば部署名が「総務部」で始まる行を検索する必要があります。LIKE述語は、SQL（Structured Query Language：データベースを操作・検索するための言語）で、文字列の一部が一致するかを調べる検索条件です。
このように、部名と課名の文字列を手掛かりにして検索する対象は、「ある部に属する従業員の氏名の一覧」です。選択肢ではイです。B案なら、従業員の課コードから課表をたどり、その課の部コードを確認できます。部名や課名の文字列の形に頼らずに、部と課の関係を使って検索できます。
誤りやすいポイント
- aで「課名」を選ばないようにします。問題になっているのは、同じ部に複数の課がある場合に、各課の行に重ねて記録される部名です。
- bの「重複更新」は、検索に時間がかかるという意味ではありません。同じ情報が複数の行にあり、変更時に複数箇所を直す必要があることを指します。
- cでは、「従業員の氏名の一覧」全体ではなく、「ある部に属する」従業員を探す点に注目します。A案では、部に属する複数の課を文字列の一部から探す必要があります。
- A案でも、ある課だけの従業員を探す場合は、課まで含んだ部署コードを使えます。LIKEが必要になる例として問題にしているのは、複数の課をまとめた「ある部」の従業員一覧です。
FAQ
Q: B案では、従業員表に部コードではなく課コードを持たせるのはなぜですか？
A: 従業員が所属する課を課コードで示せるためです。課表には、その課が属する部の部コードがあります。従業員表から課表、部表の順にたどれば、従業員がどの部に属するかを確認できます。
A: 従業員が所属する課を課コードで示せるためです。課表には、その課が属する部の部コードがあります。従業員表から課表、部表の順にたどれば、従業員がどの部に属するかを確認できます。
Q: LIKEを使う検索が、なぜ柔軟性の低さにつながるのですか？
A: 部名が部署名のどこに、どのような形で含まれているかに検索方法が左右されるからです。部名と課名を別々に管理していれば、文字列の形ではなく、表に記録された部と課の関係を使って検索できます。
A: 部名が部署名のどこに、どのような形で含まれているかに検索方法が左右されるからです。部名と課名を別々に管理していれば、文字列の形ではなく、表に記録された部と課の関係を使って検索できます。
Q: 正規化をすると、表の数は必ず少なくなりますか？
A: 必ず少なくなるわけではありません。情報の重複や、更新時の不整合が起きにくいように表を分けることが目的です。この問題では、部と課を別々の表で管理するB案が、その考え方に沿っています。
A: 必ず少なくなるわけではありません。情報の重複や、更新時の不整合が起きにくいように表を分けることが目的です。この問題では、部と課を別々の表で管理するB案が、その考え方に沿っています。
関連キーワード: データベース正規化、重複更新、表設計、LIKE述語
設問2：
問題文を見る B案の構成でデータベースを再構築した後に、課ごとの平均年齢を算出し、表示する。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT 課表.課コード, 課表.課名, AVG(従業員表.年齢)
FROM 課表, 従業員表
WHERE
解答群
ア：課表.課コード = 従業員表.課コード GROUP BY 課表.課コード, 課表.課名
イ：課表.課コード = 従業員表.課コード GROUP BY 課表.部コード, 課表.課名
ウ：従業員表.年齢 = ANY (SELECT COUNT(従業員表.年齢) FROM 従業員表)
エ：従業員表.年齢 = ANY (SELECT COUNT(従業員表.年齢) FROM 従業員表 GROUP BY 課表.課コード)
模範解答
ア
解説
解答の導き方
この設問は、B案の表から課ごとに従業員の平均年齢を求めるSQLを考える問題です。SQL（Structured Query Language：データベースに問い合わせたり、データを扱ったりするための言語）では、「どの表を使うか」「表の行をどう対応させるか」「何ごとに集計するか」を順に指定します。
まず、図5の従業員表には「課コード」があり、課表にも「課コード」があります。このコードを使えば、従業員が所属する課と課名を対応させられます。
FROMで課表と従業員表を指定しただけでは、両方の表の行が組み合わされます。そこで、同じ課のデータだけを対応させる条件が必要です。WHEREに指定する条件は、次のとおりです。
課表.課コード = 従業員表.課コード
これは、課表と従業員表を課コードで結び付ける条件です。表結合（関連する項目を使って、複数の表のデータを対応させること）を行うことで、従業員の年齢と、その従業員が所属する課を一緒に扱えます。
次に、SELECTには課コード、課名、そして従業員表.年齢の平均を求めるAVGが指定されています。課ごとに平均を出すには、同じ課の従業員をひとまとめにして計算します。そのために使うのがGROUP BYです。
課ごとに分けるので、まとめる項目は課コードと課名です。
GROUP BY 課表.課コード, 課表.課名
SQLでは、WHEREで行を選び、その後にGROUP BYでグループを作ります。したがって、WHEREに続く部分には、課コードを対応させる条件と、課コード・課名でグループ化する指定の両方が必要です。この形に合うのはアです。
誤りやすいポイント
- 課ごとの集計なのに、部コードでグループ化してしまうことがあります。部の中には複数の課があるため、部コードだけでは課ごとに分けられません。
- 表を二つ並べただけで、課コードを対応させる条件を忘れると、従業員と課の正しい組合せになりません。
- AVGは平均を求める関数です。COUNTは件数を数える関数なので、年齢とCOUNTの結果を比較しても、課ごとの平均年齢は求められません。
- 課ごとの平均を出す場合、課コードと課名をGROUP BYに指定します。SELECTに表示する項目のうち、集計関数で計算しない項目をグループ化するためです。
FAQ
Q: 課コードと課名の両方をGROUP BYに指定するのはなぜですか？
A: SELECTで課コードと課名を表示するためです。これらの項目をグループ化すると、課ごとに分けた結果に課コードと課名を表示できます。
A: SELECTで課コードと課名を表示するためです。これらの項目をグループ化すると、課ごとに分けた結果に課コードと課名を表示できます。
Q: 従業員表だけでは、平均年齢を求められませんか？
A: 従業員表だけでも全従業員の平均年齢は求められます。しかし、課ごとに分けるには、従業員の課コードから課名を確認する必要があるため、課表との対応付けが必要です。
A: 従業員表だけでも全従業員の平均年齢は求められます。しかし、課ごとに分けるには、従業員の課コードから課名を確認する必要があるため、課表との対応付けが必要です。
Q: 部コードでまとめると、なぜ課ごとの平均にならないのですか？
A: 同じ部に複数の課があるためです。部コードでまとめると、その部に所属する複数の課の従業員が同じグループに入り、課ごとの平均になりません。
A: 同じ部に複数の課があるためです。部コードでまとめると、その部に所属する複数の課の従業員が同じグループに入り、課ごとの平均になりません。
関連キーワード: SQL、表結合、集約関数、GROUP BY、平均値
設問3：
問題文を見る 従業員表は、受発注情報を管理する表などから、従業員番号を外部キーとして参照される。このため、従来は特に利用を制限せずに社内公開していたが、個人情報保護の観点から、必要最小限の情報だけを公開するビューを作成することにした。ビューで公開する項目は、従業員番号、氏名、課コード、内線とする。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
CREATE VIEW 従業員公開表 AS
解答群
ア：ALTER TABLE 従業員表 ADD (従業員番号, 氏名, 課コード, 内線)
イ：ALTER TABLE 従業員表 DROP 入社年月日, 住所, 自宅電話, 年齢
ウ：SELECT * FROM 従業員表 WHERE 従業員番号 IS NOT NULL
エ：SELECT 従業員番号, 氏名, 課コード, 内線 FROM 従業員表
模範解答
エ
解説
解答の導き方
この設問では、従業員表の中から「従業員番号、氏名、課コード、内線」だけを公開するビューを作るSQLを選びます。
ビューとは、表のデータを別の表のように見せる仕組みです。元の表の中から必要な項目だけを見せることで、住所や自宅電話などを利用者に公開せずに済みます。
SQL（Structured Query Language：データベースを操作するための言語）では、ビューを作るときに、どの表からどの項目を取り出すかを指定します。問題文のSQLは「CREATE VIEW 従業員公開表 AS」で始まっています。その後ろには、ビューに表示するデータを取り出すSELECT文が続きます。
SELECT文では、SELECTの後に取り出したい項目を並べ、FROMの後に取り出す元の表を指定します。今回必要なのは従業員表から「従業員番号、氏名、課コード、内線」の4項目です。したがって、次の形の文が当てはまります。
SELECT 従業員番号, 氏名, 課コード, 内線 FROM 従業員表
これは解答群のエです。必要な4項目だけをビューに表示できるため、個人情報の公開を必要最小限にできます。
ほかの選択肢も確認します。アとイはALTER TABLEを使っています。これは表の構造を変更する命令で、ビューを作る命令ではありません。また、ウの「SELECT *」は従業員表の全項目を取り出します。「従業員番号 IS NOT NULL」は従業員番号が空でない行を選ぶ条件ですが、表示する項目を限定するものではありません。そのため、公開したくない項目も表示されてしまいます。
誤りやすいポイント
- 「従業員番号が空でない行を選ぶ」ことと、「公開する項目を絞る」ことを混同しやすいです。WHERE句は主に行を選ぶ条件を指定し、SELECTの後に並べる項目名は表示する列を指定します。
- 「SELECT 」の「」は全項目を表します。公開する項目を限定する設問では、必要な項目名を一つずつ指定します。
- ALTER TABLEは表そのものの構造を変更する命令です。ビューを作るCREATE VIEWの後ろには、ビューに表示するデータを取り出すSELECT文を指定します。
FAQ
Q: ビューを作ると、元の従業員表から項目が削除されますか？
A: いいえ。ビューで見せる項目を絞っても、元の従業員表の項目は削除されません。利用者から見える内容を限定するための仕組みです。
A: いいえ。ビューで見せる項目を絞っても、元の従業員表の項目は削除されません。利用者から見える内容を限定するための仕組みです。
Q: 「従業員番号 IS NOT NULL」は、何を指定していますか？
A: 従業員番号が空ではない行を選ぶ条件です。表示する項目を限定する指定ではありません。
A: 従業員番号が空ではない行を選ぶ条件です。表示する項目を限定する指定ではありません。
Q: ビューに表示する項目の順番は、どう決まりますか？
A: SELECTの後に項目名を並べた順番です。今回のSQLでは「従業員番号、氏名、課コード、内線」の順に表示されます。
A: SELECTの後に項目名を並べた順番です。今回のSQLでは「従業員番号、氏名、課コード、内線」の順に表示されます。
関連キーワード: ビュー、SELECT文、列、WHERE句、ALTER TABLE
設問4：
問題文を見る 設問3で作成したビューと図6に示す受注表を使用して、営業部海外課に在籍する従業員が、2011年7月1日から2011年9月30日の期間中に受注した案件の受注総額を算出する。営業部海外課の課コードは“S101”で、2011年7月1日以降の従業員の異動はない。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT SUM(受注表.受注額)
FROM 受注表, 従業員公開表
WHERE 従業員公開表.課コード = 'S101' AND
解答群
ア：受注表.従業員番号 = 従業員公開表.従業員番号 AND 受注表.受注日 BETWEEN '20110701' AND '20110930'
イ：受注表.受注日 BETWEEN '20110701' AND '20110930'
ウ：受注表.受注日 IN (SELECT COUNT(*) FROM 受注表 WHERE 受注表.受注日 BETWEEN '20110701' AND '20110930')
エ：受注表.受注日 IN (SELECT SUM(受注表.受注額) FROM 受注表 WHERE 受注表.受注日 BETWEEN '20110701' AND '20110930')模範解答
ア
解説
解答の導き方
求めるのは、営業部海外課に在籍する従業員が、指定された期間に受注した案件の受注額の合計です。SQL（Structured Query Language：データベースを操作するための言語）では、対象となる従業員、受注日、受注額をそれぞれ正しく扱う必要があります。
まず、本文には「営業部海外課の課コードは“S101”」とあります。そのため、従業員公開表から、課コードが'S101'の従業員を選びます。この条件はSQL文にすでにあります。
WHERE 従業員公開表.課コード = 'S101'
次に、その従業員が受注した案件を探します。受注表と従業員公開表を従業員番号で結び付ければ、誰の受注かを判断できます。問題のFROM句には「受注表, 従業員公開表」とありますが、同じ従業員を対応付ける条件はまだありません。そこで、次の条件が必要です。
受注表.従業員番号 = 従業員公開表.従業員番号
さらに、本文には対象期間が「2011年7月1日から2011年9月30日」とあります。受注表の受注日をこの期間に絞る条件も必要です。
受注表.受注日 BETWEEN '20110701' AND '20110930'
BETWEENは、指定した範囲内の値を選ぶ条件です。この場合、開始日と終了日も範囲に含まれます。
この2つの条件を既存の課コードの条件とANDでつなぐと、「課コードが'S101'の従業員による、期間内の受注」を選べます。そのうえで、SUM(受注表.受注額)が対象となる受注額を合計します。
したがって、従業員番号で表を結び付ける条件と、受注日を期間で絞る条件の両方があるアが適切です。
誤りやすいポイント
- 課コードの条件だけでは、どの受注がその従業員によるものか分かりません。受注表と従業員公開表を従業員番号で結び付ける条件が必要です。
- 受注日だけを絞っても、営業部海外課以外の従業員による受注が含まれる可能性があります。
- BETWEENは、期間の開始日と終了日を含みます。
- ウは受注日を期間で直接絞っていません。また、受注日の条件に件数を使っているため、受注日と件数が対応していません。
- エは受注日を受注額の合計と比べています。受注日と金額は意味の異なる項目です。
FAQ
Q: FROM句に2つの表を書けば、自動的に従業員と受注が対応しますか？
A: いいえ。従業員番号のような共通の項目を使って、どの行どうしを対応させるか指定する必要があります。
A: いいえ。従業員番号のような共通の項目を使って、どの行どうしを対応させるか指定する必要があります。
Q: なぜ受注額を合計する前に、従業員番号で表を結び付けるのですか？
A: 結び付けないと、営業部海外課の従業員と無関係な受注まで合計に入ったり、同じ受注が重複して数えられたりするおそれがあるためです。
A: 結び付けないと、営業部海外課の従業員と無関係な受注まで合計に入ったり、同じ受注が重複して数えられたりするおそれがあるためです。
Q: 「2011年7月1日以降の従業員の異動はない」という条件は、何に関係しますか？
A: 期間中に所属が変わっていないため、従業員公開表の課コードを使って、期間中の対象従業員を判断できます。
A: 期間中に所属が変わっていないため、従業員公開表の課コードを使って、期間中の対象従業員を判断できます。
関連キーワード: SQL、表の結合、WHERE句、BETWEEN、集計関数、ビュー
