正規化後の会員表に必要な主キーと外部キーを確かめる
ITパスポート試験 令和5年度 問59 ／ データベース › データベース設計
過去問にチャレンジ
関係データベースで管理された"会員管理"表を正規化して，"店舗"表，"会員種別"表及び"会員"表に分割した。"会員"表として，適切なものはどれか。ここで，表中の下線は主キーを表し，一人の会員が複数の店舗に登録した場合は，会員番号を店舗ごとに付与するものとする。
- ア
- イ
- ウ
- エ
会話形式の解説
講師今回は正規化の問題です。正規化とは、データの重複や矛盾をなくすために、表どうしの関連を保ったまま表を分ける作業のことです。注目してほしいのは、会員番号が店舗ごとに付けられるという条件と、分けたあとの表どうしをどうつなぐかの2点です。
受験者まず、店舗名と会員種別名はそれぞれ店舗表と会員種別表に移ったので、会員表には残らないと考えました。でも、そのあと会員表に何を残すかで迷っています。
講師その出発点で合っています。店舗名は店舗コードで、会員種別名は会員種別コードで一つに決まるので、別の表に切り出します。すると残るのは、店舗コード、会員番号、会員名、会員種別コードの4つです。
受験者会員表なので、主キーは会員番号だけでよい気がします。会員番号と会員名だけの表ではだめなのでしょうか。
講師分割前の表を見てください。会員番号1は札幌、東京、大阪の各店舗にあります。会員番号だけでは、どの店舗の会員か区別できません。主キーは1行を一意に特定するための項目なので、会員番号単独では役目を果たせないのです。
受験者なるほど。そうすると、会員番号と会員名に会員種別コードを加えた表も、主キーが会員番号だけなので同じ理由で誤りですね。
講師そのとおりです。会員種別コードがあるので会員種別表とはつながりますが、会員番号が重複するため1行を特定できず、どの店舗での登録かも分からなくなります。そこで、会員番号と店舗コードを組み合わせた複合主キーが必要になります。複合主キーとは、複数の項目を組み合わせて一意にする主キーのことです。
受験者会員番号と店舗コードを主キーにして会員名を持つ表は、一意に特定できるので正解だと思いかけましたが、何か足りないのでしょうか。
講師会員種別コードがありません。分割前の表にあった、どの会員がどの種別かという情報が失われ、会員種別表と関連付けられなくなります。他の表の主キーを参照するために残しておく項目を外部キーと呼びますが、その外部キーが抜けているわけです。
受験者では、会員番号と店舗コードの複合主キーに、会員名と会員種別コードを持つ表が、一意に特定できて種別との対応も残るので正解ですね。
講師はい、それが正解です。補足すると、表の例では同じ名前の会員が東京ではプラチナ、大阪では一般になっています。会員種別は店舗ごとの会員に対して決まるので、会員種別コードは会員表に置く必要があります。
講師確認のコツとして、分割後の店舗表・会員種別表・会員表を結合したとき、元の会員管理表の情報が戻せるかを考えてみてください。主キーで一意に特定できるかと、外部キーで関連が保たれているかの両方を確かめるのが大切です。
まとめ
この問題は、会員管理表を店舗表・会員種別表・会員表に正規化したとき、会員表に何を残すかを問うものです。店舗名と会員種別名はそれぞれのコードで決まるため別表に移し、会員表には店舗コード、会員番号、会員名、会員種別コードが残ります。会員番号は店舗ごとに付けられて重複するため、店舗コードと組み合わせた複合主キーが必要です。さらに、会員種別表と関連付けるための外部キーとして会員種別コードを残す必要があり、これらを満たす表が正解になります。
解答・解説
正解はエです
この問題のテーマ
関係データベースの正規化で"会員管理"表を"店舗"表・"会員種別"表・"会員"表に分けたとき、"会員"表に入れる項目と、1行を決める主キーを選ぶ問題です。
解き方の考え方
最初に、分割前の"会員管理"表で、ほかの項目によって決まる項目を探します。店舗名は店舗コードで、会員種別名は会員種別コードで決まるので、それぞれ"店舗"表と"会員種別"表へ移ります。すると"会員"表には、店舗コード、会員番号、会員名、会員種別コードが残ります。次に、会員番号が店舗ごとに付けられる点を確かめます。会員番号だけでは1行を特定できないため、店舗コードと組み合わせた複合主キーにします。最後に、会員種別表と関連付ける会員種別コードが残っているかを見て決めます。
選択肢ごとの解説
- ×ア会員番号と会員名だけの表です。会員番号1は札幌・東京・大阪の各店舗に存在するため、店舗コードがないとどの会員か区別できません。会員種別コードもないので、会員がどの種別に属するかという対応も失われます。したがって誤りです。
- ×イ会員番号、会員名、会員種別コードを持ち、主キーが会員番号だけの表です。会員種別コードがあるので会員種別表とは関連付けられます。しかし会員番号は店舗ごとに付けられて重複するため、単独では1行を一意に識別できません。どの店舗での登録かも分からないので、誤りです。
- ×ウ会員番号、店舗コード、会員名を持つ表です。会員番号と店舗コードの複合主キーなので、会員を一意に識別する点は満たしています。しかし会員種別コードがなく、会員種別表と関連付けられません。分割前にあった「どの会員がどの種別か」という情報が失われるので、誤りです。
- ○エ会員番号と店舗コードの複合主キーに、会員名と会員種別コードを持つ表です。複合主キーにより、店舗ごとに重複する会員番号を区別できます。また、会員種別コードを外部キーとして会員種別表を参照できます。一意性の確保と元の情報の保持を両方満たすので、正しいです。
補足
選択肢ウのように主キーが正しくても、会員種別コードが抜けた表は会員種別表と関連付けられません。分割後の表を結合して、元の表を復元できるかも確かめましょう。なお、表の例では同じ会員名の人が東京と大阪で異なる会員種別になっています。会員種別は店舗ごとの会員に対して決まるため、会員種別コードは会員表に置く必要があります。
POINT
- 正規化は、重複や矛盾をなくすために、関連を保ったまま表を分割する作業です。
- ある項目によって一意に決まる項目（店舗コード→店舗名など）は、別の表に切り出します。
- 番号が店舗ごとに付けられる場合のように単独で一意にならない項目は、他の項目と組み合わせて複合主キーにします。
- 分割後の表には、他の表の主キーを参照する外部キーを残して、表どうしの関連を保ちます。