データベースの正規化

データベースの正規化は、データの冗長性を減らし、データの整合性を向上させるために、一連のいわゆる正規形に従ってリレーショナルデータベースを構造化するプロセスです。これは、英国のコンピューター科学者 Edgar F. Coddがリレーショナルモデルの一部として初めて提案しました。

正規化では、データベースの列(属性) とテーブル(関係) を整理して、それらの依存関係がデータベース整合性制約によって適切に適用されるようにします。これは、合成(新しいデータベース設計の作成) または分解(既存のデータベース設計の改善)のプロセスによって、いくつかの正式なルールを適用することで実現されます。

目的

1970年にコッドが定義した第一正規形の基本的な目的は、第一階述語論理に基づいた「ユニバーサルデータサブ言語」を使用してデータのクエリと操作を可能にすることであった。^[1]そのような言語の例としてSQLがあるが、コッドはSQLに重大な欠陥があるとみなしていた。^[2]

1NF (第 1 正規形) を超える正規化の目的は、Codd によって次のように述べられました。

関係のコレクションを、望ましくない挿入、更新、削除の依存関係から解放します。

新しいタイプのデータが導入されたときに関係のコレクションを再構築する必要性を減らし、アプリケーションプログラムの寿命を延ばします。

リレーショナルモデルをユーザーにとってより有益なものにするため。

クエリ統計に対して中立な関係のコレクションを作成します。これらの統計は時間の経過とともに変化する可能性があります。

— EF Codd、「データベースリレーショナルモデルのさらなる正規化」^[3]

リレーションを変更 (更新、挿入、または削除) しようとすると、十分に正規化されていないリレーションで次の望ましくない副作用が発生する可能性があります。

挿入異常: 特定の事実をまったく記録できない状況があります。たとえば、「教員とそのコース」関係の各レコードには、教員 ID、教員名、教員採用日、コースコードが含まれる場合があります。したがって、少なくとも 1 つのコースを教える教員の詳細は記録できますが、コースコードを null に設定しない限り、まだコースを教える割り当てられていない新しく採用された教員の詳細は記録できません。
更新異常: 同じ情報が複数の行に表現される可能性があるため、関係を更新すると論理的な矛盾が生じる可能性があります。たとえば、「従業員のスキル」関係の各レコードには、従業員 ID、従業員の住所、スキルが含まれる可能性があります。したがって、特定の従業員の住所の変更は、複数のレコード (スキルごとに 1 つ) に適用する必要がある場合があります。更新が部分的にしか成功しなかった場合 (従業員の住所が一部のレコードで更新され、他のレコードでは更新されない場合)、関係は矛盾した状態のままになります。具体的には、関係は、この特定の従業員の住所が何であるかという質問に対して矛盾した回答を提供します。
削除異常: 特定の状況では、特定の事実を表すデータを削除すると、まったく異なる事実を表すデータも削除する必要があります。前の例で説明した「教員とそのコース」関係は、このタイプの異常に悩まされています。教員が一時的にどのコースにも割り当てられなくなった場合、その教員が記載されている最後のレコードを削除する必要があり、コースコードフィールドが null に設定されていない限り、実質的に教員も削除されます。

データベース構造を拡張する際の再設計を最小限に抑える

完全に正規化されたデータベースでは、既存の構造をあまり変更せずに、新しいタイプのデータに対応するために構造を拡張できます。その結果、データベースと対話するアプリケーションへの影響は最小限に抑えられます。

正規化された関係、および 1 つの正規化された関係と別の正規化された関係の関係は、現実世界の概念とそれらの相互関係を反映しています。

正規形

コッドは1970年に正規化の概念と現在では第一正規形（1NF）として知られているものを導入しました。 ^{[4]コッドは1971年に}第二正規形（2NF）と第三正規形（3NF）を定義し、^[5]コッドとレイモンド・F・ボイスは1974年にボイス・コッド正規形（BCNF）を定義しました。 ^[6]

ロナルド・フェイギンは1977 年に第 4 正規形(4NF)を導入し、 1979 年には第 5 正規形(5NF)を導入しました。クリストファー・J・デイトは2003 年に第6 正規形(6NF) を導入しました。

非公式には、リレーショナルデータベースの関係は、第3正規形を満たす場合、「正規化されている」と表現されることが多い。^[7]ほとんどの3NF関係には、挿入、更新、削除の異常がない。

正規形（最も正規化されていないものから最も正規化されているものまで）は次のとおりです。

段階的な正規化の例

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

つまり、データが正規化されていない形式（最も正規化されていない形式）にあり、最高レベルの正規化を達成することを目指す場合、最初のステップは第 1 正規形への準拠を確実にすること、2 番目のステップは第 2 正規形が満たされていることを確認すること、というように、データが第 6 正規形に準拠するまで上記の順序で処理が行われます。

しかし、 4NFを超える正規形は主に学術的な関心事であり、それらが解決するために存在する問題は実際にはほとんど現れないことに注意する必要がある。 ^[11]

次の例のデータは、ほとんどの正規形と矛盾するように意図的に設計されています。実際には、データはすでにある程度正規化されているため、正規化手順の一部を省略できることがよくあります。1 つの正規形の違反を修正すると、より高次の正規形の違反も修正されることがよくあります。例では、各手順で 1 つのテーブルが正規化対象として選択されているため、最終的に一部のテーブルが十分に正規化されていない可能性があります。

初期データ

次のような構造のデータベーステーブルがあるとする: ^[10]

この例では、各書籍には著者が 1 人だけいるものと想定します。

リレーショナルモデルに準拠するテーブルには、行を一意に識別する主キーがあります。この例では、主キーは{Title, Format}の複合キーです(下線で示されています)。

1NFを満たす

最初の正規形では、各フィールドには 1 つの値が含まれます。フィールドには、値のセットやネストされたレコードを含めることはできません。

件名には件名値のセットが含まれており、準拠していないことを意味します。

この問題を解決するために、主題は別のSubjectテーブルに抽出されます。^[10]

非正規化形式の 1 つのテーブルの代わりに、1NF に準拠する 2 つのテーブルが存在するようになりました。

2NFを満たす

以下のBookテーブルには複合キー{Title, Format}があることを思い出してください。このキーのサブセットが行列式である場合、2NF を満たしません。設計のこの時点では、キーは主キーとして確定されていないため、候補キーと呼ばれます。次のテーブルを検討してください。

候補キーの一部ではない属性はすべてTitleに依存しますが、PriceのみFormatにも依存します。2NFに準拠し、重複を削除するには、候補キー以外のすべての属性が候補キーの一部ではなく、全体に依存する必要があります。

このテーブルを正規化するには、{Title}を (単純な) 候補キー (主キー) にして、候補キー以外のすべての属性が候補キー全体に依存するようにし、Price を別のテーブルに移動して、 Formatへの依存関係を維持できるようにします。

これで、BookテーブルとPriceテーブルの両方が2NFに準拠するようになりました。

3NFを満たす

Bookテーブルには、依然として推移的な機能依存関係があります ({Author Nationality} は {Author }に依存し、{Author} は {Title} に依存します)。出版社 ({Publisher Country} は {Publisher} に依存し、{Publisher} は {Title} に依存します) とジャンル ({Genre Name} は {Genre ID} に依存し、{Genre ID} は {Title} に依存します) にも同様の違反があります。したがって、Bookテーブルは 3NF ではありません。これを解決するには、{Author Nationality}、{Publisher Country}、および {Genre Name} をそれぞれ独自のテーブルに配置して、推移的な機能依存関係を排除します。

EKNFを満たす

基本キー正規形 (EKNF) は厳密には 3NF と BCNF の中間に位置し、文献ではあまり議論されていません。EKNF は、両方の問題 (つまり、3NF は「寛容すぎる」ことと、BCNF は「計算が複雑になりやすい」こと) を回避しながら、 「3NF と BCNF の両方の顕著な特性を捉える」ことを目的としています。文献ではほとんど言及されていないため、この例には含まれていません。

4NFを満たす

データベースは、さまざまな場所に店舗を所有する複数のフランチャイズを持つ書籍小売業者のフランチャイズによって所有されていると仮定します。そのため、小売業者は、さまざまな場所の書籍の在庫状況に関するデータを含むテーブルを追加することにしました。

このテーブル構造は複合主キーで構成されているため、キー以外の属性は含まれず、すでにBCNFになっています(したがって、以前のすべての正規形も満たしています)。ただし、入手可能なすべての書籍が各エリアで提供されていると仮定すると、タイトルは特定の場所に明確にバインドされていないため、テーブルは4NF を満たしていません。

つまり、第 4 正規形を満たすには、この表も分解する必要があります。

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

ETNFを満たす

フランチャイズ店が別のサプライヤーから本を注文することもできると仮定します。この関係には次の制約も適用されます。

特定のサプライヤーが特定のタイトルを供給している場合
そしてそのタイトルはフランチャイジーに提供される
フランチャイジーはサプライヤーから供給を受けており、
その後、サプライヤーはフランチャイジーに所有権を付与する。^[12]

この表は4NFですが、サプライヤIDはその射影の結合に等しくなります: {{サプライヤID、タイトル}、{タイトル、フランチャイズID}、{フランチャイズID、サプライヤID}}。この結合依存関係のどのコンポーネントもスーパーキーではありません(唯一のスーパーキーは見出し全体です)。そのため、この表はETNFを満たしておらず、さらに分解することができます: ^[12]

分解により ETNF コンプライアンスが生成されます。

5NFを満たす

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

このテーブルを分解すると冗長性が減り、次の 2 つのテーブルが生成されます。

これらのテーブルを結合するクエリは次のデータを返します。

JOIN は、必要以上に 3 行多く返します。関係を明確にするために別のテーブルを追加すると、3 つの個別のテーブルが作成されます。

JOIN は次に何を返すでしょうか? 実際には、これら 3 つのテーブルを結合することはできません。つまり、データ損失なしでFranchisee - Book - Location を分解することは不可能であり、したがってテーブルはすでに5NF を満たしています。

CJ Dateは、 5NFのデータベースだけが真に「正規化」されていると主張している。^[13]

DKNFの満足

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

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

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

言い換えれば、たとえば 50 ページしかない本に「厚い」と表示することを妨げるものは何もありません。これにより、表はDKNFに違反することになります。

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

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

6NFを満たす

第6正規形のシンプルで直感的な定義は、「行に主キーと最大で1つの他の属性が含まれている場合、テーブルは6NFである」というものです。^[14]

つまり、たとえば、1NF の作成中に設計された Publisherテーブルは次のようになります。

さらに 2 つのテーブルに分解する必要があります。

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

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

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

参照

注釈と参考文献

^ 「リレーショナルデータモデルを採用すると、応用述語計算に基づく汎用データサブ言語の開発が可能になります。関係の集合が正規形であれば、1 階述語計算で十分です。このような言語は、提案されている他のすべてのデータ言語の言語力の尺度となり、それ自体がさまざまなホスト言語 (プログラミング言語、コマンド指向言語、問題指向言語) に組み込むための有力な候補となります (適切な構文修正が必要)。」 Codd、「大規模共有データバンク用のリレーショナルデータモデル」 2007 年 6 月 12 日、Wayback Machineにアーカイブ、p. 381
^ Codd, EF 第 23 章「SQL の重大な欠陥」、The Relational Model for Database Management: Version 2、Addison-Wesley (1990)、pp. 371–389
^ Codd, EF「データベースリレーショナルモデルのさらなる正規化」、34 ページ
^ ab Codd, EF (1970 年 6月)。「大規模共有データバンクのリレーショナルデータモデル」。Communications of the ACM。13 ( 6): 377–387。doi : 10.1145 /362384.362685。S2CID 207549016。
^ abcd Codd, EF 「データベースリレーショナルモデルのさらなる正規化」。(Courant Computer Science Symposia Series 6、「データベースシステム」、ニューヨーク市、1971 年 5 月 24 ～ 25 日発表) IBM Research Report RJ909 (1971 年 8 月 31 日)。Randall J. Rustin (編)、『データベースシステム: Courant Computer Science Symposia Series 6』に再掲載。Prentice-Hall、1972 年。
^ Codd, EF「リレーショナルデータベースシステムに関する最近の調査」。IBM リサーチレポート RJ1385 (1974 年 4 月 23 日)。Proc . 1974 Congress (ストックホルム、スウェーデン、1974 年)、NY: North-Holland (1974 年) に再掲載。
^ Date, CJ (1999).データベースシステム入門. Addison-Wesley. p. 290.
^ Darwen, Hugh; Date, CJ; Fagin, Ronald (2012). 「リレーショナルデータベースで冗長なタプルを防ぐための正規形」(PDF)。第15 回国際データベース理論会議の議事録。EDBT/ICDT 2012 合同会議。ACM 国際会議議事録シリーズ。Association for Computing Machinery。p . 114。doi : 10.1145 /2274576.2274589。ISBN 978-1-4503-0791-8. OCLC 802369023. 2016年3月6日時点のオリジナルよりアーカイブ(PDF) 。2018年5月22日閲覧。
^ Kumar, Kunal; Azad, SK (2017 年 10 月)。「データベース正規化設計パターン」。2017年 4 回目の IEEE ウッタルプラデーシュ支部国際電気・コンピューター・電子工学会議 (UPCON)。IEEE。pp. 318–322。doi : 10.1109 / upcon.2017.8251067。ISBN 9781538630044. S2CID 24491594。
^ abc 「 MySQLでのデータベース正規化: 4 つの簡単な手順」。ComputerWeekly.com。2017年 8 月 30 日時点のオリジナルよりアーカイブ。2021年3 月 23 日閲覧。
^ 「データベースの正規化: 第 5 正規形とそれ以降」。MariaDBナレッジベース。2019 年1 月 23 日閲覧。
^ ab Date, CJ (2015 年 12 月 21 日)。新しいリレーショナルデータベース辞書: 用語、概念、および例。「O'Reilly Media, Inc.」。p. 138。ISBN 9781491951699。
^ Date, CJ (2015 年 12 月 21 日)。新しいリレーショナルデータベース辞書: 用語、概念、および例。「O'Reilly Media, Inc.」。p. 163。ISBN 9781491951699。
^ 「正規化 - 例を使って6NFを理解したい」。Stack Overflow 。 2019年1月23日閲覧。
^ Microsoft Corporation。列ストアインデックス: 概要。https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview。2020 年 3 月 23 日にアクセス。

さらに読む

Date, CJ (1999)、『データベースシステム入門』（第8版）。Addison-Wesley Longman。ISBN 0-321-19784-4。
ケント、W. (1983)リレーショナルデータベース理論における 5 つの正規形の簡単なガイド、Communications of the ACM、vol. 26、pp. 120–125
H.-J. Schek、P. Pistor 統合データベース管理および情報検索システムのためのデータ構造

外部リンク

ケント、ウィリアム (1983年2 月)。「リレーショナルデータベース理論における 5 つの正規形の簡単なガイド」。Communications of the ACM。26 ( 2): 120–125。doi : 10.1145/358024.358054。S2CID 9195704。
データベース正規化の基礎 2007 年 2 月 5 日にWayback Machineにアーカイブされました(Mike Chapple (About.com))
データベース正規化の紹介 2011年9月28日アーカイブ、Wayback Machine、パート2 2011年7月8日アーカイブ、Wayback Machine
Mike Hillyer 著「データベース正規化入門」。
Fred Coulson による最初の 3 つの正規形に関するチュートリアル
Microsoftによるデータベース正規化の基礎の説明
Chaitanya による DBMS の正規化 (beginnersbook.com)
データベース正規化のステップバイステップガイド
ETNF – 基本タプル正規形 2016年3月6日アーカイブ、Wayback Machine

[1] 「リレーショナルデータモデルを採用すると、応用述語計算に基づく汎用データサブ言語の開発が可能になります。関係の集合が正規形であれば、1 階述語計算で十分です。このような言語は、提案されている他のすべてのデータ言語の言語力の尺度となり、それ自体がさまざまなホスト言語 (プログラミング言語、コマンド指向言語、問題指向言語) に組み込むための有力な候補となります (適切な構文修正が必要)。」 Codd、「大規模共有データバンク用のリレーショナルデータモデル」 2007 年 6 月 12 日、Wayback Machineにアーカイブ、p. 381

[2] Codd, EF 第 23 章「SQL の重大な欠陥」、The Relational Model for Database Management: Version 2、Addison-Wesley (1990)、pp. 371–389

[3] Codd, EF「データベースリレーショナルモデルのさらなる正規化」、34 ページ

[Codd1970-4] Codd, EF (1970 年 6月)。「大規模共有データバンクのリレーショナルデータモデル」。Communications of the ACM。13 ( 6): 377–387。doi : 10.1145 /362384.362685。S2CID 207549016。

[Codd,_E.F_1971-5] Codd, EF 「データベースリレーショナルモデルのさらなる正規化」。(Courant Computer Science Symposia Series 6、「データベースシステム」、ニューヨーク市、1971 年 5 月 24 ～ 25 日発表) IBM Research Report RJ909 (1971 年 8 月 31 日)。Randall J. Rustin (編)、『データベースシステム: Courant Computer Science Symposia Series 6』に再掲載。Prentice-Hall、1972 年。

[CoddBCNF-6] Codd, EF「リレーショナルデータベースシステムに関する最近の調査」。IBM リサーチレポート RJ1385 (1974 年 4 月 23 日)。Proc . 1974 Congress (ストックホルム、スウェーデン、1974 年)、NY: North-Holland (1974 年) に再掲載。

[DateIntroDBSys-7] Date, CJ (1999).データベースシステム入門. Addison-Wesley. p. 290.

[8] Darwen, Hugh; Date, CJ; Fagin, Ronald (2012). 「リレーショナルデータベースで冗長なタプルを防ぐための正規形」(PDF)。第15 回国際データベース理論会議の議事録。EDBT/ICDT 2012 合同会議。ACM 国際会議議事録シリーズ。Association for Computing Machinery。p . 114。doi : 10.1145 /2274576.2274589。ISBN 978-1-4503-0791-8. OCLC 802369023. 2016年3月6日時点のオリジナルよりアーカイブ(PDF) 。2018年5月22日閲覧。

[9] Kumar, Kunal; Azad, SK (2017 年 10 月)。「データベース正規化設計パターン」。2017年 4 回目の IEEE ウッタルプラデーシュ支部国際電気・コンピューター・電子工学会議 (UPCON)。IEEE。pp. 318–322。doi : 10.1109 / upcon.2017.8251067。ISBN 9781538630044. S2CID 24491594。

[:0-10] 「 MySQLでのデータベース正規化: 4 つの簡単な手順」。ComputerWeekly.com。2017年 8 月 30 日時点のオリジナルよりアーカイブ。2021年3 月 23 日閲覧。

[11] 「データベースの正規化: 第 5 正規形とそれ以降」。MariaDBナレッジベース。2019 年1 月 23 日閲覧。

[:2-12] Date, CJ (2015 年 12 月 21 日)。新しいリレーショナルデータベース辞書: 用語、概念、および例。「O'Reilly Media, Inc.」。p. 138。ISBN 9781491951699。

[13] Date, CJ (2015 年 12 月 21 日)。新しいリレーショナルデータベース辞書: 用語、概念、および例。「O'Reilly Media, Inc.」。p. 163。ISBN 9781491951699。

[14] 「正規化 - 例を使って6NFを理解したい」。Stack Overflow 。 2019年1月23日閲覧。

[15] Microsoft Corporation。列ストアインデックス: 概要。https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview。2020 年 3 月 23 日にアクセス。