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

問2　受注管理システムのデータベース設計に関する次の記述を読んで，設問1～3に答えよ。 A社は，個人宅を訪問し，主にキッチン，浴室，洗面所，トイレなどの清掃をサービスとして提供する業者である。A社では，受注管理システムを新たに構築することになり，B君がデータベース設計を任された。 〔業務概要〕 1. 組織の特性 (1) 地域ごとに店舗があり，本店及び各店舗はそれぞれ店舗番号で一意に識別される。 (2) 本店は顧客からの引合いを受け付け，各店舗は引合いのあった顧客の見積りを行い，注文が確定した後，サービスを実施する。 2. 会員の特性 顧客は，最初の注文時に会員登録される。会員は，会員番号で一意に識別される。 3. サービスの特性 (1) サービスは，サービスコードで一意に識別される。 (2) サービスには，個別サービス，セットサービス及びオプションサービスの3種類があり，サービス区分でどの種類のサービスかが識別される。 ①　個別サービスとは，キッチン清掃，レンジフード清掃，浴室清掃など，1か所の清掃を指す。個別サービスのサービス内容，標準価格，標準作業時間数は決まっている。個別サービスの例を表1に示す。 ②　セットサービスとは，個別サービスを複数組み合わせたもので，個別サービスそれぞれの標準価格の合計よりも割安な価格で提供する。セットサービスに対して，選択可能な個別サービス群，選択可能数，標準価格は決まっている。一つの個別サービスは，複数の異なるセットサービスに組み込まれることがある。セットサービスの例を表2に示す。 ③　オプションサービスとは，会員の依頼によって個別サービスに追加する清掃で，単独では提供しない。オプションサービスのサービス内容，適用可能な個別サービス，標準価格，標準作業時間数は決まっている。一つのオプションサービスを適用できる個別サービスは，一つだけである。一つの個別サービスに対して，複数のオプションサービスを追加できる。セットサービスで選択した個別サービスに対しても，オプションサービスを追加できる。オプションサービスの例を表3に示す。 表1　個別サービスの例 表2　セットサービスの例 表3　オプションサービスの例 4. 注文の特性 (1) 注文は，注文番号で一意に識別される。 (2) 注文にはスポット注文と継続注文があり，注文区分で識別される。 ①　スポット注文とは，1回だけ清掃を行うケースの注文である。 ②　継続注文とは，同じ清掃を複数回にわたって行うケースの注文である。 ・会員は，期間，回数及び曜日を選択する。 ・会員は，期間と回数について，24週間に6回，48週間に6回，48週間に12回など，あらかじめ決められた組合せの中から選択する。 ・期間，回数，曜日の組合せは，継続パターンコードで識別される。また，それによって継続割引率は異なる。 (3) 一つの注文で，複数のサービスを指定できる。また，個別サービスとセットサービスを一緒に指定することもできる。 5. 引合い，見積り，注文の確定の流れ (1) 本店で受け付けた引合いは，地域ごとの店舗に割り当てられる。割り当てられた店舗は，会員宅を訪問して見積りを行う。 (2) 見積りによって，サービスごとの実施項目，適用価格が確定する。継続注文の場合，この確定内容は，2回目以降も継続する前提である。適用価格は，標準価格と異なる場合がある。 (3) 見積りにおいて，予定年月日，予定開始時刻，予定終了時刻を決定する。継続注文の場合，予定開始時刻と予定終了時刻は2回目以降もこの決定が適用されるが，予定年月日は初回だけに適用される。 (4) 見積り結果は，セットサービスで選択した個別サービス，及び個別サービスに追加したオプションサービスの対応関係が分かるように，サービス名の前に記号を付け，関係するサービスを並べて表示する。また，同一の清掃を複数箇所に対して行う場合，それぞれを識別できるようにするために，実施場所に，所在を表示する。見積り結果の例を図1に示す。 (5) 会員が見積り結果を確認し，同意すれば注文が確定する。 図1　見積り結果の例 6. 実施の流れ (1) 会員宅を訪問して注文内容に従って清掃を行う単位を，“実施”と呼ぶ。スポット注文では1回の注文で1回の実施が発生し，継続注文では1回の注文で複数回の実施が発生する。 (2) 1回の実施に対して，一意となる実施番号が付与される。1回の実施には一つ又は複数のサービスが含まれ，サービスごとに実施明細番号が付与される。 (3) 注文確定によって，スポット注文と継続注文の初回の実施予定を決定し，登録する。その際，個別サービス，オプションサービスごとに，予定作業時間数を割り当てる。 (4) 継続注文の2回目以降は，実施時に次回の予定年月日を決め，その時点で次回の実施予定を登録する。 (5) 1回の実施で予定していた，全ての清掃が終了した時点で，実施年月日，実施開始時刻，実施終了時刻を登録する。また，個別サービス，オプションサービスごとに実施作業時間数を登録する。 (6) サービスごとの実施作業時間数は，サービスの作業改善，標準作業時間数の見直しのために，過去の実績を分析する場合に照会する。 〔データベースの設計〕 B君は，現在の業務概要と次の方針に基づいて，テーブル構造を図2のように設計することにした。 (1) セットサービスで選択された個別サービスも，単品で選択された個別サービスも，“注文明細”テーブルには同様の形式で格納する。 (2) 注文の確定時に“注文”テーブル及び“注文明細”テーブルに行を作成する。実施予定登録時に“実施”テーブル及び“実施明細”テーブルに行を作成する。 (3) “実施”テーブルの行から対応する“注文”テーブルの行，“実施明細”テーブルの行から対応する“注文明細”テーブルの行を特定するために，外部キーを設定する。 店舗（店舗番号，店舗名，郵便番号，住所，電話番号） 主キー：店舗番号 会員（会員番号，会員名，登録年月日，郵便番号，住所，連絡先電話番号） 主キー：会員番号 サービス（サービスコード，サービス区分，サービス名，サービス内容，標準価格，標準作業時間数，個別サービス選択可能数，個別サービスコード） セット組合せ（セットサービスコード，個別サービスコード） 主キー：セットサービスコード，個別サービスコード 継続パターン（継続パターンコード，期間，回数，曜日，継続割引率） 注文（注文番号，会員番号，店舗番号，注文年月日，注文区分，継続パターンコード，予定年月日，予定開始時刻，予定終了時刻） 主キー：注文番号 外部キー：会員番号，店舗番号 注文明細（注文番号，注文明細番号，サービスコード，適用価格，実施場所，実施項目） 主キー：注文番号，注文明細番号 外部キー：サービスコード 実施（実施番号，予定年月日，実施年月日，実施開始時刻，実施終了時刻，注文番号） 主キー：実施番号 外部キー：注文番号 実施明細（実施番号，実施明細番号，予定作業時間数，実施作業時間数，注文番号，注文明細番号） 主キー：実施番号，実施明細番号 外部キー：注文番号，注文明細番号 図2　テーブル構造（未完成） 〔継続注文の変更〕 検討中のテーブル構造では，継続注文について，初回の実施後，継続する2回目以降にオプションサービスの追加及び取消しを行いたくても対応できない。この点について，会員からの要望を満たせていないことが判明した。そこで，B君は，2回目以降の実施に対して，オプションサービスの追加及び取消しを行えるように，次の対応を考えた。 (1) 次回の実施予定を決定するときに，変更の要望を確認し，変更があれば必要な見積りを実施して，適用価格と変更の要望に伴う予定開始時刻及び予定終了時刻の変更を決定する。 (2) 当初の注文確定時点の情報を含め，変更履歴を注文変更年月日ごとに保存する。 解答に当たっては，巻頭の表記ルールに従うこと。 なお，テーブル構造の表記は，“関係データベースのテーブル（表）構造の表記ルール”を用いること。さらに，主キー及び外部キーを明記せよ。 また，新たに追加するテーブル名及び列名は，本文中で与えられた語句を用いて適切な名称とすること。 設問1　図2の“サービス”テーブルと“継続パターン”テーブルについて，(1)～(3)に答えよ。 (1) “サービス”テーブルには，セットサービス，個別サービス及びオプションサービスの3種類のサービスが格納される。そこで，“サービス”テーブルを，3種類のサービスに共通の列をもつ“サービス共通”テーブルと，各サービスに固有の列をもつテーブルに分割することにした。列が冗長にならないように，各テーブルの構造を記述せよ。 (2) “継続パターン”テーブルの候補キーを全て答えよ。 なお，複数の列から構成される候補キーは｛　｝でくくること。 (3) “継続パターン”テーブルは，第1正規形，第2正規形，第3正規形のうち，どこまで正規化されているか。また，部分関数従属性，推移的関数従属性の有無を，“あり”又は“なし”で答えよ。“あり”の場合は，その関数従属性の具体例を，次の表記法に従って示せ。 関数従属性　表記法 部分関数従属性　列1→列2 推移的関数従属性　列1→列2→列3 設問2　図2の“注文明細”テーブルについて，(1)，(2)に答えよ。 (1) 注文明細のサブタイプ構造を図3に示す。この3種類のサブタイプは，一つの注文の中で対応関係をもち得る。その対応関係を示すリレーションシップを図3に記入せよ。その場合，対応関係にゼロを含むか否かを区別して表現する場合の表記ルールを用いること。 図3　注文明細のサブタイプ構造 (2) (1)で答えたリレーションシップを成り立たせるために，現在の“注文明細”テーブルに列を二つ追加することにした。それらの列に設定する値の意味を，具体的にそれぞれ40字以内で述べよ。 設問3　注文と実施の管理について，(1)，(2)に答えよ。 (1) “実施明細”テーブルの行は“注文明細”テーブルの行に基づいて作成される。その際に，“実施明細”テーブルに反映する必要がない“注文明細”テーブルの行がある。その行を15字以内で述べよ。また，反映する必要がない理由を20字以内で述べよ。 (2) 〔継続注文の変更〕に対応するために，次の方針でテーブルの構造を見直すことにした。 ・新たなテーブルは追加せず，図2のテーブル構造を変更する。 ・テーブル構造の変更は，変更対象のテーブルに対して，同じ役割の列を一つだけ追加する。 この方針で見直した場合の追加する列について，役割を表す適切な列名を答えよ。また，変更すべきテーブル名を全て答え，それらのテーブルごとに，追加する列が主キーを構成する場合は主キー欄に“○”，外部キーを構成する場合は外部キー欄に“○”を記入して，次の表を完成させよ。 なお，表の欄は全て埋まるとは限らない。 解答表（テーブル名／主キー／外部キー）