This question's explanation and your answers so far.
一つの表で管理されていた受注データを，受注に関する情報と商品に関する情報に分割して，正規化を行った上で関係データベースの表で管理する。正規化を行った結果の表の組合せとして，最も適切なものはどれか。ここで，同一商品で単価が異なるときは商品番号も異なるものとする。また，発注者名には同姓同名はいないものとする。 受注データ
| 受注番号 | 発注者名 | 商品番号 | 商品名 | 個数 | 単価 |
|---|---|---|---|---|---|
| T0001 | 試験花子 | M0001 | 商品1 | 5 | 3,000 |
| T0002 | 情報太郎 | M0002 | 商品2 | 3 | 4,000 |
| T0003 | 高度秋子 | M0001 | 商品1 | 2 | 3,000 |
| 受注番号 | 発注者名 ／ 下段表: 商品番号 | 商品名 | 個数 | 単価 |
|---|
| 受注番号 | 発注者名 | 商品番号 ／ 下段表: 商品番号 | 商品名 | 個数 | 単価 |
|---|
| 受注番号 | 発注者名 | 商品番号 | 個数 | 単価 ／ 下段表: 商品番号 | 商品名 |
|---|
| 受注番号 | 発注者名 | 商品番号 | 個数 ／ 下段表: 商品番号 | 商品名 | 単価 |
|---|
The order-side table has no product number, so the foreign key that links it to the product table is lost. Moreover, it places quantity in the product table, but quantity is determined by the order number (a per-order value) and does not depend on the product number, so it cannot be placed in the product table. Incorrect.
Placing quantity in the product table is the error. Quantity is not a value fixed per product but is determined per order (order number → quantity), so it does not belong in the product table whose primary key is the product number. Even for the same product, the quantity changes from order to order, which becomes a source of duplication and inconsistency.
Leaving the unit price in the order-side table is the error. The unit price is uniquely determined by the product number (product number → unit price), so unless it is moved to the product table, the unit price appears repeatedly in rows with the same product number, and the duplication remains. The product table has only “product number, product name” and lacks the unit price, so the normalization is incomplete.
正解 The correct answer is エ. Organizing the functional dependencies of the original order data: once the order number is fixed, the orderer name, product number, and quantity are uniquely determined (order number → orderer name, product number, quantity); once the product number is fixed, the product name and unit price are uniquely determined (product number → product name, unit price). The key point is that the condition “when the same product has different unit prices, the product number is also different” guarantees the dependency product number → unit price. Therefore, the table for orders is “order number (primary key), orderer name, product number (foreign key), quantity,” and the table for products is “product number (primary key), product name, unit price.” Quantity varies with each order, so it stays on the order side; unit price is determined by the product, so it is moved to the product side to eliminate duplication. This matches the combination of エ.