データベース正規化チュートリアル：1NF 2NF 3NFBCNFの例

database normalization tutorial

このチュートリアルでは、データベースの正規化と、1NF 2NF3NFやBCNFなどのさまざまな正規形とSQLコードの例について説明します。

データベースの正規化は、データベーススキーマの設計に使用されるよく知られた手法です。

正規化手法を適用する主な目的は、データの冗長性と依存性を減らすことです。正規化は、それらのテーブル間の論理関係を定義することにより、大きなテーブルを複数の小さなテーブルに分割するのに役立ちます。

学習内容：

• データベースの正規化とは何ですか？
  • DBMS正規化の利点
  • データベース正規化のデメリット
  • 正規形の種類
    • ＃1）1NF（第一正規形）
    • ＃2）2NF（第2正規形）
    • ＃3）3NF（第3正規形）
    • ＃4）ボイスコッド正規形（3.5正規形）
    • ＃5）第4正規形（4正規形）
    • ＃6）5番目の通常の形式（5つの通常の形式）
  • よくある質問と回答
• 結論
  • 推奨読書

データベースの正規化とは何ですか？

データベースの正規化またはSQLの正規化は、関連するデータを1つのテーブルにグループ化するのに役立ちます。属性データまたは間接的に関連するデータは異なるテーブルに配置され、これらのテーブルは親テーブルと子テーブルの間の論理関係で接続されます。

1970年、エドガーF.コッドは正規化の概念を考案しました。彼は「大規模共有銀行のデータのリレーショナルモデル」という名前の論文を共有し、そこで彼は「第一正規形（1NF）」を提案しました。

DBMS正規化の利点

データベースの正規化には、次の基本的な利点があります。

1. 正規化は、データを1つの場所にのみ保存することでデータの重複を回避するため、データの整合性を高めます。
2. 正規化は、同じスキーマの下で類似データまたは関連データをグループ化するのに役立ちます。これにより、データのグループ化が向上します。
3. 正規化により、インデックスの作成が高速化されるため、検索が高速化されます。したがって、正規化されたデータベースまたはテーブルがOLTP（オンライントランザクション処理）に使用されます。

データベース正規化のデメリット

DBMSの正規化には、次の欠点があります。

1. たとえば、製品や従業員の関連データを1つの場所で見つけることができず、複数のテーブルを結合する必要があります。これにより、データの取得に遅延が発生します。
2. したがって、正規化はOLAPトランザクション（オンライン分析処理）では適切なオプションではありません。

先に進む前に、次の用語を理解しましょう。

• エンティティ： エンティティは実際のオブジェクトであり、そのようなオブジェクトに関連付けられたデータがテーブルに格納されます。このようなオブジェクトの例は、従業員、部門、学生などです。
• 属性： 属性はエンティティの特性であり、エンティティに関する情報を提供します。 例えば、 テーブルがエンティティの場合、列はそれらの属性です。

正規形の種類

＃1）1NF（第一正規形）

定義上、繰り返し列またはデータグループを持たないエンティティは、第一正規形と呼ばれます。第一正規形では、すべての列が一意です。

以下は、第一正規形（1NF）の場合、従業員と部門のテーブルがどのように見えるかを示しています。

ここでは、EmployeesテーブルとDepartmentテーブルの両方のすべての列が1つにまとめられており、すべてのデータが1つの場所で利用できるため、deptNumのように列を接続する必要はありません。

ただし、必要なすべての列が含まれるこのようなテーブルは、管理が難しいだけでなく、操作の実行も難しく、ストレージの観点からも非効率的です。

＃2）2NF（第2正規形）

定義上、1NFであり、その属性の1つであるエンティティは主キーとして定義され、残りの属性は主キーに依存します。

以下は、従業員と部門テーブルがどのように見えるかの例です。

従業員テーブル：

部門テーブル：

EmpDeptテーブル：

ここでは、1NF形式のテーブルを3つの異なるテーブルに分割したことがわかります。 Employeesテーブルは、会社のすべての従業員に関するエンティティであり、その属性は各従業員のプロパティを記述します。このテーブルの主キーはempNumです。

同様に、Departmentsテーブルは、会社のすべての部門に関するエンティティであり、その属性は各部門のプロパティを記述します。このテーブルの主キーはdeptNumです。

3番目の表では、両方の表の主キーを組み合わせています。 EmployeesテーブルとDepartmentsテーブルの主キーは、この3番目のテーブルでは外部キーと呼ばれます。

ユーザーが1NFで行ったものと同様の出力が必要な場合、ユーザーは主キーを使用して3つのテーブルすべてを結合する必要があります。

サンプルクエリは次のようになります。

＃3）3NF（第3正規形）

定義上、テーブル/エンティティがすでに第2正規形であり、テーブル/エンティティの列が主キーに非推移的に依存している場合、テーブルは第3正規と見なされます。

次の例を使用して、非推移的な依存関係を理解し​​ましょう。

「Customer」という名前のテーブルに次の列があるとします。

顧客ID –一意の顧客を識別する主キー
CustomerZIP –お客様が居住する地域の郵便番号
CustomerCity –顧客が居住する都市

上記の場合、CustomerCity列はCustomerZIP列に依存し、CustomerZIP列はCustomerIDに依存します。

上記のシナリオは、CustomerCity列のCustomerID、つまり主キーへの推移的な依存関係と呼ばれます。推移的な依存関係を理解し​​た後、この依存関係の問題について説明しましょう。

CustomerCityを更新せずに、CustomerZIPを別の都市の郵便番号に更新するためにテーブルに不要な更新が行われ、データベースが不整合な状態のままになる可能性があります。

この問題を修正するには、CustomerZIP（主キーとして）とCustomerCityの2つの列を保持する別のテーブル（CustZIPテーブルなど）を作成することで実行できる推移的な依存関係を削除する必要があります。

CustomerテーブルのCustomerZIP列は、CustZIPテーブルのCustomerZIPへの外部キーです。この関係により、CustomerCityに変更を加えることなくCustomerZIPが更新される更新に異常がないことが保証されます。

＃4）ボイスコッド正規形（3.5正規形）

定義上、テーブルはボイスコッド正規形と見なされます。すでに第3正規形であり、AとBの間のすべての機能依存性について、Aがスーパーキーである必要があります。

この定義は少し複雑に聞こえます。 それをよりよく理解するためにそれを壊してみましょう。

• 機能従属性： テーブルの属性または列が同じテーブルの別の属性または列を一意に識別する場合、テーブルの属性または列は機能的に依存していると言われます。
  例えば、 empNumまたはEmployeeNumber列は、Employeeテーブル内のEmployee Name、EmployeeSalaryなどの他の列を一意に識別します。
• スーパーキー： テーブル内の単一の行を一意に識別できる単一のキーまたは複数のキーのグループは、スーパーキーと呼ばれます。一般的に、コンポジットキーなどのキーを知っています。

次のシナリオを検討して、第3正規形に問題がある場合と、ボイスコッド正規形がどのように救済されるかを理解しましょう。

上記の例では、empNum1001と1007の従業員は2つの異なる部門で働いています。各部門には部門長がいます。部門ごとに複数の部門長がいる場合があります。経理部門と同様に、レイモンドとサマラは2つの部門長です。

この場合、empNumとdeptNameはスーパーキーです。これは、deptNameが主要な属性であることを意味します。これらの2つの列に基づいて、すべての行を一意に識別できます。

また、deptNameはdeptHeadに依存します。これは、deptHeadが非プライム属性であることを意味します。この基準は、テーブルがBCNFの一部であることを失格にします。

これを解決するために、以下に説明するように、テーブルを3つの異なるテーブルに分割します。

従業員テーブル：

部門テーブル：

＃5）第4正規形（4正規形）

定義上、関連するエンティティを説明する2つ以上の独立したデータがない場合、テーブルは第4正規形になります。

＃6）5番目の通常の形式（5つの通常の形式）

テーブルは、第4正規形の条件を満たす場合にのみ、第5正規形と見なすことができ、データを失うことなく複数のテーブルに分割できます。

よくある質問と回答

Q＃1）データベースの正規化とは何ですか？

回答： データベースの正規化は設計手法です。これを使用して、データベース内のスキーマを設計または再設計し、データをより小さく、より関連性の高いテーブルに分割することで、冗長なデータとデータの依存関係を減らすことができます。

Q＃2）正規化の種類は何ですか？

経験豊富なPDFのSQLサーバーインタビューの質問と回答

回答： 以下は、データベーススキーマの設計に使用できるさまざまなタイプの正規化手法です。

• 第一正規形（1NF）
• 第2正規形（2NF）
• 第3正規形（3NF）
• ボイスコッド正規形（3.5NF）
• 第4正規形（4NF）
• 5番目の通常の形式（5NF）

Q＃3）正規化の目的は何ですか？

回答： 正規化の主な目的は、データの冗長性を減らすことです。つまり、データは1回だけ保存する必要があります。これは、同じデータを2つの異なるテーブルに格納しようとしたときに発生する可能性のあるデータの異常を回避するためですが、変更は一方にのみ適用され、もう一方には適用されません。

Q＃4）非正規化とは何ですか？

回答： 非正規化は、データベースのパフォーマンスを向上させる手法です。この手法は、データの冗長性を削除する正規化されたデータベースとは対照的に、データベースに冗長データを追加します。

これは、JOINを実行して複数のテーブルからデータを取得するのがコストのかかる巨大なデータベースで行われます。したがって、冗長データは、JOIN操作を回避するために複数のテーブルに格納されます。

結論

これまでに、3つのデータベース正規化フォームを実行しました。

理論的には、Boyce-Codd Normal Form、4NF、5NFなどのより高度な形式のデータベース正規化があります。ただし、3NFは、本番データベースで広く使用されている正規化形式です。

幸せな読書！！

推奨読書

• JMeterを使用したデータベーステスト
• MongoDBデータベースバックアップの作成
• MongoDBデータベース作成チュートリアル
• 複雑なデータモデルを構築するためのトップ10データベース設計ツール
• MongoDBパフォーマンス：ロックパフォーマンス、ページフォールト、データベースプロファイリング
• Altibaseオープンソースリレーショナルデータベースレビュー
• クエリとパフォーマンスを監視するためのMongoDBデータベースプロファイラー
• Oracleデータベースをテストする方法