正解What we need is the “customer-by-employee” transportation expense report in Figure 1, that is, a table with one row per combination of a customer and an employee, where the amount is the total for that combination. First, use Figure 2 to check which table holds each required column. The amount and the customer number exist only in the detail table, and the employee number exists only in the settlement table. In other words, looking at only one of these tables cannot tell you “which customer, which employee, and how much was spent”. So in (2), the settlement table narrowed down in step (1) (applied in the previous month and approved) is joined with the detail table on the slip number, the column common to both tables. Now a single row contains the employee number, the customer number, and the amount together. Next, in (3), the joined result is aggregated by identical customer number and employee number to total the amounts, producing a table of customer number, employee number, and total amount. This is exactly the granularity of Figure 1. Finally, in (4), joining this table with the employee table and the customer table adds the names, the employee name and the customer name, and Figure 1 is complete. The decisive point is that (4) states “obtain the customer name and the employee name”: unless both the customer number and the employee number remain in the table from (3), the names cannot be looked up. Only ア satisfies this condition.
×Incorrectイ
The join in (2) is correct, but (3) uses only the customer number as the aggregation key, so the employee number disappears during aggregation. The result is a “by customer” total table with no breakdown by employee. Even if you tried to join with the employee table in step (4) to obtain the employee name, it cannot be done because the employee number that would serve as the join key no longer remains, and you also cannot produce the form in Figure 1 where the same customer 000100 is split into two rows by employee.
×Incorrectウ
The opposite of イ: (3) uses only the employee number as the aggregation key, so the customer number is lost. The result is a “by employee” total table with no breakdown by customer. In step (4) you cannot join with the customer table to obtain the customer name, and it does not satisfy the required “customer-by-employee” aggregation granularity.
×Incorrectエ
The procedure breaks down in two ways. First, (2) tries to aggregate by the combination of customer number and employee number using only the detail table, but as Figure 2 shows, the detail table has no employee number column, so this aggregation itself is impossible. Second, even supposing it could be done, aggregation removes the slip number from the table, so joining with the table from (1) on the slip number in (3) is also impossible. In addition, aggregating first and only then narrowing down to the previous month and approved slips would mix the amounts of out-of-scope slips into the totals.
ここがポイント
Using the basic relational database operations, join, selection, and projection, in the right way. To bring columns spread across several tables into a single row, a join on a common column (here, the slip number) is required, and aggregation comes after that join.
The aggregation (grouping) key must match the granularity of the table you finally want to output. For “customer-by-employee”, the key is the combination of customer number and employee number; any column not included in the aggregation key is lost from the table and can no longer be used for subsequent joins.