ITPEC IP April 2024 Question 26

Topic: DatabasesAlso asked in March 2018

There are “Sales”, “Customer” and “Product” tables in a relational database. Among lists A through C, which of the following contains all and only the lists that can be created from the data in these tables? Here, a solid underlined part represents a primary key, and a dashed underlined column represents a foreign key.

SalesSalesNumberCustomerNumberProductNumberSalesDateSalesAmountCustomerCustomerNumberCustomerNameProductProductNumberProductCategoryNameProductName
AThe list of customers who bought more than a certain amount of money during a certain period in the pastBThe list of products that were out of stock in the previous monthCThe ranking list for sales amount by product in the previous month

Explanation

A and C — both can be computed from the sales records, but no table holds any stock data.

The Sales table records who bought what, when, and for how much: CustomerNumber, ProductNumber, SalesDate and SalesAmount.

  • •A Select the sales in the period by SalesDate, total SalesAmount per CustomerNumber, keep the customers above the amount, and join Customer for their names: possible.
  • •B Being out of stock needs inventory quantities, which none of the three tables contains; a product with no sales may simply not have sold: not possible.
  • •C Select last month's sales by SalesDate, total SalesAmount per ProductNumber, sort in descending order, and join Product for the names: possible.

Answer (c)

Why not others:

  • •(a) A, B — includes B, which needs stock data, and omits C
  • •(b) A, B, C — includes B
  • •(d) B, C — includes B and omits A, which the sales records support

Key rule: A list can be produced only from attributes the tables hold or can compute by joining and aggregating; information never recorded, such as stock levels, cannot be derived.

Databases · AI-written, may contain errors

Practice this exam

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.