このページでは、概念設計・論理設計・物理設計の順番、3層スキーマ構造とデータの独立性、E-R図の読み方と、多対多を分ける連関エンティティがわかります。
- 一言で言うと：
- データベースを「概念設計 → 論理設計 → 物理設計」の順に作る話
- 構造を変えてもアプリが困らないようにする3層スキーマ構造と、データの関係を絵にするE-R図が出てくる
作る順番（概念設計 → 論理設計 → 物理設計）→ 変更に強くする枠組み（3層スキーマ構造、データの独立性）→ 関係を絵にする（E-R図、連関エンティティ）
覚え方の軸
- 設計は「意味 → 形 → 速さ」の順
- 概念設計は、DBMSを気にせずデータの意味と関連を表す（E-R図、UMLのクラス図）
- 論理設計は、使うDBに合わせた形（表の構造）にする（関係モデルなら主キー・外部キー・制約・ビュー）
- 物理設計は、性能のための設計（ディスク容量、ブロック長、インデックス、表の分割）
- 3層スキーマは「外・概念・内」と「論理独立・物理独立」の対応
- 外部スキーマ（ビュー）⇔ 概念スキーマの間が論理データ独立性
- 概念スキーマ ⇔ 内部スキーマ（インデックス、ブロック長）の間が物理データ独立性
- 古い呼び名：サブスキーマ＝外部スキーマ、記憶（格納）スキーマ＝内部スキーマ
- E-R図は「エンティティ」と「関連」の2つだけで描く
- 関連は1対1、1対多、多対多の3種類
- 多対多は、間に連関エンティティをはさんで「1対多」＋「多対1」に分ける（連関エンティティの主キーは、両側の主キーの組）
- 強と弱は「ひとりで存在できるか」
- 外部キーが主キーの一部になっている（親がいないと存在できない）のが弱エンティティ
データベースの設計：意味 → 形 → 速さの順に作る（★）
- データベース：
- データを体系的に整理し、蓄積したデータの集まり
- 同時に、対象とする実世界の状態や構造を表現したものでもある
- データベースの設計手順：
- 概念設計 → 論理設計 → 物理設計の順に行う
- データモデル：
- データ体系や業務ルールを、一定の基準に従って表現（モデル化）したもの
設計の流れ
| 段階 | 作るもの | 中身 |
|---|---|---|
| 概念設計 | 概念データモデル（E-Rモデルなど） | 実世界のデータを調べて抽象化する。DBMSに依存しない |
| 論理設計 | 論理データモデル（階層モデル、ネットワークモデル、関係モデル） | 使うデータベースに実装できる形に変換する |
| 物理設計 | 物理データモデル（階層型DB、網型DB、関係DB） | 性能を満たすための物理的構造を決める |
① 概念設計
- 概念設計：
- 対象とする実世界のデータすべてを調査・分析して、抽象化した概念データモデルを作る
- 概念データモデル：
- コンピュータへの実装を意識せず、データの持つ意味と、データ間の関連を、あるがままに表すことに重点を置いたデータモデル
- だから、特定のDBMS（DataBase Management System：データベース管理システム）に依存しないで関連を表せる、E-R図やUMLのクラス図を使う
- E-R図は後で詳しく扱う
- 概念設計の例え：
- 家を建てる前に、「家族は何人で、どんな部屋がいるか」を紙に書き出す段階。まだ木造か鉄筋かは決めない
データ分析
- データ分析：
- 業務で使っている帳票や伝票、画面などを調べて、どんなデータ項目があり、どれが必要かをすべて洗い出す
- データ項目の標準化：
- 洗い出したデータ項目を、一定の基準に従って標準化する
- 異音同義語（呼び名は違うが中身は同じ）や同音異義語（呼び名は同じだが中身が違う）をなくす
- 正規化で重複をなくす：
- 各データ項目の関連を整理して正規化を行い、複数箇所にある同じデータ項目（重複項目）をなくす（正規化は後述）
- データ項目の標準化と定義：
- ①データ項目名の標準化
- ②データ項目の意味の定義
- ③データ項目の桁数や型（タイプ）の統一
- ④各データ項目の発生源や発生量の明確化
- 標準化の例：
- 「日付」と「年月日」という2つの項目があり、これらが違う実体なら、それぞれの意味を定義して、違う実体であることをはっきりさせる
- データ分析に参加する人：
- 業務で十分に使えるデータベースにするため、データ分析の初期段階から、部門の管理者や業務担当者が検討に参加する
トップダウンアプローチとボトムアップアプローチ
| アプローチ | やり方 | できるモデル |
|---|---|---|
| ボトムアップアプローチ | 画面や帳票などから項目を洗い出し、データ分析を行った結果としてモデルを作る | 現実型の概念データモデル |
| トップダウンアプローチ | 最初にモデルを作ってから、データ分析を行う | 理想型の概念データモデル |
- どちらでも守ること：
- 最終的なデータモデルは、正規化されていて、業務上必要なデータ項目をすべて備えていなければならない
- 組み合わせて使う：
- どちらか一方だけではなく、例えばボトムアップで作ったものをトップダウンで見直すなど、業務に合った方法を使うことが大事
- 2つのアプローチの例え：
- ボトムアップは、今ある書類の山を整理して、家の間取りを描き起こす
- トップダウンは、まず理想の間取りを描いてから、今の暮らしに合うか確かめる
② 論理設計
- 論理設計：
- E-R図やUMLのクラス図で表した概念データモデルは、そのままではデータベースに実装できる形になっていないことがある
- そこで、概念データモデルを、データベースに実装できる形式、つまり論理データモデルに変換する
- 概念データモデル＝論理データモデル：
- 2つを区別せず、「概念データモデル＝論理データモデル」と扱うこともある
- 論理データモデル：
- データベース構造モデルとも呼ばれる
- 利用者とデータベースの間のインタフェースの役割をする
- だから、どのデータベースで実装するかによって、使う論理データモデルが違う
- 論理データモデルがある利点：
- 利用者とデータベースの間に論理データモデルがあるので、利用者は、データがどんな形式で格納されているかという物理的な構造を一切意識せずにデータベースを操作できる
論理データモデルの種類と特徴（★）
| モデル | 対応するDB | 特徴 |
|---|---|---|
| 階層モデル | 階層型データベース | 階層構造（木構造）で表す。親レコードに対する子レコードは複数ありうるが、子レコードに対する親レコードはただ1つ。だから、親子間の多対多を表そうとすると冗長になる |
| 網モデル（ネットワークモデル） | 網（ネットワーク）型データベース | レコードどうしを網構造で表す。親子間の多対多も表せる |
| 関係モデル | 関係型データベース | データを2次元の表（テーブル）で表す。1つの表は独立していて、親レコード・子レコードという関係は持たない |
- 3つのモデルの例え：
- 階層モデルは、会社の組織図。部下の上司は必ず1人
- 網モデルは、路線図。駅（レコード）どうしが網の目につながる
- 関係モデルは、Excelの表。1枚ずつ独立していて、同じ値（キー）で突き合わせる
関係モデルへの変換
- 関係データベースで実装する場合：
- 概念データモデルを基に、主キーや外部キーを含めたテーブル構造を作る
- テーブルの各列（データ項目）に設定する非NULL制約や検査制約なども検討する
- 非NULL制約：
- 空値（NULL）の登録を許可しない制約
- 検査制約：
- 登録できるデータの値や範囲を設定する制約
- ビューの設計：
- 個々のアプリケーションプログラムや利用者が使いやすいように、ビューの設計（定義）を行うのも、この段階
- テーブル構造の例：
- 社員表（社員コードが主キー、部門コードが外部キー、社員名）
- 部門表（部門コードが主キー、部門名）
- 社員表の部門コードは、部門表の部門コード（主キー）を参照する
- 制約の例：空値（NULL）は許可しない
③ 物理設計
- 物理設計：
- データベースに求められる性能を満たすための設計
- データの量や利用頻度、さらに運用面も考えて、最適なアクセス効率と記憶効率が得られるように、データベースの物理的構造を設計する
- 具体的にやること：
- ディスク容量の見積り
- ブロック長やインデックス（索引）の設計
- アクセス効率を上げるための表の分割や、複数ディスクへの分割の検討
- ブロック：
- DBMSが読み書きするディスクI/Oの単位
- ディスクI/Oが性能のボトルネックになることが多いので、最適なブロック長を考える
- インデックスや表の分割は、後の「データベース管理システム」で扱う
- 論理設計に戻ることもある：
- 物理設計の結果、十分な性能が出ないと判断したら、論理設計に戻ってデータ構造を見直す
- 例えば、正規化で分割した表を結合する非正規化
- 物理設計の例え：
- 間取りが決まった後で、「よく使う台所は玄関の近くに」「物置の棚は何段にするか」を決める段階
データベース設計における参照モデル：構造を変えてもアプリが困らない（★）
- 3層スキーマ構造：
- 3層スキーマアーキテクチャともいう
- データベース設計における参照モデル
- 3層スキーマ構造が必要な理由：
- 実世界が変わると、データベースも変更が必要になる
- 多くはデータを変えれば済むが、データ構造や物理的な格納構造を変える必要が出ることもある
- そこで、データベースの構造を変えても、その影響をアプリケーションプログラムが受けないように、データの記述と操作の枠組み（スキーマ）を、外部、概念、内部の3層に分けて管理する
- これで、データの独立性を確保する
- データの独立性：
- 「データとアプリケーションプログラムを分離し、データベースの構造が変わっても、アプリケーションプログラムの修正を必要としない」という特性
- ANSI/SPARC 3層スキーマモデル：
- 3層スキーマ構造は、ANSI（米国規格協会）のSPARC（標準化計画要求委員会）が提案したもの
- だから、ANSI/SPARC 3層スキーマモデルとも呼ばれる
データベースの3層スキーマ（★）
| スキーマ | 中身 | 何が当たるか |
|---|---|---|
| 外部スキーマ | 利用者やアプリケーションプログラムから見たデータの記述（利用するデータの定義） | 関係データベースのビュー定義 |
| 概念スキーマ | データベース全体の論理的データ構造の記述（データの論理的構造の定義）。「概念スキーマ＝論理データモデル」だが、定義のときは使うDBMSの特性が加味される | 表構造（テーブル構造） |
| 内部スキーマ | 概念スキーマを、コンピュータ上に具体的に実現するための記述（データの物理的構造の定義） | ブロック長、表領域サイズ、インデックスの定義 |
2つのデータ独立性
- 論理データ独立性：
- 実世界の変化に応じて概念スキーマを変える必要が出ても、その変更は、アプリケーションプログラムとは独立に行えるという特性
- 外部スキーマと概念スキーマの間の独立性
- 物理データ独立性：
- データの物理的な格納構造を変えても、アプリケーションプログラムに影響が及ばない、つまり、アプリとは独立に内部スキーマを変えられるという特性
- 概念スキーマと内部スキーマの間の独立性
- 3層スキーマの例え：
- 外部スキーマは、お店の「メニュー表」（客が見る形）
- 概念スキーマは、厨房の「レシピ帳」（全体の決まり）
- 内部スキーマは、「冷蔵庫のどの棚に何を置くか」（物理的な置き方）
- 冷蔵庫の配置を変えても（物理データ独立性）、レシピを少し変えても（論理データ独立性）、客はいつものメニューで注文できる
- CODASYLモデルの用語（試験）：
- サブスキーマや記憶（格納）スキーマという用語が選択肢に出ることがある
- これらは、3層スキーマ構造の原形となったCODASYLモデルの用語
- サブスキーマ ＝ 外部スキーマ、記憶（格納）スキーマ ＝ 内部スキーマ
インメモリデータベース
- インメモリデータベース：
- データを直接メモリに配置することで、最速のパフォーマンスを出せるデータベース
- 速い理由：
- 従来のデータベースは、ディスクに記録されたデータをメモリに読み込んで処理するので、ディスク入出力がボトルネックになる
- インメモリデータベースは、ディスク入出力がないので速い
- 列指向（カラム指向）：
- 最近のインメモリデータベースの多くは、データをカラム（列）型フォーマットでメモリに置く列指向を採用している
- だから、集計や分析処理などのクエリが速くなる
- 例えば、「全社員の給与の合計」を出すとき、給与の列だけをまとめて読めばよい
- 弱点（揮発性）：
- メモリ上のデータは、電源を切ると消える
- 揮発性への対策：
- データを定期的にディスクに保存する機能
- 別のスタンバイデータベースにデータの複製を取るレプリケーション機能
- インメモリデータベースの例え：
- 毎回倉庫（ディスク）まで取りに行かず、よく使う道具を全部机の上（メモリ）に広げて作業する。速いが、停電で机の上が片付けられてしまうので、ときどき倉庫にも控えをしまっておく
E-R図：エンティティと関連で描く（★）
- E-R図：
- 対象世界の情報を、実体（エンティティ）と、実体間の関連（リレーションシップ）の2つの概念で表した図
- E-R図（E-Rモデル）の特徴：
- ①実体間の関係の意味を表現できる
- ②いろいろなデータ構造を表現できる
- ③関係モデルとの親和性が高い
① 実体（エンティティ）
- エンティティ：
- 実世界を構成する要素で、実世界をモデル化するときの対象物
- エンティティの種類：
- “顧客”、“商品”といった、物理的な実体を伴うもの
- 物理的な実体を伴わない抽象的なもの（注文、受注などの事象）
- 属性：
- エンティティが持つ、それ自体の性質や特徴を表すもの
- インスタンス：
- エンティティの属性に具体的な値を持たせたもの
- 例えば、“顧客”エンティティの「C001 田中太郎 港区 09000000000」という1件
- 識別子：
- 1つのインスタンスを一意に識別できる属性
- エンティティには、必ず識別子が必要
- 関係データベースの表の主キーに当たる（以降「主キー」と呼ぶ）
- 主キーの例：
- “顧客”の属性は「顧客番号、顧客名、住所、電話番号」。一意に識別できるのは顧客番号
- 顧客名・電話番号が主キーに向かない理由：
- 顧客名は、同姓同名がありうる
- 電話番号は主キーになりうるが、変わる可能性があるので、運用面から向かない
- 関係データベースへの対応：
- E-R図のエンティティ名を表名、属性名を列名にして、適切なデータ型で表を定義する
- エンティティとインスタンスの例え：
- エンティティは「名簿の様式（顧客番号・名前・住所の欄）」、インスタンスは「その様式に書き込んだ1人分」
② 関連（リレーションシップ）
- 関連（リレーションシップ）：
- 「顧客は商品を注文する」「社員は組織に所属する」といった、業務上の規則やルールで生まれる、エンティティ間の関係（意味的関係）
- 関連の種類：
- インスタンスどうしの対応関係によって、1対1、1対多、多対多の3つがある
- 対応関係の表し方：
- 1対1：線（──）
- 1対多：「多」の側に矢印（──→）
- 多対多：両側に矢印（←→）
- 多対多の例：
- 「1人の顧客は複数の商品を注文し、1つの商品は複数の顧客から注文を受ける」なら、“顧客”と“商品”は多対多
- リレーションシップは1つとは限らない：
- 「商品を注文する」「商品を返品する」という関連があれば、“顧客”と“商品”の間に、“注文”と“返品”の2つのリレーションシップがある
③ 「多対多」から「1対多」への変換
- 多対多の問題：
- エンティティ間に多対多の関連があると、関係データベースへの実装が難しい
- そこで、多対多の関連を、1対多と多対1の関連に分解する
- 分解のやり方：
- リレーションシップである“注文”を、1つのエンティティとしてとらえる
- その主キーに、“顧客”の主キー（顧客番号）と“商品”の主キー（商品番号）を持たせる
- これで、“顧客”と“注文”は1対多、“注文”と“商品”は多対1になる
- 連関エンティティ：
- 多対多のエンティティ間に介入させたエンティティ
- 例えば、上の“注文”（顧客番号、商品番号、注文日、注文数量）
- 参照制約で整合性を保つ：
- 連関エンティティ“注文”の顧客番号と商品番号を、それぞれ“顧客”“商品”の主キーを参照する外部キーにすると、データの整合性（参照制約）が確保できる
- 午後問題のポイント（試験）：
- E-R図の空欄問題で、2つのエンティティの間にはさまれ、両方から矢印が向かっているエンティティは、連関エンティティである可能性が高い
- 連関エンティティの主キーは、双方の主キーの組であることに注目する
- 交差データ：
- 上の“注文”の注文日と注文数量のように、“顧客”と“商品”の両方が特定されたときに発生するデータ
- 顧客が同じ商品を別の日に注文することを考えるなら、注文日も主キーに組み込む必要がある
- 連関エンティティの例え：
- 「生徒」と「部活」は多対多（1人が複数の部活、1つの部活に複数の生徒）。間に「入部届」（生徒番号＋部活番号）をはさめば、生徒と入部届は1対多、入部届と部活は多対1になる
④ 独立エンティティと依存エンティティ
- 1対多のときの外部キー：
- 一般に、エンティティ間に1対多の関連があるとき、「多」側のエンティティは、「1」側のエンティティの主キー項目を外部キーとして持つ
- 弱エンティティ（弱実体）：
- 外部キーが主キーの一部になっている場合、そのエンティティは「1」側のエンティティに依存する
- 例えば、上の“注文”は、“顧客”と“商品”がなければ存在できない（“顧客”か“商品”のインスタンスがなくなれば、関連する“注文”のインスタンスもなくなる）
- このような、他のエンティティに依存するエンティティ
- 強エンティティ（強実体）：
- “顧客”や“商品”のように、どのエンティティにも依存せず、独立に存在できるエンティティ
- “注文”を強エンティティにする方法：
- 主キー項目に注文番号を入れ、顧客番号と商品番号を主キーから外す（外部キーとしては残る）
- 強と弱の例え：
- 「親」と「子どもの通知表」。通知表は、生徒がいないと存在できない（弱）。でも通知表に「通知表番号」を付けて独立させれば、それ自体で管理できる（強）
⑤ ループ構造の表現
- ループ（再帰的）構造：
- E-R図は、ループ構造も表せる
- 例えば、「（親）部品は複数の（子）部品からできていて、（子）部品は1つの（親）部品に属する」という1対多の階層的な関連
- ループ構造の例：
- “部品”エンティティが、部品番号と親部品番号を持つ。自分自身に向かって矢印が出る
- P1の親部品番号はNULL、P2・P3の親はP1、P4の親はP2
- 最上位の部品には親がないので、親部品番号にはNULLが入る
- ループ構造の例え：
- 社員表に「上司の社員番号」の欄を作る。上司も同じ社員表の中にいる。社長だけは上司の欄が空欄
混同しやすいペア
- 概念設計 ／ 論理設計 ／ 物理設計
- 概念設計は、DBMSに依存しない概念データモデルを作る（E-R図、UMLのクラス図）
- 論理設計は、実装できる論理データモデルに変換する（主キー・外部キー・制約・ビュー）
- 物理設計は、性能のための物理的構造を決める（容量見積り、ブロック長、インデックス、表の分割）
- 異音同義語 ／ 同音異義語
- 異音同義語は、呼び名は違うが意味が同じもの
- 同音異義語は、呼び名は同じだが意味が違うもの
- ボトムアップアプローチ ／ トップダウンアプローチ
- ボトムアップは、帳票や画面から項目を洗い出し、結果として現実型のモデルを作る
- トップダウンは、最初に理想型のモデルを作ってから分析する
- 階層モデル ／ 網モデル ／ 関係モデル
- 階層モデルは、木構造。子の親は1つだけで、多対多は冗長になる
- 網モデル（ネットワークモデル）は、網構造。多対多も表せる
- 関係モデルは、2次元の表。親子関係を持たない
- 非NULL制約 ／ 検査制約
- 非NULL制約は、空値を許さない
- 検査制約は、登録できる値や範囲を決める
- 外部スキーマ ／ 概念スキーマ ／ 内部スキーマ
- 外部スキーマは、利用者から見たデータ（ビュー定義）
- 概念スキーマは、全体の論理的データ構造（表構造）
- 内部スキーマは、物理的な格納構造（ブロック長、表領域サイズ、インデックス）
- 論理データ独立性 ／ 物理データ独立性
- 論理データ独立性は、概念スキーマを変えてもアプリに影響しない
- 物理データ独立性は、内部スキーマ（格納構造）を変えてもアプリに影響しない
- サブスキーマ ／ 記憶（格納）スキーマ
- サブスキーマは、外部スキーマのこと（CODASYLの用語）
- 記憶（格納）スキーマは、内部スキーマのこと（CODASYLの用語）
- エンティティ ／ インスタンス
- エンティティは、モデル化の対象物（顧客、商品、注文）
- インスタンスは、エンティティの属性に具体的な値を入れた1件
- 識別子 ／ 主キー
- 識別子は、E-R図で、インスタンスを一意に識別する属性
- 主キーは、関係データベースで、それに当たるもの
- 連関エンティティ ／ 交差データ
- 連関エンティティは、多対多の間に入れて1対多・多対1に分けるエンティティ（主キーは両側の主キーの組）
- 交差データは、両方のエンティティが決まったときに発生するデータ（注文日、注文数量）
- 強エンティティ ／ 弱エンティティ
- 強エンティティは、どのエンティティにも依存せず、独立に存在できる
- 弱エンティティは、外部キーが主キーの一部で、他のエンティティがないと存在できない
- インメモリデータベース ／ 従来のデータベース
- インメモリデータベースは、データをメモリに置くので速いが、揮発性がある（ディスク保存やレプリケーションで対策）
- 従来のデータベースは、ディスクから読むので、ディスク入出力がボトルネックになる