関係DBスキーマ設計事例

関係DBのスキーマ設計事例

96年総選挙のデータ分析

ステップ1：プログラム仕様の確定

ステップ2：属性の抽出

ステップ3：計算で求められる属性の除去

ステップ4：関数従属性の抽出

ステップ5：関数従属性に従ったスキーマ設計

ステップ6：完成した関係スキーマ

ステップ1：プログラム仕様の確定

データベース化の対象となるデータが確定している場合には，ステップ1はスキップできる．（データ独立性を確保するためには，スキップした方が良い．）

アプリケーション開発にデータ入力が含まれる場合には，ステップ1を行うことで不要な作業を減らせる．

アプリケーションが必要とする機能を列挙する．

各機能毎に必要なデータ（属性）を列挙する．

各属性は複数のフィールドから構成されないこと．複数フィールドから構成される場合には，各フィールドを属性とする．

小選挙区選挙

比例代表選挙

党派別の当選者数と得票総数（正式名称，当選者数，得票総数）

小選挙区別結果（県名，選挙区，氏名，党派，議員歴，得票数）

選挙区/得票順当選者一覧（県名，選挙区，氏名，党派，議員歴，得票数）

落選者一覧（県名，選挙区，氏名，党派，議員歴，得票数，惜敗率）

区分別当選者数集計（党派，議員歴，当選者数，当選者総数）

ブロック別集計（ブロック，正式名称，当選者数，得票総数）

比例代表議席数（ブロック，正式名称，議席数，得票総数）

比例代表選挙結果（ブロック,正式名称，氏名，議員歴，名簿順位，惜敗率，当落）

ステップ2：プログラムが必要とするデータ（属性）の抽出

党派の正式名称，党派別小選挙区当選者数（全国），党派別小選挙区得票総数（全国），ブロック，県名，選挙区，氏名，党派，議員歴，得票数，惜敗率，党派別小選挙区当選者数（各ブロック），党派別小選挙区得票総数（各ブロック），党派別比例代表議席数（各ブロック），党派別比例代表得票総数（各ブロック），名簿順位，当落

（注）：重複立候補者を考慮して，小選挙区立候補者と比例代表立候補者は区別しない．

（注）：特定の情報を属性値とするか属性とするかの選択

属性とする例

学籍番号	数学	理科	英語
90S401	100	80	60
90S402	85	90	70
90S403	75	70	100

属性値とする例

学籍番号	科目	成績
90S401	数学	100
90S401	理科	80
90S401	英語	60
90S402	数学	85
90S402	理科	90
90S402	英語	70
90S403	数学	75
90S403	理科	70
90S403	英語	100

属性とすると，データ量が少なくて済む．

属性値とすると，科目の追加・削除に強い．（スキーマ変更が不要）

二次記憶の大容量化の現状を考慮すると，追加削除が予想される情報は属性値とする方が有利な場合が多い．

ステップ3：計算で求められる属性の除去

計算で求められる属性は，原則として関係スキーマに含めない．（冗長性の除去）

計算のために必要な属性があれば，それを追加する．

計算で求められる属性	計算のために必要な属性
党派別小選挙区当選者数（全国）	各小選挙区の当選者，当選者の所属党派
各小選挙区の当選者	各小選挙区の立候補者，各立候補者の得票数
党派別小選挙区得票総数（全国）	小選挙区の各立候補者の得票数，所属党派
惜敗率	立候補者の得票数，その立候補者の小選挙区における当選者の得票数
党派別小選挙区当選者数（各ブロック）	各小選挙区の当選者，当選者の所属党派，小選挙区の所属ブロック
党派別小選挙区得票総数（各ブロック）	小選挙区の各立候補者の得票数，所属党派，小選挙区の所属ブロック
党派別比例代表議席数（各ブロック）	党派別得票数（各ブロック），ブロック定数よりドント式アルゴリズムを適用して求める．
党派別比例代表得票総数（各ブロック）	党派別得票数（各ブロック）
当落	ブロック，党派，名簿順位，惜敗率，党派別比例代表議席数（各ブロック）よりプログラムで求める．

プログラムで求める属性（比例代表議席数，当落，惜敗率，ブロック別の党派得票数）は，Visual Basicプログラムの実行効率を考慮して，属性として格納する．（計算時間の短縮）

ステップ4：関数従属性の抽出

抽出された属性

党派の正式名称，ブロック，県名，選挙区，氏名，党派，議員歴，得票数，党派別比例代表議席数（各ブロック），名簿順位，当落，ブロック定数，惜敗率，党派別比例代表得票数（各ブロック）

上記属性間に成立する関数従属性（FD, Functional Dependency）

候補者番号 → 県名，選挙区，得票数

小選挙区立候補者に対して成立

同姓同名を考慮すると，氏名だけでは立候補者を特定できない．

属性「候補者番号」を導入

党派 → 正式名称

逆方向の関数従属性も成立する．

データ圧縮のため，こちら方向のFDを使用する．

党派毎に異なる略称が付けられていると仮定する．

ブロック → ブロック定数

党派，ブロック → 議席数，得票総数

県名 → ブロック

党派，県名 → 得票数

ブロック毎の党派得票数は，県別の得票数から求める．

候補者番号 → 氏名，党派，議員歴

全ての候補者に対して成立

候補者番号 → ブロック，名簿順位，当落，惜敗率

比例代表立候補者に対して成立

ステップ5：関数従属性に従ってスキーマを設計する．

それぞれの関数従属性に現れる属性を用いて関係スキーマを構成する．

関数従属性の左辺が関係スキーマのキーとなる．

原則として，それぞれの関係スキーマは異なるキーを持つようにする．（左辺が同一の関数従属性は，一つの関係スキーマにまとめる．）

上記の例外事項

「小選挙区得票」に出現する候補者は，「立候補者」に出現する候補者の一部

「比例代表名簿」に出現する候補者は，「立候補者」に出現する候補者の一部

上記の情報を「立候補者」に格納した場合，空値が多く発生するため，分かり難くなる．

関数従属性に現れない属性が存在した場合，独立した関係スキーマを定義する．

関係スキーマ	関数従属性
小選挙区得票	候補者番号 → 県名，選挙区，得票数
政党	党派 → 正式名称
比例代表ブロック	ブロック → ブロック定数
比例代表議席数	党派，ブロック → 議席数，得票総数
比例代表県	県名 → ブロック
比例代表得票	党派，県名 → 得票数
比例代表名簿	候補者番号 → ブロック，名簿順位，当落，惜敗率
立候補者	候補者番号 → 氏名，党派，議員歴

スキーマグラフを作成する．

ステップ6：完成した関係スキーマ

関係「小選挙区得票」

属性	主キー	データ型	説明
候補者番号	*	整数	テーブル「立候補者」に登録した候補者番号
県名		文字列	例：熊本1区の場合、熊本熊本1区の場合、1
選挙区		整数	例：熊本1区の場合、熊本熊本1区の場合、1
得票数		整数	候補者の得票数

関係「政党」

属性	主キー	データ型	説明
党派	*	文字列	全角1文字略称（例：自由民主党 → 自）
正式名称		文字列

関係「比例代表ブロック」

属性	主キー	データ型	説明
ブロック	*	文字列	比例代表ブロック名
ブロック定数		整数

関係「比例代表議席数」（属性値はプログラムで設定するため，入力不要．）

属性	主キー	データ型	説明
党派	*	文字列	関係「政党」に登録した党派名略称
ブロック	*	文字列	比例代表ブロック名
議席数		整数
得票総数		整数

関係「比例代表県」

属性	主キー	データ型	説明
県名	*	文字列
ブロック		文字列	比例代表の所属ブロック
県番号		整数	通し番号（並べ替えのために使用）

関係「比例代表得票」

属性	主キー	データ型	説明
党派	*	文字列	関係「政党」に登録した党派名略称
県名	*	文字列	例：熊本県の場合、熊本
得票数		整数	候補者の得票数

関係「比例代表名簿」

属性	主キー	データ型	説明
候補者番号	*	整数	関係「立候補者」に登録した候補者番号
ブロック		文字列	例：北海道ブロックの場合、北海道
名簿順位		整数	候補者の名簿順位（所属政党の当該ブロックにおける順位）
当落		論理型	当選または落選（アプリケーション側で設定する）
惜敗率		実数型	小選挙区との重複立候補者の惜敗率（アプリケーション側で設定する）

関係「立候補者」

属性	主キー	データ型	説明
候補者番号	*	整数	各候補者に異なる番号をつける。小選挙区、比例代表、重複立候補の区別はしない。
氏名		文字列
党派		文字列	関係「政党」に登録した党派名略称
議員歴		文字列	前職「前」、元職「元」、新人「新」

質問およびコメントは掛下（kake@is.saga-u.ac.jp）まで．

sitemap