正解Option ア divides the data into 3 tables, each mapped exactly to one entity with no redundancy or omission. The Customer table holds “Customer No. (primary key), Customer Name, Address”, the Product table holds “Product No. (primary key), Product Name, Unit Price”, and the Order table holds “Order No. (primary key), Customer No., Product No., Quantity, Date”. Customer names and product names may be duplicated and so cannot uniquely identify a record, but ア references the Customer and Product tables from the Order table using Customer No. and Product No. (foreign keys), so it can correctly determine which customer and which product each order refers to. In addition, the address is stored only in the Customer table, the unit price only in the Product table, and the quantity and date only in the Order table, so the same fact is never duplicated in several places. This is a normalized design free of update anomalies, and it satisfies all the requirements.
×Incorrectイ
The Customer and Product tables are fine, but the Order table references customers and products by the potentially duplicate “Customer Name” and “Product Name” instead of Customer No. and Product No. If there are customers or products with the same name, they cannot be uniquely identified and cannot be joined correctly. References should be made by the number that serves as the primary key, so this is inappropriate.
×Incorrectウ
The Customer table mistakenly includes “Date”, which is an attribute of each individual order, so one customer can hold only a single date and update anomalies occur. Furthermore, the Order table holds “Unit Price”, which is inherent to a product (and there is no Product table), and it references records by name; it is redundant and under-decomposed, so it is not normalized.
×Incorrectエ
The Product table mistakenly includes “Quantity”, which is an attribute of each individual order; the Order table redundantly holds “Address”, which is inherent to a customer (and there is no Customer table); and customers are also referenced by the potentially duplicate customer name. The attributes are not placed on the correct entities and the design is redundant, so it is not a normalized design.
ここがポイント
Normalization: place each attribute in the table of the entity it belongs to, and do not duplicate the same fact (address belongs to the customer, unit price to the product, and quantity and date to the order). Relationships between tables are linked by foreign keys.
Principle of primary keys and references: values that may be duplicated, such as customer names and product names, cannot be used for unique identification, so references and joins are done using the primary keys (Customer No. and Product No.).