ITパスポート 2023年問59

問題文

関係データベースで管理された“会員管理”表を正規化して、“店舗”表、“会員種別”表及び“会員”表に分割した。“会員”表として、適切なものはどれか。ここで、表中の下線は主キーを表し、一人の会員が複数の店舗に登録した場合は、会員番号を店舗ごとに付与するものとする。

🖊️ 解答を隠して解いてみる

選択肢

ア：

イ：

ウ：

エ：（正解）

🔒 解説は解答すると表示されます

会員管理表の正規化【ITパスポート解説】

正解の理由

選択肢エが正しい理由は、会員を一意に特定するための「主キー（Primary Key：行を一意に特定する列）」として、店舗ごとに付与される会員番号（つまり「店舗コード＋会員番号」の組み合わせ）を使っている点です。問題文に「一人の会員が複数の店舗に登録した場合は、会員番号を店舗ごとに付与する」と書かれているため、会員番号だけでは全体で一意にならず、店舗コードと組み合わせて初めて一意になります。したがって、会員テーブルは以下の列を持ち、主キーは（店舗コード, 会員番号）の複合主キー、会員種別コードは「外部キー（Foreign Key：他の表の主キーを参照する列）」として分離された会員種別表を参照する形が正しい設計です。

会員表の構成（選択肢エの内容）

店舗コード（主キーの一部、外部キーとして「店舗」表を参照）
会員番号（主キーの一部）
会員名（会員の名前）
会員種別コード（外部キーとして「会員種別」表を参照）

店舗名や会員種別名は、それぞれ「店舗」表と「会員種別」表に持たせることで、同じ情報の重複（データの冗長）を避けられます。

解法ステップ

元の表から「各列の役割」を考える
- 店舗コード → 店舗名を決める（店舗コードがあれば店舗名が一意に決まる）
- 会員種別コード → 会員種別名を決める
- 会員番号 → 問題文の条件で「店舗ごとに付与される番号」なので、単独では一意にならない
「関数従属性（ある列で別の列が決まる関係）」を確認する
- 店舗コード → 店舗名
- 会員種別コード → 会員種別名
- (店舗コード, 会員番号) → 会員名, 会員種別コード
正規化の方針に従って表を分割する
- 店舗に関する情報を「店舗」表へ（店舗コードが主キー）
- 会員種別の情報を「会員種別」表へ（会員種別コードが主キー）
- 会員に関する情報は残りを「会員」表へ。主キーは（店舗コード, 会員番号）
外部キーを使って表を結合できるようにする
- 会員表の店舗コードは店舗表の主キーを参照（外部キー）
- 会員表の会員種別コードは会員種別表の主キーを参照（外部キー）

（補足）SQLで表定義すると次のようになります（イメージ）：

CREATE TABLE 会員 (
  店舗コード CHAR(3),
  会員番号 INT,
  会員名 VARCHAR(100),
  会員種別コード CHAR(2),
  PRIMARY KEY (店舗コード, 会員番号),
  FOREIGN KEY (店舗コード) REFERENCES 店舗(店舗コード),
  FOREIGN KEY (会員種別コード) REFERENCES 会員種別(会員種別コード)
);

選択肢別の誤答解説

※設問の選択肢は記号で与えられています。以下は各選択肢が想定する設計に対する解説です。

ア: 「会員番号だけを主キーにしている」
誤り。問題文の条件で、会員番号は店舗ごとに付与されるため、全店舗を通して一意になりません。会員番号のみを主キーにすると、異なる店舗の同じ会員番号が衝突してしまいます。
イ: 「店舗コードを主キーにして会員情報をそのまま格納している（店舗ごと複数会員を許していない）」
誤り。店舗コードを単独で主キーにすると、1店舗につき1行しか入らない設計になり、1店舗に複数の会員データを保存できません。実際のデータでは店舗ごとに複数会員がいるため不適切です。
ウ: 「会員表に店舗名や会員種別名をそのまま残している（冗長）」
誤り。店舗名や会員種別名を会員表に保持すると、同じ店舗名・会員種別名が何度も繰り返され、更新時に整合性が保ちにくくなります（例：店舗名が変更されたとき、すべての該当行を更新する必要が出る）。正規化の目的はこの冗長を排除することです。
エ: 「店舗コード＋会員番号を主キーとし、会員名と会員種別コードを持つ」
正解。店舗ごとの会員番号付与ルールに合致し、冗長を避けつつ外部キーで店舗表・会員種別表とつなげられる正しい設計です。選択肢エがこの形になっています。

よくある誤解

「会員番号が唯一なら会員番号だけで良い」は誤り
- 問題文で会員番号は店舗ごとに再利用されることが明記されています。だから単独の会員番号では一意にならず、必ず店舗コードと組み合わせて考えます。
「人の名前（会員名）を主キーにしてもよい」は避ける
- 名前は重複や変更が起きやすく、主キーとして不適切です。通常、識別子（番号やコード）を主キーにします。
「正規化＝必ず全て分割すれば良い」ではない
- 正規化はデータの冗長や異常を減らす目的ですが、実務ではパフォーマンスや運用を考えて意図的に冗長を許すこともあります。試験問題では設問の条件に従った正しい正規化を選びます。

補足コラム

正規化の目的は「データの重複をなくし、更新や削除の際の矛盾（更新異常）を防ぐ」ことです。今回のように「店舗名」や「会員種別名」を別表に切り出すのは典型的な正規化の手順です。
「複合主キー（Composite Key）」とは、複数の列を組み合わせて主キーにすることです。今回の（店舗コード, 会員番号）はその例です。別の方法としては、全行で一意となる連番の「代理キー（サロゲートキー、Surrogate Key）」を導入する方法もありますが、問題文のルールをそのまま反映するなら複合主キーが自然です。

FAQ

Q1: 店舗名を会員表に残してもダメですか？
A1: 技術的には可能ですが、同じ店舗名が複数行に登場すると冗長になり、店舗名を変更すると複数箇所を更新しなければならずミスの原因になります。問題の指示どおり「店舗」表に分けるのが正解です。

Q2: 会員が複数店舗に登録している場合、個人をどう識別しますか？
A2: 問題文では「会員番号は店舗ごとに付与」となっているため、個人（実際の人）を全体で一意に識別するIDは存在しない前提です。もし全店舗で一意の個人IDが欲しいなら、新たに共通の会員ID（サロゲートキー）を設計する必要があります。

Q3: 会員種別名は変更されることがありますか？
A3: 可能性はあります。だから会員種別名はコード（会員種別コード）で参照し、表示する際に会員種別表から名前を参照するのが正しい設計です。

関連キーワード: 正規化、主キー、外部キー、複合主キー、データ冗長、関数従属性、テーブル分割