DB基礎 正規化と関係演算

はじめに

今回は実施まであと10日に迫ったIPAのDBスペシャリスト試験への対策としてDBの基礎的な部分の復習をしていきます。
本業に加えて副業、個人開発等も行いながらのため対策の時間をなかなか捻出できていませんが、受験申し込みはしたので試験当日はしっかり受験会場には行きたいと思います。

RDB（関係データベース）の基本概念の簡単な紹介

関係データベース（RDB: Relational Database）は、関係モデルに基づいてデータを管理するデータベースです。このモデルでは、データはテーブル（関係）の形で格納され、テーブルは行（タプル）と列（アトリビュート）によって構成されます。各行はユニークなキー（主キー）によって識別され、列はテーブル内の個々のデータ項目を表します。また、テーブル間の関連性は外部キーによって表現されます。

より噛み砕いて説明するとテーブルは、エクセルでいうところのシートのようなもので、行と列でデータが整理されています。行は、各々のデータを意味し、列はデータの属性を示します。例えば、人の名前と年齢があれば、「名前」と「年齢」が列となります。

正規化と関係演算について

• 正規化 「正規化」は、データベースを作るときのルールの一つです。これは、データをきれいに、効率よく整理するためのものです。正規化の目的は、データの重複をなくして、データの整理や管理をしやすくすることです。重複を避けることで、データが変わった時に、他のデータと矛盾しないようにします。

• 関係演算 「関係演算」とは、RDBの中のデータを取り出したり、新しいデータを作ったり、データを組み合わせたりする方法のことです。これを使えば、たくさんのデータの中から、欲しいデータを見つけ出すことができます。データベースの言葉で、これを行うことを「クエリ」といいます。

関係データベースの基礎

テーブル

RDBにおいて、データは「テーブル」と呼ばれる格子状の表の中に入れられます。一つのテーブルは、ある特定のテーマやカテゴリのデータを保持します。例えば、顧客情報を持つ「顧客テーブル」があるとしましょう。

行（タプル）

テーブルの中の一つ一つの「行」は、ひとまとまりのデータを表します。たとえば、「顧客テーブル」の一行は、一人の顧客の情報を表します。

列（アトリビュート）

「列」は、データの性質や特徴を表します。たとえば、「顧客テーブル」の列には、「顧客ID」、「名前」、「電話番号」などがあります。

キー（主キー、外部キー）の概念

• 主キー

主キーは、テーブルの中の各行をユニークに特定できるデータのことです。例えば、「顧客ID」が主キーになります。主キーの値は、テーブル内で重複してはいけません。
RailsやLaravelでは、主キーはデフォルトで「id」という名前の列になります。しかし、主キーは「id」に限られるものではありません。
また、これらのフレームワークではデフォルトでは連番で主キーが割り振られますが、主キーは一意であれば構わないので連番である必要はありません。
たとえば、uuidなどを使ってランダムな文字列を主キーにすることもできます。

• 外部キー

外部キーは、別のテーブルの主キーを参照する列のことを言います。外部キーを使うと、異なるテーブル間でデータを関連付けることができます。例えば、注文テーブルが顧客テーブルを参照することで、どの顧客がどの商品を注文したかが分かります。

これにより、「注文テーブル」の「顧客ID」列の値は、「顧客テーブル」の「顧客ID」列に存在する値でなければなりません。これが外部キーの基本的なルールです。

多くのDBMSでは、外部キー制約を設定することができます。 外部キー制約とは、関連するテーブル間でのデータの正確性を保証するための制約です。

外部キー制約の具体的な役割:

1. 整合性の確保: 外部キーに設定されている列のデータは、参照先のテーブルの主キーに存在する値でなければなりません。これにより、間違ったデータや存在しないデータが入力されるのを防ぎます。

2. 参照制約: 参照されている主キーのデータが削除されると、それに紐づく外部キーのデータも影響を受けます。具体的には、データを削除できなくなるか、紐づくデータも一緒に削除されます。これも設定によります。

3. 一貫性の維持: 外部キー制約により、テーブル間のデータの一貫性が維持されます。これによって、データの信頼性が高まり、データの管理が容易になります。

これらの基本概念を理解することで、データベースがどのようにデータを管理しているのかが見えてきます。そして、これらの概念を用いてデータを効率よく扱うことができるようになります。

正規化の基礎

wikipedia の説明も十分わかりやすいですが、具体例を考えながら、噛み砕いて理解してみます。

正規化の目的

正規化は、データベース設計のステップの一つで、データの重複を避け、データの整合性を保つために行います。これによって、データの追加、更新、削除が効率的になり、データベースの性能が向上します。

1. データの重複の排除： 正規化を通じて、テーブル間でのデータの重複や冗長性を排除します。これによって、データの一貫性と正確性が向上します。

2. データの整合性の保持： 正規化によって、データの追加、更新、削除の際の不整合リスクが低減します。これによって、データの整合性が維持されます。

3. 効率的なデータアクセス： 適切に正規化されたデータベースは、不要なデータの重複を避けることができ、データの検索や更新の効率が向上します。

4. 拡張性と柔軟性の向上： 正規化されたデータベースは、新しいデータタイプやテーブルの追加が容易になります。

正規化のプロセスと正規形

正規化のプロセスでは、データベースをいくつかの「正規形」に分けます。ここでは、第一正規形から第三正規形までを紹介します。

• 第一正規形

各列が原子的な（分割できない）値を持ち、全てのエントリがユニークである状態です。

• 第二正規形 (2NF):

1NFを満たし、部分関数従属が存在しない状態です。これは、各情報がその行の主キーにのみ依存する状態です。

• 第三正規形 (3NF):

2NFを満たし、推移的関数従属が存在しない状態です。これは、非キー属性が他の非キー属性に依存しない状態です。

正規化の例

例として、以下のような「顧客注文テーブル」があります。

顧客ID	顧客名	注文ID	商品名
1	田中	A1	パソコン
1	田中	A2	椅子
2	鈴木	A3	テーブル

このテーブルを正規化すると下記のようになります。 なお、正規化のことを「normal form」と呼ぶこともあります。

• 第一正規形 (1NF):

各列が原子的な値を持つように、「商品名」を分けます。しかし、この例では既に1NFを満たしています。

• 第二正規形 (2NF):

部分関数従属を取り除くため、「顧客テーブル」と「注文テーブル」に分割します。

部分関数従属とは、主キーの一部のみに依存する関数従属のことです。例えば、以下のテーブルでは、主キーである「顧客ID」と「注文ID」の一部である「顧客ID」にのみ依存する「顧客名」が部分関数従属です。

顧客テーブル:

顧客ID	顧客名
1	田中
2	鈴木

注文テーブル:

注文ID	顧客ID	商品名
A1	1	パソコン
A2	1	椅子
A3	2	テーブル

• 第三正規形 (3NF):

この例では2NFの段階で推移的関数従属は存在しないため、3NFも満たしています。

この例では、第二正規形と第三正規系の区別がわかりづらいので、違う例を考えてみます。

学生ID	学科	学科の教授
1	コンピュータ	田中
2	数学	鈴木
3	コンピュータ	田中

このテーブルでは、「学科の教授」は「学科」に依存しています。また、「学科」は「学生ID」に依存しています。従って、「学科の教授」は「学生ID」に推移的関数従属しています。これは3NFに違反しています。

このテーブルを3NFに正規化するためには、「学科」テーブルと「学生」テーブルに分割することが一つの解決策です。 学生テーブル:

学生ID	学科
1	コンピュータ
2	数学
3	コンピュータ

学科テーブル:

学科	学科の教授
コンピュータ	田中
数学	鈴木

これで、「学科の教授」の推移的関数従属が解消され、テーブルが第三正規形を満たします。

このように正規化を行うことで、データの重複を避け、データの整合性が保たれます。これにより、データの管理が容易になり、データベースの性能も向上します。

以降の正規化について

ちなみに、第4正規形や第5正規形も存在しますが、これらはあまり一般的ではないようで、業務上必要になることは多くありません。
過去問をいくつか解いてみたところ、第3正規形までの問題は出題頻度がとても高いです。

頻度は高くないですが第4正規形に関連する知識を問われるものは下記のような問題がありました。

関係モデルにおいて，情報無損失分解ができ，かつ，関数従属性保存が成り立つ変換が必ず存在するものはどれか。ここで，情報無損失分解とは自然結合によって元の関係が必ず得られる分解をいう。

情報無損失分解 とは、分解された部分同士の自然結合によって元の関係を過不足なく復元できる性質、関数従属性保存とは、その名の通り分解後の部分が元の関係に存在した関数従属性を保っている性質をいいます。

正規化はデータの更新時異常を解消するために「第1正規形」→「第2正規形」→「第3正規形」→「ボイスコッド正規形」→「第4正規形」→「第5正規形」という順序で関係を情報無損失分解していきますが、このうち元の関係の関数従属性を保ったまま分解可能なのは第3正規形までです。

関係演算

関係演算の基礎

関係演算は、データベースから情報を取り出したり、複数のテーブルを組み合わせたりする際に行う基本的な操作です。ここでは、主に「選択」、「射影」、「結合」という3つの基本演算について、具体的なテーブルとSQLクエリを用いて説明します。

例:

1. 顧客テーブル

   顧客ID	顧客名
   1	田中
   2	鈴木

2. 注文テーブル

   注文ID	顧客ID	商品名
   A1	1	パソコン
   A2	1	椅子
   A3	2	テーブル

1. 選択（Selection）

• 説明: 選択は、テーブルの中から特定の条件を満たす行を抜き出す操作です。SQL文におけるWHERE句に相当します。
• SQLクエリ: SELECT * FROM テーブル名 WHERE 条件;

例:

-- 顧客名が「田中」の行を抜き出します。
SELECT * FROM 顧客 WHERE 顧客名 = '田中';

このクエリによって以下のような結果が得られます。

顧客ID	顧客名
1	田中

2. 射影（Projection）

• 説明: 射影は、テーブルの中から特定の列を抜き出す操作です。SQL文におけるSELECT句に相当します。
• SQLクエリ: SELECT 列名 FROM テーブル名;

例:

-- 顧客テーブルから「顧客名」の列だけを抜き出します。
SELECT 顧客名 FROM 顧客;

このクエリによって以下のような結果が得られます。

顧客名
田中
鈴木

3. 結合（Join）

• 説明: 結合は、異なるテーブルを共通の列に基づいて組み合わせる操作です。
• SQLクエリ: SELECT * FROM テーブル1 JOIN テーブル2 ON テーブル1.列名 = テーブル2.列名;

例:

-- 顧客テーブルと注文テーブルを「顧客ID」で結合します。
SELECT * FROM 顧客 JOIN 注文 ON 顧客.顧客ID = 注文.顧客ID;

このクエリによって以下のような結果が得られます。

顧客ID	顧客名	注文ID	商品名
1	田中	A1	パソコン
1	田中	A2	椅子
2	鈴木	A3	テーブル

業務においては対応するSQLが最初に頭に浮かびますが、射影のような日本語での操作とパッと思いつかないものもあります。

その他の関係演算

ここまで紹介した演算はDB独自のものですが、他にも集合演算のような基本的な演算があります。

1. 和（UNION）

論理和(OR)、和集合を求める演算子です。

簡単にいうとどちらかに含まれるものを取り出す演算子です。ただし、重複するものは取り除かれます。

和集合を求めるためには、両方のテーブルの列の数とデータ型が同じである必要があります。

SQLでは、UNION句が該当します。

例: テーブル1:

商品ID	商品名
1	パソコン
2	椅子

テーブル2:

商品ID	商品名
2	椅子
3	テーブル

これらのテーブルに対してUNIONを実行すると、以下のようなテーブルが得られます。

商品ID	商品名
1	パソコン
2	椅子
3	テーブル

SQLクエリの例:

SELECT * FROM テーブル1
UNION
SELECT * FROM テーブル2;

2. 積（INTERSECT）

論理積(AND)とは、積集合を求める演算子です。

両方のテーブルに共通する行を取り出す演算子です。

積演算の結果は以下のようなテーブルになります。

商品ID	商品名
2	椅子

SQLクエリの例:

SELECT * FROM テーブル1
INTERSECT
SELECT * FROM テーブル2;

3. 差（EXCEPT）

差集合を求める演算子で、片方のテーブルに含まれ、もう一方のテーブルには含まれない行を取り出します。

SQLでは、EXCEPT句が該当します。

差演算の結果は以下のようなテーブルになります。

商品ID	商品名
1	パソコン

SQLクエリの例:

SELECT * FROM 商品テーブル1
EXCEPT
SELECT * FROM 商品テーブル2;

4. 商（DIVISION）

商を求める際の基本的な考え方は、一方のテーブルの全ての要素がもう一方のテーブルに存在する時、その要素を取り出します。 ただし、SQL標準には直接的な商演算のサポートは含まれておらず、商演算を行うためには、他の基本演算を組み合わせる必要がありますので省略します。

参考

データベーススペシャリストドットコム

所感

これまでも基本情報や応用情報といったIPAの試験を受けてきましたが、今回のDBスペシャリスト試験はこれまでの試験と比べても難易度が高いと言われています。

ただ、基本的な対策はやっぱり過去問になると思うので、残り時間は少ないですが、演習中心に対策を進めていきたいと思います。