概要

情報技術の基礎として理解しておきたい データベース設計 について、DBMS、データベースの種類、E-R図、データモデル、正規化、制約、性能改善を整理する。

注文の表を第3正規形まで分け、同じ情報の重複が更新時に起こす問題を確認する。登録させたくない具体的な行からキーやNULLの制約を選ぶ練習も行う。

この記事の構成

• データベース管理システム
  データベース管理システム（DBMS：Database Management System）とはデータの安全保管と整合性を保つことを目的にした管理システムであり、次の機能に大別される。
• データベースの種類
  データベースの種類と各項目の特徴を整理。
• E-R図
  E-R図（Entity-Relationship Diagram）とは業務で扱う対象をエンティティ、その性質を属性、対象同士の関係をリレーションシップとして表すデータ構造の設計図。
• データベースモデル
  データベースモデルの意味と要点を具体例から整理。
• データベース設計
  データベース設計の意味と要点を具体例から整理。
• データベースの正規化
  注文の具体的な表を第3正規形まで分け、更新時の不整合を減らす理由を確認。
• テーブル間の制約
  登録したくない行から主キー・外部キー・NOT NULL・CHECKを選ぶ。
• データベースのパフォーマンス改善
  データベース設計後の性能に関する問題でデータベースのパフォーマンスが起因するケースがある。

データベース管理システム

データベース管理システム（DBMS：Database Management System）とはデータの安全保管と整合性を保つことを目的にした管理システムであり、次の機能に大別される。

• メタデータ管理
  データとメタデータ（データの付帯情報が記載されたデータ）を管理。

• クエリ管理
  データベースに対して処理要求（問い合わせ）を行うクエリ（SQL）を管理。

• トランザクション管理
  排他制御や障害回復など複数のトランザクションを管理。

データベースの種類

上記 DBMSは以下のデータベース（種類）に大別される。

• 階層型データベース
  データを親ノードと子ノードから成るツリー状（階層型）で表現する手法。
  子ノードを重複登録しない限り、子ノードが複数の親ノードを持つことはできない。データへの経路があらかじめ定まるため経路に沿った探索は効率的だが、性能は実装やデータ構造にも依存する。

• ネットワーク型データベース
  データを親ノードと子ノードから成る網状構成で表現する手法。
  網状構成なため重複登録が不要で子ノードが複数の親ノードを持つことができるが、データの抽出や特定手段がデータ構造に依存するため、データ構造について熟知していないとデータアクセスが難しい。

• 関係型データベース（リレーショナルデータベース：RDB）
  レコード（行）、フィールド（列）から成るテーブル同士の関連付けでデータを表現する手法。
  柔軟なデータの取扱いができ、複雑な関係性を表現できる。大規模なデータではテーブル設計、インデックス、SQL、実行計画などに応じた性能設計が必要となる。

• オブジェクト指向データベース
  オブジェクト指向に対応し、データそのものとデータの処理方法を1つのオブジェクトとしてデータを表現した手法。
  複雑なデータ構造を高速に処理でき、多様なデータ形式を束ねて管理する場合に適している。

E-R図

E-R図 （Entity-Relationship Diagram）とは業務で扱う対象を エンティティ 、その性質を 属性 、対象同士の関係を リレーションシップ として表すデータ構造の設計図を指す。

また、 リレーションシップ の基本的な 対応関係 （カーディナリティ）は以下の \(3\) 種類となる。\(1\) 対多を反対側から見たものが多対 \(1\) であり、別の関係種類ではない。

• \(1\) 対 \(1\)
  \(1\) つのデータに対する関連データがただ \(1\) つである場合のリレーションシップ。

• \(1\) 対 多
  \(1\) つのデータに対する関連データが 複数 ある場合のリレーションシップ。

• 多 対 多
  複数データに対応するデータが 複数 ある場合のリレーションシップ。

また、上記 対応関係（カーディナリティ） を E-R図 に表す際、次の UML表記 （多重度表記ルール）で表現する場合もある。

• 「顧客に注文がある」と「注文に顧客が必要」を区別する
  顧客は登録直後には注文が0件でもよく、一人で複数回注文できる。一方、一つの注文は必ず一人の顧客に属すると決めたなら、顧客から見た注文数は0件以上、注文から見た顧客数はちょうど1件になる。

  この関係を表にすると、注文表に顧客IDを持たせて外部キーで参照先を制約する。ただし、外部キーだけではNULLを許せる場合があるため、必ず顧客を指定させるにはNOT NULLも必要になる。関係の本数だけでなく、0件を許すかどうかが列の制約につながる。

データベースモデル

データベースモデルはユーザーへの見せ方やシステムに対する追加要件など柔軟に対応できるよう以下のデータモデルとスキーマに分類され構成される。

• 概念データモデル
  業務上のエンティティや関係、業務ルールを特定のDBMSや実装方法に依存せず表したモデル。E-R図などを用いて表現する。

• 論理データモデル
  概念データモデルを基にテーブル、属性、主キー、外部キー、正規化など、採用するデータモデルに沿った論理構造を表す。

• 物理データモデル
  論理データモデルを利用するDBMSのデータ型、インデックス、パーティション、格納方法などに落とし込んだモデルを指す。

• 外部スキーマ
  利用者やアプリケーションごとに必要なデータの見え方を定義したもので、ビューなどが該当する。

• 概念スキーマ
  データベース全体の論理構造や制約を定義し、外部スキーマと内部スキーマをつなぐ。

• 内部スキーマ
  概念スキーマで定義されたデータをDBMS内部でどのように格納しアクセスするかを定義したもの。

データベース設計

データ仕様の基盤となるデータベース設計のアプローチ手段として、以下 \(2\) 種類の方法がある。

※ 主キー は行を一意に識別するために選んだキー、 候補キー は行を一意に識別できる最小の属性の組、 非キー属性 はどの候補キーにも含まれない属性を指す。

• トップダウンアプローチ
  利用者の要望を基にシステム要件からデータモデルを作成し設計する手法で大別して以下の流れとなる。

1. システム要件からエンティティを洗い出し、リレーションシップを定義し、 E-R図 を作成。

2. それぞれのエンティティが持つ各データの属性を洗い出し、リレーションシップを考慮して 主キー を決定する。

3. 多対多の関係を中間表で表現し、 正規化 （以下「データベースの正規化」に記載）によって属性の依存関係を整理する。業務上の多対多の関係そのものがなくなるわけではない。

• ボトムアップアプローチ
  現行システムまたは次期システムで使用する画面や帳票などを基にデータモデルを作成し設計する手法で大別して以下の流れとなる。

1. 画面仕様や帳票仕様からデータ属性の洗い出し、リレーションシップを定義。

2. リレーションシップを基に 主キー を定義し、テーブルの 正規化 （以下「データベースの正規化」に記載）を実施。

3. テーブル構成から E-R図 を作成。

また、上記アプローチから設計したデータベースに対して、 物理設計 ではアクセス効率、記憶効率の最適化を図り、データ保存もシステム要件に沿うようにHDDだけでなく、RAIDやSSDを用いるなどシステム構成から変更する場合もある。

データベースの正規化

正規化とはデータの整合性を保ち、更新時異常を減らすために属性間の関係に基づいてデータ構造を整理することを指す。

例えば重複定義されているデータを抜き出し、別テーブルにまとめることで \(1\)ヵ所にデータ保管する構造へ正規化する。

正規化の手順は下記の通り。

• 第 \(1\) 正規形：繰返し項目の排除
  各行・各列の交点に入る値を原子的な単一値とし、配列のような複数値や同じ種類の繰返し列を持たない形にする。
  ※ ドメインはある属性が取り得る値の集合を指す。

• 第 \(2\) 正規形：部分関数従属の排除
  ※ 第 \(1\) 正規形であることが前提。
  すべての非キー属性を候補キー全体に完全関数従属させる正規化で、複合候補キーの一部だけに依存する属性を別テーブルへ分ける。

  ※ 関数従属とはある属性Aが決まったら別属性Bも一意に決まる状態を指す。
  ※ 完全関数従属とはある属性が複合キー全体には関数従属するが、その一部だけには関数従属しない状態を指す。
  ※ 部分関数従属とは非キー属性が複合候補キーの一部だけに関数従属している状態を指す。

• 第 \(3\) 正規形：推移的関数従属の排除
  ※ 第 \(2\) 正規形であることが前提。
  非キー属性をどの候補キーにも推移的な関数従属させないようにする正規化で、候補キー以外の属性に関数従属している属性を抜き出して排除し、別テーブルにする。

  ※ 推移的な関数従属とは属性A→B、属性B→Cのように関数従属の連鎖関係を指し、このとき属性Aが候補キー、属性Bが候補キー以外、属性Cが非キー属性となる。

• 注文の表を値が何によって決まるかに沿って分ける
  一つの注文で同じ商品を一行だけ扱うとし、次の表のキーを「注文IDと商品IDの組」とする。顧客名や商品名は現在のマスター値を扱う例で、注文時点の名称を保存する要件は含めない。

  注文ID	商品ID	顧客ID	顧客名	商品名	数量
  1001	P1	C1	青木	ノート	2
  1001	P2	C1	青木	ペン	3
  1002	P1	C2	井上	ノート	1

  この業務では注文IDで顧客ID、顧客IDで顧客名、商品IDで商品名が決まり、注文IDと商品IDの組で数量が決まる。これらは偶然同じ値が並んでいるからではなく、業務の規則から定める依存関係である。

  各セルを単一の値にした第1正規形でも、商品名や顧客名は重複している。まずキーの一部だけに依存する情報を分け、第2正規形として次の三表を考える。

  • 注文（注文ID、顧客ID、顧客名）
  • 商品（商品ID、商品名）
  • 注文明細（注文ID、商品ID、数量）

  次に注文表の「注文ID→顧客ID→顧客名」という推移的な依存を取り除く。顧客（顧客ID、顧客名）を独立させ、注文表を（注文ID、顧客ID）にすると、この例は第3正規形になる。

  元の表ではC1の名前を直すときに複数行を更新し、片方だけ変えて不一致を作る可能性があった。分割後は顧客表の一行を直せばよい。正規化は表を小さくすること自体が目的ではなく、同じ事実を何度も管理することで起きる不整合を減らす操作である。

テーブル間の制約

関係型データベース （リレーショナルデータベース）ではリレーションシップを維持するためにテーブル間で下記の制約を設ける。

• 一意制約（ユニーク制約）
  テーブルの登録・更新時に既存レコードのフィールド設定値と重複する値は許容しない制約。

• NOT NULL制約（非ナル制約）
  テーブルの登録・更新時のフィールド設定にNULLを許容しない制約。

• 主キー制約
  行を一意に識別する列または列の組を指定する制約。値の組は一意かつNULL不可となり、テーブルに一つだけ定義できる。

• 参照制約（外部キー制約）
  テーブルの登録・更新時に他テーブルの特定フィールドの指定された値のみ許容する制約。

• ドメイン制約
  フィールド単位に指定した条件を満たしたデータのみ許容する制約。

• 検査制約（CHECK制約、形式制約）
  ドメイン制約 のうち設定する値がとる範囲の条件や形式に関する制限をかけた制約。

• 外部キーとNOT NULL、CHECKの役割を組み合わせる
  注文明細の数量を必須の正整数にしたいなら、 CHECK (quantity > 0) と NOT NULL を組み合わせる。PostgreSQLではCHECK式の結果がNULLでも制約を通るため、比較条件だけでは未入力を排除できない。

  防ぎたい状態	制約の例
  同じ注文内に同じ商品を二行登録する	注文IDと商品IDを組にした主キー
  存在しない商品IDを登録する	商品表を参照する外部キー
  数量を入力しない	NOT NULL
  数量に0や負数を入れる	CHECK (quantity > 0)

  制約は一つであらゆる不正を防ぐものではない。禁止したい具体的な行を考えると、必要な組合せを確認できる。NULLの扱いは PostgreSQLの制約の説明 でも確認できる。

データベースのパフォーマンス改善

データベース設計後に性能上の問題が見つかった場合は、実行計画や計測結果から原因を特定し、次のような方法を検討する。

• データベース設計の変更
  パフォーマンスが向上するようテーブル構造やリレーションシップを変更。
  代表例として正規化をあえて崩す 非正規化 がある。

例えば正規化したテーブルを結合した形で保持したり重複列や集計済みの値を意図的に保持したりして、参照時の結合や集計を減らす。ただし、更新時の整合性を保つ仕組みが必要となる。

• SQLのパフォーマンスチューニング
  実行計画や統計情報を確認し、検索条件、結合方法、インデックスなどを見直してSQLを効率良く処理できるようにチューニングする。EXISTS句への書換えなどが常に高速になるとは限らない。

• DBMSの機能を利用
  インデックス、パーティショニング、キャッシュ、データベースの再編成など、DBMSが提供する機能を利用する。データの全部または一部を主にメモリ上で処理するインメモリーデータベースも選択肢となるが、永続化方式や障害回復の仕組みは製品によって異なる。

• 正規化と検索の速さは別の観点で評価する
  正規化した注文一覧を表示するには、注文・顧客・注文明細・商品を結合する場合がある。表が分かれたことだけを理由に遅いと決めることはできない。件数、絞込み条件、索引、DBMSの実行計画によって処理量が変わる。

  集計結果を別に保持するなど重複を導入するなら、更新元が変わったときにいつどのように再計算するかも決める必要がある。読み出しを速くする代わりに、更新時の整合性の管理が増えるという交換条件を確認する。

まとめ

• データベース設計ではデータの関係と整合性を明確にする。
• 主キーは行を一意に識別し、外部キーは他の表との関係を表す。
• 正規化は重複や更新異常を減らし、非正規化は性能や利便性のために重複を許す。第2正規形は部分関数従属、第3正規形は推移的関数従属をなくす。
• 概念モデルは業務上の関係、論理モデルはデータベース実装に近い構造を表す。
• インデックスは検索を高速化し、制約はデータの正しさを守る。

• 正規化では業務上の依存関係に沿って同じ事実の重複管理を減らす。必須項目、参照先の存在、値の範囲はそれぞれ対応する制約を組み合わせて表す。

参考文献

• 瀬戸 美月 (\(2020\)) 『徹底攻略 応用情報技術者教科書』株式会社インプレス
• IBM「実体関連図とは」（日本語・公式解説）
• IBM, What Is Database Normalization?（英語・正規化の解説）
• PostgreSQL Documentation, Constraints（英語・公式仕様）