This question's explanation and your answers so far.
関係データベースで管理された“会員管理”表を正規化して，“店舗”表，“会員種別”表及び“会員”表に分割した。“会員”表として，適切なものはどれか。ここで，表中の下線は主キーを表し，一人の会員が複数の店舗に登録した場合は，会員番号を店舗ごとに付与するものとする。 会員管理
| 店舗コード | 店舗名 | 会員番号 | 会員名 | 会員種別コード | 会員種別名 |
|---|---|---|---|---|---|
| 001 | 札幌 | 1 | 試験 花子 | 02 | ゴールド |
| 001 | 札幌 | 2 | 情報 太郎 | 02 | ゴールド |
| 002 | 東京 | 1 | 高度 次郎 | 03 | 一般 |
| 002 | 東京 | 2 | 午前 桜子 | 01 | プラチナ |
| 003 | 大阪 | 1 | 午前 桜子 | 03 | 一般 |
店舗
| 店舗コード | 店舗名 |
|---|
会員種別
| 会員種別コード | 会員種別名 |
|---|
| 会員番号 | 会員名 |
|---|
| 会員番号 | 会員名 | 会員種別コード |
|---|
| 会員番号 | 店舗コード | 会員名 |
|---|
| 会員番号 | 店舗コード | 会員名 | 会員種別コード |
|---|
The primary key is the Member Number only and does not include the Store Code. Since a member number is assigned per store and is duplicated across stores (number 1 exists in Sapporo, Tokyo, and Osaka), it cannot be unique. Moreover, the Member Type Code is also missing, so each member's member type cannot be represented and information is lost.
Although it has the Member Type Code, the primary key is the Member Number only and lacks the Store Code, so it cannot uniquely identify member numbers that are duplicated across stores. The mistake is that it does not form a composite primary key.
The primary key “Store Code + Member Number” is correct, but the Member Type Code is missing. The member type is an attribute that depends on the combination “Store Code + Member Number” (Gozen Sakurako is Platinum in Tokyo but Regular in Osaka), and dropping it makes it impossible to restore the original Member Management table, so it is not a lossless decomposition.
正解 The key to normalization is the condition that “a member number is assigned per store”. In fact, member number = 1 exists in Sapporo, Tokyo, and Osaka alike, so a member number alone cannot uniquely identify a member. Therefore the primary key of the Member table must be the composite key “Store Code + Member Number”. Next, looking at which key each column depends on, the Member Name and the Member Type Code are determined by the combination “Store Code + Member Number” (for example, Gozen Sakurako has Member Type Code 01 = Platinum in Tokyo but 03 = Regular in Osaka, so the same member number can have a different member type depending on the store). On the other hand, the Store Name is determined by the Store Code alone, and the Member Type Name by the Member Type Code alone, so they are separated into the Store table and the Member Type table respectively. As a result, the Member table becomes “Store Code (primary key), Member Number (primary key), Member Name, Member Type Code (foreign key)”, which is a lossless decomposition that can restore the original table without any loss. This matches choice エ.