応用情報技術者ではかなりの頻度でデータベースの正規化に関する問題が出題されます。ここでマスターしましょう。
データベースの正規化とは？
データベースの正規化を行う目的は、データの更新時に無駄なデータや不整合なデータを作らないことです。データベースの更新時に発生する不都合を更新時異常と言いますが、データベースの正規化を行うことで更新時異常を防ぐことが出来ます。
正規化していないデータベース（非正規形）
そもそも正規化していないデータベースとは何でしょうか。
次のような特徴を持つテーブルが正規化されていないデータベースです。
- 1つの項目に複数の内容が含まれている
- 繰返し項目が存在する
正規化していないとどんな問題点があるでしょうか。
非正規形の問題点
データが冗長になる
株式会社A工業から何度も注文があったとき、その度に注文テーブルに顧客番号/顧客名/連絡先の情報が更新されます。また、顧客の連絡先に変更があったとき、注文テーブルにある株式会社A工業の全ての連絡先を更新し直さないといけません。
しかし、注文テーブルとは別に顧客データを持っていれば、株式会社A工業から注文があっても顧客番号だけを更新すれば良いので、無駄に容量を使うことはありません。また、連絡先が変わった時も顧客データの連絡先だけを変更すれば問題ありません。
不十分なテーブルの構造
下図のような注文テーブルの場合、1つの注文番号で2品目しか注文出来ず、3品目以上の注文には対応できません。だからと言って、100品目登録できるようにしても無駄にテーブルの容量を使うだけの可能性があります。テーブルに繰返し項目を持つとこのような問題点があります。
第一正規形
第一正規形の定義は以下の通りです。
- 繰返し項目が無い
- 1つの項目に複数の内容を含む項目が無い
実際に非正規形から第一正規形にしてみましょう。
ここから次の作業をします。
- 品目の情報が繰返し項目として登録されているので別々に登録する
- 顧客名と連絡先が同一項目に登録されているので別々に登録する
すると、下図のような第一正規形が出来上がります。
キーと関数従属
第二正規形に行く前に、正規形を語る上で重要なキーと関数従属を解説します。
主キー/候補キー/非キー属性
候補キー
候補キーとは、行を一意に認識できる項目です。この項目は複数選択しても構いません。
候補キーは何個あっても構いません。
下図のテーブルの候補キーを考えてみましょう。
ここで、「1回の注文で同じ品目が複数注文されたとき、その品目の注文はひとまとめにする」というルールがあるとします。例えば、注文番号000003の中でマウスを2個注文した後3個注文した場合、テーブルには注文番号000003でマウスを5個注文したとひとまとめにして更新されるという意味です。
注文番号だけでは注文テーブルの候補キーにはなりません。注文番号000001の行は2つあるので1つに特定出来ないからです。この注文テーブルの候補キーは{注文番号,品目番号}と{注文番号,品目名称}になります。注文番号と品目番号を指定すれば1つの行を特定出来るからです。注文番号と品目名称でも同じです。
（単価は候補キーになりません。テーブルにはありませんが、電源ケーブルが500円だった場合マウスと被ってしまうからです。）
主キー
主キーとは、候補キーの代表です。候補キーの中からどれを主キーに選択しても構いません。ただし、一般的に名称は主キーとせず、コードを主キーとします。名称は途中で変更される可能性があるからです。今回は{注文番号,品目番号}を主キーとします。
非キー属性
非キー属性とは、候補キーに含まれていない項目です。今回の例では、{注文番号,品目番号}{注文番号,品目名称}が候補キーになるので、注文番号/品目番号/品目名称以外の、注文日/顧客番号/顧客名/顧客連絡先/注文数/単価が非キー属性になります。
部分関数従属/完全関数従属/推移関数従属
関数従属とはある属性Xを指定すると自動的に属性Yの値が決まることです。
下図では、社員番号を指定すれば自動的に社員名が決まります。
これを「社員名は社員番号に関数従属する」と言います。
完全関数従属
完全関数従属とは属性Xと属性Yを指定すると属性Zが決まるが、属性Xだけを指定しても属性Zは決まらずに、属性Yだけを指定しても属性Zは決まらないことです。
下図では、{クラス番号,出席番号}を指定すれば氏名が決まります。
しかし、クラス番号だけでは氏名は決まりませんし、出席番号だけでは氏名は決まりません。これを「氏名は{クラス番号,出席番号}に完全関数従属する」と言います。
{クラス番号,出席番号}は生徒データの候補キーになります。
部分関数従属
部分関数従属とは候補キーの一部に関数従属することです。
候補キーが{X,Y}のとき、次のいずれかが成り立ちます。
- 属性Ｘを指定すれば属性Ｚが決まる
- 属性Ｙを指定すれば属性Ｚが決まる
生徒データの候補キーは{クラス番号,出席番号}でした。
クラス番号を指定すればクラス名が決まりますが、出席番号を指定してもクラス名は決まりません。これを「クラス名は{クラス番号,出席番号}に部分関数従属する」と言います。
推移関数従属
推移関数従属とは属性Xを指定すれば属性Yが決まり、属性Yを指定すれば属性Zが決まることです。
{クラス番号,出席番号}を指定すれば都道府県コードが決まり、都道府県コードが決まると出身地が決まります。これを「出身地は{クラス番号,出席番号}に推移関数従属する」と言います。
第二正規形
第二正規形の定義は以下の通りです。
- テーブル中の全ての非キー属性が候補キーに完全関数従属している
実際に第一正規形から第二正規形にしてみましょう。
注文テーブルの非キー属性は以下の6つです。また、注文テーブルの候補キーは{注文番号,品目番号}と{注文番号,品目名称}なので、これらの非キー属性は{注文番号,品目番号}と{注文番号,品目名称}に部分関数従属していると言えます。
| 注文日 | 注文番号に完全関数従属する |
|---|---|
| 顧客番号 | 注文番号に完全関数従属する |
| 顧客名 | 注文番号に完全関数従属する |
| 顧客連絡先 | 注文番号に完全関数従属する |
| 注文数 | {注文番号,品目番号}または{注文番号,品目名称}に完全関数従属する |
| 単価 | 品目番号または品目名称に完全関数従属する |
第一正規形から第二正規形にするには、部分関数従属する部分を別のテーブルに分割します。
注文日/顧客番号/顧客名/顧客連絡先は部分関数従属しているので別テーブルに分割します。
単価は部分関数従属しているので別テーブルに分割します。
注文数は候補キーに完全関数従属しているので分割せずそのまま残しておきます。
これで第二正規形に出来ました。
第三正規形
第三正規形の定義は以下の通りです。
- テーブル中の全ての非キー属性が候補キーに推移関数従属していない
実際に第二正規形から第三正規形にしてみましょう。
注文データのテーブルを見てみます。
注文番号を指定すれば顧客番号が決まり、顧客番号が決まれば顧客名/顧客連絡先が決まるので、顧客名/顧客連絡先は注文番号に推移関数従属しています。
第二正規形から第三正規形にするには、推移関数従属する部分を別のテーブルに分割します。顧客名/顧客連絡先は顧客番号に完全関数従属するので分割結果は下のようになります。
これで第三正規形に出来ました。
ボイスコッド正規化
ボイスコッド正規形の定義は以下の通りです。
- テーブル中の候補キー以外の属性が候補キーに完全関数従属する
実際に第三正規形からボイスコッド正規形にしてみましょう。
注文明細データのテーブルを見てみます。
注文明細データの候補キーは{注文番号,品目番号}と{注文番号,品目名称}でした。
候補キー{注文番号,品目番号}に着目すると、候補キー以外の属性は品目名称と注文数になります。注文数は{注文番号,品目番号}に完全関数従属していますが、品目名称は品目番号によって決定出来るので、{注文番号,品目番号}に部分関数従属しています。
候補キー{注文番号,品目名称}に着目した場合も同様で品目番号は{注文番号,品目名称}に部分関数従属します。
第三正規形からボイスコッド正規形にするには、完全関数従属していない部分を別のテーブルに分割します。今回変更しないといけないのは注文明細データのテーブルだけです。
これで全てのテーブルを正規化出来ました。
応用情報技術者試験での出題
応用情報技術者試験を突破するのに必要な知識はこれだけです。
出題例
応用情報技術者 午前試験
令和4年度春期問28
第1、第2、第3正規形とリレーションの特徴a、b、cの組合せのうち、適切なものはどれか。
a:どの非キー属性も、主キーの真部分集合に対して関数従属しない。
b:どの非キー属性も、主キーに推移的に関数従属しない。
c:繰返し属性が存在しない。
正解は”ウ”
真部分集合の解説をします。例えば、主キーを{X,Y,Z}したとき、真部分集合は{X}{Y}{Z}{X,Y}{Y,Z}{X,Z}になります。
下図の中間テストデータの主キーは{クラス番号,出席番号,科目}なので、真部分集合は
{クラス番号}{出席番号}{科目}{クラス番号,出席番号}{クラス番号,科目}{出席番号,科目}になります。
第二正規形の定義は「全ての非キー属性は候補キーに完全関数従属している」ことでした。
「全ての非キー属性は候補キーに完全関数従属している」=「どの非キー属性も、主キーの真部分集合に対して関数従属しない」です。
例えば、中間テストデータの非キー属性は氏名と点数です。
氏名は{クラス番号,出席番号,科目}に関数従属しているのであって、主キーの真部分集合である{クラス番号,出席番号}では1つの氏名は特定できません。なので、「どの非キー属性も、主キーの真部分集合に対して関数従属しない」と言えます。
応用情報技術者 午前試験
令和3年度秋期問28
受注入力システムによって作成される次の表に関する記述のうち、適切なものはどれか。受注番号は受注ごとに新たに発行される番号であり、項番は1回の受注で商品コード別に連番で発行される番号である。なお、単価は商品コードによって一意に定まる。
ア 第1正規形でない。
イ 第1正規形であるが第2正規形ではない。
ウ 第2正規形であるが第3正規形ではない。
エ 第3正規形である。
正解は”イ”
第一正規形は繰返し項目が無く、1つの項目内に複数の内容が含まれていないことでした。
よって、問題の表は第一正規形と言えます。
第二正規形は全ての非キー属性は候補キーに完全関数従属していることでした。
問題の表の候補キーは{受注番号,項番}です。単価は非キー属性ですが商品コードに完全関数従属しています。よって、第二正規形とは言えません。
よって、答えはイです。
応用情報技術者 午前試験
令和2年度秋期問28
関係”注文記録”の属性間に①～⑥の関数従属性があり，それに基づいて第3正規形まで正規化を行って，”商品”，”顧客”，”注文”，”注文明細”の各関係に分解した。関係”注文明細”として，適切なものはどれか。ここで，{X，Y} は，属性XとYの組みを表し，X→Yは，XがYを関数的に決定することを表す。また，実線の下線は主キーを表す。
注文記録（注文番号，注文日，顧客番号，顧客名，商品番号，商品名，数量，販売単価）
〔関数従属性〕
①注文番号→注文日 ②注文番号→顧客番号
③顧客番号→顧客名 ④{注文番号,商品番号}→数量
⑤{注文番号,商品番号}→販売単価 ⑥商品番号→商品名
ア 注文明細(注文番号，顧客番号，商品番号，顧客名，数量，販売単価)
イ 注文明細(注文番号，顧客番号，数量，販売単価)
ウ 注文明細(注文番号，商品番号，数量，販売単価)
エ 注文明細(注文番号，数量，販売単価)
正解は”ウ”
注文記録（注文番号，注文日，顧客番号，顧客名，商品番号，商品名，数量，販売単価）
●注文記録を第二正規形に変形する
第二正規形は全ての非キー属性は候補キーに完全関数従属していることでした。
注文記録の候補キーは{注文番号,商品番号}で、非キー属性の関数従属は以下のようになります。
| 注文日 | 注文番号に完全関数従属する |
|---|---|
| 顧客番号 | 注文番号に完全関数従属する |
| 顧客名 | 顧客番号に完全関数従属する |
| 商品名 | 商品番号に完全関数従属する |
| 数量 | {注文番号,商品番号}に完全関数従属する |
| 販売単価 | {注文番号,商品番号}に完全関数従属する |
この結果から第二正規形は以下のようになります。
- 注文（注文番号,注文日,顧客番号）
- 注文明細（注文番号,商品番号,数量,販売単価）
- 商品（商品番号,商品名）
- 顧客（顧客番号,顧客名）
第二正規形で出来たテーブルに推移関数従属しているものは無いので、これがそのまま第三正規形の結果になります。
よって、答えはウです。
応用情報技術者試験に関する他の記事
