データベースの基本設計

データベースの設計についてまとめた資料です。

Transcript

1. データベース設計に強くなる第一歩 〜データベース設計の基本編〜
2. システムとデータベース すべてのシステムは「データ」を取り扱っている. システムとデータベースは切っても切り離せない関係.
3. データベースに関係する用語 ・データ 　ある形式に揃えられた事実.
 ・データベース（DB） 　データの集まり. 二次元の表.
 ・ DBMS（Database Management System） 　データベースを管理するシステム.（MySQL、PostgreSQL）
 　データベースを使わないシステムはない.
 ・情報 　データ＋文脈. 
 　データからある文脈なり観点なりに従って、集約したり加工したもの.
 ・データベースの代表的モデル ・リレーショナルデータベース ・オブジェクト指向データベース 　・XMLデータベース
4. システムのサイクル ユーザー DBMS 情報 観点や文脈から抽出 登録・更新・削除
5. システム開発の工程 システム開発には４ステップある. 要件定義 設計 開発（実装） テスト
6. システム開発の工程 設計にはアプリケーション設計・インターフェース設計・データ設計がある. 要件定義 設計 開発（実装） テスト ・アプリケーション設計 ・インタフェース設計 ・データ設計 本日のテーマ
7. スキーマ スキーマとは、データベースの構造. データ設計において重要な概念. 外部スキーマ ユーザーから見たデータベース テーブル・ビュー（画面やデータ） SQLのselect文で定義 開発者から見たデータベース テーブル定義（データの要素やデータ同士の関係） 外部スキーマと内部スキーマの緩衝材 概念スキーマ DBMSから見たデータベース データの物理的配置（テーブルやインデックスの物理的定義） 内部スキーマ
8. 概念スキーマとデータ独立性 概念スキーマはデータ独立性を保証するためにある. 外部スキーマからの独立性を論理的独立性、内部スキーマからの独立性を物理的独立性と呼ぶ. 外部スキーマ 概念スキーマ 内部スキーマ 論理的データ独立 システム 物理的データ独立
9. 概念スキーマとデータ独立性 概念スキーマがあることで、スキーマ同士の依存性が高くなり内部スキーマは影響を受けない. 外部スキーマ 概念スキーマ 内部スキーマ システム 外部スキーマと概念スキーマ
 の変更 データの見え方を変えたい
10. 概念スキーマと論理設計 論理設計は、物理的制約には原則として依存しない. 料理に合わせて器を決める. 概念スキーマ（論理設計） 内部スキーマ（物理設計）
11. 論理設計の手順 論理設計には４ステップある. エンティティの抽出 エンティティの定義 正規化 ER図の作成
12. エンティティの抽出 エンティティの抽出 エンティティの定義 正規化 ER図の作成 エンティティとは、現実世界に存在するデータの集合体. 物理実体かどうかは関係ない. 現実世界のエンティティを最終的にはテーブルという物理的単位に格納していく. 物理的実体 ・顧客 ・社員 ・料理 物理的実体でない ・税金 ・注文履歴 ・言語
13. エンティティの定義 エンティティの抽出 エンティティの定義 正規化 ER図の作成 エンティティがどのようなデータを保持するのかを決める. エンティティの具体性を上げていき、テーブルというフォーマットに落とし込む. 社員 テーブル エンティティ 社員ID 社員名 年齢 部署
14. テーブル テーブルとは、共通点を持ったレコードの集合. テーブルにおいて、縦と横のデータの組みを「行」と「列」という. 社員ID 社員名 年齢 部署 001A 藤本 28 開発 001B 手塚 35 営業 001C 福谷 30 人事 002A 山岡 42 開発 002B 井上 27 営業 002B 井上 27 営業 行（レコード） 列（カラム・属性）
15. 主キー テーブルには、レコードを「一意に識別できる」主キーが必ず存在する. 主キーは1カラムとは限らない. 社員ID 社員名 年齢 部署 001A 藤本 28 開発 001B 手塚 35 営業 001C 福谷 30 人事 002A 山岡 42 開発 002B 井上 27 営業 主キー
16. 外部キー 外部キーは2つのテーブル間の列同士で設定する.（親子関係を作る） 子テーブルは親テーブルを参照するので一種の制約が形成される. = 参照整合性制約 社員ID 社員名 年齢 部署 001A 藤本 28 開発 001B 手塚 35 営業 001C 福谷 30 人事 002A 山岡 42 開発 002B 井上 27 営業 部署 開発 営業 人事 開発 営業 外部キー 子テーブル 親テーブル
17. データの登録での注意点 親テーブルにない部署があるレコードは子テーブルに登録できない. 社員ID 社員名 年齢 部署 001A 藤本 28 開発 001B 手塚 35 営業 001C 福谷 30 人事 002A 山岡 42 開発 002B 井上 27 営業 004A 松永 52 研究 社員ID 社員名 年齢 部署 部署 開発 営業 人事 開発 営業 子テーブル 親テーブル
18. カスケード 原則、データの削除は子から順に操作するのがベター. 親テーブルのデータを削除するときに、子テーブルのデータも削除する動作をカスケードという. 親テーブルの「人事」のデータを削除
 →子テーブルに「人事」があるレコードも削除 社員ID 社員名 年齢 部署 001A 藤本 28 開発 001B 手塚 35 営業 001C 福谷 30 人事 002A 山岡 42 開発 002B 井上 27 営業 部署 開発 営業 人事 開発 営業 子テーブル 親テーブル
19. 制約 ・NOT NULL制約 　NULLのデータを登録・更新できないようにする制約. 　可能な限りデータはNULLにしない. ・一意制約 　ある列の組について一意性を求める制約. 　何個でも設定可能. ・CHECK制約 　 ある列の取りうる値の範囲を制限する制約.
20. 正規形 エンティティの抽出 エンティティの定義 正規化 ER図の作成 正規形とは、データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式. ・冗長性 1つの情報が複数のテーブルに存在して、無駄なデータ領域と面倒な更新処理を発生させてしまうこと ・ 非一貫性 更新処理のタイムラグによって、データの不整合が発生したり、 　　そもそもデータが登録することができないようなテーブルを作ってしまうことがある
21. 第1正規形 第1正規化とは、「1つのセルの中には、1つの値しか含まない」形式にすることである. セルに複数の値を許せば、主キーが各列の値を一意に決定できない. テーブルにおいて「X列の値を決めれば、Y列の値が1つに決まる」という関数従属性を満たす必要がある. 社員ID 社員名 年齢 部署 001A 藤本 28 開発 001B 手塚 35 営業 001C 福谷 30 人事 002A 山岡 42 開発 002B 井上 27 営業 スカラ値
22. 第1正規化の流れ 社員ID 社員名 子 001A 藤本 太朗 二朗 001B 手塚 001C 福谷 花子 社員ID 社員名 子1 001A 藤本 太朗 001B 手塚 001C 福谷 花子 二朗 子2 社員ID 社員名 子 001A 藤本 太朗 001A 藤本 001B 手塚 二朗 001C 福谷 花子 社員ID 社員名 001A 藤本 001B 手塚 001C 福谷 社員ID 子 001A 太朗 001A 二朗 001C 花子 wide型 long型 主キーにNULLが含むのはNG 無駄なデータ領域が多く、拡張性が低い. 主キー
23. 第2正規化 第2正規化とは、部分関数従属を解消し完全関数従属のみテーブルにすることである. 異なるレベルのエンティティをテーブルとして分離することでもある. 会社コード 会社名 社員ID 社員名 年齢 部署コード 部署 C001 A食品 001A 藤本 28 D002 開発 C001 A食品 001B 手塚 35 D001 営業 C001 A食品 001C 福谷 30 D003 人事 C002 B化成 002A 山岡 42 D002 開発 C002 B化成 002B 井上 27 D001 営業 このテーブルの主キーは{会社コード, 社員ID}であり、「会社名」は部分関数従属している.
24. 第2正規化の流れ 第2正規化は可逆的な操作であり無損失分解である. 会社コード 会社名 社員ID 社員名 年齢 部署コード 部署 C001 A食品 001A 藤本 28 D002 開発 C001 A食品 001B 手塚 35 D001 営業 C001 A食品 001C 福谷 30 D003 人事 C002 B化成 002A 山岡 42 D002 開発 C002 B化成 002B 井上 27 D001 営業 会社コード 社員ID 社員名 年齢 部署コード 部署 C001 001A 藤本 28 D002 開発 C001 001B 手塚 35 D001 営業 C001 001C 福谷 30 D003 人事 C002 002A 山岡 42 D002 開発 C002 002B 井上 27 D001 営業 会社コード 会社名 C001 A食品 C002 B化成 第２正規形でないと、、、 社員のデータはなく、新たな会社名のデータのみを登録→社員IDをNULLにするしかない.
25. 第3正規化 第3正規化とは、テーブル内部の推移的関数従属をなくすことである. 推移的関数従属とは、テーブル内部に存在する段階的な従属関係のこという. 会社コード 社員ID 社員名 年齢 部署コード 部署 C001 001A 藤本 28 D002 開発 C001 001B 手塚 35 D001 営業 C001 001C 福谷 30 D003 人事 C002 002A 山岡 42 D002 開発 C002 002B 井上 27 D001 営業 {会社コード, 社員ID} → {部署コード} → {部署} 非キー項目が他の項目に関数従属している.
26. 第3正規化の流れ 会社コード 社員ID 社員名 年齢 部署コード 部署 C001 001A 藤本 28 D002 開発 C001 001B 手塚 35 D001 営業 C001 001C 福谷 30 D003 人事 C002 002A 山岡 42 D002 開発 C002 002B 井上 27 D001 営業 会社コード 社員ID 社員名 年齢 部署コード C001 001A 藤本 28 D002 C001 001B 手塚 35 D001 C001 001C 福谷 30 D003 C002 002A 山岡 42 D002 C002 002B 井上 27 D001 部署コード 部署 D002 開発 D001 営業 D003 人事 D002 開発 D001 営業 第3正規形でないと、、、 社員のデータはなく、新たな部署のデータのみを登録→社員IDをNULLにするしかない.
27. 正規化のまとめ 正規化は関数従属性を満たす必要がある. 第2正規化では主キー、第3正規化では非キーに着目する. 会社コード 社員ID 社員名 年齢 部署コード C001 001A 藤本 28 D002 C001 001B 手塚 35 D001 C001 001C 福谷 30 D003 C002 002A 山岡 42 D002 C002 002B 井上 27 D001 部署コード 部署 D002 開発 D001 営業 D003 人事 D002 開発 D001 営業 会社コード 会社名 C001 A食品 C002 B化成 主キー 非キー 会社コード 会社名 社員ID 社員名 年齢 部署コード 部署 C001 A食品 001A 藤本 28 D002 開発 C001 A食品 001B 手塚 35 D001 営業 C001 A食品 001C 福谷 30 D003 人事 C002 B化成 002A 山岡 42 D002 開発 C002 B化成 002B 井上 27 D001 営業
28. 参考書籍・参考記事 参考書籍 ・達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ 参考記事 ・ ・ データベース（RDB）設計の進め方！ 要件定義～システム設計ができる人材になれる記事