正解The original single table mixes information about the employee themselves (employee name, date of birth) with information about each individual exam sitting (exam type, exam date, pass/fail). Because an employee takes exams many times (for example, employee ID 0001, Sato Hanako, sat twice: on 2013/4/21 and on 2013/10/20), keeping everything in one table means the same employee name and date of birth are recorded over and over, causing wasted space and update errors. Therefore we split it into an “employee table (employee ID, employee name, date of birth)” with one row per employee, and an “exam table (employee ID, exam type, exam date, pass/fail)” with one row per exam sitting, joining the two tables by employee ID. Because the question states that “there are employees with identical surnames and given names,” the only attribute that can uniquely identify an individual is the employee ID, not the employee name, so the exam table must also reference the employee table by employee ID. This split is exactly choice ア, so ア is appropriate.
×Incorrectイ
On the exam-table side it uses the employee name rather than the employee ID for the link. The question explicitly states that “there are employees with identical surnames and given names,” so the employee name alone cannot uniquely determine whose exam record it is (it could be confused with a different employee of the same name). Because it cannot be correctly matched to the employee table, it is inappropriate.
×Incorrectウ
It breaks apart the exam type, exam date, and pass/fail that together represent a single exam sitting, putting only the exam date on the Table 1 (employee table) side and the exam type and pass/fail into the other table. Information about the same exam sitting becomes scattered, so you cannot tell which exam date corresponds to which exam type and pass/fail. Furthermore, since an employee has multiple exam dates, the employee table cannot fit each employee into a single row, and the employee name and date of birth still get duplicated. Inappropriate.
×Incorrectエ
It splits the employee name and the date of birth—both of which are employee attributes—into separate tables, and moreover places the date of birth in the same table as the exam-side exam date. The grouping of related items is broken, and the information for a single exam sitting (exam type, exam date, pass/fail) is also torn apart, with only the exam date separated off. The assignment of items is incorrect, so it is inappropriate.
ここがポイント
The basics of normalization: split the “entity (employee) information” and the “transaction/event (exam) information” that are mixed in one table into an employee table with one row per employee and an exam table with one row per exam sitting, then relate the two tables using the primary key (employee ID) as a foreign key.
Choosing the primary key: when identical names exist, the employee name cannot uniquely identify an individual, so use the employee ID as the primary key / join key. Keep the exam type, exam date, and pass/fail that represent a single exam sitting together in the same table.