平成23年秋期試験午後問題 問6
問6 データベース
⇱問題PDF
旅費交通費精算のシステム化に関する次の記述を読んで，設問1～4に答えよ。
旅費交通費精算のシステム化に関する次の記述を読んで，設問1～4に答えよ。
広告
K社は中堅の食品製造会社で，ここ数年急速に売上を伸ばしている。販売力強化のために営業社員も増員を続けているので，伝票によって手作業で行っている旅費交通費の精算処理をシステム化することにした。
システムの設計に当たり，図1に示す現行の申請書を参考にした。
〔旅費交通費精算に関する規定〕
K社における旅費交通費精算に関する規定の一部を次に示す。
〔旅費交通費精算システムのデータベース設計〕
設計中のE-R図を図2に，テーブル構造を表2に示す。データベース設計に関する仕様の一部を次に示す。
〔証憑提出用の台紙の印刷〕
証憑を提出する際は，システムから台紙を印刷し，それに証憑を貼り付けて提出する。台紙には，申請書番号，組織名，氏名の他に，証憑を必要とする明細行番号，日付，費用種別名及び金額を印字する。
指定された申請書番号から，証憑を必要とする明細行を取り出すSQL文を図3に示す。ここで，図3のSQL文において"：申請書番号"は対象となる申請書番号を表す埋込み変数である。
〔組織ごとの旅費交通費集計〕
各組織における1か月間の旅費交通費の合計を集計しレポートを出力する。集計は，社員が申請時に所属していた組織を基準にして行う。レポートには，組織コード，組織名及びその月の旅費交通費(日当を含む)の合計を印字する。組織ごとのレポートに必要なデータを取り出すSQL文を図4に示す。ここで，図4のSQL文において"：指定月開始日"，"：指定月終了日"は，それぞれレポートの出力対象となる年月の開始日，終了日を表す埋込み変数である。レポートは組織コードの昇順に出力する。
システムの設計に当たり，図1に示す現行の申請書を参考にした。
K社における旅費交通費精算に関する規定の一部を次に示す。
- 交通費及びホテルの費用は実費で請求する。
- タクシー，航空機及びホテルの費用の精算の際は，申請書に合わせて証憑(ひょう)を提出する。証憑とは，領収書や搭乗券など実際に利用したことを証明するものである。
- 出張時は，出発日から帰着日までの各日に日当がつく。日当は，出張時の職位によって表1に従い請求する。
- 旅費交通費の申請は，申請時の組織名で行う。
- 旅費交通費の申請は，費用の発生日から1か月以内に行う。
設計中のE-R図を図2に，テーブル構造を表2に示す。データベース設計に関する仕様の一部を次に示す。
- 申請書テーブルや申請明細テーブルにおける申請書番号は，申請書ごとに付与される一意の番号である。
- 申請明細テーブルの明細行番号は，申請書内の明細順に振られる番号である。
- 費用種別テーブルの内容を表3に示す。証憑フラグは，証憑を必要とする場合は'Y'，不要な場合は'N'である。
- 組織の追加や名称の変更があった場合は，新たに組織コードを割り当てて組織テーブルに追加する。
証憑を提出する際は，システムから台紙を印刷し，それに証憑を貼り付けて提出する。台紙には，申請書番号，組織名，氏名の他に，証憑を必要とする明細行番号，日付，費用種別名及び金額を印字する。
指定された申請書番号から，証憑を必要とする明細行を取り出すSQL文を図3に示す。ここで，図3のSQL文において"：申請書番号"は対象となる申請書番号を表す埋込み変数である。
各組織における1か月間の旅費交通費の合計を集計しレポートを出力する。集計は，社員が申請時に所属していた組織を基準にして行う。レポートには，組織コード，組織名及びその月の旅費交通費(日当を含む)の合計を印字する。組織ごとのレポートに必要なデータを取り出すSQL文を図4に示す。ここで，図4のSQL文において"：指定月開始日"，"：指定月終了日"は，それぞれレポートの出力対象となる年月の開始日，終了日を表す埋込み変数である。レポートは組織コードの昇順に出力する。
広告
設問1
図2のE-R図及び表2のテーブル構造について，(1)，(2)に答えよ。
- 図2中のa，bに入れる適切なエンティティ間の関連を答え，E-R図を完成させよ。図2の凡例に倣って示すこと。
- 表2中の申請明細テーブルの主キーとなる列を全て答えよ。
解答例・解答の要点
- a：→
b：←
- 申請書番号，明細行番号
解説
- 〔aについて〕
図1の申請書形式および〔旅費交通費精算システムのデータベース設計〕の「申請明細テーブルの明細行番号は，申請書内の明細順に振られる番号である」からわかるように、1つの申請につき1つ以上の明細書番号が存在します。よって、申請明細エンティティには同一の申請書番号の値を持つ複数のレコードが存在し得ると判断できます。
したがって、申請書エンティティと申請明細エンティティのカーディナリティは1対多であり、[a]には「→」が当てはまります。
∴a＝→
〔bについて〕
2つのエンティティには共通の属性"費用種別コード"があります。この属性"費用種別コード"は、費用種別エンティティ側では主キーであり、費用種別を一意に特定します。図1では異なる明細行に同一の費用種別がセットされているので、申請明細エンティティには同一の費用種別コードの値を持つ複数のレコードが存在しえることが考えられます。よって、費用種別エンティティの主キー"費用種別コード"が、申請明細エンティティでは外部キーであることがわかります。したがって、申請明細エンティティと費用種別エンティティのカーディナリティは多対1であり、[b]には「←」が当てはまります。
∴b＝←
※一般にエンティティA,B間に関係があることがわかっている場合、エンティティAの主キーの属性がエンティティBでも存在すれば、エンティティBのその属性は外部キーであることが形式的に判断できます。
※原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は「1対多」です。 - 図1を見ると、旅費交通費精算申請では明細行番号ごとに日付、用件用途、費用種別、出発地、到着地、往復、金額の情報を保持しているため、申請明細エンティティで行を一意に特定するには"申請書番号"と"明細行番号"の2つの情報が必要であることがわかります。したがって、申請明細テーブルの主キーは「申請書番号，明細行番号」です。
∴申請書番号，明細行番号
広告
設問2
図3中のc，dに入れる適切な字句を答えよ。列名は，テーブル名を省略せずに"テーブル名.列名"と記述すること。
解答例・解答の要点
c：申請明細.費用種別コード = 費用種別.費用種別コード
d：費用種別.証憑フラグ = 'Y'
d：費用種別.証憑フラグ = 'Y'
解説
〔cについて〕申請明細テーブルと費用種別テーブルがINNER JOIN句で内部結合されています。ON句に続いて記述しなければならないのは結合する列の指定です。
表同士の結合は共通の属性を使って行います。2つのテーブルの共通の属性は"費用種別コード"なので、ON句では2つの表の"費用種別コード"列を等号(=)で結びます。したがって、[c]には「申請明細.費用種別コード = 費用種別.費用種別コード」が当てはまります。
∴c＝申請明細.費用種別コード = 費用種別.費用種別コード
〔dについて〕
図3のSQL文の目的は、証憑を必要とする明細行を取り出すことなので、WHERE句には費用明細テーブルの全レコードから証憑を必要とするレコードを絞り込むための条件を記述しなければなりません。〔旅費交通費精算システムのデータベース設計〕には「証憑フラグは，証憑を必要とする場合は'Y'，不要な場合は'N'である」とあるので、「費用種別テーブルの証憑フラグ列が'Y'である」旨の条件式をWHERE句に記述すればよいことがわかります。したがって、[d]には「費用種別.証憑フラグ = 'Y'」が当てはまります。
※「費用種別.」は省略してもSQLは動作しますが、問題の指示により列名の前にテーブル名を記す必要があります。
∴d＝費用種別.証憑フラグ = 'Y'
広告
設問3
図4中のe～gに入れる適切な字句を答えよ。列名は，テーブル名を省略せずに"テーブル名.列名"と記述すること。
解答例・解答の要点
e：SUM
f：BETWEEN
g：ORDER BY 申請書.組織コード
f：BETWEEN
g：ORDER BY 申請書.組織コード
解説
〔eについて〕空欄の直後が (列名) となっていることから、空欄には何かしらの関数が当てはまると考えられます。〔組織ごとの旅費交通費集計〕には「各組織における1か月間の旅費交通費の合計を集計しレポートを出力する」とあること、さらに「～合計」という別名（エイリアス）が付けられていることつけられていることから、[e]には「SUM」が当てはまることが明らかです。
∴e＝SUM
〔fについて〕
WHERE句の条件として、申請日付が指定した月に属することが必要です。つまり、申請明細の全レコードから、申請日付が指定した月の初日（:指定月開始日）と末日（:指定月終了日）の間であるレコードを抽出することになります。このように列の値が指定された下限値と上限値の間にあるかどうかを判定したいときにはBETWEEN句が使えます。
列名 BETWEEN 下限値 AND 上限値
したがって、[f]には「BETWEEN」が当てはまります。∴f＝BETWEEN
〔gについて〕
〔組織ごとの旅費交通費集計〕には「レポートは組織コードの昇順に出力する」とあります。図4のSQL文にはこのレコードの並び順を制御する文がないので、ORDER BY句が必要であることがわかります。
昇順に整列 … ORDER BY 列名 ASC（ASCは省略可能）
降順に整列 … ORDER BY 列名 DESC
今回は組織コードの昇順に整列したいので、[g]には「ORDER BY 申請書.組織コード」が当てはまります（「申請書.」は「組織.」でも可）。降順に整列 … ORDER BY 列名 DESC
∴g＝ORDER BY 申請書.組織コード
広告
設問4
システムの試行期間において，日当の金額が誤って入力されているケースが多く発見された。そこで，社員テーブルに含まれる職位コードを基に金額が自動入力されるように変更した。しかし，その後の検証で不具合が起こる場合があることが分かった。それはどのような場合か，30字以内で述べよ。
解答例・解答の要点
社員の出張時の職位と申請時の職位が異なる場合 (22文字)
解説
〔旅費交通費精算に関する規定〕には以下の記述があります。- 日当は，出張時の職位によって表1に従い請求する。
- 旅費交通費の申請は，費用の発生日から1か月以内に行う。
これらのことからシステムで自動入力される日当の金額は、出張時の職位ではなく、申請時の職位に基づき決定されることになります。出張日から申請日までは最長1カ月の期間があるので、その間に職位が変わってしまうことは十分に考えられます。例えば、先月係長だった社員が先月中に出張し、今月に入ってから課長になったとします。今月に先月の出張についての旅費交通費精算を行う場合、システムで自動入力される日当は現在の職位である課長用の「3,500円」ですが、実際に請求すべき日当は出張時の職位である係長用の「2,500円」です。また逆に職位が課長から係長に降格するケースでも然りです。このような自動入力は〔旅費交通費精算に関する規定〕に反しています。
この不具合は「出張時と申請時で職位が異なる場合」に発生するので、その旨の解答が適切となります。
∴社員の出張時の職位と申請時の職位が異なる場合
広告
広告