ITPEC IP April 2019 Question 28

CUSTOMER, PRODUCT and ORDER, with ORDER holding the customer and product numbers — names repeat, so only the numbers can say which customer or product an order is for.

Each item depends on one key:

• •CustomerNumber determines CustomerName and Address
• •ProductNumber determines ProductName and UnitPrice
• •OrderNumber determines CustomerNumber, ProductNumber, Quantity and Date

Option (a) puts every item in the table of the key it depends on, stores each fact once, and links ORDER to the other tables through the foreign keys CustomerNumber and ProductNumber. Because different customers (and products) can share a name, a name could not do that job.

Answer (a)

Why not others:

• •(b) — ORDER refers to customers and products by name; two customers with the same name cannot be told apart, so the order cannot be tied to the right address or unit price
• •(c) — Date belongs to an order, not to a customer, so a customer could have only one order date; UnitPrice depends on the product yet is repeated in every order, and there is no PRODUCT table
• •(d) — Quantity belongs to an order, not to a product; the customer's name and address are repeated in every order, and a name cannot identify the customer

Key rule: Put each item in the table whose key it depends on, and link tables with unique identifiers, never with values that can repeat, such as names.

Databases · AI-written, may contain errors