データベース設計の手順!論理設計と正規化について詳しく書いてみる

  • 45
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

そもそも論理設計って?

データベースの構造には3層あります。

外部スキーマ

ユーザーから見たデータベース
DBがどのような機能やインターフェースを持つかを定義

概念スキーマ

開発者から見たデータベース
DBのデータ要素やデータ同士の関係を定義
概念スキーマは外部スキーマ、内部スキーマ両者の変更が互いに影響しないための緩衝材を担う
->データ独立性を実現する

内部スキーマ

DBMSから見たデータベース
論理データモデルをどのようにDBMSに格納するかを定義

論理設計とは概念スキーマを定義する設計のことを言います。

論理設計のステップ

1.エンティティの抽出
エンティティ=DBのテーブル、日本語で実体を意味します。
まずはどのようなエンティティが必要になるか、どういうデータを扱いたいかを列挙しましょう。

2.エンティティの定義
各エンティティがどのようなデータを扱うか(=各表がどのような列を持つか)を定義します。

3.正規化
システムがエンティティをスムーズに利用できるよう整理します。
こちらについては下に詳しく記述しています。

4.ER図の作成
エンティティ同士の関係を表現するER図を作成します。

正規化の前に

正規化とは

正規形とは、データベースで保持するデータの冗長性を排除し一貫性と効率性を保持するためのデータ形式です。
すなわち正規化とは冗長性や非一貫性の問題を解決するために考案された方法論です。

正規化は慣れると機械的にできます。実務における問題はむしろ正規化が終わった後にいかに崩すなのですが...まずは正規化をクリアしましょう。

関数従属性について

正規化において関数従属性の理解は必須です。

関数従属性 = 「ある属性Aの値が決まるとき、属性Bが一意に決まること」

関数は数学で習った y=f(x)のことで、xが決まればyが1つに決まります。これを「yはxに従属する」と表現します。

データベースでは「x列の値が決まればy列の値が一つに決まる」ことをいい、正規化とは
「テーブル全ての列が関数従属性を満たすように整理していくこと」です。

では具体的に第1~3正規化のやり方を説明していきます。

※実際には第5正規化までありますが、第3正規化まで達成したらその時点で以降の正規形の条件を満たすことがほとんどなので、基本的には第3正規化まで理解すれば十分です。

第1正規化

「一つのセルの中に一つの値しか含まない状態にすること」

:before

ID(主)
001 明石家さんま,大竹しのぶ IMALU
002 石原慎太郎 伸晃,良純

簡単なWebアプリを作ったことがある人はこのようなデータベースをそもそも作らないと思いますが、

:after

ID(主)
001 明石家さんま IMALU
002 大竹しのぶ IMALU
003 石原慎太郎 伸晃
004 石原慎太郎 良純

このように一つのセルに一つの値しか含まないよう、今回は行を増やして解決です。

第1正規化しないとなぜダメなの?

答えは単純で、セルに複数の値を許せば主キーが各列の値を一意に決定できなくなってしまいます。

第2正規化

「テーブル内の部分関数従属を解消し、完全関数従属のみのテーブルを作ること」

:before

学校コード(主) 学校名 学籍番号(主) 生徒名
001 A学校 000A たけし
001 A学校 000B ゴン蔵
002 B学校 010F ボンチュー

このテーブルは主キーが{学校コード,学籍番号}で、他の全ての列はこのキーに従属するが「学校名」だけは主キーの一部である「学校コード」にのみ従属しています。

このように主キーの一部の列に対して従属する列がある場合、この関係を 部分関数従属といい、
これに対して主キーを構成する全ての列に従属性がある場合を 完全関数従属といいます。

:after

生徒テーブル

学校コード(主) 学籍番号(主) 生徒名
001 000A たけし
001 000B ゴン蔵
002 010F ボンチュー

学校テーブル

学校コード(主) 学校名
001 A学校
002 B学校

このように部分関数従属の関係にあるキー列と従属列だけの独立テーブルを作ればいいです。

第2正規化しないとなぜダメなの?

上の例の場合、もし学生の情報が不明のC学校があった場合、テーブル登録ができなくなります。
主キーの一部に学籍番号が含まれているので、これがNULLではレコードを登録できないのです。

第2正規化後なら、C学校を学校テーブルに登録するだけで問題ないですね。

第3正規化

「推移的関数従属を分離すること」

:before

生徒テーブル

学校コード(主) 学籍番号(主) 生徒名 部活コード 部活名
001 000A たけし D03 野球部
001 000B ゴン蔵 D01 サッカー部
002 010F ボンチュー D03 野球部

学校テーブル

学校コード(主) 学校名
001 A学校
002 B学校

これではA学校の部活が2つ存在することしかわからず、テニス部があるかもしれません。
生徒が一人もいない部活を学籍番号が主キーである生徒テーブルには登録できません。

このような不具合は生徒テーブルの中にまだ隠れた関数従属性が残っているからです。

この二つの列の間には
{部活コード} -> {部活名}

という非キーから非キーへの関数従属が成立します。

つまり

{学校コード,学籍番号} -> {部活コード} -> {部活名}

という二段階の関数従属が存在します。このような段階的な従属関係のことを 推移的関数従属といいます。

:after

生徒テーブル

学校コード(主) 学籍番号(主) 生徒名 部活コード
001 000A たけし D02
001 000B ゴン蔵 D01
002 010F ボンチュー D02

部活テーブル

部活コード(主) 部活名
D01 サッカー部
D02 野球部

学校テーブル

学校コード(主) 学校名
001 A学校
002 B学校

部活を管理するための「部活」テーブルを独立させることで、全てのテーブルについて非キー列はキー列に対してのみ従属するようになり、推移的関数従属もなくなります。