This question's explanation and your answers so far.
問88
頻出度★★☆
P 社は，セキュリティを確保するために自社ビルを 10 の区画に分け，各区画への入退室を IC カードで行うシステムを導入し，その管理方法についての検証を始めた。この IC カードを使った入退室管理（以下，入退室管理という）の概要は次のとおりであり，入退室管理で用いるデータベースの構造（一部）を図 1 に示す。
〔入退室管理の概要〕
(1) 社員は一つの部署に所属し，IC カードを 1 人 1 枚貸与される。IC カードはカード固有の IC カード番号をもつ。IC カードの情報は IC カード登録表に登録される。
(2) 社員の入退室が許可されている区画は個別に決められており，許可された区分の情報が入退室許可表に登録される。入退室が可能な場合で，その区画が所属部署の入っている区画の場合は “1”，それ以外で入退室が必要と認められた区画の場合は “2” が，入退室許可表の許可区分に登録される。
(3) 各区画には IC カードリーダが設置されており，入退室が許可された IC カード番号を読み取ったときだけドアを解錠し，入退室の記録を入退室記録表に記録する。このとき，入室の場合は “1”，退室の場合は “−1” を入退室記録表の入退室区分に記録する。
なお，P 社は，社員に対して午前 0 時までに退社することを義務づけている。
入退室管理で用いるデータベースを使って, 入退室に関する調査を行うことにした。図1の三つの表のデータを使って行うことができる調査はどれか。
出典 平成27年度 春期 ITパスポート試験 問88
解説
問 88
//
※ Our check while writing this explanation could not fully confirm the published answer; scoring follows IPA's official key.
×Incorrectア
The fact that the counts of entry and exit records do not match can be found simply by counting the records, but the “cause” of it (tailgating, a missed record, a door left open, and so on) is not recorded as an item in the database. To tally the number of employees by cause, one would have to separately investigate and enter circumstances that are not left in the records, which cannot be done with the data of the three tables alone.
×Incorrectイ
When multiple employees enter or exit together on a single unlock, the entry and exit of an employee who was not authenticated is not left as a record at all. To identify whom that employee entered together with would mean tracking down, from the records, a person who is not in the records, which is logically impossible. Records of other employees with nearby times may serve as material for a guess, but that does not amount to having established the fact.
○ウ
正解The three tables of the entry/exit management database hold employees (and the departments they belong to), areas, and entry/exit records of “who entered or exited which area at what time”. Choice ウ, “Tally, by month, the number of times employees belonging to each department entered each area”, can be obtained simply by joining these records with the employee table to pull out the department, then grouping by area and by year-month and counting the entry records. In other words, it is an investigation that is completed with only the data the database already holds — a join of tables plus selection by condition and grouped aggregation — and no new information needs to be added. Therefore the correct answer is ウ.
×Incorrectエ
To determine an “area for which entry is not permitted”, one needs permission data on which areas each employee (or department) is allowed to enter, but the three tables record the facts of entry and exit and do not hold such permission information. Since whether entry is permitted cannot be known, the number of unpermitted entries cannot be tallied.
ここがポイント
The key to judging which investigations a database can support is whether the items the investigation needs are actually stored in the tables. Stored items can be freely combined by joining, selecting, and aggregating, but information that is not recorded (causes, permissions, companions who leave no record) cannot be derived.
An aggregation that spans several tables is done by first joining the tables on common items (employee number, area code, and so on), then counting the records for each grouping such as department, area, and year-month. This is the basic idea of joining and aggregating in a relational database.