ITPEC IP October 2025 Question 27
The “Purchase_list” table in a relational database manages the purchases of products. This table should be normalized and divided into a “Purchase” table and a “Product” table. Which of the following is the most appropriate field that should be contained in common in the two divided tables? Here, each purchase includes only one product, and can be uniquely identified by Purchase_number. Also, each product can be uniquely identified by Product_number.
Purchase_list
Explanation
Product_number — it becomes the primary key of Product and stays in Purchase as the foreign key that links each purchase to its product.
Split the fields by what they depend on:
- •Purchase: Purchase_number (key), Product_number, Count, Payment_method, Delivery_date — facts about one purchase
- •Product: Product_number (key), Product_name, Unit_price — facts about the product, the same on every purchase of it
Product_name and Unit_price depend on Product_number, not directly on Purchase_number, so in Purchase_list they repeat on every purchase of the same product. Moving them to Product stores each once; joining the two tables on Product_number rebuilds the original list.
Answer (c)
Why not others:
- •(a) Purchase_number — the key of Purchase; the Product table holds nothing per purchase, and adding it there would repeat each product once per purchase again
- •(b) Payment_method — describes one purchase and belongs only in Purchase
- •(d) Product_name — depends on Product_number and belongs only in Product; it is not stated to be unique, so it cannot serve as the link
Key rule: When normalization splits a table, the key of the new table stays behind in the original one as a foreign key; that shared column is what joins them back.
Databases · AI-written, may contain errors
This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official IP past-paper collection or Report an issue.