問68
頻出度★★★
関係データベースの表を正規化することによって得られる効果として, 適切なものはどれか。
平成26年度 秋期 ITパスポート試験 問68
テクノロジ系データベース › データベース設計
This question's explanation and your answers so far.
解説
問 68
Incorrectア
Gathering frequently used data into the same table is the opposite direction from normalization. Normalization is the work of splitting tables according to functional dependencies; it is not a design that collects data into one table based on access frequency. On the contrary, deliberately allowing duplication and merging tables in order to prioritize performance is a separate technique called denormalization, which buys read performance in exchange for accepting the risk of update inconsistency.
- イ
正解 The correct answer is イ: “It eliminates data duplication, thereby preventing the occurrence of data inconsistency during updates.” Normalization of a relational database means taking a state in which one table mixes repeating items or several different kinds of facts, splitting it into multiple tables according to functional dependencies, and arranging things so that the same fact is recorded in only one place in the database. For example, if a single order-detail table holds the product name and unit price together with the order number and product code, then the product name and unit price are stored redundantly every time the same product appears. In this state, changing a product name requires updating every relevant row without omission, and forgetting even one row produces a contradiction in which the same product code has a different product name (an update anomaly). If you normalize and split out a product table, and let the detail side refer to it by product code, then changing the product name only requires fixing one row in the product table, so the contradiction cannot arise in the first place. In this way, making it easier to keep data consistent (maintaining data integrity) by eliminating duplication is the primary effect of normalization; as a side benefit, reducing unnecessary duplication also limits wasted storage capacity.
Incorrectウ
Normalization does not aim to make the sizes of tables uniform. The criterion for splitting is always the dependency relationships among data items, and the number of rows and the size of each resulting table vary greatly depending on the data (for example, a product table may have a few hundred rows while an order-detail table has several million). Moreover, the usage efficiency of main memory is a matter of the DBMS buffer management and the hardware configuration; it is not an effect that normalization, which is logical design, directly aims at.
Incorrectエ
As normalization proceeds, tables are split, so the number of tables increases rather than decreases. Furthermore, obtaining the original information from the split tables requires joins, so the response time for queries actually tends to become longer. Normalization is a design for protecting data consistency, not a means of shortening response time. When response performance becomes a problem, it is addressed by creating indexes or, where necessary, by denormalization.
ここがポイント
- Normalization is a logical design technique that splits tables based on functional dependencies so that the same fact is stored in only one place. Its aim is to prevent update anomalies (contradictions arising from updates, insertions, and deletions) by eliminating data duplication, that is, to ensure data consistency.
- Understand the trade-off of normalization. Because the number of tables grows and joins become necessary, read performance tends to drop, and a design that deliberately keeps duplication in order to prioritize performance is called denormalization. Statements such as merging tables, reducing the number of tables, or shortening response time belong to denormalization and performance tuning, not to the effects of normalization, so they are incorrect.
用語