応用情報技術者過去問題 平成21年秋期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
旅行業務用データベースの設計に関する次の記述を読んで，設問1～3に答えよ。
旅行会社であるZ社では，四半期ごとにパッケージツアー(以下，ツアーという)の計画を作成し，発売開始後，申込みを受け付ける。Z社には，本社のほかに，地域ごとに支店があり，ツアーの申込みは，インターネットと支店店頭の両方で行える。また，ツアーの申込みに関するデータは本社のデータベースで一括して管理する。
〔ツアー〕
〔データベースの運用〕
ツアーテーブルの非キー属性の中には，候補キーに完全関数従属していない属性が存在するので，ツアーテーブルは第二正規形ではない。すなわち，非キー属性であるaとbが，候補キーの一部であるcだけに関数従属している。
顧客テーブルの非キー属性の中には，ほかの非キー属性を介して候補キーに関数従属(推移関数従属)している属性があるので，顧客テーブルは第三正規形ではない。具体的には，非キー属性であるdは，やはり非キー属性であるeに関数従属している。ただし，Z社では，入力間違いなどの可能性を考慮し，顧客テーブルの郵便番号は住所に関数従属しないものと考えている。
旅行会社であるZ社では，四半期ごとにパッケージツアー(以下，ツアーという)の計画を作成し，発売開始後，申込みを受け付ける。Z社には，本社のほかに，地域ごとに支店があり，ツアーの申込みは，インターネットと支店店頭の両方で行える。また，ツアーの申込みに関するデータは本社のデータベースで一括して管理する。
〔ツアー〕
- ツアーにはツアーコードが付されている。ツアーの内容が同じであれば，出発日が異なってもツアーコードは同じであるが，日数が異なればツアーコードは異なる。
- ツアーは，ツアーコードが同じでも，出発日によって価格が異なることがある。
- ツアーの申込みを受け付けたときには，申込番号，申込者の顧客番号，申込日，申し込んだツアーのツアーコード，そのツアーの出発日，参加人数を登録する。新規の顧客の場合には顧客番号を新たに設定し，顧客の氏名，住所，郵便番号，電話番号，電子メールアドレスを登録する。
- ツアーを申し込んだ顧客には，店頭での申込みかインターネットからの申込みかにかかわらず，それ以降，支店から四半期ごとにツアーなどに関する情報をダイレクトメールで送付する。顧客を担当する支店は，顧客の郵便番号によって決めている。発送は，その時点で担当となっている支店が行う。なお，支店間の業務量の均等化のために，担当範囲を随時見直すことにしている。
- E-R図を作成してテーブル設計を行った結果，ツアーテーブル，申込みテーブル，顧客テーブル，支店テーブルの四つのテーブルから成るデータベースを作成することにした。
- E-R図を図1に，設計したテーブルを表1に示す。なお，表1において，下線の引かれた列名は，主キーである。
- ツアーテーブルには，四半期ごとにその期のツアー商品を追加する。当該四半期の間にツアーテーブルの内容が変更されることはない。
- ツアーの申込みを受け付けるごとに，申込みテーブルに行を1件追加する。申込番号は，ツアーの申込み1件ごとに設定する。
ツアーテーブルの非キー属性の中には，候補キーに完全関数従属していない属性が存在するので，ツアーテーブルは第二正規形ではない。すなわち，非キー属性であるaとbが，候補キーの一部であるcだけに関数従属している。
顧客テーブルの非キー属性の中には，ほかの非キー属性を介して候補キーに関数従属(推移関数従属)している属性があるので，顧客テーブルは第三正規形ではない。具体的には，非キー属性であるdは，やはり非キー属性であるeに関数従属している。ただし，Z社では，入力間違いなどの可能性を考慮し，顧客テーブルの郵便番号は住所に関数従属しないものと考えている。
設問1
本文中のa～eに入れる適切な字句を解答群の中から選び，記号で答えよ。
a，b，c，d，e に関する解答群
- 価格
- 顧客番号
- 氏名
- 住所
- 出発日
- 担当支店コード
- ツアーコード
- ツアー名称
- 電子メールアドレス
- 電話番号
- 日数
- 郵便番号
解答入力欄
- a：
- b：
- c：
- d：
- e：
解答例・解答の要点
- a：サ
- b：ク
- c：キ
- d：カ
- e：シ
解説
〔a、b、cについて〕
第二正規形とは、全ての属性値が単一値であり、かつ、非キー属性が主キーに完全関数従属※している状態を指します。言い換えると、主キーに部分関数従属※する非キー属性がない状態と表すことができます
※完全関数従属とは、ある属性の値が主キーにより決定する関係のこと、部分関数従属とは、ある属性の値が主キーを構成する一部のキーにより決定する関係のことです。
〔正規化に関する検討〕によると、ツアーテーブル中の[a]及び[b]は、[c]に関数従属していると説明されています。ツアーテーブルの定義を見ると、"ツアーコード"と"出発日"の組が主キーであり、"日数"、"ツアー名称"及び"価格"の3つは非キー属性となっています。したがって、[a]及び[b]の候補は「サ：日数」「ク：ツアー名称」「ア：価格」に、[c]の候補は「キ：ツアーコード」または「オ：出発日」に絞られます。
問題文序盤の〔ツアー〕には以下の記述があります。
ツアーの内容が同じであっても、日数が異なればツアーコードは異なるという説明から、1泊2日や2泊3日のように日数ごと別のツアーコードが割り当てられることが読み取れます。つまり、"日数"は"ツアーコード"により一意に決まることがわかります。
また、出発日が異なっていても、ツアーの内容と日数が同じであればツアー名称は同じでしょうから、"ツアー名称"も"ツアーコード"により一意に決まることがわかります。
一方、価格はツアーコードと出発日によって異なりますから、主キーに完全関数従属している状態です。
以上より、第二正規形を満たしていないのは、"日数"と"ツアー名称"が主キーの一部である"ツアーコード"に部分関数従属しているからであると結論付けることができます。
∴a、b＝サ：日数、ク：ツアー名称（順不同）
c＝キ：ツアーコード
〔d、eについて〕
第三正規形とは、第二正規系の条件を満たし、かつ、非キー属性が主キーに推移的関数従属していない状態をいいます。言い換えると、非キー属性が他の非キー属性に関数従属していない状態と表すことができます。
顧客テーブルにおいて、非キー属性に関数従属している非キー属性を探すと、〔ツアーに関する業務〕の「顧客を担当する支店は，顧客の郵便番号によって決めている」という記述から、"担当支店コード"は"郵便番号"により一意に決まる、すなわち関数従属していることが読み取れます。これが顧客テーブルに残っている推移的関数従属性です。
∴d＝カ：担当支店コード
e＝シ：郵便番号
第二正規形とは、全ての属性値が単一値であり、かつ、非キー属性が主キーに完全関数従属※している状態を指します。言い換えると、主キーに部分関数従属※する非キー属性がない状態と表すことができます
※完全関数従属とは、ある属性の値が主キーにより決定する関係のこと、部分関数従属とは、ある属性の値が主キーを構成する一部のキーにより決定する関係のことです。
〔正規化に関する検討〕によると、ツアーテーブル中の[a]及び[b]は、[c]に関数従属していると説明されています。ツアーテーブルの定義を見ると、"ツアーコード"と"出発日"の組が主キーであり、"日数"、"ツアー名称"及び"価格"の3つは非キー属性となっています。したがって、[a]及び[b]の候補は「サ：日数」「ク：ツアー名称」「ア：価格」に、[c]の候補は「キ：ツアーコード」または「オ：出発日」に絞られます。
問題文序盤の〔ツアー〕には以下の記述があります。
- ツアーにはツアーコードが付されている。ツアーの内容が同じであれば，出発日が異なってもツアーコードは同じであるが，日数が異なればツアーコードは異なる。
- ツアーは，ツアーコードが同じでも，出発日によって価格が異なることがある。
ツアーの内容が同じであっても、日数が異なればツアーコードは異なるという説明から、1泊2日や2泊3日のように日数ごと別のツアーコードが割り当てられることが読み取れます。つまり、"日数"は"ツアーコード"により一意に決まることがわかります。
また、出発日が異なっていても、ツアーの内容と日数が同じであればツアー名称は同じでしょうから、"ツアー名称"も"ツアーコード"により一意に決まることがわかります。
一方、価格はツアーコードと出発日によって異なりますから、主キーに完全関数従属している状態です。
以上より、第二正規形を満たしていないのは、"日数"と"ツアー名称"が主キーの一部である"ツアーコード"に部分関数従属しているからであると結論付けることができます。
∴a、b＝サ：日数、ク：ツアー名称（順不同）
c＝キ：ツアーコード
〔d、eについて〕
第三正規形とは、第二正規系の条件を満たし、かつ、非キー属性が主キーに推移的関数従属していない状態をいいます。言い換えると、非キー属性が他の非キー属性に関数従属していない状態と表すことができます。
顧客テーブルにおいて、非キー属性に関数従属している非キー属性を探すと、〔ツアーに関する業務〕の「顧客を担当する支店は，顧客の郵便番号によって決めている」という記述から、"担当支店コード"は"郵便番号"により一意に決まる、すなわち関数従属していることが読み取れます。これが顧客テーブルに残っている推移的関数従属性です。
∴d＝カ：担当支店コード
e＝シ：郵便番号
設問2
正規化に関する検討について，(1)～(3)に答えよ。
- テーブルが第二正規形ではない場合，一般的には様々な問題が発生する可能性がある。しかし，ツアーテーブルの場合にはそのような問題は発生しないと考えられる。その理由を，本文の記述に照らし合わせて35字以内で述べよ。
- 顧客テーブルが第三正規形でないために発生する問題を，本文の記述に照らし合わせて60字以内で述べよ。
- 顧客テーブルを第三正規形になるように分解せよ。新規に追加するテーブルには適切なテーブル名を付け，表1に倣って列名を記述し，主キーを示す下線を引くこと。(※正誤判定の都合上，主キー属性は{属性名}と入力してください)
解答入力欄
- 顧客テーブルの列名：
- 新規テーブル名：
- 新規テーブルの列名：
解答例・解答の要点
- ツアーテーブルに追加された行がその後変更されることが無いから (30文字)
- 支店の担当範囲が変更されると，顧客テーブルの該当するすべての行の担当支店コードを修正しなければならない (51文字)
- 顧客テーブルの列名：顧客番号，氏名，住所，郵便番号，電話番号，電子メールアドレス
- 新規テーブル名：担当支店
- 新規テーブルの列名：郵便番号，担当支店コード
解説
- 第二正規形でないツアーテーブルでは、一般的に次のような更新時異状が発生する可能性があります。
- レコード追加時の異状
- 出発日が確定していないツアーを登録することができない
- レコード削除時の異状
- レコードの削除により、ツアーのデータが失われてしまう
- レコード修正時の異状
- ツアー名称や日数が変更になった際、対応するツアーコードをもつ全ての行の値を変更しなくてはならない
しかし、〔データベースの運用〕に「ツアーテーブルには，四半期ごとにその期のツアー商品を追加する。当該四半期の間にツアーテーブルの内容が変更されることはない」という記述があります。ツアーテーブルには行の追加後変更されることがないという特別の事情があるため、レコード修正時の異状の問題は発生しないことになります。
これが、ツアーテーブルが第二正規形ではないにもかかわらず、更新時異状の問題が発生しない理由となります。解答としては「登録された後は内容が変更されることがない」旨の記述が適切となります。
∴ツアーテーブルに追加された行がその後変更されることが無いから - 第三正規形でない顧客テーブルでは、一般的に次のような更新時異状が発生する可能性があります。
- レコード追加時の異状
- 郵便番号と担当支店コードの対応だけを格納するレコードを挿入できない
- レコード削除時の異状
- レコードの削除により、郵便番号と担当支店コードの対応関係のデータが失われてしまう
- レコード修正時の異状
- 郵便番号と担当支店の対応関係を変更する際、その郵便番号をもつ全ての行の値を変更しなくてはならない
これが、顧客テーブルが第三正規形でないために発生する問題です。
∴支店の担当範囲が変更されると，顧客テーブルの該当するすべての行の担当支店コードを修正しなければならない - 設問1や(2)を踏まえると、"郵便番号"と"担当支店コード"の関係を別テーブルに移すことになります。別テーブルに分割するときには、別テーブルで主キーとなる属性を元表に外部キーとして残します。今回は"郵便番号"→"担当支店コード"という関係を分離するので、以下のようなテーブル構造となります。これにより第三正規形を満たします。
顧客（顧客番号，氏名，住所，郵便番号，電話番号，電子メールアドレス）
担当支店（郵便番号，担当支店コード）
※表名の"担当支店"はそれらしい名前であればOKだと思います。
設問3
現在の設計では，ツアーに参加した人全員の情報をデータベースに保持しているわけではないので，参加者全員にダイレクトメールを送ることはできない。そこで，それぞれのツアーの参加者全員の情報をデータベースに格納することを検討する。そのために，図1のE-R図にエンティティを一つ追加する。また，それに従って，申込者に加えて全参加者の情報を顧客テーブルに格納するとともに，新たなテーブルを追加して，申込番号ごとに，そのツアーに参加するすべての顧客の顧客番号を保持するようにする。
これを実現するために，図1に対して，適切な名称を付したエンティティを追加し，リレーションシップを記入せよ。
これを実現するために，図1に対して，適切な名称を付したエンティティを追加し，リレーションシップを記入せよ。
解答入力欄
- o：(図表で回答する問題のため解答入力欄はありません。)
解答例・解答の要点
- o：
解説
1人の顧客に対して複数の申込み記録が作成される可能性があるので、1つの申込みについて複数の顧客を関連付けるとなると、申込みエンティティと顧客エンティティのカーディナリティ（多重度）は「多対多」になります。関係データベースでは「多対多」の関係を表現することができませんから、2つのエンティティの間に連関エンティティを用意して2つの「1対多」の関係に分離します。
この連関エンティティは、顧客エンティティの主キーである"顧客番号"と申込みエンティティの主キーである"申込番号"を持ち、顧客エンティティと1対多、申込みエンティティと1対多で関連付けられることになります。
この関連をE-R図に書き込むと解答例のような形になります。※連関エンティティの名称は"参加"が付いているそれらしい名称であればOKでしょう。
この連関エンティティは、顧客エンティティの主キーである"顧客番号"と申込みエンティティの主キーである"申込番号"を持ち、顧客エンティティと1対多、申込みエンティティと1対多で関連付けられることになります。
