The document number and the employee number alone do not make a row unique. Condition (1) says that only one copy of each document is held, but nothing forbids the same employee from borrowing that document again on a later day after it has been returned, so several loan records with the same document number and the same employee number but different loan dates can be created. A primary key does not allow duplicates, so this choice is inappropriate.
○イ
正解The correct answer is イ. As stated in [Loan processing] (4), the loan table is a loan-history table to which one row is added every time a document is loaned out, so all we need to do is find the smallest combination of columns that can uniquely identify a single row. First, by condition (1) only one copy of each document is held, but that one copy can be loaned out any number of times after it is returned, so the document number alone produces duplicate rows. Adding the employee number does not settle it either, because the same employee may borrow the same document again on a later day. So we add the loan date, and now the second half of condition (2) — a returned document is not loaned again on the same day to the same employee — takes effect and guarantees that no two or more rows can be created with the same document number, the same employee number, and the same loan date. Therefore the primary key is the combination of document number, employee number, and loan date (a composite primary key).
×Incorrectウ
It is correct that the primary key of the employee table is the employee number, but if the primary key of the loan table were also the employee number, each employee could have only one loan record. In reality one employee borrows several documents and also borrows them over and over, so the employee number is duplicated within the loan table. In the loan table it is the column that serves as a foreign key referring to the employee table; it does not become the primary key.
×Incorrectエ
It is correct that the primary key of the document table is the document number (by condition (1) each document is unique), but it does not become the primary key of the loan table. This is because when the same document is loaned out repeatedly after being returned, many loan records with the same document number line up in the loan table. Note also that [Return processing] (1) adds the condition that the return date is a blank value on top of searching by document number, which is further evidence that the document number alone cannot pin down a row of the loan table.
ここがポイント
A primary key is a column (or a combination of columns) that can uniquely identify a single row in a table; duplicate values and blank values (NULL) are not allowed in it. When no single column achieves uniqueness, several columns are combined into a composite primary key.
For tables that accumulate a history (a loan table, an order table, a sales table, and so on), the columns that represent entities (document number, employee number) alone let the same combination appear over and over, so it is standard practice to add a date or a time to the primary key. How many columns you must add before duplicates disappear is judged from the business rules written in the question — here, condition (2), which forbids loaning the document again to the same employee on the same day.