第二、第三の正規化＆ER図&Check制約

投稿日：2016年7月22日更新日：2020年5月14日

前回第一正規化を話したので、第二、第三に進んでいきます。

Contents

1 第二正規化とは？
2 第三正規化とは?
3 正規化のメリット
4 ER図
5 本日のSQLネタ Check制約

第二正規化とは？

主キーに対してすべての列が関数従属していること。この状態を完全関数従属という。
正規化されていないテーブルとは例えばマスタの値そのものが入っているようなケース。
正規化のメリットとしてはデータの不整合が起こらないこと
概念としては現実世界の実体間にある階層の差を反映する手段であること
戻すときには結合(JOIN)を用いること。戻せる性質を可逆性という。逆に言えば戻せないときには正規化が失敗している。

第三正規化とは?

主キー以外のマスタなどの値にたいしても従属性を持たせること。
このようにテーブル内部に存在する段階的な従属関係を、推移的関数従属という。
一般的な正規化はこの第三正規化までをすることが多い。

正規化のメリット

更新時の不都合・不整合を排除すること
正規化は従属性(キーと値の1対1対応)を見抜くことで可能になる。
正規形は常に非正規形に戻せる。この性質を無損失分解という。

ER図

実際の業務ではかなり小規模なアプリでもテーブルが2桁になることはめずらしくありません。中規模なもので数十、ちょっと大きなものになると数百となるでしょう。このようにテーブルが増えたときにテーブル同士の関連をみるときに、ER図があると視覚的にテーブル同士の関連をすぐに把握することができます。

正規化に関しては一件当たり前のことをいっていますが、関数従属しているかどうかは複雑なテーブルになってくるとあいまいになることは珍しくありません。

特にキーにidを使うのではなく、コードなどを入れる場合、ユーザーが自由にいれるので複雑になってきます。ここら辺は関数従属性を1つ1つ確かめながらテーブル分割をやっていきたいところですね・・・

本日のSQLネタ Check制約

check制約について。

自分はあまり使ったことがなかったんですが、テーブルのレコードに制約を付けたいときに使います。

SQLパズル第一問より

ある年度の会計開始日と終了日のテーブルを作るさい、制約を付けます。

例えばn年度の場合その年の10/1～次年度の9/30になります。そのためのテーブルの定義にcheckを使います。

create table fiscal_year_table(
   fiscal_year integer not null primary key,
   start_date date not null,
   constraint valid_start_date 
   check (
            ( EXTRACT(YEAR FROM start_date) = fiscal_year- 1 )
        AND ( EXTRACT(MONTH FROM start_date) = 10 )
		AND ( EXTRACT(DAY FROM start_date) = 01 )
   ),
   end_date date not null, 	
   constraint valid_start_date 
   check (
            ( EXTRACT(YEAR FROM end_date) = fiscal_year- 1 )
        AND ( EXTRACT(MONTH FROM end_date) = 09 )
		AND ( EXTRACT(DAY FROM end_date) = 30 )
   )
);

create table fiscal_year_table(

fiscal_year integer not null primary key,

start_date date not null,

constraint valid_start_date

check (

( EXTRACT(YEAR FROM start_date) = fiscal_year- 1 )

AND ( EXTRACT(MONTH FROM start_date) = 10 )

AND ( EXTRACT(DAY FROM start_date) = 01 )

end_date date not null,

constraint valid_start_date

check (

( EXTRACT(YEAR FROM end_date) = fiscal_year- 1 )

AND ( EXTRACT(MONTH FROM end_date) = 09 )

AND ( EXTRACT(DAY FROM end_date) = 30 )

)

);

constraint チェック名　check( チェック内容 ) です。

ただしMySQLではサポートされていません・・・