平成23年度特別 データベーススペシャリスト 午後Ⅱ 問1

問1　データベースの概念設計，論理設計，性能設計及び運用設計に関する次の記述を読んで，設問1～3に答えよ。 A社は，受託開発を中心とするソフトウェア開発会社である。今回，案件情報を管理する案件管理システム，及びプロジェクト（以下，PJという）の収支情報を管理するPJ収支管理システムをそれぞれ開発することにした。 〔顧客の管理〕 (1) A社では，原則として顧客単位に提案，契約，請求などの営業活動を行っている。ただし，顧客の規模によっては，顧客の部課単位に営業活動を行う場合がある。顧客には，社内で一意な顧客番号を付与し，部課単位には顧客番号ごとの連番を付与する。 (2) 営業活動の管理単位として，顧客を業種や地域などで分類した顧客グループを設けている。顧客は，いずれか一つの顧客グループに属する。 〔組織の概要〕 (1) 複数の営業部と複数の開発部がある。部には，社内で一意な部コードを付与する。使用されなくなった部コードを再利用することはない。 (2) 一つの営業部が，複数の顧客グループを担当する場合がある。一つの顧客グループを担当する営業部は一つである。 (3) 社員は，営業部又は開発部のいずれか一つの部に所属する。社員には，社内で一意な社員番号を付与する。使用されなくなった社員番号を再利用することはない。 (4) 営業部の社員は，一つの顧客グループの営業を担当する。複数の営業部社員が同じ顧客グループを担当する場合がある。 (5) 開発部の社員については，プロジェクトマネージャ（以下，PMという）とプロジェクトリーダ（以下，PLという）の経験の有無を管理する。 (6) 社員の所属部の異動月は不定であるが，異動日は，月初日である。 (7) 社員には，給与に連動した等級が定められている。社員の等級の変更は，年1回の定期人事で行う。 (8) 部の新設・廃止などの組織変更月は不定であるが，組織変更日は月初日である。 〔案件情報の管理〕 (1) 案件は，受注を目指して行う顧客への営業活動の単位である。営業活動の結果，案件の受注が確定した場合，顧客と契約を結ぶ。営業活動を進める中で，案件が具体化し，複数の案件に分かれ，それぞれ別の契約となる場合がある。また，顧客の都合によって，複数の案件が一つの契約にまとめられる場合がある。前者の場合は案件を分割，後者の場合は案件を統合し，案件と契約を1対1に対応付ける。分割された新しい案件は，分割前の案件と対応付ける。また，統合された元の案件は，統合後の案件と対応付ける。 なお，案件が契約に至らない場合もある。 (2) 顧客への営業活動開始時点で，案件として，案件名，案件内容，顧客の情報，案件起案日，開始予定日，終了予定日，売上見込額，案件状態，受注確度などを登録する。登録する場合，案件には，社内で一意な案件番号を付与する。案件状態は“提案中”，“契約済”などから選択し，受注確度は“高”，“中”，“低”の中から選択する。営業活動の進展に伴い，案件の登録内容を更新する。 (3) 年度ごと案件ごとの売上見込額を案件年度収支に登録する。複数年度にまたがる案件の場合，案件の開始予定日と終了予定日から月数を求めて，年度ごとの月数換算で売上見込額を比例配分し，その金額を案件年度収支として登録する。案件の開始予定日，終了予定日，売上見込額を変更した場合は，案件年度収支も変更する。 (4) 営業部は開発部と連携し，顧客への提案活動を行う。案件の規模が大きい場合や技術分野が多岐にわたる場合は，複数の開発部と連携することがある。案件の内容があいまいな間は，連携する開発部が決まっていないことがある。 (5) 案件ごとに営業責任者として営業部の社員を1名選定する。また，連携する開発部ごとに，開発責任者を1名選定する。営業責任者又は開発責任者の異動があった場合は，異動前の所属部の後任者に引き継ぐ。 (6) 案件明細は，案件を担当する開発部が決まるごとに作成する。案件ごと開発部ごとの案件明細として，案件明細名，開始予定日，終了予定日，売上見込額などを登録する。登録する場合，案件明細には，社内で一意な案件明細番号を付与する。 (7) 案件明細も案件と同様に，年度ごと案件ごとの売上見込額を案件明細年度収支に登録する。案件明細の開始予定日，終了予定日，売上見込額を変更した場合は，案件明細年度収支も変更する。 (8) 年度当初に，営業部では部ごとの当年度売上目標額を登録し，開発部では部ごとの当年度目標利益率を登録する。年度途中で見直しが発生した場合，見直し後の値に変更する。 〔PJ収支管理〕 (1) 受注が確定した場合，開発を担当するPM又はPLは案件明細ごとにPJを発足する。PJは収支管理の単位である。PJ収支データとして，PJ名，PJ開始日，PJ終了日，PJ種別コード，案件明細番号，担当開発部の部コード，PMとPLの社員番号，毎月のPJ収支計画データ（後述），PJ収支実績データ（後述）などを登録する。登録する場合，PJには，社内で一意なPJコードを付与する。PJ種別コードは，製造請負契約，作業請負契約，業務委任契約などの契約形態を区別するコードである。PJ実施中に，契約内容，PJ体制が変更になった場合，変更内容に合わせてPJ収支データを変更する。 (2) PJ収支計画データである毎月の売上，労務時間，外注費，材料費，その他費用の登録・変更方法は，次のとおりである。 ① 売上は，契約形態に基づいて，計画年月に計画額を登録する。製造請負契約の場合はPJ終了日の年月に計画額を一括して登録し，その他の契約の場合はPJ期間内の各月に月割りの計画額を登録する。 ② 労務時間は，担当社員ごと月ごとの計画時間を登録する。担当社員が未定の場合は，ダミー社員（後述）を選択し，月ごとの計画時間を登録する。また，一時的に他部の社員の支援を受ける場合，当該社員を選択し，計画時間を登録する。各社員の労務費は，“時間単価（以下，単価という）×計画時間”で計算する。 ③ 外注費は，外注会社ごと契約形態ごと計画年月ごとの計画額を登録する。 ④ 材料費とその他費用は，費用を区分する勘定科目コード単位に，支出の計画年月ごとの計画額を登録する。 図1にPJ収支計画データ登録時の入力イメージを示す。 図1　PJ収支計画データ登録時の入力イメージ (3) 労務費を計算するために，社員の等級1～8を四つのランクに分類する。年度当初に，昨年度の労務費実績や部の所属人数などから，当年度以降の10年間の年度ごと部ごとランクごとの単価を定める。毎月の労務費計算時の社員の単価は，当該月の所属部の単価を適用する。 (4) ダミー社員は，部ごとランクごとに設ける。ダミー社員には，社員を含めて一意な社員番号を付与する。 (5) 月次実績反映処理として，PJ収支管理システムの関連システム（後述）のデータを使用し，PJ収支実績データである売上，労務時間，外注費，材料費，その他費用を登録する。登録した値が誤っていた場合，登録済の値を修正せずに，翌月以降の値で修正する。例えば，2010年6月の値に誤りがあった場合，2010年7月の値で修正する。 (6) 前月までのPJ収支実績データとPJ収支計画データが異なる場合，PM又はPLは，その原因を調査し，必要に応じて当月以降のPJ収支計画データを変更する。 〔PJ収支管理システムとその関連システムからの受信データ及び受信契機〕 (1) PJ収支管理システムの関連システムとしては，図2に示す案件管理システム，売上管理システム，勤怠管理システム，購買管理システム，旅費管理システム，外注管理システムがある。 図2　PJ収支管理システムとその関連システム (2) 関連システムからのPJ収支管理システムの受信データ及び受信契機を，表1に示す。 表1　関連システムからのPJ収支管理システムの受信データ及び受信契機 〔概念データモデル，関係スキーマ，テーブル構造〕 (1) 案件管理システムの概念データモデルを図3に，関係スキーマを図4に示す。 なお，案件管理システムでは，社員の所属の履歴管理は不要であることを前提にしている。 図3　案件管理システムの概念データモデル（未完成） 図4　案件管理システムの関係スキーマ（未完成） (2) PJ収支管理システムのテーブル構造を図5に示す。 図5　PJ収支管理システムのテーブル構造（一部の列名は省略） 〔案件管理システム及びPJ収支管理システムでの集計処理〕 案件管理システムの主な集計処理を表2に，PJ収支管理システムの主な集計処理を表3に示す。PJ収支管理システムの主な集計処理の結果データのレコード構成は，図6のとおりである。 表2　案件管理システムの主な集計処理 表3　PJ収支管理システムの主な集計処理 図6　PJ収支管理システムの主な集計処理の結果データのレコード構成 〔集計処理に関する指摘及び要望〕 案件管理システム及びPJ収支管理システムの集計処理について，次のような指摘及び要望が出た。 指摘①　受注確度別集計と受注確度別開発部別集計のそれぞれの合計値が，一致しない場合がある。 指摘②　開発担当部を指定した開発担当部別計画集計や開発担当部別実績集計に，時間がかかりそうである。 指摘③　PJ収支管理システムに対するアクセスが，毎月の月次実績反映処理後の数日間に集中する。この数日間の応答時間が遅くなりそうである。 要望①　PJ発足済の案件とそうでない案件に分けて，今年度の売上見込額を知りたい。PJ発足済の案件の場合は，先月までの売上実績を考慮した売上見込額を知りたいのでPJ収支管理システムの売上見込額を使用し，そうでない案件の場合は，案件管理システムの売上見込額を使用して，集計したい。 〔組織変更に伴うデータの変更〕 1. 組織変更後のPJ収支管理システムの集計処理への要望 開発部の組織変更があった場合，組織変更後の部単位でPJ収支データを集計することにした。集計に当たって，次のような要望があった。 (1) 年度途中で組織変更があった場合，組織変更後の部が年度当初から存在していたとして，部単位でのPJ収支データを集計できること。 (2) 前年度のPJ収支データは変更しないが，今年度の組織変更後の部単位で前年度とのPJ収支データを比較できること。 なお，組織変更に伴って，複数のPJを統合することはない。 2. データの変更方法 “1. 組織変更後のPJ収支管理システムの集計処理への要望”を実現するため，当年度のPJ収支データは，次のように変更することとした。 (1) 当年度に終了した又は進行中のPJで，組織変更後，部が分割されないPJの場合，“PJ”テーブルの当該PJの部コードを組織変更後の部コードに変更する。 (2) 当年度に終了した又は進行中のPJで，組織変更後，複数の部に分割されるPJの場合，分割後の部のPJの情報を登録する。PJ収支データは，分割後の部単位の案分比率をPJごとに定めて案分する。 (3) 前年度の部単位のPJ収支データと比較するため，前年度PJと今年度の部の対応を管理する“PJ変更”テーブルを追加する。更に，“PJ変更”テーブルでは，部コードが変更される今年度PJと，複数のPJに分割される今年度PJの案分比率と分割後PJコードも管理する。また，複数の部に分割されるPJの場合のPJ収支データについて，次の2案を検討した。 案1　分割後，新設されるPJについては，新規PJとして登録し，年度内の年月ごとに“分割前のPJ収支実績値×案分比率”の値を登録する。分割後も存続するPJについては，年度内の年月ごとに“分割前のPJ収支実績値－新規PJのPJ収支実績値”の値に変更する。また，PJ収支計画値については，組織変更後に当該組織の計画値に変更する。 案2　分割後，新設されるPJについては，新規PJとして登録し，“分割前の年度内の各年月のPJ収支実績値の合計×案分比率”の値を，組織変更年月のPJ収支実績値として登録する。分割後も存続するPJについては，分割前のPJ収支実績値（以下，変更前データという）を保持し，分割後の新規PJのPJ収支実績値のマイナス値（以下，補正データという）を，組織変更年月のPJ収支実績値として追加する。分割後も存続するPJの組織変更後のPJ実績値は，変更前データと補正データを合算する。また，PJ収支計画値については，組織変更後に当該組織の計画値に変更する。 3. データの変更方法の具体例による確認 データの変更方法の妥当性を検証するため，製造請負契約の具体例を用いて，データの変更方法を確認することにした。 (1) 2010年8月1日付けの組織変更によって，部コード“0102”の部が“0102”と“0108”の部に分割されたとする。 (2) 組織変更前の部コード“0102”の“PJ”テーブル及び“PJ売上実績”テーブルは，それぞれ表4と表5のとおりとする。組織変更前と組織変更後のPJコードと案件明細番号の対応関係は，図7のとおりとする。 表4　“PJ”テーブルの具体例 表5　“PJ売上実績”テーブルの具体例 図7　組織変更前と組織変更後のPJコードと案件明細番号の対応関係 解答に当たっては，巻頭の表記ルールに従うこと。 なお，識別可能なサブタイプが存在する場合，他のエンティティタイプとのリレーションシップは，スーパータイプ又はサブタイプのいずれか適切な方との間に記述せよ。また，テーブル構造は，第3正規形の条件を満たしていること。 また，設問2と設問3において，案件管理システムのテーブル構造は，図4の関係スキーマをそのままテーブルとして定義したものとする。ただし，サブタイプが存在する場合は，スーパータイプ側にまとめたテーブルとして定義したものとする。 設問1　案件管理システムについて，(1)，(2)に答えよ。 (1) 図3中に，エンティティタイプ及びリレーションシップを追加し，図を完成させよ。サブタイプが存在する場合は，サブタイプを示せ。ただし，エンティティタイプ間の対応関係にゼロを含むか否かの表記は不要である。 (2) 図4中の［a］～［c］に入れる適切な属性名を答えよ。また，(1)で追加したサブタイプの関係スキーマ及びその主キー，外部キーを示せ。 設問2　〔集計処理に関する指摘及び要望〕について，(1)～(4)に答えよ。 (1) 指摘①の事象が発生する理由を，45字以内で述べよ。 (2) 指摘②の状況となる原因は，PJごとの労務費の集計処理における，社員の単価を求めるときの複数テーブルの結合処理にあると想定し，結合処理での検索条件列とテーブルの検索順序を次のように整理した。開発担当部別計画集計のケースについて，手順2～6中の［d］～［q］に入れる適切な列名，テーブル名を答えよ。 手順1　検索条件の列として部コードを指定し，“PJ”テーブルからPJコードを求める。PJコードごとに手順2～手順6で単価を求める。 手順2　検索条件の列として［d］を指定し，［e］テーブルを検索して［f］と［g］を求める。 手順3　検索条件の列として［f］と［h］と［i］を指定し，［j］テーブルを検索して，［k］を求める。 手順4　検索条件の列として［k］を指定し，［ℓ］テーブルを検索して［m］を求める。 手順5　検索条件の列として［f］と［n］と［o］を指定し，［p］テーブルを検索して［q］を求める。 手順6　検索条件の列として［m］と［q］と年度を指定し，“部ランク単価”テーブルを検索して単価を求める。このとき，年度の検索条件には，［g］から求まる値を指定する。 また，この検索効率を改善するために，列を二つ追加する。列を追加するテーブル名と，列名を答えよ。 (3) 指摘③の状況を解決するために，月次実績反映処理で，表3の集計処理を行い，図6の結果データをテーブルとして保持することにした。これが可能な集計処理名とその理由を，20字以内で述べよ。 (4) 要望①を実現するために，次の2案を検討した。 案A　PJ収支管理システムにおいて，当年度に存在するPJの案件明細番号，売上見込額を，日次バッチ処理で案件管理システムに送信する。案件管理システムでは，受信したデータを既存のテーブルに追加した列に登録し，案件管理システムにおいて，PJ発足済の売上見込額を検索できるようにする。 案B　案件管理システムとPJ収支管理システムで使用する関係データベース管理システム（RDBMS）として，複数データベース間の参照が可能なRDBMSを選択し，案件管理システムからPJ収支管理システムを参照する。 (i) 案Aで，列を追加する案件管理システムのテーブル名と追加する列名を示せ。 (ii) 案Bで，当月の第4営業日以降にPJ収支管理システムのPJごとの売上見込額を，案件管理システムから検索するときの問合せの内容を，表6のように整理した。表6中の［ア］～［ウ］に入れるテーブル名と，［エ］と［オ］に入れる適切な内容を答えよ。 表6　問合せの内容 設問3　〔組織変更に伴うデータの変更〕について，(1)～(3)に答えよ。 (1) 1回の組織変更を管理するための“PJ変更”テーブルの構造及びその主キー，外部キーを示せ。 (2) PJコードが“11000130”のPJについて，データの変更によって，案1の場合の“PJ”テーブル，案2の場合の“PJ売上実績”テーブルは，どのようなインスタンスとなるか。それぞれ適切な内容を，表7と表8にそれぞれ記入せよ。 なお，表のすべての記入欄が埋まるとは限らない。 表7　案1の場合の“PJ”テーブルの具体例 表8　案2の場合の“PJ売上実績”テーブルの具体例 (3) (2)の結果を参考に，表9に示す案2の場合のテーブルについて，データの変更処理で，行の追加・変更が発生するテーブル名の欄に○印を記入せよ。 表9　データの変更処理で，行の追加・変更が発生するテーブル