受注データの正規化で個数と単価をどの表に置くかを考える
ITパスポート試験 令和6年度 問81 ／ データベース › データベース設計
過去問にチャレンジ
一つの表で管理されていた受注データを，受注に関する情報と商品に関する情報に分割して，正規化を行った上で関係データベースの表で管理する。正規化を行った結果の表の組合せとして，最も適切なものはどれか。ここで，同一商品で単価が異なるときは商品番号も異なるものとする。また，発注者名には同姓同名はいないものとする。
- ア
- イ
- ウ
- エ
会話形式の解説
講師今回は、一つの受注データの表を「受注の表」と「商品の表」に分けて正規化する問題です。正規化とは、データの重複や食い違いを防ぐために、項目を「何によって決まるか」で分けて別々の表にする手法です。各項目が何によって決まるか、そこに注目して見ていきましょう。
受験者商品名、個数、単価は全部商品の話なので、商品の表にまとめればいいと思いました。
講師そこが今回の引っかけどころです。図の受注データを見てください。商品番号M0001は、受注T0001では5個、T0003では2個注文されています。商品番号が同じでも、個数は同じとは限りませんね。
受験者確かに個数は違います。そうすると、個数は商品番号だけでは決まらないということですか。
講師そのとおりです。個数は受注ごとに変わる値なので、受注の表に置く必要があります。商品の表に個数を置く案では、一つの商品に個数を一つしか持てず、受注ごとの個数を記録できません。
受験者受注番号と発注者名だけの表と、商品番号・商品名・個数・単価の表に分ける案も、同じ理由でだめなのですね。
講師個数の置き場所に加えて、その案にはもう一つ問題があります。二つの表に共通する項目がないので、どの受注でどの商品が注文されたのかを対応付けられません。分けた表は、共通の項目で結び付けられなければなりません。
受験者では、受注の表に商品番号と個数を入れて、単価も受注の表に残す案はどうでしょうか。商品の表には商品番号と商品名だけ置く形です。
講師問題文に「同一商品で単価が異なるときは商品番号も異なる」とありますね。これは、単価が商品番号だけで決まることを示す条件です。単価を受注の表に置くと、図のM0001の3,000円のように、同じ商品が注文されるたびに同じ単価が繰り返し記録されます。一部だけ書き換えると値が食い違う、更新異常の原因になります。
受験者それなら、受注の表は受注番号・発注者名・商品番号・個数、商品の表は商品番号・商品名・単価に分ける組合せが正解ですね。
講師はい。商品番号は商品の表の主キー（行を一つに特定する項目）で、受注の表では外部キー（別の表の主キーを参照する項目）として二つの表を結び付けます。商品の情報が1か所にまとまるので、重複がなくなり更新異常も防げます。
受験者「商品の話かどうか」ではなく、「何が決まればその値が決まるか」で考えればいいのですね。
講師そのとおりです。補足すると、元の表の主キーは受注番号です。主キーではない商品番号によって決まる商品名と単価を別の表に移すのが、第3正規化の考え方です。迷ったら、同じ商品が複数の受注で違う個数ずつ注文される場面を思い浮かべると判断しやすくなります。
まとめ
受注データを受注の表と商品の表に分けて正規化する問題では、各項目が何によって決まるかを確かめることが大切です。商品名と単価は商品番号で決まるので商品の表に、個数は受注ごとに変わるので受注の表に置きます。二つの表は、商品の表の主キーであり受注の表では外部キーとなる商品番号で結び付けます。単価を受注の表に残すと同じ値が重複して更新異常の原因になり、個数を商品の表に置くと受注ごとの個数を記録できません。「商品に関する情報だから」と個数を商品の表に入れてしまうのが、よくある引っかけです。
解答・解説
正解はエです
この問題のテーマ
一つの受注データの表を、受注の情報と商品の情報の二つに分けて正規化したとき、正しい表の組合せを選ぶ問題です。どの項目がどの項目によって決まるか（関数従属）を見分けられるかが問われています。
解き方の考え方
まず、各項目が何によって決まるかを確かめます。受注番号が決まれば発注者名が決まり、商品番号が決まれば商品名と単価が決まります。問題文に「同一商品で単価が異なるときは商品番号も異なる」とあるので、単価は商品番号だけで決まります。一方、個数は受注ごとに変わるので、商品番号だけでは決まらず、受注側に置く必要があります。最後に、二つの表が共通の項目（商品番号）で結び付けられるかを確認します。
選択肢ごとの解説
- ×ア受注番号と発注者名だけの表と、商品番号・商品名・個数・単価の表に分ける案です。二つの表に共通する項目がないので、どの受注でどの商品が注文されたのかを対応付けられません。また、個数を商品の表に置いているため、受注ごとの個数を記録できません。
- ×イ受注の表に商品番号を入れているので、商品番号で二つの表を結び付けることはできます。しかし、個数を商品の表に置いています。図の例では商品番号M0001が受注T0001で5個、T0003で2個注文されていますが、この案では一つの商品に個数を一つしか持てず、受注ごとの個数を表せません。
- ×ウ単価を受注の表に残している点が問題です。単価は商品番号で決まる値なので、受注の表に置くと、同じ商品が注文されるたびに同じ単価が繰り返し記録されます。図の例でもM0001の3,000円が2行に重複しており、一部だけを書き換えると値が食い違う更新異常の原因になります。
- ○エ受注ごとに決まる受注番号・発注者名・商品番号・個数を受注の表に、商品番号で決まる商品名・単価を商品の表に分けています。商品番号は商品の表の主キーで、受注の表では外部キーとして二つの表を結び付けます。商品の情報が1か所にまとまるので重複がなくなり、更新異常も防げます。
補足
「商品に関する情報だから」と考えて、個数を商品の表に入れてしまうのがよくある引っかけです。同じ商品が複数の受注で違う個数ずつ注文される場面を思い浮かべると、個数がどちらの表に入るべきかを判断しやすくなります。問題文の「同一商品で単価が異なるときは商品番号も異なる」という条件は、単価が商品番号だけで決まることを示すためのものです。
POINT
- 正規化は、データの重複や不整合を防ぐために、項目を「何によって決まるか」で分けて別の表にする手法です。
- 商品名や単価のように商品番号で決まる項目は、商品の表にまとめます。
- 個数のように受注ごとに変わる項目は、商品の表ではなく受注の表に置きます。
- 分けた表どうしは共通の項目（主キーと外部キー）で結び付けられなければなりません。
- 主キー以外の項目（ここでは商品番号）で決まる項目を別の表に移すのが、第3正規化の考え方です。