Database Design Using Normalization Questions and Answers

Answer: B

The schema in 3NF but not in BCNF is Schema II.

In Schema II, email → rollno means {email, courseid} is also a key, so rollno is a prime attribute. The dependency email → rollno has a determinant that is not a superkey, which breaks BCNF, but its right side is prime, which 3NF allows.

The other schemas fail 3NF or even earlier normal forms. Schema I has a set-valued attribute, so it is not even in 1NF. In Schema III, marks → grade makes the non-prime grade depend on the non-key marks, a transitive dependency that breaks 3NF. In Schema IV, courseid → credit makes the non-prime credit depend on part of the key, a partial dependency that breaks 2NF.

Answer: B

The weakest such normal form is 2NF.

In the old schema the key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE), but YEAR depends on only part of it, (VOLUME, NUMBER). A non-prime attribute depending on part of a candidate key is a partial dependency, which violates 2NF. So the old database is in 1NF but not 2NF.

The redesign moves YEAR into (VOLUME, NUMBER, YEAR), where (VOLUME, NUMBER) is the whole key, removing the partial dependency. Both new schemas are in 2NF (in fact in BCNF, since every determinant is a key).

1NF holds for both, so it is not a difference. 3NF and BCNF are also met by the new design, but the question asks for the weakest form the new design gains, which is 2NF.

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.