データベースの正規化

正規化はデータベース設計手法であり、リレーショナルデータベーステーブルをより高い正規形まで設計するために使用されます。プロセスは進歩的であり、前のレベルが満たされない限り、より高いレベルのデータベースの正規化を達成することはできません。

つまり、データを正規化されていない形式(最も正規化されていない)で保持し、最高の達成を目指します。正規化のレベルでは、最初のステップは第1正規形への準拠を確認することであり、第2ステップは、データが第6正規形に準拠するまで、上記の順序で第2正規形が満たされていることを確認することです。

ただし、4NFを超える正規形は、解決するために存在する問題が実際にはめったに現れないため、主に学術的な関心事であることに注意してください。

次の例のデータは通常の形式のほとんどと矛盾するように意図的に設計されています。実際には、テーブルには特定の正規形と矛盾するものが含まれていないため、正規化手順の一部をスキップできる可能性があります。また、1つの正規形の違反を修正すると、プロセス内のより高い正規形の違反も修正されることがよくあります。また、各ステップで正規化用に1つのテーブルが選択されています。つまり、このサンプルプロセスの最後に、最高の正規形を満たさないテーブルがまだ存在する可能性があります。

初期データ編集

次の構造のデータベーステーブルを作成します:

タイトル 作成者 作成者の国籍 形式 価格 件名 ページ 厚さ 発行者 発行国 発行タイプ ジャンルID ジャンル名
MySQLデータベースの設計と最適化の開始 チャドラッセル アメリカ人 ハードカバー 49.99 MySQL、

データベース、

デザイン

520 厚い 押す 米国 電子書籍 1 チュートリアル

この例では、各本の著者は1人だけであると想定しています。

1NFEditを満たす

1NFを満たすには、テーブルの各列の値がアトミックである必要があります。最初のテーブルでは、Subjectに一連のサブジェクト値が含まれているため、準拠していません。

1NFを実現する1つの方法は、繰り返しグループSubjectを使用して重複を複数の列に分割することです。

タイトル フォーマット 作成者 著者の国籍 価格 件名1 件名2 件名3 ページ 厚さ 発行者 発行国 ジャンルID ジャンル名
MySQLデータベースの設計と最適化の開始 ハードカバー チャドラッセル アメリカ人 49.99 MySQL データベース デザイン 520 厚い 押す 米国 1 チュートリアル

テーブルは正式に1NF(アトミック)に準拠していますが、このソリューションの問題は明らかです-もし書籍には3つ以上の主題があり、構造を変更せずにデータベースに追加することはできません。

より洗練された方法で問題を解決するには、表に示されているエンティティを識別して分離する必要があります。それぞれのテーブルに。この場合、Book、Subject、Publisherの各テーブルになります。

Book
タイトル フォーマット 著者 著者の国籍 価格 ページ 厚さ ジャンルID ジャンル名 発行者ID
MySQLの始まりデータベースの設計と最適化 ハードカバー チャドラッセル アメリカ人 49。99 520 厚い 1 チュートリアル 1
件名
件名ID 件名
1 MySQL
2 データベース
3 デザイン
パブリッシャー
Publisher_ID 名前
1 プレス 米国

初期データを複数のテーブルに分割するだけでは、データ間の接続が切断されます。つまり、新しく導入されたテーブル間の関係を決定する必要があります。書籍の表の出版社ID列は、書籍と出版社の間の多対1の関係を実現する外部キーであることに注意してください。

書籍は多くの主題に適合できますが、主題は多くの本に対応します。つまり、リンクテーブルを作成して、多対多の関係を定義する必要があります。

タイトル-件名
タイトル サブジェクトID
MySQLデータベースの設計と最適化の開始 1
MySQLデータベースの設計と最適化の開始 2
MySQLデータベースの設計と最適化の開始 3

正規化されていない形式の1つのテーブルの代わりに、1NFに準拠する4つのテーブルがあります。

2NFEditを満たす

Bookテーブルには、1つの候補キー(したがって、主キー)、複合キー{Title、Format}があります。次のテーブルフラグメントについて考えてみます。

タイトル 形式 作成者 作成者の国籍 価格 ページ 厚さ ジャンルID ジャンル名 発行者ID
MySQLデータベースの設計と最適化の開始 ハードカバー チャドラッセル アメリカン 49.99 520 厚い 1 チュートリアル 1
MySQLデータベースの設計と最適化の開始 電子書籍 チャドラッセル アメリカ人 22.34 520 厚い 1 チュートリアル 1
データベース管理のリレーショナルモデル:バージョン2 電子書籍 EFCodd 英国 13.88 538 厚い 2 人気のある科学 2
データベースMaのリレーショナルモデルnagement:バージョン2 ペーパーバック EFCodd 英国 39.99 538 厚い 2 ポピュラーサイエンス 2

候補キーの一部ではないすべての属性はタイトルに依存しますが、価格のみがフォーマットにも依存します。 2NFに準拠し、重複を削除するには、候補キー以外のすべての属性が、候補キーの一部だけでなく、候補キー全体に依存している必要があります。

このテーブルを正規化するには、{Title}を(単純な)候補キーにします。 (主キー)すべての非候補キー属性が候補キー全体に依存するようにし、Priceを別のテーブルに削除して、フォーマットへの依存性を維持できるようにします。

タイトル 作成者 作成者の国籍 ページ 厚さ ジャンルID ジャンル名 発行者ID
MySQLデータベースの設計と最適化の開始 チャドラッセル アメリカ人 520 厚い 1 チュートリアル 1
データベース管理のリレーショナルモデル:バージョン2 EFCodd 英国 538 厚い 2 人気のある科学 2
形式-価格
タイトル フォーマット 価格
MySQLデータベースの設計と最適化の開始 ハードカバー 49.99
MySQLデータベースの設計と最適化の開始 電子書籍 22。34
データベース管理のリレーショナルモデル:バージョン2 電子書籍 13.88
データベース管理のリレーショナルモデル:バージョン2 ペーパーバック 39.99

さて、 Bookテーブルは2NFに準拠しています。

3NFEditを満たす

Bookテーブルにはまだ一時的な機能依存性があります({AuthorNationality}は{Author}に依存し、{Titleに依存します) })。ジャンルにも同様の違反があります({ジャンル名}は{タイトル}に依存する{ジャンルID}に依存しています)。したがって、Bookテーブルは3NFにはありません。 3NFで作成するには、次のテーブル構造を使用して、{AuthorNationality}と{GenreName}をそれぞれのテーブルに配置することで一時的な機能の依存関係を排除しましょう。

タイトル 作成者 ページ 厚さ ジャンルID 発行者ID
MySQLデータベース設計の開始と最適化 チャドラッセル 520 厚い 1 1
データベース管理のリレーショナルモデル:バージョン2 EFCodd 538 厚い 2 2
形式-価格
タイトル フォーマット 価格
MySQLデータベース設計の開始と最適化 ハードカバー 49.99
MySQLデータベースの設計と最適化の開始 電子書籍 22.34
データベース管理のリレーショナルモデル:バージョン2 電子書籍 13.88
データベース管理のリレーショナルモデル:バージョン2 ペーパーバック 39.99
作成者
作成者 作成者の国籍
チャドラッセル アメリカ人
EFCodd イギリス人
ジャンル
Gen re ID ジャンル名
1 チュートリアル
2 人気の科学

EKNFEditの満足度

主な記事:エレメンタリーキーの通常の形式

エレメンタリーキーの通常の形式(EKNF)は、厳密に3NFとBCNFの間にあり、文献ではあまり説明されていません。両方の問題を回避しながら、「3NFとBCNFの両方の顕著な品質を把握する」ことを目的としています(つまり、3NFは「寛容すぎる」、BCNFは「計算が複雑になる」)。文献ではほとんど言及されていないため、この例には含まれていません。

4NFEditを満たす

データベースは、さまざまな場所にショップを所有する複数のフランチャイズ加盟店を持つ本の小売店フランチャイズによって所有されていると想定します。そのため、小売業者は、さまざまな場所での書籍の可用性に関するデータを含むテーブルを追加することにしました。

フランチャイズ-書籍の場所
フランチャイズID タイトル 場所
1 MySQLデータベースの設計と最適化の開始 カリフォルニア
1 MySQLデータベースの設計と最適化の開始 フロリダ
1 MySQLデータベースの設計と最適化の開始 テキサス
1 データベース管理のリレーショナルモデル:バージョン2 カリフォルニア
1 データベース管理のリレーショナルモデル:バージョン2 フロリダ
1 データベース管理のリレーショナルモデル:バージョン2 テキサス
2 MySQLデータベースの設計と最適化の開始 カリフォルニア
2 MySQLデータベースの設計と最適化の開始 フロリダ
2 MySQLデータベースの設計と最適化の開始 テキサス
2 データベース管理のリレーショナルモデル:バージョン2 カリフォルニア
2 データベース管理のリレーショナルモデル:バージョン2 フロリダ
2 データベース管理のリレーショナルモデル:バージョン2 テキサス
3 MySQLデータベースの設計と最適化の開始 テキサス

このテーブル構造は複合プライマリキーで構成されているため、キー以外の属性は含まれず、すでにBCNFに含まれています(したがって、以前のすべての通常の形式も満たしています)。ただし、利用可能なすべての書籍が各エリアで提供されていると仮定すると、タイトルが特定の場所に明確にバインドされていないため、テーブルが4NFを満たさないことに気付く場合があります。

つまり、 4番目の通常の形式を満たすには、次のテーブルも分解する必要があります。

フランチャイズ-本
フランチャイズID タイトル
1 MySQLデータベースの設計と最適化の開始
1 データベース管理のリレーショナルモデル:バージョン2
2 MySQLデータベースの設計と最適化の開始
2 データベース管理のリレーショナルモデル:バージョン2
3 MySQLデータベースの設計と最適化の開始
フランチャイズ-場所
フランチャイズID 場所
1 カリフォルニア
1 フロリダ
1 テキサス
2 カリフォルニア
2 フロリダ
2 テキサス
3 テキサス

これで、すべてのレコードがスーパーキーによって明確に識別されるため、4NFが満たされます。

ETNFEditを満たします

フランチャイジーがさまざまなサプライヤーに本を注文することもできるとします。関係にも次の制約が適用されます。

  • 特定のサプライヤーが特定のタイトルを提供し、そのタイトルがフランチャイジーに提供された場合
  • そしてフランチャイジーはサプライヤーによって供給されています。
  • 次に、サプライヤーはフランチャイジーにタイトルを提供します。
サプライヤー-本-フランチャイジー
サプライヤーID タイトル フランチャイズID
1 MySQLデータベースの設計と最適化の開始 1
2 データベース管理のリレーショナルモデル:バージョン2 2
3 SQLの学習 3

このテーブルは4NFですが、サプライヤIDは、予測の結合と同じです:{{サプライヤID、ブック}、{ブック、フランチャイズID}、{フランチャイズID、サプライヤID}}。その結合依存関係のコンポーネントはスーパーキーではないため(唯一のスーパーキーは見出し全体)、テーブルはETNFを満たさず、さらに分解できます:

サプライヤー-本
サプライヤID タイトル
1 MySQLデータベースの設計と最適化の開始
2 データベース管理のリレーショナルモデル:バージョン2
3 SQLの学習
本-フランチャイジー
タイトル フランチャイジーID
MySQLデータベースの設計と最適化の開始 1
データベース管理のリレーショナルモデル:バージョン2 2
SQLの学習 3
フランチャイズ-サプライヤー
サプライヤーID フランチャイズID
1 1
2 2
3 3

分解によりETNF準拠が生成されます。

5NFEditを満たします

5NFを満たさないテーブルを見つけるには、通常、データを徹底的に調べるために必要です。データに少し変更を加えた4NFの例のテーブルを想定し、5NFを満たすかどうかを調べてみましょう。

フランチャイズ-本の場所
フランチャイズID タイトル 場所
1 MySQLデータベースの設計と最適化の開始 カリフォルニア
1 SQLの学習 カリフォルニア
1 データベース管理のリレーショナルモデル:バージョン2 テキサス
2 データベース管理のリレーショナルモデル:バージョン2 カリフォルニア

このテーブルを分解すると、冗長性が低下し、次の2つのテーブルが得られます。

フランチャイズ-本
フランチャイズID タイトル
1 MySQLデータベースの設計と最適化の開始
1 SQLの学習
1 データベース管理のリレーショナルモデル:バージョン2
2 データベース管理のリレーショナルモデル:バージョン2
フランチャイズ-場所
フランチャイズID 場所
1 カリフォルニア
1 テキサス
2 カリフォルニア

これらのテーブルを結合しようとするとどうなりますか?クエリは次のデータを返します:

Franchisee –Book –Location JOINed
フランチャイズID タイトル 場所
1 MySQLデータベース設計の開始と最適化 カリフォルニア
1 データベースの学習 カリフォルニア
1 データベース管理のリレーショナルモデル:バージョン2 カリフォルニア
1 データベース管理のリレーショナルモデル:バージョン2 テキサス
1 SQLの学習 テキサス
1 MySQLデータベースの設計と最適化の開始 テキサス
2 データベース管理のリレーショナルモデル:バージョン2 カリフォルニア

どうやら、JOINは必要以上に3行を返します-anoを追加してみましょう関係を明確にするための表。最終的に3つの別々のテーブルになります:

フランチャイズ-本
フランチャイズID タイトル
1 MySQLデータベースの設計と最適化の開始
1 SQLの学習
1 データベース管理のリレーショナルモデル:バージョン2
2 データベース管理のリレーショナルモデル:バージョン2
フランチャイズ-場所
フランチャイズID 場所
1 カリフォルニア
1 テキサス
2 カリフォルニア
場所-ブーk
場所 タイトル
カリフォルニア MySQLデータベースの設計と最適化の開始
カリフォルニア SQLの学習
カリフォルニア データベース管理のリレーショナルモデル:バージョン2
テキサス データベース管理のリレーショナルモデル:バージョン2

JOINは今何を返しますか?実際には、これら3つのテーブルを結合することはできません。つまり、データを失うことなくフランチャイジー-ブックの場所を分解することはできなかったため、テーブルはすでに5NFを満たしています。

CJ Dateは、5NFのデータベースのみが真に「正規化」されていると主張しています。

DKNFEditを満足させる

前の例のBookテーブルを見て、ドメインキーの通常の形式を満たしているかどうかを確認しましょう。

論理的には、厚さはページ数によって決まります。つまり、キーではないページに依存します。 350ページまでの本は「スリム」と見なされ、350ページを超える本は「厚い」と見なされるという規則の例を設定しましょう。

この規則は技術的には制約ですが、ドメインではありません。制約もキー制約もありません。したがって、データの整合性を維持するためにドメイン制約とキー制約に依存することはできません。

言い換えると、たとえば、「厚い」だけの本を置くことを妨げるものは何もありません。 50ページ-これにより、テーブルはDKNFに違反します。

これを解決するには、厚さを定義する列挙を保持するテーブルを作成し、その列を元のテーブルから削除します。

タイトル ページ 厚さ ジャンルID 発行者ID
MySQLデータベースの設計と最適化の開始 520 厚い 1 1
データベース管理のリレーショナルモデル:バージョン2 538 厚い 2 2
SQLの学習 338 スリム 1 3
SQLクックブック 636 厚い 1 3
厚さの列挙
厚さ 最小ページ 最大ページ
スリム 1 350
厚い 351 999,999,999,999
本-ページ-ジャンル-発行者
タイトル ページ ジャンルID 発行者ID
MySQLデータベースの設計と最適化の開始 520 1 1
のリレーショナルモデルデータベース管理:バージョン2 538 2 2
SQLの学習 338 1 3
SQLクックブック 636 1 3

これにより、ドメイン整合性違反が排除され、テーブルはDKNFになります。

6NFEditを満たす

6番目の通常の形式の単純で直感的な定義は、「行に主キーと最大で1つの他の属性が含まれる場合、テーブルは6NFにある」というものです。

つまり、たとえば、1NFの作成中に設計されたPublisherテーブル

Publisher
Publisher_ID 名前
1 押す 米国

さらに進む必要があります2つのテーブルに分解:

パブリッシャー
Publisher_ID 名前
1 押す
発行国
Publisher_ID
1 米国

6NFの明らかな欠点は、単一のエンティティに関する情報を表すために必要なテーブルが急増していることです。 5NFのテーブルに1つの主キー列とN個の属性がある場合、6NFで同じ情報を表すには、N個のテーブルが必要になります。単一の概念レコードへの複数フィールドの更新には、複数のテーブルへの更新が必要になります。挿入と削除も同様に、複数のテーブルにわたる操作が必要になります。このため、オンライントランザクション処理のニーズに対応することを目的としたデータベースでは、6NFを使用しないでください。

ただし、インタラクティブな更新が許可されておらず、大量のデータに対する高速クエリに特化したデータウェアハウスでは、特定のDBMSは、列データストアと呼ばれる内部6NF表現を使用します。列の一意の値の数がテーブルの行の数よりはるかに少ない状況では、列指向のストレージにより、データ圧縮によってスペースを大幅に節約できます。列型ストレージでは、範囲クエリを高速に実行することもできます(たとえば、特定の列がXとYの間、またはX未満のすべてのレコードを表示します)。

ただし、これらすべての場合、データベース設計者はできません。個別のテーブルを作成して、6NF正規化を手動で実行する必要があります。 Sybase IQなどのウェアハウジングに特化した一部のDBMSは、デフォルトで列型ストレージを使用しますが、設計者には依然として単一の複数列テーブルしか表示されません。 Microsoft SQL Server 2012以降などの他のDBMSでは、特定のテーブルに「列ストアインデックス」を指定できます。

Write a Comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です