正解The correct answer is ア. The items of the member usage analysis table to be created are “member number, member name, gender, age, date, entry time”. Of these, member name, gender, and age exist only in the member table, while date and entry time exist only in the entry table. In other words, items that span two tables must be brought together into a single row. The operation that does this in a relational database is the join, and if the member number, which exists in both tables, is used as the join key, each single entry record in the entry table gets a row to which the member name, gender, and age from the member table for that member number are attached. Only then can “who (gender and age group)” and “when they entered” be handled in the same row, which makes it possible to count the cumulative number of users by gender and age group shown in the table. Note that one member may enter many times in a month, so the number of rows in the join result equals the number of entry records, and this leads directly to totaling the cumulative number of people.
×Incorrectイ
Sorting only changes the order of the rows in a table and does not add any items (columns), while projection is the operation of taking only the needed columns out of a single table. Even if the member table is sorted by member number and columns are taken from the entry table, the two remain separate tables and are not merged into a single row. Member name, gender, and age cannot be lined up in the same row as date and entry time.
×Incorrectウ
Selection is the operation of taking the rows that meet a condition out of a single table, and the only columns it can return are the columns that the table itself has. Even if the member table is selected using the member number of the entry table as the condition, the result contains only “member number, member name, gender, age” of the member table, and the date and entry time needed for the analysis are not obtained. This confuses the operation that narrows down rows with the join that links columns together.
×Incorrectエ
Sorting the entry table by member number only changes the order of the rows, and performing a selection on the member table after that still yields only the columns of the member table. As with ウ, the date and entry time do not line up in the same row as the member attributes, so the structure of Figure 2 cannot be created. Sorting can serve as preprocessing for analysis, but it is not an operation that links tables to each other.
ここがポイント
There are three basic relational database operations: selection = take out the “rows” that meet a condition, projection = take out the “columns” that are needed, and join = combine several tables into one table using a common item as the key. When the needed items span several tables, the operation to use is the join.
For the join key, choose an item that exists in both tables. In this question the member number, which is in both the member table and the entry table, is the key; since several entry records correspond to one member record, the join result has as many rows as there are entry records, and it can be used to total the cumulative number of uses.