問85
頻出度★★★
関係データベースで管理している“担当社員”表，“地区”表及び“顧客”表を結合して，A表を得た。結合に用いた“顧客”表はどれか。ここで，下線のうち実線は主キーを，破線は外部キーを表す。 担当社員
| 社員コード | 社員名 |
|---|
地区
| 地区コード | 地区名 |
|---|
A
| 顧客コード | 顧客名 | 社員名 | 地区名 | 代表者名 |
|---|
平成30年度 秋期 ITパスポート試験 問85
テクノロジ系データベース › データベース設計
This question's explanation and your answers so far.
解説
問 85
Incorrectア
It has only the 3 columns “Customer Code, Customer Name, Representative Name” and holds no foreign key at all. With this it can join with neither the “Employee in Charge” table nor the “District” table, so it cannot obtain either the “Employee Name” or the “District Name” that appear in table A.
Incorrectイ
It holds only the foreign key “Employee Code.” It can join with the “Employee in Charge” table to obtain the “Employee Name,” but because it lacks “District Code” it cannot join with the “District” table, so it cannot obtain table A’s “District Name.”
Incorrectウ
It holds only the foreign key “District Code.” It can join with the “District” table to obtain the “District Name,” but because it lacks “Employee Code” it cannot join with the “Employee in Charge” table, so it cannot obtain table A’s “Employee Name.”
- エ
正解 After the join, table A has 5 columns: “Customer Code,” “Customer Name,” “Employee Name,” “District Name,” and “Representative Name.” The columns inherent to the original “Customer” table are “Customer Code,” “Customer Name,” and “Representative Name,” but table A’s “Employee Name” is a column brought in by joining with the “Employee in Charge” table (primary key = Employee Code), and “District Name” by joining with the “District” table (primary key = District Code). Therefore the “Customer” table must hold both “Employee Code” and “District Code” as the foreign keys needed to join with these two tables. Thus the columns of the original “Customer” table are “Customer Code, Customer Name, Representative Name, Employee Code, District Code,” so choice エ is correct. In the figure as well, only エ provides both the “Employee Code” and “District Code” columns with a dashed underline (foreign key).
ここがポイント
- In a join, a table brings in another table’s columns by holding a foreign key that references that table’s primary key. The key point is to reason backward from the columns appearing in table A to the foreign keys the original table needs.
- A foreign key (dashed line) is a column that points to another table’s primary key (solid line). You need as many foreign keys as there are tables from which you want to bring in columns.
用語