This question's explanation and your answers so far.
“部署”表，“都道府県”表及び“社員”表を結合して，A 表を作成した。結合した“社員”表はどれか。ここで，下線は主キーを示し，破線は外部キーを示す。 部署
| 部署コード | 部署名 |
|---|
都道府県
| 都道府県コード | 都道府県名 |
|---|
A
| 社員番号 | 社員名 | 部署名 | 都道府県名 | 年齢 |
|---|
（下線のうち実線は主キー，破線は外部キーを表す。部署表：部署コードが実線下線。都道府県表：都道府県コードが実線下線。A 表：社員番号が実線下線。選択肢は 4 肢とも 社員番号 が実線下線で，部署コード・都道府県コードは破線下線。）
| 社員番号 | 社員名 | 年齢 |
|---|
| 社員番号 | 社員名 | 年齢 | 都道府県コード |
|---|
| 社員番号 | 社員名 | 年齢 | 部署コード |
|---|
| 社員番号 | 社員名 | 年齢 | 部署コード | 都道府県コード |
|---|
It has only Employee number, Employee name, and Age, and not a single foreign key. It has no column to match against either the “Department” table or the “Prefecture” table, so it cannot be joined and cannot yield the Department name and Prefecture name of table A.
Its only foreign key is Prefecture code, so it can be joined with the “Prefecture” table and yield Prefecture name, but it lacks the Department code that links to the “Department” table, so it cannot yield the Department name of table A.
Its only foreign key is Department code, so it can be joined with the “Department” table and yield Department name, but it lacks the Prefecture code that links to the “Prefecture” table, so it cannot yield the Prefecture name of table A.
正解 Table A consists of five items: Employee number, Employee name, Department name, Prefecture name, and Age. Among them, Department name exists only in the “Department” table and Prefecture name only in the “Prefecture” table, so both were brought in by the join. In a relational database, to join two tables the referencing table must have a column (a foreign key) that holds the same values as the primary key of the other table. Therefore, besides Employee number (primary key), Employee name, and Age, which appear in table A unchanged, the original “Employee” table needs two foreign keys: Department code, the primary key of the “Department” table, and Prefecture code, the primary key of the “Prefecture” table. Only エ has all five of these columns, and in the figure the Department code and Prefecture code in エ are shown with dashed underlines (foreign keys), which matches the join relationship with the two tables.