0.学習のきっかけ
正規化は、基本や応用情報技術者でなんとなく流してしまった部分でした。そのような状況の中で、業務でDB設計の部分を見直す機会がありましたが、あまりにもDBについて理解が足りず悔しかったので、学習に取り組みました。
学習の中で押さえておくべきと思った部分について、自分への問いかけとして残しています。
学習に使用した書籍
(表紙がpopでかわいい)
1.テーブルってそもそもなによ？
一言でいえば
「複数名詞をあらわす整理方法」
さらにいうと
「一意な識別子(主キー)をもった共通項によってまとめられたモノの集合」
2.正規化の導入
ではあるのだけれどこの条件を満たせばよい、ということだけでやたらめったら意味不明なテーブルができあがってしまう。
そこで、テーブルはこのように定義するとわかりやすいよ、というテーブル設計のセオリーが組み立てられた。
そのセオリーこそ「正規化」という方法。ちなみに第三正規化までできれば実務では問題なし。
3.だめだめテーブルから第一正規系へ
第一正規系とは「テーブルのセルに複合的な値を含んでいない状態」のこと
Q.複合的な値、というのは以下のテーブルのどのセル？
①「社員」テーブル
| 社員ID | 名前 | 年齢 | 性別 | 被扶養者 |
|---|---|---|---|---|
| S001 | たけし | 24 | 男 | けんじ |
| S002 | ジョン | 23 | 男 | ようこ |
| S003 | エミリー | 32 | 女 | さとし |
A.「被扶養者」列の3行目
このようなセルの作り方はRDBではだめ！
なのでこれを一つのセルには一つの値しかいれないようにすると分割する必要が出てくる
②「社員」テーブル
| 社員ID | 名前 | 年齢 | 性別 |
|---|---|---|---|
| S001 | たけし | 24 | 男 |
| S002 | ジョン | 23 | 女 |
| S003 | エミリー | 32 | 男 |
③「扶養者」テーブル(「社員」テーブルと社員IDで関連付けられている。ちなみにこのテーブルの主キーどれか？)
| 社員ID | 扶養者番号 | 被扶養者名 |
|---|---|---|
| S001 | 1 | けんじ |
| S002 | 1 | ようこ |
| S003 | 1 | きょうこ |
| S003 | 2 | さとし |
ここで抱いた疑問
いや、きれいになったかもしれないけどテーブル2個管理するのだるくね？と思いました。
ただ、テーブルには「レコードの一意性を保証する」必要があります。もう少しイメージしやすい形でいうと
主キーを特定すれば、あるレコードの列の値が一意に特定される必要があるのです。
(テーブルは関数である、という表現がよくされるらしい。入力値を一つ与えれば一つの出力値が決定される、ということ。)
Q.①のテーブルは主キーを特定しても一意に特定できない場所がある？それはどこ？
また、そうなるとなにが困る？
4.第一正規名から第二正規系へ
第一正規系を満たす以下のテーブルについて考える。
④「注文」テーブル(主キーはどれ？組み合わせの可能性も考慮)
| 顧客ID | 注文連番 | 注文日 | 顧客企業名 | 顧客規模 |
|---|---|---|---|---|
| CA | O001 | 2024/12/20 | A商社 | 大規模 |
| CA | O002 | 2024/12/21 | A商社 | 大規模 |
| CB | O001 | 2024/12/12 | B建設 | 中規模 |
| CB | O002 | 2024/12/25 | B建設 | 中規模 |
| CB | O003 | 2024/12/25 | B建設 | 中規模 |
| CC | O001 | 2024/12/1 | C化学 | 小規模 |
このテーブルを第二正規系にするには「部分関数従属」を除かなければいけない。
「部分関数従属」とは「主キーを構成する列の一部にだけ関数従属する列が存在すること」
どういうことかというと主キーの一つである顧客IDがわかれば顧客企業名と顧客規模は一意に絞れてしまって、この2列にとっては注文連番は邪魔な列でしかない。注文連番だけがわかってもどんな企業からの注文かはわからない。
→本当に「注文」の集合になっているかを考える。実際には④は「顧客企業」についての集合も混ざっていた
このような部分関数従属がある場合、そのキーと従属する列だけを別テーブルに出す必要がある。
⑤「顧客企業」テーブル
| 顧客ID | 顧客企業名 | 顧客企業規模 |
|---|---|---|
| CA | A商社 | 大規模 |
| CB | Ｂ建設 | 中規模 |
| CC | Ｃ化学 | 小規模 |
⑥「注文」テーブル
| 顧客ID | 注文連番 | 注文日 |
|---|---|---|
| CA | O001 | 2024/12/20 |
| CA | O002 | 2024/12/21 |
| CB | O001 | 2024/12/12 |
| CB | O002 | 2024/12/25 |
| CB | O003 | 2024/12/25 |
| CC | O001 | 2024/12/1 |
※第二正規系にしないとどうなる？
④の再掲
| 顧客ID | 注文連番 | 注文日 | 顧客企業名 | 顧客規模 |
|---|---|---|---|---|
| CA | O001 | 2024/12/20 | A商社 | 大規模 |
| CA | O002 | 2024/12/21 | A商社 | 大規模 |
| CB | O001 | 2024/12/12 | B建設 | 中規模 |
| CB | O002 | 2024/12/25 | B建設 | 中規模 |
| CB | O003 | 2024/12/25 | B建築 | 中規模 |
| CC | O001 | 2024/12/1 | C化学 | 小規模 |
新しく取引をすることになったD出版を登録したい、となったら顧客規模が分からない場合NULL値なり、空白をいれなければいけなくなる。(他は許容しているのにここだけ穴あきの状態になる)
他にも同じ「顧客企業名」の行が複数存在するので一部が間違って登録される恐れがある。
(毎回顧客IDを参照しておけば間違えない)
こうした更新の際のデータ不整合を「更新異常」という。更新異常をするDBなんていらない。
5.第二正規系から第三正規系へ
⑦「顧客企業」テーブル(第二正規系)
| 顧客ID | 顧客企業名 | 顧客企業規模 | 業界コード | 業界名 |
|---|---|---|---|---|
| CA | A商社 | 大規模 | D001 | 石油 |
| CB | B建設 | 中規模 | D002 | ゼネコン |
| CC | C化学 | 小規模 | D003 | バイオ |
上のテーブルは第二正規系(主キーが一つであれば自動的に第二正規系は満たす)
だけどこのテーブルでも更新異常が発生してしまう。
今ある石油、ゼネコン、バイオ以外にシステムや物流など管理したい業界が増えても、このテーブルに登録することができない。
このテーブルは「顧客企業」という単位の集合を表していて、取引がない企業の業界についてはレコードを作ることができないから。
(今は顧客になっていないシステム業界の企業を顧客テーブルに入れると「は？」って状況になる)
さあ、分割だ！まさに集合を一要素にしましょう、ということで
Q.「」と「」に分ける？
A.「顧客企業」と「業界」に分ける！
⑧顧客企業テーブル
| 顧客ID | 顧客企業名 | 顧客企業規模 | 業界コード |
|---|---|---|---|
| CA | A商社 | 大規模 | D001 |
| CB | B建設 | 中規模 | D002 |
| CC | C化学 | 小規模 | D003 |
⑨業界テーブル
| 業界コード | 業界名 |
|---|---|
| D001 | 石油 |
| D002 | ゼネコン |
| D003 | バイオ |
これで顧客になっていない業界についても業界テーブルに登録しておいて、実際にその業界の企業が顧客になったら、顧客企業テーブルに業界テーブルの情報を参照しながら入れることができる！
ここまでで第三正規系の完了です。
お付き合いいただき、ありがとうございました。
基本情報技術者の時はなんとなく、テーブルは冗長だとだめなんだ、くらいの解像度でしかありませんでしたがそれぞれの正規化の概念や特徴を知り、DB設計の際に最低限抑える必要のある一要素を理解できました。
次回は、トランザクション分離レベルや実行計画について学習します。