正規化 は、表データの冗長度と矛盾を除去するのに役立ちます。 これは、 キー列以外の列が基本キー列に依存したものになるような列の集合に表を整理するプロセスです。 そうでないなら、データ更新時に矛盾が生じることがあります。
このセクションでは、第 1、第 2、第 3、第 4 正規形の規則について簡単に説明します。 第 5 正規形の表については、データベース設計に関する多くの本の中で取り上げられており、 ここでは説明しません。
各セルに値が 1 つしかない (値のセットでない) 場合、 その表は第 1 正規形 です。 第 1 正規形の表は、それより上位の正規形の基準を満たしているとは限りません。
たとえば、次の表の場合、
「WAREHOUSE」列には「PART」の各オカレンスごとに複数の値が入っているため、
第 1 正規形に違反しています。
PART (基本キー) | WAREHOUSE |
---|---|
P0010 | Warehouse A, Warehouse B, Warehouse C |
P0020 | Warehouse B, Warehouse D |
次の例は、第 1 正規形と同じ表です。
PART (基本キー) | WAREHOUSE (基本キー) | QUANTITY |
---|---|---|
P0010 | Warehouse A | 400 |
P0010 | Warehouse B | 543 |
P0010 | Warehouse C | 329 |
P0020 | Warehouse B | 200 |
P0020 | Warehouse D | 278 |
キーでない各列に入っている情報がキー全体 に 依存している場合、 その表は第 2 正規形 です。
キー列以外の列が複合キーの一部 に従属している
場合、
第 2 正規形に違反することになります。次の例をご覧ください。
PART(基本キー) | WAREHOUSE (基本キー) | QUANTITY | WAREHOUSE_ADDRESS |
---|---|---|---|
P0010 | Warehouse A | 400 | 1608 New Field Road |
P0010 | Warehouse B | 543 | 4141 Greenway Drive |
P0010 | Warehouse C | 329 | 171 Pine Lane |
P0020 | Warehouse B | 200 | 4141 Greenway Drive |
P0020 | Warehouse D | 278 | 800 Massey Street |
基本キーは複合キーであり、「PART」(部品) 列と「WAREHOUSE」(倉庫) 列によって構成されています。 「WAREHOUSE_ADDRESS」列 (倉庫の住所) は「WAREHOUSE」の値だけに依存するものであるため、 この表は第 2 正規形の規則に違反しています。
次の点が、この設計の問題点となっています。
解決策は、表を次の 2 つの表に分割することです。
PART (基本キー) | WAREHOUSE (基本キー) | QUANTITY |
---|---|---|
P0010 | Warehouse A | 400 |
P0010 | Warehouse B | 543 |
P0010 | Warehouse C | 329 |
P0020 | Warehouse B | 200 |
P0020 | Warehouse D | 278 |
WAREHOUSE (基本キー) | WAREHOUSE_ADDRESS |
---|---|
Warehouse A | 1608 New Field Road |
Warehouse B | 4141 Greenway Drive |
Warehouse C | 171 Pine Lane |
Warehouse D | 800 Massey Street |
第 2 正規形の表が 2 つになると、パフォーマンスの面で考慮すべき点が出てきます。 部品の保管場所のレポートを作成するアプリケーションでは、 2 つの表を結合させて関係する情報を検索することが必要になります。
パフォーマンスの面での考慮事項については、 管理の手引き: パフォーマンス の『アプリケーション・パフォーマンスのチューニング』を参照してください。
キー列でない列に入っている情報が、 キー列でない他の列とは無関係でキー列にしか依存しないものであるなら、 その表は第 3 正規形です。
次の例の最初の表には、「EMPNO」列と「WORKDEPT」列が含まれています。
ここに、「DEPTNAME」列を追加するとしましょう (表 14を参照)。
新しい列は WORKDEPT に依存しますが、
基本キーは EMPNO です。
この表は第 3 正規形に違反します。
John Parker という従業員の「DEPTNAME」を変えても、
その部署の他の従業員の部署名は変わりません。
部署番号 E11 に 2 つの部署名が使われることになっています。
更新後の表に結果として矛盾が生じます。
表 13. 更新前の正規化されていない EMPLOYEE_DEPARTMENT 表
EMPNO(基本キー) | FIRSTNAME | LASTNAME | WORKDEPT | DEPTNAME |
---|---|---|---|---|
000290 | John | Parker | E11 | Operations |
000320 | Ramlal | Mehta | E21 | Software Support |
000310 | Maude | Setright | E11 | Operations |
表 14. 更新後の正規化されていない EMPLOYEE_DEPARTMENT 表
表の情報に矛盾が生じています。 | ||||
EMPNO(基本キー) | FIRSTNAME | LASTNAME | WORKDEPT | DEPTNAME |
---|---|---|---|---|
000290 | John | Parker | E11 | Installation Mgmt |
000320 | Ramlal | Mehta | E21 | Software Support |
000310 | Maude | Setright | E11 | Operations |
「WORKDEPT」と「DEPTNAME」を列とする表を新たに作成すれば、表を正規化できます。 部署名の変更などの更新はさらに容易です。 新しい表のみを更新する必要があります。
部署名と従業員名の両方を戻す SQL 照会は、 2 つの表を結合させることが必要になるため、少し複雑になります。 またこの照会は、1 つの表の照会の場合よりも実行時間が長くなります。 さらに、両方の表に「WORKDEPT」列が含まれることになるため、 付加的な記憶スペースが必要になります。
次の表は、正規化した結果として定義されるものです。
EMPNO (基本キー) | FIRSTNAME | LASTNAME | WORKDEPT |
---|---|---|---|
000290 | John | Parker | E11 |
000320 | Ramlal | Mehta | E21 |
000310 | Maude | Setright | E11 |
DEPTNO (基本キー) | DEPTNAME |
---|---|
E11 | Operations |
E21 | Software Support |
どの行にも 1 つのエンティティーに関して複数の独立した複数値情報が含まれていない場合、 その表は第 4 正規形です。
従業員、技術、言語の 3 種類のエンティティーについて考えてみましょう。
1 人の従業員が複数の技術を持っていたり、複数の言語を知っていたりするかもしれません。
この場合には、従業員と技術、従業員と言語という 2 通りの関係があります。
1 つの表でその両方の関係を表すとすれば、その表は第 4 正規形ではありません。
次の例をご覧ください。
EMPNO (基本キー) | SKILL (基本キー) | LANGUAGE (基本キー) |
---|---|---|
000130 | Data Modelling | English |
000130 | Database Design | English |
000130 | Application Design | English |
000130 | Data Modelling | Spanish |
000130 | Database Design | Spanish |
000130 | Application Design | Spanish |
むしろ、これらの関係は 2 つの表で表すべきです。
表 18. 第 4 正規形に適合している EMPLOYEE_SKILL 表
EMPNO (基本キー) | SKILL (基本キー) |
---|---|
000130 | Data Modelling |
000130 | Database Design |
000130 | Application Design |
表 19. 第 4 正規形に適合している EMPLOYEE_LANGUAGE 表
EMPNO (基本キー) | LANGUAGE (基本キー) |
---|---|
000130 | English |
000130 | Spanish |
しかし、属性が相互依存の関係にある場合 (つまり従業員がある言語を特定の技術だけに使用するような場合)、 表を分割すべきではありません。
データベースを設計する際は、まずすべてのデータを第 4 正規形の表にまとめ、 それからパフォーマンスが許容できるレベルかどうかを判断するのが得策です。 パフォーマンスが許容できない場合は、 表のデータを第 3 正規形に配列してパフォーマンスを再評価します。