問57
頻出度★★★
次に示す項目を使って関係データベースで管理する“社員”表を設計する。他の項目から導出できる，冗長な項目はどれか。 社員
| 社員番号 | 社員名 | 生年月日 | 現在の満年齢 | 住所 | 趣味 |
|---|
令和2年度 ITパスポート試験 問57
テクノロジ系データベース › データベース設計
This question's explanation and your answers so far.
解説
問 57
Incorrectア
“Date of birth” is basic data that cannot be calculated from other items; rather, it is the source item from which age in years is derived. It is essential, not redundant, so this is wrong.
- イ
正解 The correct answer is イ, “Current age in years”. In relational database design, storing a value that can be calculated or derived from other items as duplicated actual data creates redundancy and easily causes inconsistency when updates happen. Age in years can always be uniquely computed from the difference between the “Date of birth” and the current date, so there is no need to keep it in the table; it can simply be computed when needed. Therefore, the redundant item that can be derived from other items is “Current age in years”. In addition, age in years is a time-varying value that changes every year as time passes, and if it is stored as actual data, an incorrect value can remain due to a missed update, which is another reason it is a typical example of what should not be stored.
Incorrectウ
“Address” is information unique to each employee and is independent data that cannot be derived from other items such as employee number or name. It is not redundant, so this is wrong.
Incorrectエ
“Hobby” also differs from person to person and is independent data that cannot be calculated or inferred from other items. It is not redundant, so this is wrong.
ここがポイント
- A derived item (a value that can be calculated from other items) should not be kept in the table as actual data but computed when needed; this is a basic principle of normalization and redundancy elimination. Keeping it in two places causes inconsistency at update time.
- For a value that changes over time such as age in years, the standard approach in database design is to store the baseline “Date of birth” and calculate it from the difference with the current date when it is referenced.
用語