問94
頻出度★★★
商品の仕入状況を管理している関係データベースの“仕入一覧”表を正規化して，“仕入”表と“商品”表に分割したい。分割後の二つの表に共通して必要なフィールドとして，最も適切なものはどれか。ここで，仕入れは一度に一つの商品だけを仕入れることとし，仕入番号で一意に識別できる。また，商品は商品番号で一意に識別できる。 仕入一覧
| 仕入番号 | 商品番号 | 商品名 | 個数 | 単価 | 支払方法 | 納品日 |
|---|
平成28年度 春期 ITパスポート試験 問94
テクノロジ系データベース › データベース設計
This question's explanation and your answers so far.
解説
問 94
Incorrectア
The purchase number is the primary key that uniquely identifies the “Purchase” table, and it belongs only to the “Purchase” table. Since products are identified by the product number, there is no point in placing the purchase number in the “Product” table, so it does not become a common field of both tables.
Incorrectイ
The payment method is information attached to each individual purchase (dependent on the purchase number) and goes only into the “Purchase” table. Since it is not an attribute of the product itself, it does not appear in the “Product” table and is not a common field.
- ウ
正解 When the “Purchase List” table is split, it becomes a “Product” table uniquely determined by the product number (product number, product name, unit price) and a “Purchase” table uniquely determined by the purchase number (purchase number, quantity, payment method, delivery date). However, as it stands, the correspondence of which product was purchased in which purchase is lost, and the original table cannot be restored. Therefore, the product number is kept in the “Purchase” table as a foreign key. Then the product number appears in both tables, as the primary key in the “Product” table and as the foreign key in the “Purchase” table, and using it as the link we can join the two tables and reproduce the original “Purchase List”. Therefore, the field both tables need in common is the product number (ウ).
Incorrectエ
The product name is a product-specific attribute dependent on the product number and goes only into the “Product” table. Since it is not a key that links the two tables, it is inappropriate as a field to be held in common.
ここがポイント
- When splitting a table during relational database normalization, so that the resulting tables can be joined back together, one table's primary key is held in the other table as a foreign key. This primary-key-and-foreign-key column becomes the field common to both tables.
- Determine which identifier (primary key) each field depends on. Attributes dependent on the product number (product name, unit price) go to the “Product” table, attributes dependent on the purchase number (quantity, payment method, delivery date) go to the “Purchase” table, and the product number, which serves as the link, is placed in both tables.
用語