カテゴリー
DB

「多対多」と関連実態

現実世界に存在するエンティティを、ER図として記述しようとすると、「多対多」の関係を持ったエンティティができることがある。

例)

・1人の学生は、複数の講義に出席できる。

・1つの講義には複数の学生が参加できる。

・「多対多」の関連が成立している

「多対多」の問題

・両者のエンティティが共通となるキー列がないため、両エンティティを結合できない。

・もし無理矢理に関連づけるなら、「学生」テーブルに「講義コード」列を持つようにする

→しかし、それだと「学生」テーブルに、1人の学生が複数行含まれるようになってしまう。

→講義を未登録の学生は、「学生」テーブルにも登録できなくなってしまう。

・「講義」テーブルに「学生コード」列を追加したとしても同じ問題が発生する。

関連実体(associative entity)

「多対多」の問題を解決するための方法

・「受講」エンティティは「学生」エンティティと「講義」エンティティの主キーを組み合わせたキーを主キーに持つ

・すると、学生と受講の間の関連は「1対多」になる

・講義と受講の間の関連も「1対多」になる

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

IDEF1XでER図を描く

IDEF1X(アイデフワンエックス)によるER図

・角が尖ったエンティティ(会社、部署)・・・独立エンティティ:他のテーブルのデータに依存することなく、データを保持できる

・角が丸いエンティティ(社員)・・・従属エンティティ:他のテーブルにデータが存在しないと、データを保持することができない

・主キーに他テーブルを参照する外部キー(上記では会社コード)を含んでいると、従属エンティティになる

・主キーに他テーブルを参照する外部キーを含んでいなければ独立エンティティになる

カーディナリティのパターン

・カーディナリティの「多」は●で表現する。

・●は「多」の側のエンティティだけにつける。

・1対多の「1」の側には●を付けない

・部署テーブルの側の「◇」は、カーディナリティが「1」であっても部署コードがNULLでありえ、社員から部署が1つに決まらない可能性があることを表す。

・同じ外部キーでも会社コードは社員テーブルの主キーの一部であるため、NULLにはなりえないが、部署コードは主キーではないのでNULLになりえる

・外部キーにNULLを許さない場合:依存リレーションシップ:エンティティ間を実線にする。

→社員が必ず会社に属しなければならない

・外部キーにNULLを許す場合:非依存リレーションシップ:エンティティ間を点線にする。

→必ずしも社員が部署に所属しなくてもよい

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

IE表記法でER図を描く

IE(Information Engineering)表記法によるER図

見方

・会社エンティティのそばについている横棒「ー」は、相手のエンティティと対応するレコード数(カーディナリティ)が1であることを示す

・社員エンティティの側の「○」、三叉の線はそれぞれ、カーティナリティが「0」および「複数」を意味する。つまり、この2つを合わせて「0以上の複数」を意味する

・IE表記法が鳥の足と呼ばれるのは、複数を表す三叉の線に由来しています。

このER図は

・会社と社員の関連が「1対多(0も含む)」であることを表している

・「会社」テーブルから見れば、「社員」テーブルは0~複数人が対応する

・一方、「社員」テーブルから見れば、1つのレコード(=1人の社員)を決めれば必ず1つの会社が対応する

・部署と社員の関連も「1対多」。ただし部署の場合は、1人も社員が存在しない部署は存在しない。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

ER図の描き方

ER図でのテーブル(エンティティ)の表現方法

・四角の中を横線で1つのスペースに区切る

・上のスペースは主キー、下のスペースは非キー

・非キーが多い場合は代表的なものだけ記述する

・他テーブルの主キーを参照する外部キー(foreign key)は属性名の隣に「FK」記述する

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

1対1、1対多、多対多

一般に次の3パターンの関連がありえる。

1対1

あまり見かけない。

というのも、2つのテーブルのレコードが1対1に対応するということは、2つのテーブルの主キーが一致するケース

そうすれば、普通は1つのテーブルにまとめても問題ないため。

正規化の過程でこのような1対1のテーブルが作られることはない

1対多

最もよくある関連。

基本的に正規化によって生まれる関連はこのパターン

厳密には「1対多」、「0または1対多」がある

「多」の側についても「0以上」と「1以上」の場合があるが、こうした細かい区別についても、ER図で表記できる

多対多

少し特殊なパターン

最初に業務要件からテーブルを作っていくと、この「多対多」の関連を持ったテーブル郡ができることがある。

ただし、RDBのお約束として、多対多の関連は作ってはならないということになっている。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

テーブル同士の関係を見抜く

第3正規化した社員テーブルをサンプルにER図を描く

社員テーブル

会社テーブル

部署テーブル

ER図を描く時、最初に着目するポイントは、あるテーブルの主キーが、他のテーブルに列として含まれているかどうか、という点です。

なぜなら、もしのの場合、2つのテーブルの間には関連があるからです。

「会社」テーブルと「社員」テーブルの場合

「会社」テーブルの主キーである会社コードが「社員」テーブルにも含まれている。

「部署」テーブルの主キーである部署コードも「社員」テーブルに含まれている

このような場合、2つのテーブルの間には1対多の関係が成立している。

たとえば、「会社」テーブルには、1つの会社は1行しか含まれないが(会社コードが主キーなので当然)「社員」テーブルには1つの会社が複数行現れる。

つまり、「会社:1」に対して「社員:多」という関係がある。

これを日本語で言えば「1つの会社では複数の社員が働いている」と言える。

より厳密にいえば、「会社」テーブルには1人も社員のいない会社「C建設」も登録可能なので、「1つの会社には0~n人の社員が働いている」と言える。

同様に、「部署」テーブルについても、「1つの部署には1~n人の社員が所属している」といえる。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

ER図~複数のテーブルの関係を表現する

ER図(Entity-Relationship-Diagram:実体関連図)

Entity:実体(テーブル)と、Relationship:関連を表す。

代表的なフォーマットに

・IE(Information Engineering)表記法

・IDEF1X(アイデフワンエックス)

がある

IE(Information Engineering)表記法

通称「鳥の足」という名前で知られている

直感的にテーブル間の関連を理解しやすいため、初心者向けの記法

IDEF1X(アイデフワンエックス)

米国で企画された表記法で、すべての機能を学ぼうとすると、かなり覚えることが多い

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

損失分解

以下のようにテーブルをいい加減に分割してしまえば、損失分解になる

この分解は、元のテーブルを復元不可能な分解です。

両方のテーブルを結合するためのキーが存在しないからです。

しかも、年齢部署テーブルに主キーがないという点で、実際にこのように分解する価値はない。

RDBにおいて、主キーを持たないテーブルを作ることは許されない。

次のように、年齢部署テーブルに「社員ID」を主キーとしてもたせれば無損失分解となる。

これは正規化ではない(垂直分割)が、元のテーブルを復元可能な無損失分解です。

つまり、無損失分解であれば必ず正規化になるわけではない。

損失分解、無損失分解、正規化の関係を図示すると以下のようになる

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

正規化は常にするべきか?

・第3正規形までは、原則として行う

・関連エンティティが存在する場合は、関連とエンティティが1対1に対応するよう注意する

一般的に正規化を行えば行うほど、以下のメリットがある

メリット1

データの冗長性が排除され、更新時の不整合を防止できる

メリット2

テーブルの持つ意味が明確になり、開発者が理解しやすい

データベースの第1目的はデータを整合的な状態で保持することにあるため、何よりもデータ不整合を防止することに主眼が置かれる。

正規化はそのために考え出された方法論です。

一方、正規化の主な欠点は以下のようにパフォーマンスに関わるものです。

デメリット

テーブルの数が増えるため、SQL文で結合を多様することになり、パフォーマンスが悪化する

この欠点は、しばしば無視できないため、パフォーマンス向上のため、あえて正規形を低次なものに届ける設計が採用されることはあります。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

正規化の3つのポイント

ポイント1

正規化とは更新時の不都合/不整合を排除するために行う

正規化を行う目的は、何よりも更新(データ登録=INSERTも含む)時の不都合を防ぐためのもの。

また、データの冗長性を排除して、人間のオペレーションミスによるデータ不整合を防ぐ目的もある。

ポイント2

正規化は従属性を見抜くことで可能になる

正規化を行うためには、テーブル内部の従属性の関係を見抜く必要がある。

部分関数従属(第2正規形)

推移関数従属(第3正規形)

が存在していれば、まず正規化の対象になる。

また、

多値従属性が存在していたら、自分が第4正規形に反する設計をしていないか注意をする。

ただし、こうした従属性はテーブルを形式だけ見ていてもわからない。

どの列がとのキーに従属しているか、ということが業務ロジック(ビジネスルール)で決まるので、各列が業務上どのような意味と関係を持っているか、ということを調べなければならない。(業務分析の必要性)

ポイント3

正規形はいつでも非正規形に戻せる

正規化によって分割されたテーブルは、いつでも非正規化テーブルに復元することができる。

これは正規化が情報を完全に保存する無損失分解だから。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ