今回は、応用情報技術者試験、平成23年度午後試験のデータベースについて解説していきます。
実際に使われた問題については、以下のリンクよりダウンロードください。
>>応用情報技術者試験 平成23年度過去問
なお、問題文・設問については省略してありますので、上記リンクから参照ください。
それではさっそく解説に参りましょう。
もくじ
平成23年度秋期午後問題 データベース 解説
設問1
難易度：★☆☆☆☆
データベースでは非常にありふれた問題なので、確実に正解したい問題です。
まず、図1から以下の2点が分かります。
- a：1つの申請書に対して、複数の明細行が記載されている。逆に、明細行は必ず1つの申請書に紐づく形になっている。
⇒1つの明細行に対して、申請書は必ず1つ。1つ申請書に対して、明細行は1つ以上。 - b：1つの明細行に対して、必ず1つの費用種別コードが紐づく。逆に、1つの費用種別コードは、複数の明細行と紐づく（参照される）。
⇒1つの明細行に対して、費用種別コードは必ず1つ。1つの費用種別コードに対して、明細行は0以上。
したがって、
設問1 a, bの解答は
a：→
b：←
となります。
設問2
難易度：★☆☆☆☆
SQLとしては非常に易しく、これも確実に正解したいですね。
SQLを苦手にしている方は、以下のコツにしたがうと、コツをつかみやすいと思います。実際、僕はSQLは苦手でしたが、このやり方をするようになってからSQL問題を解く力が格段に上がりました。
- いきなりSQLを見ない。（←重要）
- 問題文からSQLに必要な要件を探し出す。
- テーブルの結合、絞り込み条件、グルーピング条件を考える
詳細については、別記事で解説していきたいと思います。
まず、SQLを書くための要件に当たる部分を文章中から探し出します。SQL直前の以下の記述に着目。
要件①：台紙には、申請書番号、組織名、氏名の他に、証憑を必要とする明細行番号、日付、費用種別名及び金額を印字する。
要件②：指定された申請書番号から、証憑を必要とする明細行を取り出すSQL文…
上記の項目をデータベースから引っ張ってくるためには、「申請明細」テーブルと「費用種別」テーブルのテーブルを結合すればいいことが想像できます。
この場合は、「親書番号、組織名、氏名、明細行番号、日付、費用種別、金額」を出力できればいいですね。表2のテーブルを見ると、上記のデータを含むのは「申請明細」テーブルと「費用種別」テーブルなので、これらを結合すればよさそうです。結合するときは、「費用種別コード」でしょう。これで要件①は満たせます。
ここまででいったんSQLを見ましょう。すると、INNER JOINで先ほどの2つのテーブルを結合していることが分かります。結合条件が空欄になっているので、先ほどの「費用種別コード」で結合する一文として以下を入れればよいでしょう。
設問2 c：申請明細.費用種別コード=費用種別.費用種別コード
次に絞り込み条件であるWHERE句について考えましょう。要件②には、「指定された申請書番号」から「証憑を必要とする明細行を取り出す」とあるので、結合したテーブルからどのデータを取り出すか、という条件が導けます。
「"：申請書番号"」が検索する申請書番号とあるので、WHERE句で絞り込みをかけると予想できます。
また、「証憑を必要とする明細行を取り出す」ためには、証憑フラグが「Y」となっているレコードをを取り出せばよいですね。これもWHERE句で絞り込めばいいです。したがって、答えは、
設問2 d：費用種別.費用種別コード=Y
設問3
難易度：★★☆☆☆
こちらもSQLの問題。難易度としては初級～中級レベルで決して難しくはないので確実に正解しましょう。
SQLに必要な要件を文章中から探し出します。まずは、以下の記述に着目。
要件①：各組織における1か月間の旅費交通費の合計を集計しレポートを出力する。
要件②：集計は、社員が申請時に所属していた組織を基準にして行う。
要件③：レポートには、組織コード、組織名及びその月の旅費交通費（日当を含む）の合計を印字する。
要件④：レポートは組織コードの昇順に出力する。
要件①、③からまずは「組織コード、組織名、1か月間の旅費交通費」を出せばよいようです。組織コードと組織名は「組織」テーブルから、1か月間の旅費交通費は、抽出データを1か月間に絞ったうえで「申請明細」の金額を合計すればよいので、この2つのテーブルを結合すればよいですね。ただし、図2から、この2つのテーブルには直接リレーションはないので、「申請書」テーブルも結合する必要があります。
一旦、ここまでの情報を整理すると、
①：組織、申請書、申請明細の3つのテーブルを結合する。 ⇒ 何らかの方法で結合（INNER JOIN か WHERE句での等結合かな？）
②：結合したデータを1か月間に絞る ⇒ WHERE句内でBETWEENや不等号を使いそう
③：1か月の旅費交通費を求める ⇒ SUMを使いそう
という予想がつきます。ここで、自分の予想が合っているかという確認と情報整理のために、一旦SQLに戻りましょう。（ここまで余裕な人は、SQLに戻らなくてもOK）
eは「（）」と「AS 組織交通費合計」が直後にあるので、SUMですね。
また、WHERE句内のfについては、直後に「：指定月開始日 AND ：指定月終了日」とあります。これは、結合したテーブル全体から、指定した1か月間のデータのレコードだけ抽出していると予想できます。日付を「AND」でつないでいるので、これは「BETWEEN」が入りますね。したがって、
設問3 e：SUM
設問3 f：BETWEEN
です。
そして最後に、要件②、④を満たすSQLを作ります。要件①、②には「組織を一つの単位としてまとめて出力したい」とあるので、組織名や組織コードに対して、GROUP BYを使うと予想できます。
また、要件④はそのまま、組織コードの昇順に並べ替えればいいので、組織コードに対して、ORDER BYとASCを使うと予想できます。
全ての要件についてどんなSQLを使えばいいか分かったところで、SQL文に戻ると、GROUP BYの記述は既にあって、そのあとが空欄になっています。したがって、ここは
設問3 g：ORDER BY 申請書.組織コード ASC （ASCは省略可）
設問4
難易度：★★★☆☆
応用情報におけるデータベースの試験ではよく問われるパターンの問題です。多少考える力は必要ですが、決まった型として解ける問題なので、しっかり解答できるようにしましょう。
この問題を考えるポイントは以下の2つです。
- 出張時は、出発日から帰着日までの各日に日当がつく。日当は、出張時の職位によって 表1に従い請求する。
- 旅費交通費の申請は、費用の発生日から1か月以内に行う。
ある程度高得点が取れるようになると、この文言を見ただけで、「この部分は設問で問われるな」という感覚が身に付きます。
上記2点からわかるのは、旅費申請をした時の職位コードと、実際に出張した時の職位コードは必ずしも一致しないということです。
例えば、ある社員が2020年3月20日に出張をして、2020年4月10日に出張費の申請をするとしましょう。設問には、「職位コードを基に自動計算する」とありますが、これは最新の職位コードを使うことを意味しており、出張当時2020年3月20日時点での職位コードではありません。
上記の例の、ある社員というのが2020年3月まで課長だった人で、4月から部長に昇進していたとすると、課長の職位として日当がつくべきところが、部長の職位として日当がついてしまいます。
このように、出張当時の職位と、申請時の職位が一致しない場合に誤った金額計算になると考えられます。
したがって、このことをまとめると、
設問4：社員の出張時の職位と申請時の職位が異なる場合
が解答例になります。
このような設問は、思考力が問われているというふうに考えがちですが、データベースあるある問題なので、しっかり対策して確実に正解したいですね。
さいごに
今回は、応用情報技術者試験・平成23年度秋期・データベースの過去問について解説をしました。
この年の問題はそれほど難易度は高くなかったように思いますので、高得点が取れた人は自信をもって、うまく取れなかった人はしっかりと基礎固めをするようにしましょう。
それでは。