概要

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

データベース設計では、データの重複や不整合を減らし、必要な情報を正しく取り出せる構造を作る。
E-R図、正規化、主キー・外部キーの関係をつなげて理解することが重要となる。

この記事の構成

• データベース管理システム
  データベース管理システム（DBMS：Database Management System）とは、データの安全保管と整合性を保つことを目的にした管理システムであり、次の機能に大別される。
• データベースの種類
  データベースの種類と各項目の特徴を整理。
• E-R図
  E-R図（Entity-Relationship Diagram）とは、業務で扱う対象をエンティティ、その性質を属性、対象同士の関係をリレーションシップとして表すデータ構造の設計図。
• データベースモデル
  データベースモデルの意味と要点を具体例から整理。
• データベース設計
  データベース設計の意味と要点を具体例から整理。
• データベースの正規化
  正規化とは、データの整合性を保ち、 更新時異常を減らすために、属性間の関係に基づいてデータ構造を整理すること。
• テーブル間の制約
  テーブル間の制約の意味と要点を具体例から整理。
• データベースのパフォーマンス改善
  データベース設計後の性能に関する問題でデータベースのパフォーマンスが起因するケースがある。

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

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

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

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

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

データベースの種類

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

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

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

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

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

E-R図

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

また、リレーションシップには、以下4つの対応関係（カーディナリティ）がある。

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

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

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

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

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

データベースモデル

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

• 概念データモデル
  業務上のエンティティや関係、業務ルールを、特定の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が非キー属性となる。

テーブル間の制約

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

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

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

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

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

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

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

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

データベース設計後の性能に関する問題でデータベースのパフォーマンスが起因するケースがある。 その対策となるパフォーマンス向上手段として、次の改善方法がある。

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

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

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

• DBMSの機能を利用
  インデックスの設定やデータベースの再編成によるアクセス効率アップ、DBMSのデータすべてをメモリ上に展開し、ディスクの入出力を無くすことで高速化を実現するインメモリーデータベースなどDBMSの機能を利用して高速化する。

まとめ

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

参考文献

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