問79
頻出度★★★
関係データベースのデータを正規化する目的として, 適切なものはどれか。
平成24年度 春期 ITパスポート試験 問79
テクノロジ系データベース › データベース設計
This question's explanation and your answers so far.
解説
問 79
Incorrectア
Improving the compression ratio is not the purpose of normalization. Reducing redundancy can have the side effect of making the stored volume smaller, but that is a different thing from “data compression,” which shrinks the amount of data by encoding it; moreover, because normalization splits tables apart, it also brings the side effect of a heavier processing load for joins. Normalization is ultimately a logical design for protecting the integrity of the data, not a technique aimed at reducing capacity.
- イ
正解 Normalization is a design technique that splits the tables of a relational database by focusing on functional dependencies (a relationship in which, once one item is fixed, another item is uniquely determined), thereby eliminating the situation where the same fact is stored redundantly in several places. For example, if an order table also holds the customer name and address, dozens of rows will exist for the same customer, and when the address changes you must correct every row or the table will end up with old and new addresses mixed together. If the customer information is separated out into a customer table and the order table refers to it by a customer code, the address only has to be corrected in one place, so no contradiction can arise. Eliminating redundancy in this way prevents update anomalies (missed updates, and inconsistencies on insertion or deletion) and keeps the data free of contradictions; that is the purpose of normalization, so イ, “Maintain the consistency of the data,” is appropriate.
Incorrectウ
Preventing leakage is handled by security measures such as setting access rights (limiting, for each user, which tables and columns can be read or updated), encryption, authentication, and log auditing, and has nothing to do with normalization. Even if the data is normalized, information leaks just the same when the privilege settings are lax. Splitting tables apart in itself has no effect of concealing information.
Incorrectエ
Enabling multiple users to handle the data at the same time is the role of DBMS functions such as locking, concurrency control of transactions, and commit and rollback. Normalization is about how the structure of the tables is designed, and it does not determine whether concurrent access is possible.
ここがポイント
- Normalization = logical design that splits tables based on functional dependencies and eliminates redundant storage of data. Its purpose is to prevent update anomalies (missed updates, and inconsistencies on insertion or deletion) and to maintain the consistency and integrity of the data.
- Keep straight the division of roles among confusable functions: reducing capacity is handled by data compression, preventing leakage by access-right settings and encryption, and control of concurrent access by locking and transaction management.
用語