はじめに
こんにちは。新人エンジニアのしゅんやです！
今回は、データベースを運用するうえで切っても切り離すことのできない正規化について紹介していきます。
基本情報技術者試験や応用情報技術者試験でもよく問われる分野なのでこの記事を参考にしていただければと思います。
正規化とは
- データベース設計の工程において同一のテーブル内のデータ重複をなくすために、データを分割・整理することをいいます
- データベース運用時に発生する問題を未然に防止し、矛盾を生じさせない状態を作ります
起こりえる問題とは
- 例えば違うテーブルで同じ名前のカラムがあった時、どちらのカラムを使って開発を進めていいかわからなくなるので、データや計算結果の整合性が取りにくくなってしまいます
メリット
メリットは次の4つのものが挙げられます
- データ管理が容易になる
- データの共通性の向上
- データ容量の削減
- テーブルの意味が明確になる
デメリット
デメリットは次のようなものがあります
- 意識しすぎると検索内容によってはパフォーマンスが低下する
- テーブルの数が増える
これらの問題を解消するために
- デメリットはありますが、デメリットが目立つ例はまれなので、一般的には正規化が推奨されています
- 今回は第一正規化、第二正規化、第三正規化の紹介をしていきます
第一正規化
- 繰り返し項目のをれぞれを別レコードとして独立させ、レコードの長さを整えます
→正規化後はカラムの数が同じになります - データベースは縦方向に追加していく操作に適しています
→繰り返し部分を切り離し縦に並べることを第一正規化といいます
第一正規化の例
【正規化前の表】
- 各レコードの長さがバラバラで商品の情報のカラムが繰り返しになっています
- 商品ID、商品名、単価、数量が重複しています
| 注文番号 | 注文日 | ユーザーID | 購入ユーザー名 | 商品ID | 商品名 | 単価 | 数量 | 商品ID | 商品名 | 単価 | 数量 | 商品ID | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| T001 | 2022/10/21 | U001 | 鈴木太郎 | S001 | A商品 | 100 | 1 | S002 | 商品B | 500 | 2 | ||||
| T002 | 2022/10/23 | U002 | 佐藤次郎 | S002 | C商品 | 200 | 1 | ||||||||
| T003 | 2022/10/24 | U003 | 田中三郎 | S003 | D商品 | 300 | 2 | S005 | E商品 | 300 | 1 | S006 | F商品 | 200 | 1 |
第一正規化の例
【第一正規化後の表】
- カラム数が揃っています
| 注文番号 | 注文日 | ユーザーID | 購入ユーザー名 | 商品ID | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|
| T001 | 2022/10/21 | U001 | 鈴木太郎 | S001 | A商品 | 100 | 1 |
| T001 | 2022/10/21 | U001 | 鈴木太郎 | S002 | 商品B | 500 | 2 |
| T002 | 2022/10/23 | U002 | 佐藤次郎 | S002 | C商品 | 200 | 1 |
| T003 | 2022/10/24 | U003 | 田中三郎 | S003 | D商品 | 300 | 2 |
| T003 | 2022/10/24 | U003 | 田中三郎 | S005 | E商品 | 300 | 1 |
| T003 | 2022/10/24 | U003 | 田中三郎 | S006 | F商品 | 200 | 1 |
第二正規化
- 部分関数従属している列を整理することです
→主キーが決まると、列の値が一意に定まる関係のことを「関数従属」とよびます - 第一正規化されたテーブルから、部分関数従属している列を切り出したものを第二正規化といいます
第二正規化の例
【第二正規化後の表】
- 注文IDを指定してあげれば、注文日、ユーザーID、購入ユーザー名が取得でき、商品IDを指定してあげれば、商品名、単価が取得することが出来ます
→ この関係を部分関数従属といいます
第三正規化
- 主キー以外の列に関数従属している列を整理することです
- 推移関数従属性が存在しない状態にします
第三正規化の例
【第三正規化後の表】
- 例えば、X→Y（XがYに関数従属）及びY→Z（ZがYに関数従属）の関係がある時、X→Z（ZがXに関数従属）が成立する関係のことを言います
- 簡単に言うと、ポチは犬で、犬はかわいい、ということはポチはかわいいということになります。これが、第三正規化されている状態です
正規化後の表
正規化前の表
| 注文番号 | 注文日 | ユーザーID | 購入ユーザー名 | 商品ID | 商品名 | 単価 | 数量 | 商品ID | 商品名 | 単価 | 数量 | 商品ID | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| T001 | 2022/10/21 | U001 | 鈴木太郎 | S001 | A商品 | 100 | 1 | S002 | 商品B | 500 | 2 | ||||
| T002 | 2022/10/23 | U002 | 佐藤次郎 | S002 | C商品 | 200 | 1 | ||||||||
| T003 | 2022/10/24 | U003 | 田中三郎 | S003 | D商品 | 300 | 2 | S005 | E商品 | 300 | 1 | S006 | F商品 | 200 | 1 |
正規化後の表
- 正規化される前のテーブルと比較すると、正規化された後のテーブルの方が管理、編集がしやすくなっています
正規化が必要ない時(非正規化)
【メリット】
- 正規化を進めることによるデメリットを考慮し、あえて正規化を進めずに留めておくことを指します
- 性能を上げるために「正規化を崩す」処理のことをいいます
→ 非正規化
【デメリット】
- 非正規化は、検索のパフォーマンスを向上させるが、更新のパフォーマンスを低下させてしまいます
- 後続の工程で設計変更すると、手戻りが大きくなってしまいます
※非正規化は単に正規化しないことをいうのではなく、正規化を進めた結果がどのようなもので、デメリットがどの程度あるか検討したうえで正規化の段階を退行させることが大切です
非正規化の表の例
以下の商品テーブルと購入明細テーブルがあるとします
商品名テーブル
| 商品ID | 商品名 | 単価 |
|---|---|---|
| S001 | りんご | 100 |
| S002 | みかん | 200 |
購入明細テーブル
| 購入明細ID | 商品ID | 購入数 | 購入日 |
|---|---|---|---|
| K001 | S001 | 1 | 2022/10/20 |
| K002 | S002 | 5 | 2022/10/22 |
- 商品テーブルのレコードの内容が変わると、変わる以前の購入明細が購入した時の内容と合わなくなってしまうので不整合が発生してしまいます
- 10月20日の時点ではりんごの価格が100円だったが、10月25日にりんごの価格が150円に変わった場合、当時の価格と変わってしまいます
非正規化の表の例
- 商品明細テーブルに以下のカラムを追加することにより、非正規化されて整合性が保たれます
非正規化カラム（商品名・単価）を追加した商品明細テーブル
| 購入明細ID | 商品ID | 購入数 | 購入日 | 商品名 | 単価 |
|---|---|---|---|---|---|
| K001 | S001 | 1 | 2022/10/20 | りんご | 100 |
| K002 | S002 | 5 | 2022/10/22 | みかん | 200 |
E-R図
- E-R図とは、エンティティ（もの）＋リレーションシップ（関係）の組み合わせで、システムのデータやデータ間の処理構造を設計します
- 後戻りのコストを防ぐことが出来る
- 運用、保守のフェーズで役立つ
- E-R図は、一対一、一対多、多対多の関係が存在します
例）
- 一対一は、個別指導塾で教師一人に対して生徒も一人という関係が成り立っています
- 一対多は、小学校で教師一人に対して、生徒が複数人いるという関係が成り立っています
- 多対多は、中学校以降の学校で、先生から見ても生徒から見ても複数人いるという関係が成り立っています
E-R図の多対多がいけない理由
- 不要なカラムが多数発生してしまう恐れがあります
- データベース設計において非常によくない設計であるとされています
- コーズから見てもユーザーから見ても一意に識別することが出来ません
- 空のカラムが発生してしまいます
解決方法
- 中間テーブルを用いる
→多対多の関係性があるテーブルの間に設置
→接続する2つのテーブルの外部キーを持つ
まとめ
第一正規化
- 繰り返し項目を持たない
- カラム数を同じにする
第二正規化
- 第一正規形を満たしている
- 主キーに対してすべての非キー属性が完全関数従属
第三正規化
- 第二正規形を満たしている
- すべての非キー属性がどの候補キーに対しても推移的に関数従属していない
非正規化
- 正規化を進めずあえて正規化をしない
- 正規化を戻す
E-R図
- システムのデータやデータ間の処理構造を設計する
- 後戻りのコストを防ぐことができ、運用・保守のフェーズで役に立つ
最後に
いかがでしたでしょうか
今回は、データベースを設計するうえで必ず考えないといけない正規化について紹介してきました。
基本情報技術者試験や応用情報技術者試験にもよく出題される分野なので、この記事を参考にしていただければと思います！