IPA IP 2022 Question 65

Topic: Databases

A question IPA Japan published in 令和4年度 ITパスポート試験 公開問題 問65, translated into English. ITPEC is based on the same exams.

Same question on ITPEC: Apr 2025 Q28

An “Employee” table and a “Department” table that are to be managed with a relational database were created on the basis of the conditions (i) through (v). Which of the following is the most appropriate primary key for the “Employee” table?

[Conditions](i)Each employee has one (1) employee number that is not duplicated.(ii)There can be employees with the same first name and family name.(iii)Each department has one (1) department code that is not duplicated.(iv)Each department has multiple employees.(v)Each employee belongs to only one (1) department.

Employee

EmployeeNumberEmployeeNameDepartmentCodeDateOfBirthAddress

Department

Department​CodeDepartment​NameLocation

Explanation

“EmployeeNumber” — by condition (i) it alone identifies each employee row.

A primary key must be unique for every row, never empty, and minimal: no column in it can be dropped without losing uniqueness. Check the columns of Employee against the conditions:

  • •EmployeeNumber: one per employee, never duplicated (i), so it is unique on its own
  • •EmployeeName: two employees can share a name (ii), so it is not unique
  • •DepartmentCode: a department has many employees (iv), so the same code appears on many rows; it is the foreign key to Department (v)

Answer (a)

Why not others:

  • •(b) — the pair is unique, but only because EmployeeNumber already is; the extra DepartmentCode makes the key non-minimal
  • •(c) — employees with the same name would get the same key (ii)
  • •(d) — every employee in a department would share the key (iv)

Key rule: Choose the smallest set of columns that is unique for every row; a column shared by many rows, such as a department code, belongs in the table as a foreign key.

Databases · AI-written, may contain errors

Practice this exam

This question comes from the past questions IPA Japan publishes, translated into English by ITPEC Practice. ITPEC Practice is an independent study tool and is not affiliated with IPA or ITPEC. See the original paper (PDF) or Report an issue.