IPA IP 2024 Question 81
A question IPA Japan published in 令和6年度 ITパスポート試験 公開問題 問81, translated into English. ITPEC is based on the same exams.
Order data that was managed in one (1) table is divided into information concerning orders and information concerning products, and is managed in tables of a relational database after normalization is performed. Which of the following is the most appropriate combination of tables as the result of the normalization? Here, when the unit price differs for the same product, the product number also differs. In addition, there are no orderers with the same full name.
Order data
Explanation
Orders: Order number, Orderer name, Product number, Quantity; Products: Product number, Product name, Unit price — split the data by what each item depends on.
- •Order number decides who ordered, which product and how many: T0001 and T0003 are both M0001 but with quantities 5 and 2, so the quantity belongs to the order, not to the product
- •Product number decides the product name and, because a different unit price means a different product number, the unit price as well
- •The order table keeps Product number as the link to the product table
With this split, “Product 1” and 3,000 are stored once, in the product table, however many orders name M0001.
Answer (d)
Why not others:
- •(a) — nothing links an order to its product, and the quantity sits in the product table, where M0001 could hold only one quantity
- •(b) — the order now names its product, but the quantity still sits in the product table, though it differs between orders of the same product
- •(c) — the unit price depends on the product number, not on the order, so keeping it in the order table repeats 3,000 for every order of M0001 (a transitive dependency)
Key rule: Put each item in the table of the key it depends on: values that vary by order go with the order number, values fixed for a product go with the product number.
Databases · AI-written, may contain errors
This question comes from the past questions IPA Japan publishes, translated into English by ITPEC Practice. ITPEC Practice is an independent study tool and is not affiliated with IPA or ITPEC. See the original paper (PDF) or Report an issue.