概要
- 達人に学ぶDB設計徹底指南書を読んで、重要そうなところを忘備のため、メモする。
- 第9章まであるが、まずは第3章までメモ
１章
POAはProcess Oritented Approachのこと。従来のシステム開発ではプロセス主流だった。現在ではDOA（Data Oriented Approach）が一般的。POAでは受注、発注、仕訳といったプロセスごとにデータを管理するのに対し、DOAではすべてのプロセスでデータを共用する。
DB v.s DBMS
DBは「データの集まり」を指す概念。DBMSはDBを管理するためのシステム。
主なDBMS
以下を参照のこと。Oracle、MySQL、Microsoft SQL Server、PosgreSQLがRDBMSの４強。
３層スキーマ
- 外部スキーマ（外部モデル）＝ビュー（ユーザから見たデータベース）
- 概念スキーマ（論理データモデル）＝テーブル（開発者から見たデータベース）
- 内部スキーマ（物理データモデル）＝ファイル（DBMSから見たデータベース）
２層スキーマ（外部と内部スキーマのみ）だと、変更に対する柔軟性がない。概念スキーマは緩衝材の役割。 論理的データ独立：外部スキーマ ⇔ 概念スキーマ 物理的データ独立：概念スキーマ ⇔ 内部スキーマ
２章 論理設計と物理設計
概念スキーマと論理設計
論理設計のステップ
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
内部スキーマと物理設計
物理設計のステップ
- テーブル定義
- インデックス定義
- ハードウェアのサイジング
- ストレージの冗長構成決定
- ファイルの物理配置決定
サイジング
- キャパシティとパフォーマンス両方の観点から行う
- データベースの性能の問題の8割はストレージのI/Oネックによって起きる
- データ量はシステムの運用開始から増加していくのが基本
- パフォーマンスの指標は２つ
- 処理時間＝「特定の処理が何秒以内に終了するか」
- TPS（Transaction Per Second）＝「1秒あたりにどれだけたくさん処理できるか」
- 類似のシステムのデータを流用する、またはプロトタイプを構築して検証するといった方法でサイジングを行う
- 精度の高いサイジングは難しいので、安全率をかけること、スケーラビリティの高い構成にすることが重要
冗長構成
- 少なくともRAID5で構成する。お金に余裕があればRAID10にする
ファイルの物理配置
ファイルには以下の5種類がある。
- データファイル
- インデックスファイル
- システムファイル
- 一時ファイル
- ログファイル
- 一時ファイルはサブクエリを展開したデータやソートデータなど
- DBMSはデータファイルへの変更を受け付けたあと即座にデータファイルを変更するわけではない。ログファイルにいったん変更リクエストをためて、一括して変更を反映している。ログファイルはDBMSごとに呼び名が異なる。
- Oracle: REDOログ
- PostgreSQL, SQL Server, DB2: トランザクションログ
- MySQL: バイナリログ
- ①データファイル、②インデックスファイル、③システムファイルは継続的にデータ量が増加する
- 5つのファイルのうち、I/O量が最も多いのは①データファイル。次は②インデックスファイルと④一時ファイル
- 理想的にはすべてのファイルを異なるディスク（RAIDグループ）に配置すること。I/O分散という点で望ましいが、ディスク増
- 妥協案として、I/Oコストの低いファイルを１つにまとめる。つまり、③システムファイル、⑤ログファイルを１つのディスクに配置し、それ以外は各々ディスクを用意する
バックアップ設計 主要なバックアップ方式は以下の3種類
- 増分バックアップが最も無駄が少ないが（同じデータを何度もバックアップしない）、リカバリ時に必要なファイルの個数が増えるため、リカバリに要する時間が長くなる。また完全にデータを復旧できる可能性が最も低い（すべてのバックアップファイルが正常である必要があるため）
- 「完全バックアップ＋差分バックアップ」、または「完全バックアップ＋増分バックアップ」が一般的
- バックアップウィンドウ（バックアップに使用できる時間）、リカバリウィンドウ（リカバリに使用できる時間）、何世代前までのデータを残すか、いつ時点の状態に復旧させる必要があるかを考慮して、バックアップ設計を行う
リカバリ設計
復旧手順は厳密には以下のように分けられる。この３つをすべて実施することで障害直前の状態に復旧できる。
- リストア：完全バックアップのファイルをもとにデータベースを復旧
- リカバリ：差分（または増分）バックアップしていたトランザクションログを適用する
- ロールフォワード：データベースサーバーに残っているトランザクションログを適用する
3章 論理設計と正規化
テーブルの構成要素
‐ テーブルの行をレコードとも呼ぶ（ほかの資料ではインスタンスと呼んでいる場合もあった） ‐ テーブルの列をカラム、あるいは属性とも呼ぶ - キーには、主キーと外部キーがある - 外部キーの役割は、参照整合性制約を課すこと - 外部キーが設定されている場合、データの削除は子から順に行うほうが良い - それ以外には、親レコードを削除する際に「親がいない子」もあわせて削除する、あるいは削除SQL文をエラーにする、といった方法もある。こういった一連の削除動作を「カスケード」と呼ぶ - キーとなる列にはコードやIDなどの表北池野定まった固定長文字列を用いる - 列ごとに以下の制約を設定できる - NOT NULL制約：値がNULLでないこと - 一意制約：値に重複がないこと - CHECK制約：とりうる値を制限する。例：「１～５までの整数」、「"開発"、”人事”、”営業”のいずれかの文字列」 - テーブル定義において、可能な限りNOT NULL制約を付加すること - テーブル名、列名には以下の制約がある（DBMSによっては標準外の独自拡張として、日本語や特殊文字をサポート） - 使用できる文字は、半角アルファベット、半角数字、アンダーバーのいずれか - 先頭の文字はアルファベット - 同じテーブル名、同じ列名は存在できない
正規化とは何か？
- 正規化の目的は、一つの情報が複数のテーブルに存在して無駄な領域と煩雑な更新処理を発生させてしまうことを防ぐ。データの冗長性を排除してデータの不整合を防ぐ ‐ 正規化によって作られるのが正規形 ‐ 正規形にはレベル５まであるが、レベル３まで覚えておけばとりあえずOK
- 基本的には正規化＝テーブルの分割
- 正規化とは元に戻すことができる可逆的な操作 i.e. 非正規形 ⇔ 正規形
- 正規化の逆操作は、結合（JOIN）
第１正規形
- 行と列が交差するマス（エクセルだとセルと呼ばれる）には、一つの値しか含まない
第２正規形
- 第２正規形は、部分関数従属の解消によって得られる i.e. 部分関数従属 → 完全関数従属
- 部分関数従属とは、主キーの一部の列がほかの列に従属していること
- テーブルの分割によって解消できる
- 部分関数従属があると、何が悪いのか？
- 主キーが不明な場合に（NULLの場合に）、主キーが従属している列の値を登録できない
第３正規形
‐ 第３正規形は、推移的関数従属の解消によって得られる i.e. 推移的関数従属 → 完全関数従属 ‐ あるテーブルに社員ID、部署コード、部署名の列がある。それぞれは{社員ID} → {部署コード} → {部署名}という関数従属性がある。この場合、テーブルに推移的関数従属があるという - テーブルの分割によって解消できる
第3.5・4・5正規形
- これはスキップする
- 第3.5正規形は、正式にはボイスーコッド正規形、またはBCNF （Boyce-Codd normal form）と呼ばれる
