Zenn
🧻

データベース基礎知識

に公開
SQL
Database
tech

本記事の概要

本記事では、データベース管理システムの基礎知識について解説します。特に、データ管理の階層構造、コネクションとセッション、SQLの分類、主キーと外部キー、ACID特性、関数従属・正規化について、その概念や具体例を解説しています。

データ管理の階層構造

RDBMSにおけるデータ管理の階層構造は一般的に次のようになっています。

Instance
    └─ Database
          └─ Schema
                 └─ Table, View などのオブジェクト

なお、この階層構造はOracle, PostgreSQL, MySQLなどの使用するRDBMSごとに少しずつ異なるため、注意が必要です。例えば、MySQLではインスタンス直下にデータベースがあり、それがスキーマとしても機能します（スキーマとデータベースがほぼ同義のものとして扱われます）。

# MySQLの場合
Instance
    └─ Database (=Schema)
          └─ Table, View などのオブジェクト

インスタンス（Instance）

  • インスタンスとは、RDBMSの実行中のプロセスやそのメモリ空間を指す
  • RDBMSの中で複数のインスタンスを実行できる
  • 各インスタンスは互いに独立しており、異なる設定で運用できる
  • インスタンスは通常、TCPポート番号などで一意に識別される
  • MySQLやOracleでは、インスタンスはDBサーバが実行されているプロセスを指す

データベース（Database）

  • データベースは、関連するデータを体系的に格納したもの
  • ひとつのインスタンス内に複数のデータベースが存在できる
  • 例: 顧客管理データベース、在庫管理データベース

スキーマ（Schema）

  • テーブルやビューなどのオブジェクトを区分けするための論理的構造のこと
  • ひとつのデータベース内に複数のスキーマを作成できる
  • スキーマを使って、用途や管理単位に応じてデータベース内のオブジェクトを整理できる
  • スキーマにより名前空間を分割してオブジェクト名の衝突を防ぐ役割もある
  • PostgreSQLではスキーマが標準的に用いられ、publicスキーマがデフォルトで提供される

具体例

例えばECサイトのデータベースをPostgreSQLで構築した場合、次のようになります。

インスタンス
 └── ECサイト用データベース (ec_site_db)
        ├── publicスキーマ (public)
        │      ├── ユーザーテーブル (users) / ID, 氏名, メールアドレス, パスワード など
        │      ├── 商品テーブル (products) / 商品ID, 商品名, 価格, 在庫数 など
        │      └── 注文テーブル (orders) / 注文ID, ユーザーID, 注文日, 商品ID, 数量 など
        └── 管理者用スキーマ (admin)
               └── ログテーブル (admin_logs) / ログID, 管理者ID, 操作内容, 日時 など

コネクションとセッション

コネクション

  • コネクションはクライアントアプリケーションとDBサーバ間の論理的な通信経路
  • TCP/IPなどのネットワークプロトコルを利用して接続される
  • 一度作成されたコネクションは、閉じられるまで維持される
  • コネクションを確立すると、DBの認証（ユーザー名、パスワード）を経て接続が許可される
  • JDBCやpsycopg2などのライブラリは、コネクションの確立・管理を行うための実装に該当

セッション

  • セッションはコネクション上に確立されるユーザーとDBサーバ間の実行環境や対話状態のこと
  • コネクションが確立されると、セッションが開始される
  • コネクションが切断されると、関連するセッションが終了する
  • ユーザーごとのトランザクションの実行状態や環境設定（タイムゾーンなど）を保持する
  • トランザクションはセッション単位で管理される
  • 通常、1つのコネクションは、1つのセッションを持つ（1対1）

SQL言語の分類

SQLの命令は主に以下の3つのカテゴリに分類されます。

  • DDL: データ定義言語
  • DML: データ操作言語
  • DCL: データ制御言語

DDL（Data Definition Language）: データ定義言語

データベース構造（スキーマ）を定義・変更するための命令です。

主要なコマンド

コマンド 説明
CREATE テーブルやデータベース、インデックスなどのオブジェクトを作成
ALTER 既存のテーブル構造を変更（列の追加・削除など）
DROP テーブルやデータベースなどを削除

DML（Data Manipulation Language）: データ操作言語

テーブルの中身（データ）を操作するための命令です。

主要なコマンド

コマンド 説明
SELECT データの取得
INSERT 新しいデータの追加
UPDATE 既存データの更新
DELETE データの削除

DCL（Data Control Language）: データ制御言語

アクセス権限やセキュリティ管理のための命令です。

主要なコマンド

コマンド 説明
GRANT ユーザーに権限を与える
REVOKE 付与した権限を取り消す

主キーと外部キー

主キー

主キー（Primary Key）は、テーブル内の各レコードを一意に識別するための列（または列の組み合わせ）のことで、次のような特徴を持ちます。

  • 一意性: テーブル内で重複した値を持つことができない
  • 非NULL: NULLを許容しない。
  • 識別性: その値を使って各レコードを明確に区別できる。

主キーの例

顧客テーブル

顧客ID(主キー) 顧客名 電話番号 住所
001 田中一郎 090-1111-2222 東京都渋谷区
002 佐藤花子 080-3333-4444 大阪府大阪市
003 山田太郎 070-5555-6666 福岡県福岡市

この例では、「顧客ID」が主キーとなっており、各顧客レコードを一意に識別するために使用されています。主キーの値は絶対に重複せず、NULLも許容されません。

外部キー

外部キー（Foreign Key）とは、他のテーブルの主キーを参照してテーブル間の関連付けをするための列であり、次のような特徴を持ちます。

  • 他のテーブルの主キーに対応する。
  • 参照整合性（データ間の整合性）を保つために用いられる。
  • 一つのテーブルに複数の外部キーを持つことができる。

例えば、以下のように外部キーが設定されます。

部署テーブル

部署番号（主キー） 部署名
10 営業部
20 開発部

社員テーブル

社員番号（主キー） 氏名 部署番号（外部キー）
001 山田太郎 10
002 佐藤花子 20
003 鈴木一郎 10

ACID特性

ACID特性とは、SQLデータベースにおけるトランザクションが満たすべき4つの重要な特性のことをいいます。

Atomicity（原子性）

トランザクション内のすべての処理は「すべて成功する」か「すべて失敗してロールバックされる」かのどちらかでなければなりません。つまり、中途半端な状態は存在しません。例えば、銀行振込の場合、送金元の口座からの引き落としと送金先の口座への入金が両方とも成功するか、または両方とも行われないことを保証します。

Consistency（一貫性）

トランザクションが実行される前後で、データベースは常に定義された整合性制約（主キー、外部キー制約やチェック制約など）を満たした状態（常に一貫性のある状態）でなければなりません。

Isolation（独立性）

複数のトランザクションが同時に実行されても、それぞれが互いに影響を与えないように振る舞わなければなりません（並列処理されたトランザクションも見かけ上は直列に実行されたかのように振る舞われます）。

Durability（永続性）

一度コミットされたトランザクションの結果は、システム障害が起きても失われないように保証されるべきです。例えば、システムがクラッシュしても、トランザクションの結果が失われることがあってはいけません。また、通常これはログ機構によって実現されます。

関数従属と正規化

関数従属の概念

ある属性（または複数の属性から構成される）の値が決まると、それに伴って別の属性の値が一意に定まることを「関数従属」といいます。
例えば、社員ID（主キー）, 氏名, 部署で構成されたテーブルがあるとします。

社員ID(主キー) 氏名 部署
1001 山田 太郎 営業部
1003 佐藤 次郎 経理部
1002 鈴木 花子 人事部

このとき、次のように関数従属性が成立します。

  • 社員ID → 氏名
  • 社員ID → 部署

部分関数従属

部分関数従属は、主キーが複合キーである場合にのみ成立し、複合キーの一部分だけで非キー属性が一意に定まる状態を指します。

例えば、主キーを「学生番号」＋「科目番号」とした履修テーブルがあるとします。

学生番号(主キー) 科目番号(主キー) 氏名 科目名
1001 M101 山田 太郎 数学
1001 C102 山田 太郎 化学
1002 M101 鈴木 花子 数学

この場合、「学生番号」のみで「氏名」が決定されています（例: 1001 → 山田太郎, 1002 → 鈴木花子）。これは複合主キーの一部だけで非キー属性が決定されているため、「部分関数従属」と呼びます。
部分関数従属が存在する場合、第2正規形（2NF）への正規化によってこれを解消します。

推移的関数従属

推移的関数従属とは、非キー属性がさらに別の非キー属性を一意に決定するような関数従属をいいます。
例えば、主キーを「従業員番号」とする従業員テーブルがあるとします。

従業員番号(主キー) 氏名 部署番号 部署名
E001 山田 太郎 D01 営業部
E002 佐藤 次郎 D02 経理部
E003 鈴木 花子 D01 営業部

この例では、「従業員番号」→「部署番号」→「部署名」という従属関係があります。「部署番号」は非キー属性でありながら、「部署名」を決定しています。このような関係を「推移的関数従属」と呼びます。
推移的関数従属が存在する場合、第3正規形（3NF）への正規化によってこれを解消します。

正規化の概念

正規化とは、データベースの設計を整理し、冗長性（重複）を排除して、データの整合性を保つためのルールのことで、特に第1～第3正規形はこの概念の基礎となります。

第1正規形（1NF）

第1正規形のルールを次に示します。

  • 各属性（列）の値が原子的（これ以上意味のある単位に分割できない）であること。
  • 1つのセルには、単一の値だけを格納する（繰り返しや複数値を許容しない）。

第1正規形ではない例（1つのセルに複数値）

ID 氏名 電話番号
1 山田太郎 090-1234-5678, 080-9876-5432
2 鈴木花子 070-2345-6789

第1正規形の正しい例（1セル1つの値に分割）

ID 氏名 電話番号
1 山田太郎 090-1234-5678
1 山田太郎 080-9876-5432
2 鈴木花子 070-2345-6789

この例では、山田太郎の複数の電話番号を別々の行に記録していますが、IDと氏名の組み合わせが一意でなくなっています。より適切な設計としては、以下のように電話番号を別テーブルに分けるべきです：

ユーザーテーブル

ID(主キー) 氏名
1 山田太郎
2 鈴木花子

電話番号テーブル

電話番号ID(主キー) ユーザーID(外部キー) 電話番号
1 1 090-1234-5678
2 1 080-9876-5432
3 2 070-2345-6789

このように分割することで、各ユーザーに複数の電話番号を関連付けられます。

第2正規形（2NF）

第2正規形のルールを次に示します。

  • 第1正規形を満たしていること。
  • 部分関数従属を排除すること（非キー属性が複合主キー全体に対してのみ依存し、複合主キーの一部に依存しないこと）。

第2正規形ではない例（主キーの一部に依存するデータが存在）

注文ID(主キー) 商品ID(主キー) 商品名 数量
1001 A01 ボールペン 10
1001 B02 ノート 5
1002 A01 ボールペン 20

※ この表では注文IDと商品IDのペアが複合主キーとして機能していますが、「商品名」は「商品ID」のみに依存しています（部分関数従属）。

第2正規形の正しい例（主キー全体に対してのみ依存する形へ分割）

注文テーブル

注文ID(主キー) 商品ID(主キー) 数量
1001 A01 10
1001 B02 5
1002 A01 20

※ 注文IDと商品IDのペアが複合主キー

商品テーブル

商品ID(主キー) 商品名
A01 ボールペン
B02 ノート

第3正規形（3NF）

第3正規形のルールを次に示します。

  • 第2正規形を満たしていること。
  • 推移的関数従属を排除すること（非キー属性が、他の非キー属性を経由して間接的に主キーに依存する状態を排除）。

第3正規形ではない例（推移的依存がある）

顧客ID(主キー) 顧客名 地域コード 地域名
C001 佐藤次郎 001 東京
C002 高橋一郎 002 大阪
C003 山本花子 001 東京

※この場合、「地域名」は「地域コード」に依存しており、「地域コード」は「顧客ID」に依存しています。つまり「地域名」は「顧客ID」に直接依存するのではなく、「地域コード」を経由して間接的に依存しています（推移的関数従属）。

第3正規形の正しい例（推移的依存を排除し、テーブル分割）

顧客テーブル

顧客ID(主キー) 顧客名 地域コード
C001 佐藤次郎 001
C002 高橋一郎 002
C003 山本花子 001

地域テーブル

地域コード(主キー) 地域名
001 東京
002 大阪

正規形まとめ

正規形 要件の概要 ポイント
第1正規形 セルに1つの値だけ格納する。 1セルに複数値を入れないこと。
第2正規形 主キーの一部にだけ依存するデータを排除。 主キー全体にのみ依存するよう分割。
第3正規形 主キー以外の列が別の非キー列に依存しない。 推移的依存を排除する。

まとめ

本記事では、データベースの基礎知識として、データ管理の階層構造からSQLの分類、主キーと外部キー、ACID特性、関数従属と正規化の概念までの範囲を解説しました。

Discussion