データベースの正規化と3つの段階について
カテゴリ:データベース編
正規化とは
正規化とは値の重複など、冗長な部分を取り除き、データベースのテーブルやカラムをより合理的に分割することを指します。
テーブルを分けない(正規化しない)事によるデメリットはある項目の値が変更されたらその値を持つ全てのレコードを更新しなければならなくなる事です。レコードが何万行もあればその作業は大きな負荷になります。
正規化は3つの段階に分けられ、それぞれ第1正規化、第2正規化、第3正規化と呼びます。
第1正規化
第1正規化は繰り返される属性を排除することを指します。 ここでいう繰り返される属性は、以下のように1つのレコード内で特定のカラムが繰り返されている状態です。
社員番号 | 氏名 | 資格コード | 資格名 | 取得日 |
---|---|---|---|---|
1 | 鈴木太郎 | 001 | IPパスポート | 2018-05-10 |
002 | 基本情報 | 2018-07-28 | ||
003 | 応用情報 | 2019-03-04 | ||
2 | 田中正志 | 003 | 応用情報 | 2019-08-16 |
3 | 木村三郎 | 001 | IPパスポート | 2020-03-30 |
そもそもこの第1正規化がなされていない状態ではデータベース化する事はできません。 これは以下のように、資格コード、資格名とにレコードを分ける必要があります。
第1正規化後
社員番号 | 氏名 | 資格コード | 資格名 | 取得日 |
---|---|---|---|---|
1 | 鈴木太郎 | 001 | IPパスポート | 2018-05-10 |
1 | 鈴木太郎 | 002 | 基本情報 | 2018-07-28 |
1 | 鈴木太郎 | 003 | 応用情報 | 2019-03-04 |
2 | 田中正志 | 003 | 応用情報 | 2019-08-16 |
3 | 木村三郎 | 001 | IPパスポート | 2020-03-30 |
この状態であればデータベース化する事が可能です。
第2正規化
第2正規化は主キーによって決まる項目を別テーブルに分割する事を指します。
Note主キーが複数ある場合は一部の主キーによって決まる項目(キー以外の属性:非キー属性)が存在する場合を指し、これを部分関数従属と呼びます。
そして主キーが決まれば値が決まる主キー以外の項目が存在しない状態を第2正規形と呼びます。 具体的にはある主キー項目の値が決まれば、それに従属して値が決まる項目を分けるようにします。
先程の例では、登録番号を主キー(PK)として追加した場合、登録番号が決まれば、取得日も決まります。 そのため、登録番号をキーにして取得日を別テーブルに分割できます。
登録番号(PK) | 社員番号 | 氏名 | 資格コード | 資格名 | 取得日 |
---|---|---|---|---|---|
1 | 1 | 鈴木太郎 | 001 | IPパスポート | 2018-05-10 |
2 | 1 | 鈴木太郎 | 002 | 基本情報 | 2018-07-28 |
3 | 1 | 鈴木太郎 | 003 | 応用情報 | 2019-03-04 |
4 | 2 | 田中正志 | 003 | 応用情報 | 2019-08-16 |
5 | 3 | 木村三郎 | 001 | IPパスポート | 2020-03-30 |
第2正規化後
分割後、以下のようになります。
登録番号(PK) | 社員番号 | 氏名 | 資格コード | 資格名 |
---|---|---|---|---|
1 | 1 | 鈴木太郎 | 001 | IPパスポート |
2 | 1 | 鈴木太郎 | 002 | 基本情報 |
3 | 1 | 鈴木太郎 | 003 | 応用情報 |
4 | 2 | 田中正志 | 003 | 応用情報 |
5 | 3 | 木村三郎 | 001 | IPパスポート |
登録番号(PK) | 取得日 |
---|---|
1 | 2018-05-10 |
2 | 2018-07-28 |
3 | 2019-03-04 |
4 | 2019-08-16 |
5 | 2020-03-30 |
第3正規化
第3正規化は主キー以外の項目によって決まる項目も別テーブルに分割する事を指します。
Note非キー項目同士で、値が決まれば一方の値も決まる事を推移的関数従属と呼びます。
そして非キー項目同士で、値が決まれば一方の値も決まる項目が存在しない状態を第3正規形と呼びます。
今回の例では、主キーではない社員番号が決まれば氏名も決まります。また資格コードが決まれば資格名も決まります。従って社員番号をキーにして氏名、同様に資格コードをキーにして資格名も別テーブルに分割できます。
登録番号(PK) | 社員番号 | 氏名 | 資格コード | 資格名 |
---|---|---|---|---|
1 | 1 | 鈴木太郎 | 001 | IPパスポート |
2 | 1 | 鈴木太郎 | 002 | 基本情報 |
3 | 1 | 鈴木太郎 | 003 | 応用情報 |
4 | 2 | 田中正志 | 003 | 応用情報 |
5 | 3 | 木村三郎 | 001 | IPパスポート |
第3正規化後
分割後、以下のような4つのテーブルに分割された状態が第3正規化後の最終形態になります。
登録番号(PK) | 社員番号 | 資格コード |
---|---|---|
1 | 1 | 001 |
2 | 1 | 002 |
3 | 1 | 003 |
4 | 2 | 003 |
5 | 3 | 001 |
登録番号(PK) | 取得日 |
---|---|
1 | 2018-05-10 |
2 | 2018-07-28 |
3 | 2019-03-04 |
4 | 2019-08-16 |
5 | 2020-03-30 |
社員番号(PK) | 氏名 |
---|---|
1 | 鈴木太郎 |
2 | 田中正志 |
3 | 木村三郎 |
資格コード(PK) | 資格名 |
---|---|
001 | IPパスポート |
002 | 基本情報 |
003 | 応用情報 |
これら4つのテーブルでは、それぞれ登録番号、登録番号、社員番号、資格コードが主キーとなり、主キー以外で決定される項目はもう無くなりましたね。
まとめ
データベース正規化は一言で言えばある項目が決まれば従属して決まる項目を、とことん別テーブルに分けるという事です。この従属して決まる項目がある状態を推移的関数従属といいます。そして推移的関数従属が無い状態が完全に正規化された状態である第3正規形となります。
また、テーブルを分けるという事は、クエリを実施する際に分けたテーブルに跨ってクエリを実施する必要が出てきます。そのためには副問合せが必須になってきます。
以上、データベースの正規化と3つの段階について解説いたしました。
公開日時:2020年05月03日 18:27:06