問72
頻出度★★☆
関係データベースで管理された“売上”表，“顧客”表及び“商品”表がある。a～cのうち，これらの表のデータを用いて作成できるものだけを全て挙げたものはどれか。ここで，下線のうち実線は主キーを，破線は外部キーを表す。 売上
| 売上番号 | 顧客番号 | 商品番号 | 売上年月日 | 売上額 |
|---|
顧客
| 顧客番号 | 顧客名 |
|---|
商品
| 商品番号 | 商品カテゴリ名 | 商品名 |
|---|
a 過去のある期間に一定額以上の売上があった顧客の一覧 b 前月に在庫切れがあった商品の一覧 c 直近1か月の商品別売上額ランキング
平成27年度 秋期 ITパスポート試験 問72
テクノロジ系データベース › データ操作
This question's explanation and your answers so far.
解説
問 72
Incorrectア
a can be created, but b “A list of products that were out of stock in the previous month” cannot be created because no table has a column representing the stock quantity (inventory amount). Since it includes b, which cannot be created, this option is wrong.
Incorrectイ
a and c can be created, but b cannot be created because none of the three tables contain any data about inventory. Since it includes b, this option is wrong.
- ウ
正解 The correct answer “ウ” is a and c. In a relational database, target data can be created only when the needed items exist as columns of the tables and the tables can be joined by primary and foreign keys. For a “A list of customers whose sales reached a certain amount or more during a past period”, the “Sales” table has the sales date (to narrow the period), the sales amount (to judge whether it reaches a certain amount or more), and the customer number; by joining with the “Customer” table on the customer number, which is a foreign key, a list including customer names can be created. For c “A sales-amount ranking by product for the most recent one month”, you can narrow the “Sales” table to the most recent one month by sales date, total the sales amount per product number, and join with the “Product” table to add the product name, thereby creating the ranking. Meanwhile, b cannot be created because there is no inventory data. Therefore only a and c can be created, and the answer is “ウ”.
Incorrectエ
c can be created, but b cannot be created because there is no inventory data, and moreover the creatable a is missing. Since it includes b, which cannot be created, and lacks a, this option is wrong.
ここがポイント
- In a relational database, data can be created only when the items needed for the target data exist as columns of the tables and can be joined by primary and foreign keys, using selection (narrowing rows by conditions), join (connecting tables by a common key), and aggregation. Information not present in the tables (the stock quantity in this question) cannot be derived.
- The decisive reason b cannot be created is that the stock-quantity column needed to judge “out of stock” does not exist in any of the Sales, Customer, or Product tables. Distinguish that whether sales exist and whether stock exists are different data.
用語