Developing SQL Databases — Question 146

You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening stores, must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the StoreID, ProductID,
Qtysold, Totprodsales, and Datesold columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.
What should you include in your solution?

Answer options

• A. Create a unique constraint.
• B. Create a foreign key constraint to the StoreID column in each of the store tables.
• C. Create a rule and bind it to the StoreID column.
• D. Create a check constraint.
• E. Create a table-valued user-defined function.

Correct answer: A

Explanation

The correct answer is A, as creating a unique constraint will ensure that each row in the summary table is distinct based on StoreID, ProductID, and Datesold, preventing duplicate entries. Option B is incorrect because a foreign key constraint would not prevent duplicates; it only maintains referential integrity. Option C does not apply here, as a rule is not designed to enforce uniqueness. Option D is also not suitable since a check constraint merely validates data but does not ensure uniqueness. Option E is irrelevant to the requirement of avoiding duplicates.