P 社は，セキュリティを確保するために自社ビルを 10 の区画に分け，各区画への入退室を IC カードで行うシステムを導入し，その管理方法についての検証を始めた。この IC カードを使った入退室管理（以下，入退室管理という）の概要は次のとおりであり，入退室管理で用いるデータベースの構造（一部）を図 1 に示す。 〔入退室管理の概要〕 (1) 社員は一つの部署に所属し，IC カードを 1 人 1 枚貸与される。IC カードはカード固有の IC カード番号をもつ。IC カードの情報は IC カード登録表に登録される。 (2) 社員の入退室が許可されている区画は個別に決められており，許可された区分の情報が入退室許可表に登録される。入退室が可能な場合で，その区画が所属部署の入っている区画の場合は “1”，それ以外で入退室が必要と認められた区画の場合は “2” が，入退室許可表の許可区分に登録される。 (3) 各区画には IC カードリーダが設置されており，入退室が許可された IC カード番号を読み取ったときだけドアを解錠し，入退室の記録を入退室記録表に記録する。このとき，入室の場合は “1”，退室の場合は “−1” を入退室記録表の入退室区分に記録する。 なお，P 社は，社員に対して午前 0 時までに退社することを義務づけている。 P社では，一度の解錠で同時に複数の社員が入退室することがあり，入室と退室の記録の数が合わないという問題が発生していることが分かった。そこで，毎日，入退室記録表を使って，前日の入退室の回数が一致しない社員がいないかどうかを確認することにした。次に示す入退室確認では，前日の日付で，同一区画での入室と退室の回数が一致していないICカード番号を調べる手順を示している。a，bに入れる字句の適切な組合せはどれか。 〔前日の入退室確認〕 (1) 入退室年月日が前日の日付であるレコードを全て取り出し，ICカード番号の昇順で，かつ，区画番号の同じレコードが並ぶように整列する。 (2) (1)で整列したレコードに対して，ICカード番号と区画番号が同じレコードごとに，それらの a し，その結果の値とICカード番号及び区画番号とを併せて表として記録する。 (3) (2)で記録した a した値が b ICカード番号と区画番号を出力する。
平成27年度 春期 ITパスポート試験 問87
This question's explanation and your answers so far.
問 87
※ Our check while writing this explanation could not fully confirm the published answer; scoring follows IPA's official key.
Incorrectア
Even if you add up values based on the zone number (add 1 if the zone number is 1, add 2 if it is 2), the value being added is determined solely by the zone number and does not reflect at all whether the record is an entry or an exit. The total simply becomes the zone number multiplied by the number of records, which has nothing to do with the difference between entries and exits. For example, in the zone with zone number 1, even for an obvious mismatch such as 2 entries and 0 exits, the total is 2, which is even, so it does not satisfy b = 'is odd' and would be overlooked. Furthermore, in the zone with zone number 2 the total is always 2 times the number of records, so it is always even, and not a single mismatch can be extracted in that zone. In addition, the building has 10 zones, yet the addition rule is defined only for zone numbers 1 and 2, so it does not even work as a procedure.
- イ
正解 In the preamble of long question A, [Overview of entry/exit management] (3), it is specified that the entry/exit classification in the entry/exit record table records 1 for an entry and −1 for an exit. Therefore, in step (2), when you sum (aggregate) the values of the entry/exit classification for each group of records having the same IC card number and zone number, that total represents (number of entries) x 1 + (number of exits) x (−1) = the number of entries minus the number of exits. If the entry and exit counts match, the total is exactly 0; if they do not match, a non-zero value remains (positive if there are more entries, negative if there are more exits). Therefore a is 'sum the values of the entry/exit classification' and b is 'is not 0', and by outputting in step (3) the IC card numbers and zone numbers whose total is not 0, you can extract exactly those employees whose entry and exit counts in the same zone did not match on the previous day, with nothing missed and nothing extra. This is choice イ. Another advantage of this signed-code design is that the sign and absolute value of the total also tell you which side occurred how many more times.
Incorrectウ
One record in the entry/exit record table holds only one entry or one exit, not both, so it is inherently impossible to obtain the time difference between an entry and an exit on a per-record basis, and the aggregation operation for a cannot be carried out. Condition b does not match the purpose either. Company P requires employees to leave the office by midnight, so the total time spent in the same zone calculated from one day's worth of records for the previous day can never exceed 24 hours, and this criterion would output nothing at all. Besides, how long someone stays is a separate matter from whether the entry and exit counts match.
Incorrectエ
Counting the number of records gives you the number of entries plus the number of exits, and tells you nothing about how many of them were entries and how many were exits. Whether that count is even or odd cannot decide whether the two match. For example, 2 entries and 1 exit do not match, yet the count is 3, which is odd, so it fails b = “is even” and is missed. Conversely a normal case such as 1 entry and 1 exit gives a count of 2, which is even, so it would be output in large numbers even though it should not be. The criterion produces both misses and false outputs, so it does not serve the purpose.
- If you record paired events (entry and exit) with a signed classification code of +1 / −1, you can judge at once whether anything is missing or extra just by whether the total is 0. A total of 0 means everything pairs up; a non-zero total means one side occurred more often. This is a standard design technique.
- The basic form of aggregation processing is: sort, then aggregate per key combination, then select and output only the rows that meet the condition. The key point is which combination of columns you group by. This question aggregates per combination of IC card number and zone number, so step (1) sorts the records in advance in ascending order of IC card number and so that records with the same zone number are adjacent.