Works by

Works by

プログラミング等IT技術関連でメモする

関係データベースにおける正規形・正規化操作

関係データベースにおける「正規化」とは

正規化とは、関係データベース(RDB)におけるテーブルを、より利用しやすい形に整形する操作を指す。

関係データベースとテーブル

関係データベース(RDB)ではデータの集合を、個々のデータを表す「行(レコード)」と、各データの持つ属性を表す「列(カラム)」からなるテーブルで表現する。

列1 列2 列3 列4

データ集合をテーブルで表現する場合、さまざまなパターンが考えられる。そのどのパターンでもデータの保持は可能で、更新や参照もできることにはできる。

ただし、データの持ち方を少し誤るだけで、データの参照・更新操作が煩雑になるばかりか、保守性・再利用性などの面で後々苦労することになる。

たとえば、卸売業者HOGEの販売管理システム上で以下のような伝票テーブルが管理されているとする。 このシステムでは、一つのお得意先(顧客)が一つの商品を注文を確定させるたびに、一つの伝票データが発行される。

伝票テーブル

顧客ID 顧客名 顧客郵便番号 顧客住所 商品ID 商品名 価格 個数
001 foo
商店
000-0000 東京都foo区 1 HOGEチョコレート 200 10
001 foo
商店
000-0000 東京都foo区 2 HOGEキャラメル 50 20
002 bar
商店
222-2222 東京都bar区 1 HOGEチョコレート 200 30

卸売業者HOGEの目玉商品であるHOGEチョコレートは人気で、多くのお得意先から仕入れの依頼が来る。 そのため、伝票の商品項目として同じチョコレートがいくつも記録されている。
当然であるが、HOGEチョコレートには一つ価格が割り当てられ、商品を識別するために一意のIDも割り当てられている。

では、このチョコレートの名称を「 Willy WXnka 」に変更したいとしよう。

このとき、DBの管理者は「値が"HOGEチョコレート" である全ての商品名」を変更しなければならない。

顧客ID 顧客名 顧客郵便番号 顧客住所 商品ID 商品名 価格 個数
001 foo
商店
000-0000 東京都foo区 1 HOGEチョコレート 200 10
001 foo
商店
000-0000 東京都foo区 2 HOGEキャラメル 50 20
002 bar
商店
222-2222 東京都bar区 1 HOGEチョコレート 200 30

SQLなら 、

UPDATE 伝票テーブル 
SET 商品名 = 'Willy WXnka'
WHERE 商品名 = 'HOGEチョコレート';

を実行する必要がある。

このSQLで、少なくとも2件のレコードに更新をかけなければならない。今は伝票レコードが3件のみだが、今後もっと増えるかもしれない。そうなれば、さらに変更に手間がかかる。

また、この名称変更が行われるたびにこのような更新をしなければならない。しかも商品名だけではなく、商品IDや商品の価格にも同じことが言える。DB管理者の気苦労は耐えない。

このような事態を回避するのに、正規化は使われる。

第一正規形

第一正規形では、一行の中に繰り返し項目が存在するものを取り除く。

仮に以下のようなテーブルが定義されていた場合、第一正規形にすると、

顧客ID 顧客名 顧客郵便番号 顧客住所 商品ID 商品名 価格 個数
001 foo商店 000-0000 東京都foo区 1 HOGEチョコレート 200 10
2 HOGEキャラメル 50 20
002 bar商店 222-2222 東京都bar区 1 HOGEチョコレート 200 30

顧客ID 顧客名 顧客郵便番号 顧客住所 商品ID 商品名 価格 個数
001 foo
商店
000-0000 東京都foo区 1 HOGEチョコレート 200 10
001 foo
商店
000-0000 東京都foo区 2 HOGEキャラメル 50 20
002 bar
商店
222-2222 東京都bar区 1 HOGEチョコレート 200 30

のように、繰り返し表現をしていた顧客ID001 のレコードが分割されて管理される。

ちなみに、元の伝票テーブルは第一正規形に正規化済みである。

第二正規形

第二正規形では、関数従属という概念が登場する。

関数従属とは

カラムAとBが存在し、Aの値が決まるとおのずとBが決定するような関係があるとき、「BはAに関数従属する」という。

そして第二正規形では、主キーの一部だけの要素で一意に定まる(部分関数従属と呼ぶ)ような非キー属性がなくなるようにテーブルを分割する。
キー属性とは、その属性が決まることでレコードが一意に定まるような属性のこと。
複数存在しても良いし、ペアでキー属性となることも許される。

伝票テーブルのカラムを見てみる。

f:id:rennnosukesann:20180211151557p:plain

商品名の値は顧客ID・商品IDからなる主キーの部分属性である商品IDから一意に定まる。

これでは第二正規形の条件を満たしていない。

なので、第二正規形と成るようにテーブルを分割してみる。

伝票テーブル

顧客ID 商品ID 個数
001 1 10
001 2 20
002 1 30

商品テーブル

商品ID 商品名 価格
1 HOGEチョコレート 200
2 HOGEキャラメル 50

顧客テーブル

顧客ID 顧客名 顧客郵便番号 顧客住所
001 foo商店 000-0000 東京都foo区
002 bar商店 222-2222 東京都bar区

この状態であれば、各テーブルの各非キー属性はただ一つの主キーに関数従属する。

この時の関数従属を完全関数従属と呼ぶ

第三正規形

第三正規形では、推移的関数従属を取り除く。

推移的関数従属とは

カラムX,Y,Zが存在し、Xの値が決まるとYの値が一意に定まり、かつYの値が決まるとZの値が一意に定まる時、ZはXに推移的関数従属するという。

第二正規形時の顧客テーブルを再度チェックしてみる。

顧客テーブル

顧客ID 顧客名 顧客郵便番号 顧客住所
001 foo商店 000-0000 東京都foo区
002 bar商店 222-2222 東京都bar区

第二正規形なので、主キーである顧客IDによってすべてのカラムの値が一意に定まる。
同時に、顧客住所によって顧客郵便番号が一意に定まる。
このことから、顧客郵便番号は顧客住所を経由して、顧客IDに推移的に関数従属しているといえる。

既存の顧客テーブルを第三正規形に合わせて整形すると、
テーブルはさらに以下のように分けられる。

顧客テーブル

顧客ID 顧客名 住所
001 foo商店 東京都foo区
002 bar商店 東京都bar区

住所テーブル

住所 郵便番号
東京都foo区 000-0000
東京都bar区 222-2222

実業務上での正規化

正規化はテーブルの利便性向上のために行うため、システムに求められる要件次第では
あえて正規化しない場合もある。

たとえば上記の第三正規形の例であれば、住所情報へのアクセスのしやすさ・管理のしやすさから
同じ顧客テーブルに住所情報を保持する場合もある(そのほうが多いかもしれない)。

また、上記の例では住所と郵便番号からなるテーブルを作成しているが、
仮に文字列である住所を主キーとした場合、一意性担保の点で設計上疑問が残るので、
住所IDかなにか別の主キーを設けると思われる(もしそうした場合、第三正規形ではなくなる)。

参考文献

関数従属性 - Wikipedia

第4回 データベースの正規化|OSS-DB入門|OSS-DB道場|受験対策|DBスペシャリストを認定する資格 OSS-DB技術者認定試験