平成27年度春期 データベーススペシャリスト 午後Ⅰ 問2

問2　データベースの設計に関する次の記述を読んで、設問1、2に答えよ。 D社は、電気設備工事を受注し、自社で施工する会社である。D社では、今回、工事案件を管理するシステム（以下、案件管理システムという）を構築することになった。 〔対象業務の概要〕 1．組織の管理 （1）三つの営業部と九つの工事部がある。部は、部コードで一意に識別する。 （2）年度当初に、営業部の当年度目標受注額と、工事部の当年度目標原価率を設定して管理する。 2．社員の管理 （1）社員は、社員番号で一意に識別する。 （2）社員は、営業部又は工事部のいずれか一つの部に所属する。 3．顧客の管理 （1）顧客は、顧客番号で一意に識別する。 （2）顧客を類別する顧客グループを設ける。顧客グループは、顧客グループコードで一意に識別する。 （3）顧客は、顧客グループのいずれか一つに所属する。 4．顧客グループと営業部の関係 （1）一つの営業部は、複数の顧客グループを担当する。一つの顧客グループを、複数の営業部が担当することはない。 （2）1人の営業部社員は、一つの顧客グループを担当する。一つの顧客グループを、複数の営業部社員が担当する場合がある。 5．案件の管理 （1）案件は、営業活動の単位である。案件は、案件番号で一意に識別する。 （2）案件ごとに、案件名、案件状態（“商談中”、“受注”、“失注”、“消滅”）、案件内容、案件開始日、顧客、受注見込額、担当営業部などを記録する。案件状態が“失注”又は“消滅”となった案件は無効とする。 （3）商談が進み、案件を担当する工事部が決定した時点で、案件詳細を記録する。 ① 案件詳細は、案件詳細全体で一意な案件詳細番号で識別し、案件詳細名、工事開始予定日、工事終了予定日、担当工事部、売上見込額、見込原価（労務費、材料費など）などを記録する。 ② 受注した案件の規模・難易度・期間などによって、複数の工事部が担当することになった場合、工事部ごとに案件詳細を記録する。 ③ 複数の案件詳細を記録した後に、作業内容の見直しによって担当工事部が減った場合、担当から外れた工事部の案件詳細は無効とする。 ④ 一つの案件に対応する無効としていない案件詳細の売上見込額の合計は、案件の受注見込額と一致させる。 （4）案件の詳細化によって、一つの案件を複数に分割する場合がある。逆に、複数の案件を一つに統合する場合がある。ただし、案件の分割と統合が、同時に行われることはない。案件ごとに、分割の場合は分割元案件番号を、統合の場合は統合先案件番号を記録する。また、案件の統合後、不要となった案件は無効とする。 （5）案件を分割した場合、分割前の案件詳細が分割後の案件のいずれかに対応付けられたり、案件詳細が分割前の案件に対応付けられたままとなったりすることがある。 （6）複数の案件を一つに統合した場合、統合前の案件詳細が統合後の案件に対応付けられる。これらの案件詳細のうち、工事部が同じものは一つに統合し、不要となった案件詳細は無効とする。 （7）案件の変更は、担当営業部の社員が実施する。案件詳細の変更は、担当営業部の社員又は担当工事部の社員のいずれかが実施する。案件の変更時、その変更履歴及び変更を実施した社員を記録する。案件詳細についても同様に記録する。 6．受注の記録 案件状態が“受注”となった時点で、案件ごとに受注として記録する。これ以降、案件及び案件詳細が変更されることはない。受注は、受注番号で一意に識別する。受注ごとに、受注名、受注日、契約開始日、契約終了日、受注額、契約種別（“請負”、“保守”など）、対応する案件番号などを記録する。また、受注明細として、担当工事部ごとの受注明細名、受注明細額などを記録する。受注明細は、受注番号、受注明細番号で一意に識別する。 7．案件の集計 （1）顧客グループ名ごと案件状態名ごとに、受注見込額を集計する。 （2）顧客グループ名ごと案件状態名ごと工事部名ごとに、売上見込額を集計する。 〔概念データモデルと関係スキーマ〕 〔対象業務の概要〕に基づいて作成した、案件管理システムの概念データモデルを図1に、関係スキーマを図2に示す。 図1 概念データモデル（未完成） 図2 関係スキーマ（未完成） 解答に当たっては、巻頭の表記ルールに従うこと。 設問1　図1の概念データモデル及び図2の関係スキーマについて、（1）、（2）に答えよ。 （1）図2中の【a】～【h】に入れる適切な外部キーとなる属性の属性名を答えよ。 （2）図1のリレーションシップは未完成である。必要なリレーションシップを全て記入し、図を完成させよ。ここで、図2の関係“案件変更履歴”の案件名以降の属性に対応するリレーションシップ、及び関係“案件詳細変更履歴”の案件詳細名以降の属性に対応するリレーションシップの表記は不要である。また、エンティティタイプ間の対応関係にゼロを含むか否かの表記は不要である。 なお、識別可能なサブタイプが存在する場合、他のエンティティタイプとのリレーションシップは、スーパタイプ又はサブタイプのいずれか適切な方との間に記述せよ。 設問2　図2の関係スキーマを、テーブルとして定義する。ここで、サブタイプの関係とスーパタイプの関係は、スーパタイプの関係にまとめたテーブルとして定義することを前提として、案件の集計について、（1）、（2）に答えよ。 （1）図3中の【ア】～【シ】に入れる適切なテーブル名又は列名を答えよ。 ① 有効な案件（無効フラグが0である案件）について、顧客グループ名ごと案件状態名ごとに受注見込額を集計するSQL文 図3 案件の集計用のSQL文 ② 有効な案件及び案件詳細（無効フラグが0である案件及び案件詳細）について、顧客グループ名ごと案件状態名ごと工事部名ごとに売上見込額を集計するSQL文 （2）図3中の①と②のSQL文を実行すると、①の受注見込額の合計と、②の売上見込額の合計が一致しない場合がある。その理由を45字以内で述べよ。