Database Design Using Normalization Questions and Answers

Answer: B

Correct answer: False

Normalization removes modification anomalies at the cost of more tables, more joins and more complex SQL, and where anomalies cannot arise the cost buys nothing. A read-only database is the standard case, and reporting and warehouse designs are deliberately denormalized. BCNF is the usual target for an updateable database, not a universal rule.

Answer: B

Correct answer: False

Multivalued dependency anomalies are not harmless. Two independent multivalued facts in one relation force every combination of them to be stored, so adding one value of the first requires a new row for every existing value of the second, and the redundancy grows as their product. Splitting the relation removes the problem entirely.

Answer: C

Correct answer: Three rows have the values Brown, NULL, and Blue in the same column.

The missing values problem is the presence of nulls, and the difficulty is that a null is ambiguous: it may mean the value is unknown, or that it does not apply, or that nobody has entered it yet. Since the three call for different treatment and the data does not distinguish them, every null has to be investigated rather than assumed.

Answer: B

Correct answer: False

The condition runs the other way: BCNF requires every determinant to be a candidate key. Candidate keys are always determinants in any relation, since identifying a row fixes all its attributes, so the statement as written is true of every relation and tests nothing. The requirement that bites is that no determinant may be anything other than a candidate key.

Answer: D

Correct answer: never

A read-only database is by definition never updated after it is loaded, which is what changes its design priorities: modification anomalies cannot occur, so normalization loses its purpose and denormalized structures may be preferred for query speed. Data is refreshed by reloading rather than by updating in place.

Answer: D

Correct answer: BCNF.

This is the definition of Boyce-Codd Normal Form: every determinant - every left-hand side of a functional dependency - must be a candidate key. A determinant that is not a key allows the same fact to be repeated across many rows, which is precisely what produces update anomalies. Note the direction: all determinants must be candidate keys, not the reverse.

Answer: B

Correct answer: second step.

Determining the primary keys belongs to the second step, along with the functional dependencies from which they follow and the foreign keys that depend on them. The row and column survey comes first, and testing whether the referential integrity constraints implied by those foreign keys actually hold comes third.

Answer: D

Correct answer: One row has the value "He is interested in a Silver Porsche from the years 1978-1988" in a column.

A general-purpose remarks column holds free text into which users put whatever they think worth recording, so the same column contains different kinds of fact in different rows. It cannot be queried reliably, cannot be validated, and the information in it - here a colour, a model and a year range - belongs in columns of its own.

Answer: A

Correct answer: True

First normal form is simply the definition of a relation restated: single-valued cells, homogeneous columns, no duplicate rows, no significance to order. So any table that qualifies as a relation is already in 1NF, and getting into it is a matter of eliminating repeating groups and multivalued cells rather than of examining dependencies.

Answer: B

Correct answer: The inconsistent values problem

Grouping on a column and counting the members of each group exposes values that should be the same but are not: variant spellings, different word orders and stray punctuation each form their own group. A quick look at the group list makes the inconsistency obvious in a way that scanning the rows would not.