Database Design Using Normalization Questions and Answers

Answer: B

Correct answer: Three rows have the values Brown Small Chair, Small Chair Brown, and Small Brown Chair in the same column.

All three rows describe the same thing but no two strings match, so grouping, counting and joining on the column all give wrong answers. It arises wherever free text is entered without a controlled vocabulary, and the remedy is to constrain the values - by a lookup table or a check constraint - rather than to clean them repeatedly.

Answer: A

Correct answer: advantage

This is the central benefit and the reason normalization exists. A relation carrying more than one theme suffers insertion, deletion and update anomalies: a fact cannot be recorded without an unrelated one, deleting a row destroys information, and a single change must be made in many places. Decomposing into well-formed relations removes all three.

Answer: A

Correct answer: eliminates

Properly normalized tables store each fact once. The repetition of non-key data across rows is exactly what the normal forms are defined to remove, and its absence is what makes update anomalies impossible. Foreign key values do repeat, but they are the links between relations rather than duplicated data in the sense meant here.

Answer: B

Correct answer: True

This is the multivalue problem, and it is a first normal form violation: a cell must hold a single value. Several telephone numbers or skills packed into one cell cannot be searched, sorted or counted reliably, and the remedy is to move the repeating values into a separate table with one row for each.

Answer: A

Correct answer: True

Modification anomalies are not a theoretical nicety: they cause facts to be lost on deletion, facts to be unrecordable until unrelated ones are known, and the same value to be updated inconsistently in different rows. Normalizing to BCNF removes all of them, and that is why BCNF rather than 3NF is the working target.

Answer: A

Correct answer: True

A referential integrity constraint requires that every value appearing in a foreign key already exist as a key value in the referenced relation, which is exactly what this phrase states. It is enforced by the database rather than by the application, and it is what prevents a sale from referring to a customer who does not exist.

Answer: B

Correct answer: disadvantage

Normalization spreads data across more tables, so a query that once read a single table now needs joins and often subqueries. That extra complexity is a real cost - harder to write, harder to read and slower to run - and it is one of the two standard disadvantages set against the elimination of modification anomalies. It is why denormalization is sometimes chosen deliberately.

Answer: B

Correct answer: True

A normalized design stores each fact in one place, which is what makes update anomalies impossible: there is no second copy to fall out of step. Foreign key values are repeated, but they are the mechanism by which relations are linked rather than duplicated data, and they are maintained by referential integrity rather than by hand.

Answer: A

Correct answer: read-only

Normalization exists to prevent modification anomalies, and a database that is never updated cannot suffer them. What remains is the cost: more tables, more joins, more complex and slower queries. So for a read-only database the balance tips the other way, and deliberately denormalized structures are the norm - which is exactly what a data warehouse star schema is.

Answer: A

Correct answer: True

Normalization distributes data across more tables, so retrieving what was once a single row now requires joins, and more complex conditions and subqueries besides. This is one of the two standard disadvantages set against the elimination of modification anomalies, and it is the reason denormalization is sometimes chosen for read-only data.