午後Ⅰ試験対策：データベースの設計に関する問題の解き方

はじめに

今回は、午後Ⅰ試験対策として、データベースの設計に関する問題の解き方を、平成28年度午後Ⅰ試験問1の解説と併せて紹介します。この内容は午前Ⅱ試験や午後Ⅱ試験の解答にも役立つので、しっかりと身につけましょう。なお、午後Ⅰ試験の出題傾向や学習方法については、第1回を参照してください。

データベース設計に関する基本用語

エンティティタイプ

実体（エンティティ）に含まれるデータ項目（属性）のうち、データベースで管理する必要があるものを抽出した結果です。例えば、実体「顧客」が持つ属性に「顧客番号」「顧客名」「住所」「代表者名」「創業年月日」「メールアドレス」があったとしても、システムで管理すべき属性が「顧客番号」「顧客名」「住所」「メールアドレス」「代表者名」のとき、エンティティタイプ「顧客」の属性は「顧客番号」「顧客名」「住所」「メールアドレス」「代表者名」となります（図1）。

関係

「関係＝エンティティタイプ」と考えます。エンティティタイプ「顧客」の関係名は「顧客」です。

図1：エンティティタイプ

候補キー、主キー、非キー属性（図2）

候補キー
関係に含まれる1件のデータを識別するための属性または属性の集まり（複合キー）のことで、関係には1つ以上の候補キーがあります。例えば、関係「顧客」において1件の社員データを識別するための属性として「顧客番号」と「顧客名」＋「住所」の組み合わせ、および「メールアドレス」が存在した場合、「顧客番号」と「{顧客名,住所}」、および「メールアドレス」が候補キーです。

主キー
候補キーのうち主に使用するもので、関係には主キーが1つだけ存在します。例えば、関係「顧客」の候補キーが上記の3つの場合、社員を識別するために主に使用するのが「顧客番号」だとすると、「顧客番号」が主キーとなります（図1）。なお、主キーの属性名の下に実線の下線を引く場合があります。

図2：候補キー、主キー、非キー属性

非キー属性
どの候補キーにも含まれない属性のことです。例えば、「代表者名」がどの候補キーにも含まれない場合、「代表者名」が非キー属性となります。

外部キー

主キーと関連づけて定義し、主キーと一致する値のみ保持できるようにした属性または属性の組み合わせのことです。例えば、関係「顧客」の主キーが「顧客番号」で、関係「注文」において関係「顧客」と関連を持たせたい場合、関係「注文」に外部キー「顧客番号」を定義し、関係「顧客」の主キー「顧客番号」との関連を定義します（図3）。なお、外部キーの属性には点線の下線を追加する場合があります。

図3：外部キー

スーパタイプ／サブタイプ

顧客には法人顧客と個人顧客があり、法人顧客と個人顧客の両方に共通の属性として「顧客番号」「顧客名」「住所」「顧客種別」があったとします。一方、法人顧客のみ存在する属性に「担当者名」が、個人顧客のみ存在する属性に「生年月日」「性別」があったとします。このような場合、法人顧客と個人顧客に共通の属性をスーパタイプ「顧客」に持たせ、法人顧客にのみ存在する属性をサブタイプ「法人顧客」、個人顧客にのみ存在する属性をサブタイプ「個人顧客」に持たせます。スーパタイプ「顧客」とサブタイプ「法人顧客」「個人顧客」の主キーは全て「顧客番号」とします。そして、サブタイプの主キーはスーパタイプの外部キーとなります（図4）。

図4：スーパタイプとサブタイプ

リレーションシップ

エンティティ間の関連のことで、外部キーを用いてリレーションシップを定義します。

概念データモデル

関係「注文」には関係「顧客」の主キーである属性「顧客番号」を参照する外部キーが存在し、またスーパタイプ「顧客」にはサブタイプ「法人顧客」と「個人顧客」が存在したとします。関係「注文」に１件の顧客に対応する注文データが複数存在した場合、概念データモデルは図5の通りです。

図5：概念データモデル

関係スキーマ

外部キーで説明した関係「顧客」と「注文」を関係スキーマで表すと以下のようになります。

関数従属性

ある属性の値が決まれば異なる属性の値が一意に決まる、属性間の関連のことです。例えば、顧客番号の値が決まれば顧客名の値が一意に決まる場合、「顧客名は顧客番号に関数従属する」といい、「顧客番号→顧客名」と表現します。

正規化

正規化には、「第1~５正規化」と「ボイスコッド正規化」があり、正規化した結果を「正規形」といいます。午後Ⅰ試験では、第1正規形を第3正規形にする問題が出題されています。そこで、第2正規化と第3正規化について紹介します。

第2正規化

第1正規形に対して部分関数従属性（複合キーの一部の属性だけに関数従属する属性が存在する属性間の関係）を排除し、全ての非キー属性が完全関数従属した関係のことです。例えば、関係「注文商品」に含まれる属性「商品名」と「商品単価」が、主キー{受注番号，商品番号}の一部である属性「商品番号」に部分関数従属しているとします。このとき、第2正規化を実施して属性「商品番号」を主キーとする関係「商品」に分離します。これにより関係「商品」と「注文商品」は完全関数従属した状態となり、第2正規形となります（図6）。

図6：第2正規化

第3正規化

第2正規形に対して候補キーを起点とした推移的関数従属性（属性A→属性B→属性Cが成立する属性間の関係）を排除した関係のことです。例えば、関係「注文」に含まれる属性「顧客名」が非キー属性「顧客番号」に関数従属している場合、関係「注文」には「注文番号→顧客番号→顧客名」という推移的関数従属性が存在することになります。そこで第3正規化を実施し、属性「顧客番号」を主キーとする関係「顧客」に分離します。これで関係「顧客」と「注文」には推移的関数従属性がなくなり、第3正規形となります（図7）。

図7：第3正規化

午後Ⅰ試験を解くためのポイント

一般的に、問題文中から以下の内容が読み取れることが多いので、チェックしましょう（ただし100%ではないため文脈から最終判断すること）。

• 関係名の後ろに「ID」「番号」「コード」がついた属性名は主キーである
• 「○○を一意に識別する」（○○は関係）と記載された属性は候補キーである
• 「○○ごとに付与する」「○○との組み合わせで識別する」（○○は属性）と記載された属性は○○との複合キーである
• 「○○には、（一つまたは）複数の××が存在する」（○○と××は関係）と記載されている場合、××には○○の主キーを参照する外部キーが存在する
• 「○○には△△と▲▲がある」「○○は△△と▲▲に分類される」（○○、△△、▲▲はいずれも関係）と記載されている場合、スーパタイプが○○、サブタイプが△△と▲▲である

平成28年度午後Ⅰ試験問1に挑戦！

それでは、平成28年度午後Ⅰ試験問1に挑戦してみましょう。問題文はこちらからダウンロードしてください。また、解答用紙はこちらに用意しました。解答例はこちらを参照してください。

問題の解答にかける時間は35分を目標にしましょう。

設問1の解説

(1)候補キーの抽出

図4の関係「周辺施設」から候補キーを選ぶために問題文を読みます。〔Webサイトの概要〕(3)に「駐車場には，一つ又は複数の周辺施設が定められている。一つの施設は，複数の駐車場の周辺施設として定められる場合がある」とあること、また、表1の「駐車場ID」「施設ID」の意味から、{施設ID,駐車場ID}が候補キーであることがわかります。

さらに、表1の属性名「駐車場経度,駐車場緯度,施設経度,施設緯度」の意味に「駐車場及び施設には，経度及び緯度の組合せを一つ定めている。経度及び緯度を組み合わせた位置上に，駐車場又は施設が複数存在することはない。」と記載されていることから、属性「施設ID」と属性の組{施設経度,施設緯度}が1対1に対応することがわかります。従って、{施設経度,施設緯度,駐車場ID}も候補キーとなります。

(1)部分関数従属性の有無と具体例
図4の関係「周辺施設」の属性から部分関数従属性を見つけます。表1の属性名「施設名」の意味に「施設の名称」とあることから、「施設ID→施設名」の部分関数従属性が導けます。

また、〔Webサイトの概要〕(2)と(3)「駐車場及び施設は，いずれか一つのエリアに属する」「施設は，いずれか一つのカテゴリに属する」より、「施設ID→施設エリアコード」「施設ID→カテゴリコード」の部分関数従属性が導けます。

(1)推移的関数従属性の有無と具体例
表1の属性名「カテゴリ名」の意味に「カテゴリの名称」とあることから、「カテゴリID→カテゴリ名」の関数従属性が導けます。これと(1)の部分関数従属性から、「施設ID→カテゴリコード→カテゴリ名」の推移的関数従属性が存在することがわかります。

(2)正規形
(1)より、関係「周辺施設」には部分関数従属性があることから、関係「周辺施設」は第1正規形です。

(2)第3正規化

• 主キー定義
  (1)において、部分関数従属性や推移的関数従属性の起点が「施設ID」となっているため、関係「周辺施設」の主キーは{施設ID，駐車場ID}とします。
• 第2正規化
  施設IDだけで値が決まる属性を、関係「施設」として分離します（第二正規形）。
  

  施設（施設ID，カテゴリコード，カテゴリ名，施設エリアコード，施設名，施設経度，施設緯度）
  周辺施設（施設ID，駐車場ID，所要時間）

• 第3正規化
  カテゴリコードで値が決まる属性を分離します。また図4の関係「エリア」の属性から、施設エリアコードは関係「エリア」の主キー「エリアコード」を参照する外部キーであることがわかります（第3正規形）。
  

  カテゴリ（カテゴリコード，カテゴリ名）
  施設（施設ID，カテゴリコード，施設エリアコード，施設名，施設経度，施設緯度）
  周辺施設（施設ID，駐車場ID，所要時間）

設問2の解説

(1)関係スキーマの定義

• (a)
  〔Webサイトの概要〕(2)「駐車場及び施設は，いずれか一つのエリアに属する」より、関係「駐車場」には関係「エリア」の主キーを参照する外部キーが必要となります。このとき、設問1で関係「施設」に外部キー「施設エリアコード」が存在したことから、「駐車場エリアコード」とします。



• (b)(c)
  〔駐車場の概要〕(1)「月極駐車場：契約期間を定め，契約期間中は自由に入出庫できる駐車場」より、月極駐車場は駐車場単位で契約することがわかるため、外部キー「駐車場ID」が必要となります。また、〔駐車場の概要〕(1)①「利用できるのは，契約した会員だけである」より、外部キー「会員ID」が必要となります。



• (d)
  〔会員およびポイントの概要〕(2)「ポイントが付与されると，ポイント付与の基となった支払データに対して，ポイント付与済みであることが記録される」と表1の属性名「ポイント付与フラグ」の意味から、関係「月極駐車場利用」には「ポイント付与フラグ」が必要となります。



• (e)
  〔駐車場の概要〕(2)①1に時間貸駐車場は「会員でなくても利用することができる」とあります。また、〔会員およびポイントの概要〕(2)「会員には，毎月の支払額〔時間貸駐車場で会員カードを提示して支払った支払額（中略）ポイントが付与される」とあるため、関係「会員時間貸駐車場利用」には外部キー「会員ID」が必要となります。

(2)エンティティタイプ間のリレーションシップの記入
以下の内容から概念データモデルを完成させます（→は関係間の関連）。

• 〔駐車場の概要〕「駐車場は，月極駐車場と時間貸駐車場のいずれかに分類される」より、スーパタイプ「駐車場」とサブタイプ「月極駐車場」「時間貸駐車場」の関連がある
• 設問1(1)(e)の解説より、スーパタイプ「時間貸駐車場利用」とサブタイプ「会員時間貸駐車場利用」の関連と「会員→会員時間貸駐車場利用」の関連がある
• 設問1(1)(b)(c)の解説より、「会員→月極駐車場契約」「月極駐車場→月極駐車場契約」の関連がある
• 図4の複合キーより、「時間貸駐車場→時間帯別料金パターン」「時間帯別料金パターン→時間帯別料金設定曜日」「時間貸駐車場→時間貸駐車場利用」「月極駐車場契約→月極駐車場利用」の関連がある

設問3の解説

(1)ポイント管理上の不具合
〔会員及びポイントの概要〕(3)「会員は，ポイントを消費してポイント交換商品と交換することができる。交換を行うと，付与年月が古いポイントから順に消費され，その内容が記録される」とあります。そして図1、2から図2の消費ポイント300ポイントは2015年4月付与の250ポイントと5月付与の50ポイントが充当され、消費ポイント100ポイントは5月付与の10ポイントと6月付与の90ポイントが充当されているように見えます。

ところが、図4の関係「ポイント交換」の属性では、1回の交換商品で充当できるポイントの付与年月は1月分だけであり、複数の付与年月のポイントを合算してポイント交換を行う場合、どの付与年月のポイントをどれだけ消費したか記録できないことになります。

(2) (a)関係”ポイント交換”から削除する属性名
(1)より、関係「ポイント付与」から属性「付与年月」を削除します。

(2) (b)(c)関係“ポイント消費”の具体例
ポイントを交換する際に、関係「ポイント付与」データに対して、「いつ」「何ポイント消費したか」わかればよいので、図7の通りにすれば良いことになります。

図8：表2　関係“ポイント消費”の具体例

おわりに

今回は、午後Ⅰ試験の頻出内容から、データベースの設計に関する問題の解き方を紹介しました。次回も午後Ⅰ試験の頻出内容から、SQLに関する問題の解き方を紹介します。