The order table has no product number, so there is no way to link which order corresponds to which product. In addition, the quantity is information belonging to each order, yet it is placed on the product table side, so when the same product is ordered by several orders the quantities cannot be held correctly. This is an incorrect split from which the original data cannot be restored.
×Incorrectイ
It is correct that the order table and the product table are linked by the product number, but the quantity remains in the product table. The quantity depends on the order number, not on the product number, so in the product table it cannot be decided whether to enter 5 or 2 for M0001, and only one quantity can be held per product row. The item is placed in the wrong table.
○ウ
正解Normalization means dividing the items that are mixed together in a single table according to “what determines each value (functional dependency)”, so that duplication and update inconsistencies are eliminated. Looking at each item of the order data, the orderer name and the quantity are determined once the order number is fixed (information for each individual order), whereas the product name and the unit price are determined once the product number is fixed (information for each product). In fact, T0001 and T0003 share the same product number M0001, and the product name “Product 1” and the unit price 3,000 are recorded redundantly. Therefore, if the order-related information is split into 〈Order No., Orderer Name, Product No., Quantity〉 and the product-related information into 〈Product No., Product Name, Unit Price〉, the duplication of the product name and the unit price is removed, and changing a unit price requires editing only one row of the product table. The two tables are linked by the product number (a foreign key on the order table side and the primary key on the product table side), so the original order data can be restored by joining them. The combination that shows this structure is choice ウ.
×Incorrectエ
The unit price remains in the order table. From the condition given in the question, “when the unit price differs even for the same product name, the product number is also different”, the unit price depends on the product number, so it is an item that should be placed in the product table. If it is left in the order table, the unit price of the same product is recorded redundantly once per order, every price revision requires editing multiple rows, and a missed update causes an inconsistency (update anomaly).
ここがポイント
The basis of normalization is to split tables according to “what determines each item (functional dependency)”. Items that depend on the order number (orderer name, quantity) go into the order table, and items that depend on the product number (product name, unit price) go into the product table.
The split tables must be able to be joined through a common item (here the product number, which is the primary key of the product table and a foreign key in the order table) so that the original data can be restored. Preventing update anomalies by removing duplication is exactly the purpose of normalization.