OSS-DB入門
第4回 データベースの正規化
今回はデータベースの正規化について取り上げます。
PostgreSQL固有ではなく、RDBMS全般に関わる一般的なテーマですが、データベースを扱う上で重要な下地となる部分ですので、ぜひ基礎固めとしてご利用ください。「正規化」はOSS-DB Exam Silverの出題範囲にも含まれていますので、受験を考えている方も気を抜かずに取り組みましょう。
正規化とは
データの重複をなくし整合的にデータを取り扱えるようにデータベースを設計することを、データベースの正規化と呼びます。正規化を行っておくと、データの追加・更新・削除などに伴うデータの不整合や喪失が起きるのを防ぎ、メンテナンスの効率を高めることができます。
正規化の段階には、第1〜第5正規形およびボイスコッド正規形がありますが、ここでは、データベースを設計する際に一般的に用いられる第1〜第3正規形までを、前回登場したテーブルをもとにして説明していきます。
非正規形
正規化がまったく行われておらず、1行の中に複数の繰り返し項目が存在するようなテーブルは非正規形と呼びます。
前回は、ある卸売業者の商品管理表だけをみてきましたが、この商品管理表は入荷業務を進める際には仕入先に紐づけて処理されています（仕入先ごとにバインダーで綴じて管理しているようなイメージです）。そこで、仕入先も考慮に入れた表を描いてみましょう。なお、入荷処理を見ていくわけですから、在庫数だけではなく入荷数のカラムも追加しておきます。
※本連載で例示するテーブルは、卸売業務を想定したデータ群のうち、記事のテーマに添った箇所を部分的に抜粋し簡略化したものです。例えば出荷処理のことを考慮から外していたり、仕入先や代表者のIDを設定していない（名称のみで一意に識別可能とし、同名はないものと仮定している）など、業務データとしては不十分な点もあることをご了承ください。
商品管理表
| 仕入先 | 住所 | 代表者名 | 代表者連絡先 | 商品名 | １個当たりの金額 | 在庫数 | 入荷日 | 入荷数 | 備考 |
|---|---|---|---|---|---|---|---|---|---|
| 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 10 | 2012-07-01 | 10 | 青森産 |
| ぶどう | 300 | 10 | 2012-07-01 | 10 | |||||
| りんご | 150 | 30 | 2012-07-03 | 20 | 青森産 | ||||
| みかん園 | 愛媛県松山市 | 松山花子 | 222-2222 | みかん | 80 | 30 | 2012-07-02 | 30 |
この表の場合、ひとつの仕入先に対して、複数の商品が繰り返して存在しているため、非正規形に該当します。
第1正規形
リレーショナルデータベースでは原則としてレコード単位で個々のデータを扱いますので、非正規形のデータはデータベースに格納することすらできません。まずは、繰り返し項目のそれぞれを別レコードとして独立させます。
また、在庫数は入荷数の合計から導出することができます。このように他のカラムから導出可能な項目は、この段階で削除しておきます。
※普通、在庫数は (入荷数の合計 - 出荷数の合計) から求めるものですが、上述のように、ここでは簡易的に入荷業務のみを考えています。
この結果、表は以下のようになります。
商品管理表
| 仕入先 | 住所 | 代表者名 | 代表者連絡先 | 商品名 | １個当たりの金額 | 入荷日 | 入荷数 | 備考 |
|---|---|---|---|---|---|---|---|---|
| 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 2012-07-01 | 10 | 青森産 |
| 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | ぶどう | 300 | 2012-07-01 | 10 | |
| 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 2012-07-03 | 20 | 青森産 |
| みかん園 | 愛媛県松山市 | 松山花子 | 222-2222 | みかん | 80 | 2012-07-02 | 30 |
繰り返し項目を持たず、導出項目の排除されたこの表は第1正規形に該当します。
第2正規形
第1正規形にすることでデータベースに格納できるようにはなりましたが、データ管理の観点からはまだまだ不十分です。例えば、新たな仕入先と契約したとしても、実際に何らかの商品を入荷するまでは、その情報を管理することができません。また、仕入れ先の代表者名が変更になると、複数のレコードを更新しなければならないため不整合を生じる恐れがあります。これは、仕入先情報、商品情報、入荷履歴といった独立した情報をすべて同一のレコードで扱っていることが理由です。これらを別々のテーブルに分割することを考えましょう。
第1正規形のテーブルを見てみると、レコードを一意に定める要素は{仕入先, 商品名, 入荷日}（これを主キーと呼びます）であることが分かります。主キー以外の項目（非キー属性）について、主キーの一部の要素だけで決まる（部分関数従属と言います）ものがあれば別テーブルに分離させましょう。各項目の従属関係は次の図を参照してください。
図1. 各項目の従属関係 ※下線は主キーであることを示します
テーブルを分割させた結果は次のようになります。
商品テーブル
| 商品名 | １個当たりの金額 | 備考 |
| りんご | 150 | 青森産 |
| ぶどう | 300 | |
| みかん | 80 |
仕入先テーブル
| 仕入先 | 住所 | 代表者名 | 代表者連絡先 |
|---|---|---|---|
| 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 |
| みかん園 | 愛媛県松山市 | 松山花子 | 222-2222 |
入荷テーブル
| 商品名 | 仕入先 | 入荷日 | 入荷数 |
|---|---|---|---|
| りんご | 津軽ファーム | 2012-07-01 | 10 |
| ぶどう | 津軽ファーム | 2012-07-01 | 10 |
| みかん | みかん園 | 2012-07-02 | 30 |
| りんご | 津軽ファーム | 2012-07-03 | 20 |
いずれのテーブルにおいても非キー属性は主キーに従属する形になりました。この状態を完全関数従属と呼び、こちらを満たしている形式が第2正規形となります。
第3正規形
テーブルを分割することで、データの冗長性もだいぶ取り除かれました。しかし、まだ十分とは言えません。津軽ファームの代表者である陸奥太郎さんが他の農園の代表も兼任しているとしたらどうなるでしょう。陸奥太郎さんの連絡先が変更された場合には、やはり複数レコードを更新する必要が出てきてしまいます。
主キー以外の項目について項目同士で依存関係を持っているもの（推移的関数従属と言います）も、別テーブルに切り分けましょう。
商品テーブル
| 商品名 | １個当たりの金額 | 備考 |
|---|---|---|
| りんご | 150 | 青森産 |
| ぶどう | 300 | |
| みかん | 80 |
仕入先テーブル
| 仕入先 | 住所 | 代表者名 |
| 津軽ファーム | 青森県弘前市 | 陸奥太郎 |
| みかん園 | 愛媛県松山市 | 松山花子 |
電話帳
| 名前 | 連絡先 |
|---|---|
| 陸奥太郎 | 111-1111 |
| 松山花子 | 222-2222 |
入荷テーブル
| 商品名 | 仕入先 | 入荷日 | 入荷数 |
|---|---|---|---|
| りんご | 津軽ファーム | 2012-07-01 | 10 |
| ぶどう | 津軽ファーム | 2012-07-01 | 10 |
| みかん | みかん園 | 2012-07-02 | 30 |
| りんご | 津軽ファーム | 2012-07-03 | 20 |
このように、非キー属性がすべて主キーに完全関数従属し、なおかつ推移的関数従属性の排除された形式を第3正規形と呼びます。
まとめ
正規化の作業を通じて、データの冗長性が軽減され、整合的に管理できるようになっていく過程がつかめたでしょうか。次回は実際にSQLを用いてデータの挿入・更新・削除といったことを行いますので、正規化のメリットをより強く実感できるものと思います。
それぞれの正規形の条件をあらためて以下にまとめます。
第1正規形
- 繰り返し項目を持たない
- 導出項目を持たない
第2正規形
- 第1正規形を満たしている
- 主キーに対してすべての非キー属性が完全関数従属
第3正規形
- 第2正規形を満たしている
- すべての非キー属性がどの候補キーに対しても推移的に関数従属していない
本章では、非正規形から第3正規形まで段階的に正規化を進めてきましたが、実際の業務では一足飛びにいきなり第3正規形で設計をすることもあると思います。また逆に、検索効率を考えて、あえて正規化の程度を落とすこともあるでしょう。手順に捉われ過ぎることなく、アプリケーションの利用シーンやパフォーマンス要件などに応じて柔軟にデータベースを設計できるようになることを目指しましょう。
データの正規化については、データベースに関する書籍やネット上の記事などで数多く取り扱われています。ぜひご自身でも調べてみて、理解を深めていってください。
補足
今回正規化したテーブルをデータベースに登録するためのSQLは以下のようになります。
前回作成したproductsテーブルは、正規化の過程で若干変更されましたので、DROP TABLEでテーブルを一旦削除して、再度CREATE TABLEを実行しています。（テーブルに既にデータが入力されている場合や、他のデータベースオブジェクトから参照されているようなケースでは、ALTER TABLEを使って既存テーブルにそのまま変更を加えることもできます。）
また、それぞれのテーブルの主キーに対しては、PRIMARY KEYを指定することで主キー制約を付与しています。これにより、当該カラムの値から一意にレコードを定められることが保証されます。NOT NULLとUNIQUE制約を組み合わせることでも同等の効果は得られます。
DROP TABLE products;
CREATE TABLE products(
name varchar(255) PRIMARY KEY,
price integer CHECK (price > 0),
etc text
);
CREATE TABLE supplier(
farm_name varchar(255) PRIMARY KEY,
address varchar(255),
rep_name varchar(255)
);
CREATE TABLE telbook(
rep_name varchar(255) PRIMARY KEY,
tel varchar(20)
);
CREATE TABLE orders(
name varchar(255),
farm_name varchar(255),
arrival_date date,
order_num integer,
PRIMARY KEY(name, farm_name, arrival_date)
);