もう一問、ITパスポート試験平成21（2009）年秋期の問題から。次のような伝票を示した上で…
次のような設問がある。
問100
図２の請求書を表として記録することになった。購求書の表を作成するに当たり，伝票番号，日付，取引先コード，部署名，商品コード，数量，単価の七つの項目を記録することにした。これらの項目を正規化して記録するとき，表の構成として適切なものはどれか。ウ
伝票番号 日付 取引先コード 部署名
伝票番号 商品コード 数量 単価 （選択肢ア、イ、エ略）
スポンサーリンク
正規化により解答する方法は以下の通り。
設問中にあるように、伝票に記載されている内容のうち次の項目を表にする。
| 伝票番号 | 日付 | 取引先コード | 部署名 | 商品コード | 数量 | 単価 |
第一正規化は完了していることにする（実はここでちょっと面倒な問題があるのだが、今はサラっと次に行かせてもらいます）。
第二正規化に先立って、主キーの選定を行う。主キーは「伝票番号」と「商品コード」である。一枚の伝票に日付と取引先が一つずつ記されているから、伝票番号が決まれば日付と取引先コードと取引先の部署名は決定される（逆に、同じ日に伝票は何枚も発行できるから日付が決まっても伝票番号は決まらない。また同じ取引先と何度も取引するから、取引先コードが決まっても伝票番号は決まらない）。
一方、「数量」と「単価」を決定するためには、「伝票番号」と「商品コード」の両方が決まる必要がある。どの取引先が、どの商品を、ということが決まって、初めていくつ買ったかということが決まるからである。
従って第二正規化を行うと、「伝票番号」のみを主キーとする表
| 伝票番号 | 日付 | 取引先コード | 部署名 |
と、「伝票番号」と「商品コード」を主キーとする表
| 伝票番号 | 商品コード | 数量 | 単価 |
の二つに分割されることになる。これが「ウ」すなわち正解の選択肢となる。
実は「単価」は「商品コード」が決まると決まるから、後の表はさらに
| 伝票番号 | 商品コード | 数量 |
と
| 商品コード | 数量 |
に分割することができる。これが第三正規化の完了した状態である（引用しなかった問題の説明文に、同じ取引先でも異なる部署が発注することがあるとあるから、前の表はこれ以上分割することはできない）。
ここまでは、情報処理技術者試験の参考書に説明が載っている。
問題は、Accessでは主キーが複数の項目の組合せとなる表が作れないことだ。すなわち今回の例では
| 伝票番号 | 商品コード | 数量 | 単価 |
または
| 伝票番号 | 商品コード | 数量 |
といった表を作ることはできない。一枚の伝票で複数の商品を発注した場合、同じ伝票番号を持つレコードが現れるからだ。すなわち「伝票番号」はユニークではない。同様に同じ商品を異なる取引先が発注することもあるから、「商品コード」もユニークではない。
ユニークな「ID」という項目を頭にくっつけて
| ID | 伝票番号 | 商品コード | 数量 | 単価 |
または
| ID | 伝票番号 | 商品コード | 数量 |
とするしかないのだ。
Accessでのデータベース構築を前提とした正規化の手順は、以下の通り。第二正規化に先立って、各伝票の「商品コード」ごとにユニークな「ID」を付加する必要がある。
だがそうすると「ID」が主キーになり、この時点で第二正規化は完了していることになる。
| ID | 伝票番号 | 日付 | 取引先コード | 部署名 | 商品コード | 数量 | 単価 |
表をさらに分割するには、第三正規化の段階で、複数の項目によって決定される項目を洗い出し、複数の項目によって決定される項目と、単独の項目によって決定される項目を、別の表として分割するしかない。
すなわち、「伝票番号」だけで他の項目がすべて決定する表
| 伝票番号 | 日付 | 取引先コード | 部署名 |
と、「伝票番号」と「商品コード」によって他の項目がすべて決定する表
| ID | 伝票番号 | 商品コード | 数量 | 単価 |
に分離する作業は、第三正規化において行われることになる。
第三正規化でそんなことをするなんて、どこにも書いてないぞ。少なくとも情報処理技術者試験の参考書には。データベースの専門書を読めば書いてあるのだろうか？
また、「前編」に引用した問題と、今回引用した問題の違いは、この点にある。「前編」の問題には「受注番号」という他の項目をすべて決定する項目があらかじめ含まれており、今回の問題にはそのような項目が存在しないということだ。
今回の問題には、もう一つ気になるところがある。上記の第三正規化を行った状態で、さらに表を分割できるということだ。すなわち後者の表が
| ID | 伝票番号 | 商品コード | 数量 |
と
| 商品コード | 単価 |
に分割できるのだが、これは第何正規化になるのだろう？ これも含めて第三正規化と称するのだろうか？ これもどこにも書いてないぞ。「ID」という項目を追加したことにより、「伝票番号」と「商品コード」は主キーではなくなってしまったのだから、「商品コード」と同格のはずである。とすれば、これも第三正規化でよさそうな気がする。しかしそうすると、第二正規化と第三正規化を区別する意味が薄れてしまうような気もする。
追記：
id:nhk777 さんから「前編」へのコメントとして、AccessでもID以外の項目を主キーにでき、複数項の組合せを主キーにすることもできるというご指摘をいただきました。当方の勉強不足のようです。しかし「ユニークなIDを付加しなければならない」であっても「ユニークなIDを付加することができる」であっても、今回述べた趣旨は成立すると考えたので、そのまま予約投稿することにします。