存在する属性をサブタイプ「個人顧客」に持たせます。主キーは全て「顧客番号」とします。サブタイプの主キーはスーパタイプの外部キーとなります(図4)。図4:スーパタイプとサブタイプリレーションシップエンティティ間の関連のことで、外部キーを用いてリレーションシップを定義します。概念データモデル関係「注文」には関係「顧客」の主キーである属性「顧客番号」を参照する外部キーが存在し、またスーパタイプ「顧客」にはサブタイプ「法人顧客」と「個人顧客」が存在したとします。関係「注文」に1件の顧客に対応する注文データが複数存在した場合、概念データモデルは図5の通りです。図5:概念データモデル関係スキーマ外部キーで説明した関係「顧客」と「注文」を関係スキーマで表すと以下のようになります。顧客(顧客番号,顧客名,住所,メールアドレス)注文(注文番号,注文日,顧客番号,注文合計金額)関数従属性ある属性の値が決まれば異なる属性の値が一意に決まる、属性間の関連のことです。正規化には、「第1~5正規化」と「ボイスコッド正規化」があり、正規化した結果を「正規形」といいます。午後I試験では、第1正規形を第3正規形にする問題が出題されています。そこで、第2正規化と第3正規化について紹介します。第1正規形に対して部分関数従属性(複合キーの一部の属性だけに関数従属する属性が存在する属性間の関係)を排除し、全ての非キー属性が完全関数従属した関係のことです。含まれる属性「商品名」と「商品単価」が、主キー{受注番号,商品番号}の一部である属性「商品番号」に部分関数従属しているとします。このとき、第2正規化を実施して属性「商品番号」を主キーとする関係「商品」に分離します。これにより関係「商品」と「注文商品」は完全関数従属した状態となり、第2正規形となります(図6)。第2正規形に対して候補キーを起点とした推移的関数従属性(属性A→属性B→属性Cが成立する属性間の関係)を排除した関係のことです。いう推移的関数従属性が存在することになります。主キーとする関係「顧客」に分離します。これで関係「顧客」と「注文」には推移的関数従属性がなくなり、第3正規形となります(図7)。午後I試験を解くためのポイント一般的に、問題文中から以下の内容が読み取れることが多いので、チェックしましょう(ただし100%ではないため文脈から最終判断すること)。関係名の後ろに「ID」「番号」「コード」がついた属性名は主キーである「○○を一意に識別する」(○○は関係)と記載された属性は候補キーである「○○ごとに付与する」「○○との組み合わせで識別する」(○○は属性)と記載された属性は○○との複合キーである「○○には、(一つまたは)複数の××が存在する」(○○と××は関係)と記載されている場合、××には○○の主キーを参照する外部キーが存在する「○○には△△と▲▲がある」「○○は△△と▲▲に分類される」(○○、△△、▲▲はいずれも関係)と記載されている場合、スーパタイプが○○、サブタイプが△△と▲▲である平成28年度午後I試験問1に挑戦!それでは、平成28年度午後I試験問1に挑戦してみましょう。問題文はこちらからダウンロードしてください。解答用紙はこちらに用意しました。解答例はこちらを参照してください。問題の解答にかける時間は35分を目標にしましょう。図4の関係「周辺施設」から候補キーを選ぶために問題文を読みます。〔Webサイトの概要〕(3)に「駐車場には,一つ又は複数の周辺施設が定められている。一つの施設は,複数の駐車場の周辺施設として定められる場合がある」とあること、また、表1の「駐車場ID」「施設ID」の意味から、{施設ID,駐車場ID}が候補キーであることがわかります。駐車場緯度,施設経度,施設緯度」の意味に「駐車場及び施設には,経度及び緯度の組合せを一つ定めている。経度及び緯度を組み合わせた位置上に,駐車場又は施設が複数存在することはない。」と記載されていることから、属性「施設ID」と属性の組{施設経度,施設緯度}が1対1に対応することがわかります。従って、{施設経度,施設緯度,駐車場ID}も候補キーとなります。図4の関係「周辺施設」の属性から部分関数従属性を見つけます。表1の属性名「施設名」の意味に「施設の名称」とあることから、「施設ID→施設名」の部分関数従属性が導けます。〔Webサイトの概要〕(2)と(3)「駐車場及び施設は,いずれか一つのエリアに属する」「施設は,いずれか一つのカテゴリに属する」より、「施設ID→施設エリアコード」「施設ID→カテゴリコード」の部分関数従属性が導けます。表1の属性名「カテゴリ名」の意味に「カテゴリの名称」とあることから、「カテゴリID→カテゴリ名」の関数従属性が導けます。これと(1)の部分関数従属性から、「施設ID→カテゴリコード→カテゴリ名」の推移的関数従属性が存在することがわかります。主キーは{施設ID,駐車場ID}とします。施設IDだけで値が決まる属性を、関係「施設」として分離します(第二正規形)。施設(施設ID,カテゴリコード,カテゴリ名,施設エリアコード,施設名,施設経度,施設緯度)周辺施設(施設ID,駐車場ID,所要時間)第3正規化カテゴリコードで値が決まる属性を分離します。図4の関係「エリア」の属性から、施設エリアコードは関係「エリア」の主キー「エリアコード」を参照する外部キーであることがわかります(第3正規形)。カテゴリ(カテゴリコード,カテゴリ名)施設(施設ID,カテゴリコード,施設エリアコード,施設名,施設経度,施設緯度)周辺施設(施設ID,駐車場ID,所要時間)設問2の解説(1)関係スキーマの定義(a)〔Webサイトの概要〕(2)「駐車場及び施設は,いずれか一つのエリアに属する」より、関係「駐車場」には関係「エリア」の主キーを参照する外部キーが必要となります。このとき、設問1で関係「施設」に外部キー「施設エリアコード」が存在したことから、「駐車場エリアコード」とします。(b)(c)〔駐車場の概要〕(1)「月極駐車場:契約期間を定め,契約期間中は自由に入出庫できる駐車場」より、月極駐車場は駐車場単位で契約することがわかるため、外部キー「駐車場ID」が必要となります。〔駐車場の概要〕(1)1「利用できるのは,契約した会員だけである」より、外部キー「会員ID」が必要となります。「ポイント付与フラグ」が必要となります。(e)〔駐車場の概要〕(2)11に時間貸駐車場は「会員でなくても利用することができる」とあります。外部キー「会員ID」が必要となります。以下の内容から概念データモデルを完成させます(→は関係間の関連)。〔会員及びポイントの概要〕(3)「会員は,ポイントを消費してポイント交換商品と交換することができる。交換を行うと,付与年月が古いポイントから順に消費され,その内容が記録される」とあります。図1、2から図2の消費ポイント300ポイントは2015年4月付与の250ポイントと5月付与の50ポイントが充当され、消費ポイント100ポイントは5月付与の10ポイントと6月付与の90ポイントが充当されているように見えます。ところが、図4の関係「ポイント交換」の属性では、1回の交換商品で充当できるポイントの付与年月は1月分だけであり、複数の付与年月のポイントを合算してポイント交換を行う場合、どの付与年月のポイントをどれだけ消費したか記録できないことになります。(a)関係”ポイント交換”から削除する属性名(1)より、関係「ポイント付与」から属性「付与年月」を削除します。(b)(c)関係“ポイント消費”の具体例ポイントを交換する際に、関係「ポイント付与」データに対して、「いつ」「何ポイント消費したか」わかればよいので、図7の通りにすれば良いことになります。関係“ポイント消費”の具体例おわりに今回は、午後I試験の頻出内容から、データベースの設計に関する問題の解き方を紹介しました。次回も午後I試験の頻出内容から、SQLに関する問題の解き方を紹介します。所属し、データベース領域の教育(特にOracleデータベース)を担当。現在は情報システムの開発技術教育を担当し、要件定義や設計担当者を育成している。受講者が主体的に考え、楽しく学べる研修の提供により、高い評価をいただいている。ITでは、より付加価値の高いコンテンツを会員サービスとして提供しています。ITのWebサイトにログインすることでさまざまな限定特典を入手できるようになります。VeloCloudが選ばれる理由SD-WANとはなにか。Linuxエンジニア育成への思いF5とNGINXの組み合わせで、サービス公開をよりセキュアに。ITがお届けするポッドキャストです。