ITPEC IP April 2025 Question 28

Topic: Databases

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 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.