できるエンジニアになるためのちょい上DB術/第2章 概念設計
3.3 論理設計の手順(4/4)
3.3.8 ER図からRDBMSへの変換
最後に論理設計で設計したER図の各要素をデータベースの各オブジェクトに変換します。
ER図の要素 | RDBMS |
---|---|
エンティティ | 表 |
属性 | 列とデータ型、データ長 |
識別子 | 主キーと索引 |
リレーションシップ | 外部キー |
ビジネスルール | 制約 |
参照制約 | 外部キー制約 |
削除制約 | 外部キー制約+オプション |
最後に論理設計で設計したER図の各要素をデータベースの各オブジェクトに変換します。
ER図からRDBへの変換
図3-21で示されるER図をデータベースの表に変換してみましょう。
図3-21のエンティティをRDBMSの各要素に変換すると図3-22のようになります。
リレーションは、1対多の多側のエンティティに、1側の一意識別子を外部キーとして取り込むことによって属性に変換されます。
多側の属性に1側の一意識別子を、外部キーとして取り込むことによって、「多側の行がどの1側の行に対応しているか」を外部キーの値で判断できるようになります。
クイズ4
図3-23の関係にあるエンティティを考えた場合、外部キー列がnull値を許すかどうかを検討しなさい。
- 1.顧客一重注の関係で、受注の外部キー列:顧客番号
- 2.商品一受注明細の関係で、受注明細の外部キー列:製品番号
クイズ4の解答例
スーパータイプ/サブタイプのRDBMSへの変換
基本的なER図からの変換作業は以上ですが、スーパータイプ/サブタイプを表に実装する方法は次の3とおりが考えられます。
この3パターンを比較し、場合によって最も最適なパターンを使い分けられるようにしてください。
【pattern1】
すべてのエンティティを1つの表にまとめる
【pattern2】
スーパータイプに1つの表、それぞれのサブタイプごとに1つずつ表を作成する
【pattern3】
サブタイプにスーパータイプの共通属性をもたせてサブタイプの数分、表を作成する
図3-24 スーパータイプ/サブタイプのER図
【pattern1】すべての行を同一表
- 全行を1つの表で管理するため、結合処理が不要
- サブタイプを見分けるための属性「職種」を追加しているため、サブタイプごとの行の識別が容易
- 柔軟性に欠ける
(サブタイプの追加は列の追加を伴うため、表の定義そのものが変更される必要がある) - null値をもつ列が増える
(自分が属するサブタイプ以外のサブタイプに必要な属性はnullになる)
【pattern2】スーパータイプ/サブタイプごとに別表
- サブタイプの行を取得するために、必ず結合が必要になる
(パフォーマンスが悪い) - 柔軟性が高い
(サブタイプの追加は表の追加なので、変更が容易) - 正規化を忠実に実装しているため、記憶容量も最小で済む
【pattern3】サブタイプの数だけ表作成
- サブタイプのみの検索であれば結合は不要
- スーパータイプのレベルで検索したい場合はUN10N旬を使用する必要があり、パフォーマンスは劣る
- 柔軟性が高い
(サブタイプの追加は表の追加のみで済むため、変更が容易)
3つのパターンをマトリクスにまとめると、以下のようになります。
pattern | RDBMS実装方法 | メリット | デメリット |
---|---|---|---|
1 | すべての行を同一表 | 全行検索の際、結合処理が不要 サブタイプごとの検索も可能 |
サブタイプの増加に弱い |
2 | スーパータイプ/サブタイプごとに別表 | 正規化に準じているため、柔軟性が高い:サブタイプの追加が容易 | サブタイプレベルの行検索でも、2表の結合が必要 |
3 | サブタイプの数だけ表作成 | サブタイプのみの検索であれば結合は不要 サブタイプの追加は容易 |
スーパータイプレベルでの全行検索は、UNION句を使用して結合する必要がある |
3つのパターンをマトリクスにまとめると、以下のようになります。
アプリケーションでどのような問い合わせが最も頻繁に起きるか、サブタイプの追加がどの程度の頻度で起きる可能性があるか、また、それぞれの行数なども考えてどのパターンで実装するかを決めてください。
論理設計で作成される表定義
【画像をクリックで拡大します】
論理設計で作成される索引定義
インデックス名 | 表名 | 種類 | 構成列名 | キー長 | キー 種類数 |
DISTINCT値 |
---|---|---|---|---|---|---|
ORDER_PK | ORDERS | PK | ORDER_ID | 8 | 1 | |
CUST_ORDER_IX | ORDERS | CUST_ORDER_ID | 8 | 1 | ||
ORD_CUST_IX | ORDERS | CUST_COMAPNY_ID | 5 | 3 | ||
CUST_DEPT_ID | 3 | 3 | ||||
CUST_SEQ_ID | 3 | 3 | ||||
ORD_DLV_IX | ORDERS | DLV_COMPANY_ID | 5 | 3 | ||
DLV_SEPT_ID | 3 | 3 | ||||
DLV_SEQ_ID | 3 | 3 | ||||
ORD_ACCT_IX | ORDERS | ACCT_COMPANY_ID | 5 | 3 | ||
ACCT_DEPT_ID | 3 | 3 | ||||
ACCT_SEQ_ID | 3 | 3 | ||||
DEPT_IX | ORDERS | DEPARTMENT_ID | 3 | 1 | ||
EMP_IX | ORDERS | EMPLOYEE_ID | 5 | 1 |
表3-16 販売管理サブシステム:受注表(ORDERS)
解説トレーナー