SQL シリーズ

データベース学習 1 データベース導入: https://blog.yexca.net/ja/archives/86
データベース学習 2 関係モデル: https://blog.yexca.net/ja/archives/87
データベース学習 3 SQL 言語: https://blog.yexca.net/ja/archives/88
データベース学習 4 関係データベース理論: https://blog.yexca.net/ja/archives/89
データベース学習 5 正規形: https://blog.yexca.net/ja/archives/90
データベース学習 6 データベース設計: この記事
データベース学習 7 データベースの制御機能: https://blog.yexca.net/ja/archives/92

データベース設計の戦略

トップダウン（上から下へ）とボトムアップ（下から上へ）の2つの方法があって、たいていはこの2つを組み合わせて使うよ。

データベース設計のステップ

ニューオーリンズ法は、現在広く認められている、かなり完成度が高くて権威のあるデータベース設計手法なんだ。これはデータベース設計を主に4つの段階に分けているよ。

1. ユーザ要件分析：ユーザのニーズを集めて、システムの境界を決める。
2. 概念設計：概念モデルを記述する。E-R 法を使うのが理想的だね。
3. 論理設計：E-R 図を関係スキーマに変換して、正規化を行う。
4. 物理設計：論理モデルをコンピュータ上で具体的にどう実現するか決める。

その後に、データベースの実施段階と、運用・保守段階へと進むんだ。

要件分析段階

要件分析は、プロジェクトが決まった後に、ユーザと設計者が「どんなデータ（内容）を扱うか」「どんな機能（動作）が必要か」を整理して記述すること。つまり、ユーザの視点からシステムを理解する作業だね。

このプロセスは、その後の論理設計や物理設計、それにアプリケーション設計のすべての根拠になる、とっても大事な基礎なんだ。

• 要件分析段階の作業

組織の状況調査 → 業務活動の把握 → ユーザ要件の明確化 → システムの境界決定 → システム機能の分析 → システムデータの分析 → 分析報告書の作成

• 要件分析段階のドキュメント

調査で得られたデータは断片的だったり局所的だったりするから、分析担当者と設計者はさらに分析を進めてユーザの要望を表現し、要件定義書、データ辞書、データフロー図 (DFD) を作る必要があるよ。

E-R モデル

E-R (実体-関連) モデルは概念モデルの一つで、現実世界を表現するために使われるよ。

実体 (Entity)

長方形で表して、枠の中に実体名を書くよ。

関連 (Relationship)

ひし形で表して、枠の中に関連名を書く。実体と無向線（矢印のない線）でつなぎ、線の横に関連のタイプ (1 : 1、1 : n、m : n) を書き込むんだ。

<1> 2つの異なる実体間の関連

• 1対1 (1 : 1)：実体集合 E₁ の1つの実体が、実体集合 E₂ の最大1つの実体と関連していること。
• 1対多 (1 : n)：実体集合 E₁ の1つの実体が、実体集合 E₂ の複数の実体と関連できること。
• 多対多 (m : n)：実体集合 E₁ の複数の実体が、実体集合 E₂ の複数の実体と関連できること。

<2> 3つ以上の異なる実体集合間の関連

3つ以上の実体集合の間でも、1 : 1 : 1、1 : 1 : n、1 : m : n、r : n : m といった関連が存在するよ。

<3> 同一実体集合内のバイナリ関連

同じ実体集合の中の実体同士でも、1 : 1、1 : n、m : n の関連があるんだ（例：上司と部下など）。

属性 (Attribute)

属性は実体の持つ特徴のことで、楕円で表すよ。枠の中に属性名を書き（主キーには下線を引く）、実体と線でつなぐ。

それぞれの属性には値の範囲（ドメイン）があるんだ。同じ実体集合なら属性やドメインは同じだけど、具体的な値は実体ごとに異なるよ。

E-R モデルの属性には、こんな分類がある：

1. 単純属性と複合属性

単純属性は、それ以上分解できない最小単位のもの。複合属性は、さらに小さな部分（別の属性）に分けられるもの。 特に断りがない場合は、ふつう単純属性を指すよ。

2. 単値属性と多値属性

単値属性は、ある特定の実体に対して値が1つだけ決まるもの。 多値属性は、値が複数あるもの。

3. NULL 属性

実体がある属性の値を持っていない、あるいは不明なときに NULL 値を使うよ。「意味がない」か「わからない」ってことだね。

4. 派生属性

他の属性から計算して導き出せる属性のこと。 例えば、社員に「入社日」と「勤続年数」という属性があるとき、「勤続年数」は「現在の日付」と「入社日」の差から計算できるよね。この場合「勤続年数」が派生属性になるんだ。

弱実体 (Weak Entity)

現実世界には、実体間の従属関係を表す特殊な関連があるんだ。例えば「社員」と「家族」の関連。家族は常に特定の社員に属しているよね。このように、ある実体の存在が別の実体の存在を前提としている場合、その実体を「弱実体」と呼ぶよ。

E-R 図では、弱実体は二重線の長方形で表すんだ。

スーパークラスとサブクラス

例えば「職員」という実体は、「パイロット」「整備士」「管理者」という実体のスーパークラス（親クラス）になれる。スーパークラスとサブクラスの間には継承関係があるよ。

サブクラスの長方形は両端に縦線があり、スーパークラスとの連結線には丸印（○）がつくんだ。

概念構造設計段階

E-R 法を使って概念モデルを構築する段階だよ。

要件分析で得られたデータを分類・整理して、実体、属性、関連を特定していく。具体的なステップとしては、局所的な応用範囲を選び、個別の E-R 図（部分 E-R 図）を設計し、それらを最後に統合するんだ。

要件分析 → データの抽象化 → 部分ビューの設計 → 統合と衝突の解消 → 冗長性の排除と修正 → 論理構造設計へ

局所的な応用の選択

データフロー図 (DFD) の適切なレベルを選んで、その各部分を1つの局所的な応用に対応させ、特定の機能を実現するようにするよ。

部分 E-R 図の設計

各応用範囲を分けた後、それぞれに対して E-R 図（部分 E-R 図、またはローカル E-R 図）を設計していくんだ。

E-R 図の統合

部分 E-R 図ができあがったら、それらを1つにまとめる。このとき、図の間で起きている衝突を解決するのが目的だよ。最終的に1つのグローバル E-R 図を完成させるんだ。

衝突には主に次の3つのタイプがある：

1. 属性の衝突

同じ属性が別の部分 E-R 図にあるとき、設計者や視点の違いで、型、値の範囲、単位などが一致しないことがある。コンピュータには1つの形式でしか保存できないから、ここで統一する必要があるんだ。

2. 命名の衝突

意味は同じなのに名前が違う、あるいは名前は同じなのに意味が違う、といったケース。これも統一が必要だね。

3. 構造の衝突

同じ実体が別の図で違う属性を持っていたり、ある図では「実体」として扱われているのに別の図では「属性」として扱われていたりすること。これも調整が必要だよ。

論理構造設計段階

概念設計をもとにして、具体的なデータモデル（階層型、網型、関係型など）に合わせて設計する段階。ここでは、グローバル E-R 図から関係モデルの論理構造を作る方法を説明するね。主なステップは、データモデルの決定、E-R 図からの変換、完全性制約（実体完全性、参照完全性、ユーザ定義完全性）の決定、そしてユーザビューの作成だよ。

概念設計 → データモデルへの変換 → 正規化 → スキーマの最適化 → ユーザサブスキーマの設計 → 物理設計へ

E-R 図から関係スキーマへの変換

E-R 図は情報の世界を記述したもので、そのままではコンピュータで処理しにくいんだ。だから関係データベースで扱えるように「関係スキーマ」に変換するよ。E-R 図の3要素（実体、属性、関連）を、関係スキーマという唯一の構造に変換していくんだ。

一、実体から関係スキーマへの変換

E-R 図の各実体を、それぞれ1つの関係スキーマに変換する。実体名がスキーマ名になり、実体の属性がスキーマの属性になる。実体の識別子（ID）が関係のキーになるよ。

二、関連から関係スキーマへの変換

1. 1対1関連の変換

1つの方法は、関連をどちらか一方の実体のスキーマに統合すること。統合先の属性セットに、もう一方の実体の主キーと、関連自体が持っている属性を追加すればOK。主キーは元のままだよ。

もう1つの方法（あまり使われないけど）は、関連を独立した1つの関係スキーマにすること。名前は関連名にして、属性には関連する両方の実体の主キーと関連自体の属性を入れる。キーはどちらか一方の主キーを選べばいい。

2. 1対多関連の変換

1つの方法は、関連を「多」側の実体のスキーマに統合すること。「多」側の属性セットに、「1」側の実体の主キーと関連の属性を追加する。主キーは「多」側のもののままだよ。

もう1つの方法（これもあまり使われない）は、独立したスキーマを作ること。属性は両方の実体の主キーと関連の属性。キーは「多」側の実体の主キーになるよ。

3. 多対多関連の変換

多対多の場合は、必ず独立した関係スキーマにする必要があるんだ。スキーマ名は関連名。属性は、関連する両方の実体の主キーと、関連自体の属性。キーは、両方の主キーを組み合わせたものになるよ。

4. 多対多対多（3元以上）関連の変換

3つの場合とだいたい同じだよ。

5. スーパークラスとサブクラスの変換

スーパークラスもサブクラスも、それぞれ1つの関係に変換できる。このとき、スーパークラスの主キーをサブクラスの関係にも追加してあげるんだ。

関係スキーマの正規化

1. 意味論（セマンティクス）にもとづいて、各スキーマのデータ依存性を特定する。
2. データ依存性にもとづいて、正規形（第1〜第5正規形など）を確認する。
3. 条件を満たしていない場合は、分解アルゴリズムを使って、3NF、BCNF、あるいは 4NF まで分解する。
4. 関係スキーマの評価と修正を行う。

物理設計段階

論理設計 → データベースの物理構造（物理モード）の決定 → 物理構造の評価 → 実施段階へ

データベースの実施と保守

物理設計 → データベースの定義 → データの流し込みとアプリ作成 → 試運転 → 運用・保守段階へ