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

問2　データベースの設計に関する次の記述を読んで，設問1～3に答えよ。 J社は，完成品を組み立てる製造業者に部品を供給する部品製造業者である。J社では，生産管理のための新システムを開発する予定である。そこで，システム部のK部長の下にプロジェクトチームを編成し，L君がデータベースの設計を担当することになった。 〔業務概要〕 1．部品管理 部品は，一つ又は複数の部品から構成され，図1に示すような階層構造で管理されている。 図1 部品の階層構造の例 図1において，各階層の上位から見た下位の部品を子部品，下位から見た上位の部品を親部品と呼ぶ。子部品を組み立てて親部品を作る。図1では，部品Aは部品B，C及びDの親部品であり，部品B，C及びDは部品Aの子部品である。部品Bは部品E，Dの親部品であり，逆に部品Dから見ると，部品A，Bはともに親部品である。 親部品と子部品は，ともに部品として管理する。部品は，部品番号によって，J社内で一意に識別される。 (1) 部品の区分 部品は次の三つの観点で区分されている。 (a) 調達区分 部品には，社外から調達するものとJ社で製造するものがあり，調達区分はこの観点から分類する場合の区分である。前者を調達品，後者を製造品と呼ぶ。調達品か製造品かは，部品ごとに決められている。図1で，部品A，Bは製造品，部品C，D及びEは調達品である。同じ調達品でも多くの場合，複数の調達先から仕入れている。 (b) 販売区分 部品には，販売するものと販売しないものがあり，販売区分はこの観点から分類する場合の区分である。前者を製品と呼ぶ。製品は，部品番号とは別に，製品番号によってJ社内で一意に識別される。製造品だけが製品として販売され，調達品はそのまま製品として販売されることはない。 (c) 顧客仕様区分 部品には，J社が顧客の設計仕様に従って製造又は調達するものとそれ以外のものがあり，顧客仕様区分はこの観点から分類する場合の区分である。前者を顧客仕様部品と呼ぶ。顧客仕様部品の中で，顧客に販売するものを顧客仕様製品と呼ぶ。顧客仕様製品を構成する子部品には，顧客仕様部品以外の部品が含まれることがある。 (2) リードタイム 部品には，部品ごとにリードタイム（以下，LTという）が決められている。 LTには，1階層LTと全階層LTがある。 (a) 1階層LT 製造品の場合は製造LTと呼び，子部品がすべてそろっている状態での親部品の組立期間である。調達品の場合は調達LTと呼び，社外から調達するのに必要な期間で，調達先ごとに設定される。 (b) 全階層LT 全階層LTはすべての子部品をそろえるための期間に親部品の組立期間を加えたものである。部品の全階層LTを算出するときに，子部品に調達品がある場合は，同じ調達品の中で，最大の調達LTが用いられる。全階層LTは，構成部品が変更になるたびに一括して再計算される。 図1で示した部品の調達LT，製造LT及び全階層LTを，表1に示す。 表1 部品の調達LT，製造LT及び全階層LT (3) 部品使用開始日・部品使用終了日と部品販売開始日・部品販売終了日 一つの部品は，子部品として使用されたり，製品として販売されたりする。部品が，子部品として使用される日，又は製品として出荷される日を，使用日と呼ぶ。新規部品の最初の使用日を部品使用開始日，最後の使用日を部品使用終了日と呼ぶ。部品は，製造又は調達された日の翌日から使用可能になる。 新規部品の注文受付を開始する日を部品販売開始日と呼び，部品の注文受付を終了する日を部品販売終了日と呼ぶ。 (4) 製品番号の付与 製品には，通常，一つの製品番号を付与するが，複数の製品番号を付与する場合もある。後者は，量産効果によってコストを抑えるため，性能の高い部品を集中製造し，性能の低い製品として転用する場合である。このため，部品，製品に対して，それぞれ部品仕様，製品仕様を分けて定義する。 (5) 部品構成の管理 (a) 構成管理 一つの親部品を製造するときの，子部品の種類とその使用数量を表したものを部品構成表と呼ぶ。表2に部品構成表の例を示す。構成適用開始日，構成適用終了日は，部品構成表の各子部品に関する情報の有効期間を規定するものである。ここで，構成適用終了日の初期値には，9999-12-31が設定されている。 部品構成表は，新規の親部品の製造時に新規作成され，子部品の変更時に変更される。表2では，部品Aについて，次の新規作成・変更が行われたことを表している。 ① 2007年9月1日に，部品B，C及びDを使用して，部品Aを新規に製造開始。 ② 2008年9月30日に，部品Cの使用を終了。2008年10月1日から部品Fの使用を開始。 ③ 2008年10月1日から，部品Dの使用数量を8個から4個に変更。 表2 部品構成表の例 (b) 登録日付のチェック 部品構成表の新規作成・変更に伴い，子部品の手配計画も変更される。構成適用開始日に当該子部品をそろえられるよう手配する日を，部品手配開始日と呼ぶ。また，当該子部品の手配を終了する日を，部品手配終了日と呼ぶ。部品手配開始日を設定する際には，構成適用開始日と部品の各LTとを比較し，実現性をチェックする。 2．顧客管理 (1) 顧客と企業の登録 顧客とは，J社が取引を行う事業所の単位である。複数の事業所を有するような企業では，一つの企業で複数の顧客が登録されることがある。顧客に対しては，J社内で一意な顧客番号を付与し，顧客番号とは別に企業に対しては，J社内で一意な企業コードを付与する。顧客単位に取引の開始・終了を管理し，一度取引が終了した顧客と取引を再開する場合は，新たな顧客番号を付与する。 (2) 顧客と営業担当者との関係 1顧客に対して，1人の営業担当者が割り当てられる。1人の営業担当者は複数の顧客を担当することができる。組織変更によって，営業担当者が変わることがあるが，契約内容の問合せなどに備えて，過去の営業担当者を特定する仕組みが必要である。1人の営業担当者がある顧客の担当から外れた後，再度同じ顧客を担当することがある。 3．顧客仕様製品の新システムへの登録 顧客仕様製品を新規に受注する場合には，J社側で顧客仕様製品を新システムに登録する。その場合，顧客から指定された顧客仕様製品名と顧客ごとに一意な顧客仕様製品コードを登録し，対応する部品番号は新システムで一意な値が自動採番される。ただし，受注したときに顧客仕様製品名と顧客仕様製品コードが未定の場合，顧客仕様製品名と顧客仕様製品コードは空値（NULL）のままで新システムに登録される。その後，顧客仕様製品名と顧客仕様製品コードが決定した時点で，それぞれ登録する。 〔テーブル設計〕 L君はデータベースの設計に当たり，まず図2に示すテーブルを設計した。これに対し，K部長からは次のような指摘があった。 ［指摘事項］ ① 主キー，外部キーが設定されていないテーブルがある。 ② “顧客担当”テーブルは第3正規形にした方がよい。 ③ “部品構成”テーブルの日付は，実現性の面から矛盾しない日付となるように制約を整理する必要がある。 ④ “部品”テーブルは主キーの設定に誤りがあるので，再設計する必要がある。 図2 L君が設計したテーブル構造（未完成） 解答に当たっては，巻頭の表記ルールに従うこと。 なお，テーブル構造の表記は，“関係データベースのテーブル（表）構造の表記ルール”を用いること。さらに，主キー及び外部キーを明記せよ。 設問1〔テーブル設計〕におけるK部長の指摘事項①，②について，（1）～（3）に答えよ。 （1）“顧客担当”テーブルは，第1正規形である。第2正規形でない理由を，列名を用いて具体的に60字以内で述べよ。 （2）“顧客担当”テーブルを第3正規形に分割し，分割後の“顧客担当”テーブル及び新たなテーブルの主キー及び外部キーも併せて答えよ。ここで，新たに作成するテーブルについては，内容を表す適切なテーブル名として本文中の用語を用いること。 （3）“顧客仕様製品”テーブルには二つの候補キーがある。これらの候補キーに関して，（a），（b）に答えよ。 （a）二つの候補キーのうち，適切な主キーを答えよ。 （b）もう一方の候補キーが主キーとして不適切な理由を，候補キーを具体的に示し，60字以内で述べよ。 設問2〔テーブル設計〕におけるK部長の指摘事項③について，（1），（2）に答えよ。 なお，解答に当たっては，本文中の用語を用いて，具体的に述べること。 （1）子部品の種類が変更される場合，変更後の子部品の，部品手配開始日，構成適用開始日，全階層LTとの間に生じる制約を，50字以内で述べよ。 （2）親部品が新規に製造される場合，新規作成された部品構成表の構成適用開始日と，親部品の，部品使用開始日と製造LTとの間に生じる制約を，60字以内で述べよ。 設問3〔テーブル設計〕におけるK部長の指摘事項④について，（1），（2）に答えよ。 （1）“部品”テーブルを第3正規形に再設計し，主キー及び外部キーを正しく設定せよ。ここで，再設計したテーブルについては，内容を表す適切なテーブル名として，本文中の用語を用いること。 （2）“部品”テーブルの列の値が設定されるかどうかは，部品によって異なる。これを表すために，図2の“部品”テーブルに，部品区分を表す列を追加する。 そこで，部品区分とその組合せを，表3のように整理した。表3中のY，Nの意味を，本文中の用語を用いて述べよ。ここで，組合せ3（部品区分1～3がそれぞれY，N，Yである部品）は顧客仕様製品を表す。 表3 部品区分と組合せを整理した表