うぃろぅです。
たまには実務的なところもやっておこうということでやっていきます。
今日のテーマ
DB設計 基礎
内容
- ER図
- 正規化
- 設計タスク
大学のときにうっすらやった記憶があるようなないような。あと応用情報技術者試験とか。受かったら大体忘れるよね。
わかりやすい記事があった。インターネットには知見があふれている…。
DB設計の位置づけ
ウォーターフォール型の工程においての上流工程である。
抜け / 漏れがあると手戻りの幅が大きくなるため、コストが増大する。
そのため、DE設計はおざなりにせずきっちり行わねばならない。どの工程もおざなりにするな
DB設計の流れ
- 概念設計
エンティティ抽出、属性抽出、正規化等。 - 論理設計
サマリ・エンティティのの検討、非正規化等。 - 物理設計
テーブル設計、インデックス設計、セキュリティ設計等。
DBかするかしないかというところから検討していく。
概念設計
- 業務 / データ分析を行う
- DBMSには非依存
必要なデータ項目を漏れなく洗い出すことが重要。
アウトプットは概念ER図で行う。
論理設計
- パフォーマンスを考慮
性能を意識して概念設計のデータモデルを見直す必要がある。
アウトプットは論理ER図で行う。
物理設計
- 実際のマシンに応じて考慮する
- 領域設計を行う
仮想化も選択肢に入れればいいんじゃないですかね…?
運用形態を考慮し、物理設計書にアウトプットする。
ER図
この記事を読み込めば大体理解できる。
ERとは
Entity
Relationship
属性の関係を表す図。
「顧客」「受注」「明細」「商品」のように必要な要素をエンティティごとに分け、線で繋ぐ。
書き方にはいくつかタイプがあり、IE型やIDEF1X型等がある。
エンティティとは
業務処理の過程で処理 / 管理の対象となる情報のこと。
具体的なインスタンス(Oさん 女性 20代、Hさん 女性 30代等)を抽象化したもの(氏名 性別 年齢層)がエンティティということになる。
もの、組織、場所、出来事がエンティティになることが多い。
種類としてはこんな感じ。大きく分けて2種類。
属性
エンティティで保存すべき情報。エンティティって書きすぎて頭おかしなるで。
- ID
- 氏名
- 誕生日
- 住所
みたいな感じ。識別キーをIDにすることが多い。
識別キー属性とは、「その情報があれば対象が一意に定まる」属性のこと。
識別キーが1つのこともあれば複数の事もある。
リレーションシップ
業務処理上必要となるエンティティ間の関係。
「何対何か」「必ず結びつくのか」「どういう関係なのか」を表す。
カーディナリティ
インスタンス同士が何対何で結びつくか。
- 1つのキャラを1人の声優が演じる場合1対1
- ポプ子とピピ美のCVは1対多
- 収録スタジオと声優の出先は多対多…ちょっと苦しい
外部キー
カーディナリティの多側に1側の識別キーがコピーされて外部キーとなる。
オプショナリティ
自分側に対して相手側のエンティティが任意か必須かという性質のこと。
- 声優が事務所に所属するのは必須ではない、みたいな。
多対多の分解
多対多は2つの1対多関係に分解できる。
交差エンティティを追加して分割する。
- 「声優」「スタジオ」をまとめて「訪れた回数」で履歴管理するとか…ええい例がわかりにくい
- 「商品」「倉庫」を「在庫」で仲介する、というのがわかりやすい例 「倉庫番号」と「商品番号」を外部キーとして管理する。
多対多は必ず分解して概念設計を終える。
ER図記述時の留意点
「線は直角に引く」「リソース系は同じ列に並べる」等ルールを決めて運用するといつ誰が見てもわかりやすい図となる。心がけるべし。
スーパータイプ / サブタイプ
プログラミングで言うスーパークラス / サブクラスと捉えるとわかりやすい。共通属性を抽出して継承するイメージ。
応用的な関係
木構造1対多の自己ループとなる。営業所の下に部があってその下に課があって…が例。
正規化
この記事がわかりやすい。
データの冗長な部分を排除し、重複なくグルーピングする手法。
データ更新 / 追加 / 変更に強いDBにするために必須。
基本的には第三正規系までで充分取り扱えるが、第四、第五…とまだ続きがあるにはある。
第一正規化
単純に複数持っているデータを分割するのみ。
| 商品番号 | 商品名 | 色 |
|---|---|---|
| 001 | メモ帳 | 赤 |
| 青 | ||
| 002 | ボールペン | 黒 |
これを
| 商品番号 | 商品名 | 色 |
|---|---|---|
| 001 | メモ帳 | 赤 |
| 001 | メモ帳 | 青 |
| 002 | ボールペン | 黒 |
こうする。
主キーないけど例ということで。
第二正規化
複数の主キーで一意に定まるデータ、1つの主キーで定まるデータが混在しているDBを分割する。
| 商品番号 | 色番号 | 商品名 | 色 |
|---|---|---|---|
| 001 | 01 | メモ帳 | 赤 |
| 001 | 02 | メモ帳 | 青 |
| 002 | 01 | ボールペン | 黒 |
| 002 | 02 | ボールペン | 緑 |
「商品番号」「色番号」を主キーとする。
この場合、「商品番号」「色番号」が決まれば「色」は一意に定まるが、「商品名」は「商品番号」がわかれば一意に定まる。
なのでこの表を
| 商品番号 | 商品名 |
|---|---|
| 001 | メモ帳 |
| 002 | ボールペン |
| 商品番号 | 色番号 | 色 |
|---|---|---|
| 001 | 01 | 赤 |
| 001 | 02 | 青 |
| 002 | 01 | 黒 |
| 002 | 02 | 緑 |
こう分割する。
第三正規化
主キー以外の部分が決まれば一意に定まるDBを分割する。
| 商品番号 | 商品名 | 棚番号 | 棚名 |
|---|---|---|---|
| 001 | メモ帳 | 101 | 紙類 |
| 002 | 付箋 | 101 | 紙類 |
| 003 | 鉛筆 | 102 | 筆記類 |
| 004 | ボールペン | 102 | 筆記類 |
主キーは「商品番号」。
上記の例だと「棚番号」がわかれば「棚名」がわかる。メモ帳は筆記類じゃないかって?細けえことはいいんだよ!!
なのでこれを
| 商品番号 | 商品名 | 棚番号 |
|---|---|---|
| 001 | メモ帳 | 101 |
| 002 | 付箋 | 101 |
| 003 | 鉛筆 | 102 |
| 004 | ボールペン | 102 |
| 棚番号 | 棚名 |
|---|---|
| 101 | 紙類 |
| 102 | 筆記類 |
こうする。第二正規化と第三正規化はよくわからなくなりがち。
トップダウン / ボトムアップ分析
概念設計時に情報を抽出するときの分析方法。
わかりやすかった記事を貼っておく。
要件(全体像)をもとに「これが必要になりそう」「そのためにこの情報が必要」と分析していく。考慮漏れがおきることがある。
既存のDBや資料を基にデータ分析を行う。現状分析が主となるため新しく何かを始めるにはデータが足りない。
相互補完して進めていくのがベター。
ボトムアップ分析の手順
- エンティティ抽出
- 属性抽出
- 識別キー決定
- リレーションシップ抽出
- 正規形確認
- ボトムアップモデル統合
現在使っている伝票や発注表等から「顧客」「受注」「商品」等を抽出し、それを細分化していく。
識別キー決定やリレーションシップ抽出は順番が前後することも大いにある。
トップダウン分析の手順
- エンティティ抽出
「商品の注文を受け」、「発注する」なら「商品」「受注」「発注」が必要だな、みたいな。 - 属性抽出
将来的に必要になるであろう要素から属性を抽出する。 - 識別キー決定
要件には出てこない要素のため、一般的に「商品番号」が必要だな、とくみあげていく。 - リレーションシップ抽出
「1回の注文で複数注文ができる」のであれば「受注」と「商品」が1対多だな、といったところ。 - 正規形確認
トップダウンはインプットに対して1つのモデルが作成されるため、統合が不要。
ER図統合
トップダウン分析によって得られたER図とボトムアップ分析によって得られたER図を統合して1つのモデルにする。MECEに。
モデル見直し
目的
検索パフォーマンス向上のため。
join hoge join fuga(以下ループ)ってすると重くなるもんね。適度に結合するのも大事。
必要な情報
- 非機能要件
レスポンス時間、業務の運用要件等。 - データアクセスの頻度 / 量
1時間当たりどれくらい、ピーク時にどれくらい等。 - データの性質
各エンティティのデータ件数、1対多関係の多側のカーディナリティ、データ蓄積時間等
非正規化
テーブルの結合を行う。
詳しい記事があったので詳細はこちらを参照。
リンク先にもあるが「正規化を行わない」のではなく「正規化を行ったが、様々な要素を考慮した結果、あえて正規化したモデルを結合する」という手順が大事。それなら質問されても明確に答えられる。
導出項目の取込
導出項目とは、他のテーブルや列から導き出せるデータ項目のこと。
例としては集計が挙げられる。単価と個数から金額を計算するのか、注文数が膨大になるため「金額」を属性とするのか。こちらもパフォーマンスとの兼ね合いとなる。
サマリ・エンティティ
導出項目を保存するエンティティをサマリ・エンティティと呼ぶ。
集計する項目数が非常に多い場合は集計した結果をサマリ・エンティティに保存しておき、そこを検索することで処理時間を向上させるのが狙いとなっている。
代替キーの検討
しばしば議論に上がるナチュラルキーとサロゲートキー。どちらにもメリットがありデメリットもあるのでよく検討することが必要。
物理設計
テーブル変換
ERモデルからテーブルに変換する。
- エンティティ → テーブル
- 属性 → 列
- 識別キー → 主キー
- 外部キー → 外部キー
と置き換える。
ささにデータの型や桁数なども決める必要がある。
ドメイン定義
属性の取りうる値の範囲を管理する。
属性の一元管理を行えるため、変更に強くなる。
ドメイン定義を使おう、という記事(乱暴)。
テーブル分割
パフォーマンス向上のためにテーブルを分割することがある。
- 垂直分割
- 水平分割
わかりやすい。でもこの事例にはとりかかりたくないね。ディスガイアアプリ、元気してるかな…
月ごと、頻度ごと等で分けることで検索を迅速に。インデックスもこの考え方に通じるものがあるはず。
インデックス
ややこしい内容だがしっかり理解するべき。
実データ検索ではなくポインタ検索、がポイントになりそう。C言語みがある…ないか。
検索したいデータとそのデータが存在するポインタのオブジェクトを検索し、そのポインタを用いてデータに直接アクセスするため、大量のデータを上から順番に見ていくより早くなる、という流れ。
更新に時間がかかるようになるため、データ量とにらめっこしてパフォーマンスが向上するかを判断する必要がある。
インデックスの効果
where句で頻繁に使用する- 列の値が比較的一意
- テーブルの結合で使用する
- 更新の少ない
- 比較的大きなテーブル
といった列にインデックスを当てると良い。
ビュー
実データを持たない仮想表のこと。
よく使うselect文に名前をつけ、そのデータに対して条件検索を行うことでパフォーマンスが上がる。
必要とならないデータを隠す、という点でも利点がある。
仮想表に対しても普通のテーブル同様の操作ができるため、テーブルとビューの意識をせずに取り扱える。
物理設計
物理設計の範囲
- OS / ハードウェア / RDBMSの選定
- DB構造の定義
- 運用計画の定義
DB内容以外のほぼ全て、ということになる。すごく広い。
物理設計の全体像
という流れとなる。最初から最後まで。
OS / RDBMSの選定
- 人員のスキル
- 予算
- サポート体制
- 商品 / フリーソフト
どの要素を落とすか、トレードオフ要素は何になるのか、等考えることは多い。頭を抱えよ。
テーブル / インデックス定義
テーブル
- ヒープ表
- 索引構成表
インデックス
- 単一型索引、コンポジット索引
- 一意索引、非一意索引
- ビットマップ索引
資源見積もり
- テーブル / インデックスの見積もり
- RDBMSシステムファイルの見積もり
ログファイルの領域、作業用領域、ディクショナリ等も考慮
ストレージ構成選択
安全性や性能を考慮し、どこにどのデータを格納するかを検討する。
ファイルアクセス
- テーブルデータ
- インデックスデータ
- ディクショナリ
- 作業用
- ログ
といったようにアクセスを分散させるようにする。
データ格納方式
- ファイル・システム
頻繁にやり取りする場合パフォーマンスが低下する。 - RAW
ファイル・システムを使わないため読み書きの速度が速いが要領の拡張やファイルコピーが難しい。
セキュリティ計画
構成要素
- ユーザーアカウント
- 権限
- ロール
ユーザーに対する権限の付与 / 剥奪を楽にする機能。ユーザーに対して権限を1つずつ与えるのではなく、予め一定の権限が与えられたロールを付与することで権限操作が楽になる。
不正アクセス防止のため、監査機能が重要となる。
パフォーマンス監視計画作成
パフォーマンスが悪くなっている兆候が見えた時点で対応する方が当然ながら良い。
そのため、定期的にパフォーマンスをベースラインと比較して監視する機能を活用すると対応が後手に回らない。
バックアップ / リカバリー計画作成
- バックアップ
「データは壊れるもの」である。そのため、オンラインバックアップ / オフラインバックアップを定期的に行う必要がある。
バックアップ方式により環境構築に変化があるため考慮が必要。
まとめ
今日の内容はあくまで基本。実際に手を動かす過程で得られる知見、経験を大事に知識を拡充させていくのがベスト。
めっちゃ書いた。今日の執筆時間は動画が4時間くらいあったため調べる時間と併せて3時間程度。
ではまた。
