ITPEC IP April 2018 Question 27

Data inconsistency is prevented by eliminating duplication — the purpose of normalisation.

Normalisation splits tables so that each fact is stored in only one place, according to the dependencies among the data items. When a customer's address, for example, is repeated in every order row, changing it in some rows but not others leaves the data contradictory (an update anomaly). After normalisation the address lives in one row of a customer table, so one update keeps everything consistent.

Answer (b)

Why not others:

• •(a) — gathering frequently used data into one table is denormalisation, the opposite, done for performance
• •(c) — normalisation is about logical structure; it does not make table sizes uniform or aim at memory efficiency
• •(d) — normalisation increases the number of tables, and queries then need more joins, so response time tends to grow

Key rule: Normalisation trades more tables and joins for storing each fact once, which prevents update, insertion and deletion anomalies.

Databases · AI-written, may contain errors