ITPEC IP October 2023 Question 26
When the “Department,” “Location,” and “Employee” tables are joined to create Table A, which of the following is the “Employee” table that is to be joined? Here, an underline indicates a primary key, and a dotted underline indicates a foreign key.
Department
Location
A
Explanation
Employee with both Department_code and Location_code as foreign keys — table A takes a name from each of the other two tables, so Employee must reference both.
Table A holds, per employee, Employee_name and Age, which belong to the employee itself, plus two names that live elsewhere:
- •Department_name is only in Department, reached through its primary key Department_code
- •Location_name is only in Location, reached through its primary key Location_code
To join an employee to its department and to its location, each employee row must carry both codes as foreign keys. Option (d) has Employee_number as primary key, the employee's own attributes, and both foreign keys.
Answer (d)
Why not others:
- •(a) — no foreign key at all, so Employee cannot be joined to either table and neither Department_name nor Location_name can be obtained
- •(b) — Location_code gives Location_name, but without Department_code the Department_name is missing
- •(c) — Department_code gives Department_name, but without Location_code the Location_name is missing
Key rule: To join tables, the referencing table must hold the primary key of each table it draws data from as a foreign key; one foreign key per referenced table.
Databases · AI-written, may contain errors
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.